pgctl

Postgres backup, restore, and — above all — verification, for databases in S3-compatible object storage.

A backup nobody has restored is a rumour. pgctl turns it into a fact, on a schedule: backup verify restores your newest backup into a throwaway database, boots it, proves it holds real data, and tears it down. If that ever fails, it fails loudly — which is the whole point.

Built on barman's barman-cloud-* tools. pgctl orchestrates them and adds the guards that stop a broken backup from passing as a good one.

Configure

Targets live in a YAML file, resolved from --config, else $PGCTL_CONFIG, else ./pgctl.yaml. Copy example.yaml and edit it:

targets:
  production:
    host: db.example.com          # ssh'd to, unless --local
    container: example-postgres   # your live postgres container on that host
    image: ghcr.io/example/postgres-barman:18
    db: example
    role: example
    server: production            # the barman server name
    bucket: s3://example-backups/api
    endpoint: https://s3.example.com
    retention_days: 30
    smoke_sql: SELECT count(*) FROM public.users

pgctl holds no credentials: it reads the object-storage credentials out of your running postgres container's own environment.

Use

pgctl backup list   --env production   # what backups exist
pgctl backup run    --env production   # take a base backup, then prune
pgctl backup verify --env production   # THE DRILL: restore, boot, smoke, tear down
pgctl restore       --env production --to '2026-07-12 03:00:00'

Two modes

Over ssh (default). For a database that only listens on its own host: pgctl ssh's in, then docker execs into the postgres container — which already holds the credentials barman needs. Set ssh_user per target, or pass --ssh-user.

Local (--local). pgctl runs alongside barman and postgres, inside the recovery image. This is the mode for the day the host you would have ssh'd into is the thing you are recovering from. All it needs is the bucket and the credentials.

Recovering

Everything needed is in one container — pgctl, barman-cloud-*, and postgres, pinned together. No host to ssh into, nothing to install.

# Restore to a point in time, into ./recovered on this machine.
docker run --rm -it \
  -e AWS_ACCESS_KEY_ID -e AWS_SECRET_ACCESS_KEY -e AWS_DEFAULT_REGION=<region> \
  -v "$PWD/pgctl.yaml:/pgctl.yaml:ro" \
  -v "$PWD/recovered:/var/lib/postgresql" \
  ghcr.io/metsaapp/pgctl:latest \
  restore --local --config /pgctl.yaml --env production --to '2026-07-12 03:00:00'

A point-in-time restore pauses at the target instead of promoting past it. If you picked the wrong second, that costs you a retry rather than the recovery. Promotion is a decision, and it should be yours.

The drill

backup verify restores the newest backup into a throwaway container on the same host, boots it, proves it holds real data, and tears it down. It never touches the live database or the live volume.

sequenceDiagram
    autonumber
    participant C as pgctl
    participant H as database host
    participant S3 as object storage
    participant D as throwaway container

    C->>H: live container running? (1)
    C->>S3: list backups
    S3-->>C: backups
    Note over C: keep only DONE, newest,<br/>and not older than retention (2)
    C->>H: df: room for 2x the backup? (3)
    C->>H: copy credentials out of the live container (0600) (4)
    C->>S3: restore base backup
    S3-->>H: base backup -> scratch PGDATA
    C->>H: test -s PGDATA/PG_VERSION (5)
    Note over C,H: THE guard. Empty here and postgres<br/>would initdb a fresh, healthy,<br/>EMPTY database that passes every check.
    C->>H: write postgresql.auto.conf + recovery.signal
    C->>D: docker run (no published port)
    D->>S3: replay WAL
    C->>D: poll: still running? (6) recovery complete? (7)
    Note over C,D: pg_isready succeeds DURING recovery,<br/>so readiness alone proves nothing.
    C->>D: pg_is_in_recovery() = f, marker in logs (8)
    C->>D: smoke query (9)
    D-->>C: row count
    C->>D: remove container + scratch (always) (10)

Loading

The premise is that a drill which passes on a broken backup is worse than no drill: it converts an unknown into a false certainty. Every step is a guard, each here because there is a specific way the drill can come up green while the backup is worthless. They fail closed — anything that cannot be verified is a failure, never an assumption.

#	Failure it catches	Guard
1	The live container is down, so its env and its barman config cannot be read.	Assert the live container is running before anything else.
2	The newest backup is FAILED or half-written; the backup list includes those too.	Keep only status == DONE, take the newest.
2	Everything passes, but the backup is 40 days old because backups broke a month ago.	Reject a backup older than the retention window — a test of current backups, not just of barman.
3	The restore fills the host's disk — a production outage, and postgres stops archiving WAL when it can't write.	df the filesystem the scratch dir lands on; require 2x the backup's size, since it must land and expand.
4	The throwaway can't reach object storage to replay WAL, because the credentials were incomplete.	Copy the credentials into a 0600 file; require all three, or refuse.
5	The restore silently produces an empty data directory; postgres then initdbs a pristine empty database that answers every health check.	After the restore, assert test -s $PGDATA/PG_VERSION. Never boot otherwise.
6	The container exited seconds after booting, and polling to the timeout turns a 5-second failure into a 10-minute one.	Check it's still running on every poll; fail immediately with its last output.
7	The container is up but recovery never finished — pg_isready succeeds during recovery.	Poll for postgres's own archive recovery complete marker, not for readiness.
8	Replay stopped short and nobody noticed.	Assert pg_is_in_recovery() is f and the marker is in the logs — after the wait, where they can't race.
9	The smoke query returns empty because psql errored, and empty reads as zero.	SQL only via stdin to psql -f -; ON_ERROR_STOP=1; check the exit code; parse the output as an integer. Empty is a parse error, not a zero.
10	A killed run leaks a whole restored database onto the host's disk, every run, on a schedule.	Teardown in a defer, registered before anything is created. It sweeps every stale scratch dir, not just this run's.

Each guard is specified, with the failure it exists to catch, in openspec/specs/restore-drill/spec.md.

Develop

go test ./...            # no docker, no network
golangci-lint run ./...

The ssh hop, Docker, object storage, and barman are not mocked. The drill is the integration test; run it and it exercises all four for real.

Behaviour is specified in openspec/specs/, one file per capability. Changes start with /opsx:propose, not a direct edit — see AGENTS.md.