Skip to content

Back up and restore PostgreSQL

PostgreSQL holds the control-plane metadata: workspaces, leases, snapshots, mount tickets, and audit events (spec §8.7). The object store holds the immutable content-addressed bytes (spec §3.2). This runbook backs up the metadata database. Back up the object store separately for the content. See Handle object-store corruption.

Spec reference

This runbook follows spec §25.5 (PostgreSQL failover and restore runbook) and spec §17.6 (database failure). The schema is spec §8.7. Migrations MUST be backward-compatible during rolling deploys (spec §21.8).

AWC ships no backup tool

AWC does not include a backup or restore utility. You run PostgreSQL's own tooling: pg_dump, pg_basebackup, or a managed equivalent. The spec requires automated failover and point-in-time recovery (PITR) in production (spec §17.6).

Detect

Run this runbook on any of these triggers:

  • Your scheduled backup cadence. The spec requires PITR in production, so the WAL archive runs continuously (spec §17.6). Take a new base backup on a fixed schedule so the WAL archive stays replayable in a reasonable time.
  • Before an upgrade or destructive maintenance on the database host. See Upgrade a deployment.
  • After a database failure. The restore path is part of Recover from a PostgreSQL failover.

Procedure

Back up the database

  1. Take a consistent backup. Choose one of the two methods below.

    A logical dump is one transactional snapshot of the schema and data. It is consistent as of the moment pg_dump starts. It does not give PITR.

    # One consistent snapshot of the metadata database (spec §8.7).
pg_dump --format=custom --no-owner --no-privileges \
  "$DATABASE_URL" \
  -f "awc-$(date -u +%Y%m%dT%H%M%SZ).dump"

    Use this for a nightly copy, a restore drill, or a move between PostgreSQL major versions.

    A physical backup plus a WAL archive gives PITR. This is the method the spec requires for production (spec §17.6).

    # Base backup from the primary or a streaming replica.
pg_basebackup -D /var/backups/awc/base -Xs -P -c fast \
  -h "$PGHOST" -U repl

    Keep a continuous WAL archive. In postgresql.conf:

    archive_mode = on
archive_command = 'aws s3 cp --quiet %p s3://awc-wal-archive/%f'

  2. Copy the backup off the database host. Store it in a separate failure domain: another host, another rack, or object storage. Never leave the only copy on the database host. A backup that dies with the primary is not a backup.

Restore to a target instance

  1. Stop the AWC services that connect to the database. Stop awc-control and awc-worker so they do not reconnect mid-restore. awc-control reads DATABASE_URL at startup and refuses to run without it.

  2. Restore the backup to a fresh target instance. Use the method that matches your backup.

    Restore into an empty database. Do not restore over a live database.

    createdb -T template0 awc_restored
pg_restore --dbname awc_restored --no-owner --no-privileges \
  awc-YYYYMMDDTHHMMSSZ.dump

    If you must reload an existing database, add --clean --if-exists so pg_restore drops each object before it recreates it.

    Replay WAL to a target time, then promote.

    # 1. Stop the target PostgreSQL. Clear its data directory.
# 2. Copy the base backup into the data directory.
# 3. Start PostgreSQL. It replays the WAL archive to the target, then
#    promotes.

    Set the recovery target in postgresql.auto.conf:

    restore_command = 'aws s3 cp --quiet s3://awc-wal-archive/%f %p'
recovery_target_time = '2026-08-12 14:30:00+00'
recovery_target_action = 'promote'

    Place an empty recovery.signal file in the data directory. Do not create standby.signal for a restore you intend to promote. Do not run AWC against a primary that is still replaying WAL (spec §25.5 step 2).

  3. Point AWC at the new primary. Set DATABASE_URL to the target connection string. Restart awc-control, then awc-worker.

  4. Confirm migrations applied. Both awc-control and awc-worker run every pending migration on startup through the embedded MIGRATOR. sqlx records each applied migration in the _sqlx_migrations table. The set is forward-only. Never revert a migration; a faulty migration is fixed by a forward corrective migration (spec §21.8).

  5. Reconcile metadata against the object store. After the restore, compare the five records the spec requires: published snapshot records, workspace versions, audit events, object-store snapshot manifests, and host-reported mount sessions (spec §17.6). See the Verify steps below.

Verify

Confirm the restore when all of these hold:

  • _sqlx_migrations shows the full set applied, with no new failures, and it matches your other environments.

    SELECT version, success FROM _sqlx_migrations ORDER BY version;

  • Row counts match the source for the metadata tables.

    SELECT COUNT(*) AS workspaces     FROM workspaces;
SELECT COUNT(*) AS snapshots      FROM snapshots;
SELECT COUNT(*) AS leases         FROM workspace_leases;
SELECT COUNT(*) AS mount_sessions FROM mount_sessions;
SELECT COUNT(*) AS audit_events   FROM audit_events;
SELECT COUNT(*) AS idempotency    FROM idempotency_keys;

  • No workspace points at a missing snapshot. This count MUST be zero.

    SELECT COUNT(*) AS dangling_heads
FROM workspaces w
LEFT JOIN snapshots s ON s.id = w.head_snapshot_id
WHERE s.id IS NULL;

  • Every snapshots.manifest_digest resolves to an object in the object store. The object store is the source of truth for content (spec §3.2). A missing manifest is an object-store problem; see Handle object-store corruption.

  • An idempotency replay returns the committed result (spec §17.2). A replay with the same key that returns a different result signals divergence.
  • awc-control starts and logs both listeners: REST on AWC_CONTROL_LISTEN and gRPC on AWC_CONTROL_GRPC_LISTEN.
  • awc_lease_renewal_failures_total and awc_api_errors_total return to baseline.

Impact

State the data risk plainly:

  • A logical backup is consistent as of the moment it started. Every metadata mutation after that is absent from the dump. PITR closes that gap.
  • A point-in-time restore loses every metadata mutation after the target time. That includes workspace head advances, leases, mount tickets, and audit events (spec §17.6). AWC cannot rebuild those rows.
  • The system never reconstructs a head from the object store. Orphan snapshot objects after a restore are immutable candidates, not inferred publications (spec §17.6). Leave them for garbage collection (GC).
  • This procedure does not back up content. The object store holds the immutable bytes (spec §3.2, §8.8). Back it up under its own policy. A restored database that points at missing objects is a degraded snapshot, not a recovered one.
  • Host writes are untouched by a database restore. Warm reads and local writes continue without the control plane (spec §17.5). See Recover from host loss if a host was also lost.

Current scope

The build consumes PostgreSQL but does not manage it. Be aware of these gaps:

  • AWC ships no backup, restore, or failover tooling. Provision PITR and failover in your Postgres layer, for example with Patroni or a managed Postgres with a WAL archive. AWC consumes the primary through DATABASE_URL only (spec §17.6).
  • Migrations run automatically on startup. Both awc-control and awc-worker apply pending migrations through MIGRATOR. There is no separate migrate subcommand and no dry run. The set is forward-only and additive.
  • The connection pool is fixed at 16. awc-control and awc-worker each open at most 16 PostgreSQL connections. Size the new primary's connection budget for both, plus your job concurrency.
  • No database-specific metric exists. There is no awc_db or migration gauge in awc-telemetry. Confirm a restore from the verification queries above and from awc_lease_renewal_failures_total.

When the restore is part of a wider database failure, follow Recover from a PostgreSQL failover. When the object store itself is the failed dependency, follow Recover from an object-store outage.