Backups

AxiomDB uses pgBackRest to provide continuous WAL archiving and point-in-time recovery (PITR). Backup schedules, restore plans, and restore operations are managed through the gateway API and the ops console.

Overview

AxiomDB integrates with pgBackRest for PostgreSQL backup management. The system supports:

Full and incremental backups
WAL archiving for point-in-time recovery (PITR)
Restore plans (what data would be recovered) before committing a restore
Per-project backup policies

Backup jobs are executed via square-dbctl on the VPS and tracked in the provisioning_jobs table with action restore_plan or restore.

Listing backups

GET /api/v1/projects/:project_id/backups
Authorization: Bearer <paseto-v4-token>

Response:

{
  "project_id": "...",
  "backups": [
    {
      "label": "20250702-120000F",
      "type": "full",
      "start_time": "2025-07-02T12:00:00Z",
      "stop_time": "2025-07-02T12:04:32Z",
      "size_bytes": 1073741824,
      "database_size_bytes": 1006632960,
      "wal_included": true
    },
    {
      "label": "20250703-000000I",
      "type": "incr",
      "start_time": "2025-07-03T00:00:00Z",
      "stop_time": "2025-07-03T00:01:15Z",
      "size_bytes": 20971520,
      "database_size_bytes": 1006632960,
      "wal_included": true
    }
  ],
  "pitr_available": true,
  "oldest_restore_point": "2025-07-02T12:00:00Z",
  "latest_restore_point": "2025-07-03T06:30:00Z"
}

Getting restore options

Generate a preview of what a restore would recover before committing:

POST /api/v1/projects/:project_id/backups/restore-plan
Authorization: Bearer <paseto-v4-token>
Content-Type: application/json

{
  "target": "2025-07-03T02:00:00Z",
  "target_type": "time"
}

Field	Values	Description
`target`	ISO timestamp or backup label	Point in time or specific backup
`target_type`	`"time"` or `"label"`	How to interpret the `target` field

Response (202 Accepted — async job):

{
  "job_id": "...",
  "status": "queued"
}

Poll GET /api/v1/jobs/:job_id until status = "succeeded". The job's output.plan contains:

{
  "backup_label": "20250702-120000F",
  "backup_type": "full",
  "pitr_target": "2025-07-03T02:00:00Z",
  "size_to_restore_bytes": 1073741824,
  "estimated_duration_seconds": 120,
  "database": "sq_servers_prod",
  "wal_segments_needed": 8
}

Performing a restore

Data loss warning. A restore overwrites the target database with the backup snapshot. All changes made after the restore point are permanently lost. There is no undo. Requires owner or admin role.

POST /api/v1/projects/:project_id/backups/restore
Authorization: Bearer <paseto-v4-token>
Content-Type: application/json

{
  "target": "2025-07-03T02:00:00Z",
  "target_type": "time",
  "confirm": "restore sq_servers_prod"
}

The confirm field must exactly match "restore {database_name}". This prevents accidental restores.

Response (202 Accepted):

{
  "job_id": "...",
  "status": "queued"
}

The restore job:

Takes a final backup snapshot of the current state (for rollback)
Stops active connections
Runs pgbackrest restore --target ...
Brings PostgreSQL back online
Validates connectivity
Updates the job status to succeeded

Backup policy

Configure how often backups run and how many are retained:

GET /api/v1/projects/:project_id/backups/policy
Authorization: Bearer <paseto-v4-token>

PUT /api/v1/projects/:project_id/backups/policy
Authorization: Bearer <paseto-v4-token>
Content-Type: application/json

{
  "full_backup_schedule": "0 2 * * 0",
  "incremental_schedule": "0 2 * * 1-6",
  "retention_full": 2,
  "retention_diff": 7
}

Field	Default	Description
`full_backup_schedule`	Weekly Sunday 2AM	cron expression for full backups
`incremental_schedule`	Daily 2AM	cron expression for incremental backups
`retention_full`	2	Number of full backups to retain
`retention_diff`	7	Days of incremental backups to retain

pgBackRest configuration

AxiomDB installs and configures pgBackRest on the VPS via square-dbctl. The configuration file is generated at /etc/pgbackrest/pgbackrest.conf.

Key configuration:

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
start-fast=y
stop-auto=y
compress-type=lz4

[main]
pg1-path=/var/lib/postgresql/14/main

WAL archiving is enabled via postgresql.conf:

archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

Backup operations in the ops console

The Backups page in the ops console provides:

Feature	Description
Backup list	Sortable table of full and incremental backups with size and duration
PITR timeline	Visual timeline showing the oldest and latest restore points
Restore plan	Generate a dry-run plan before committing
Restore wizard	Step-by-step restore with confirmation, progress tracking, and job polling
Policy editor	Configure backup schedules and retention

The ops console polls GET /api/v1/jobs/:job_id every 3 seconds during an active restore and updates the progress bar in real time.