Scheduled PostgreSQL backup to restic

Sard saves a PostgreSQL database as a pg_dump logical dump (custom format), streamed straight into a restic repository. The dump never lands on the host disk, and the data never passes through the Sard server.

What is saved

One database: pg_dump in custom format, streamed into restic with no temporary file.
The cluster’s roles and other global objects (pg_dumpall --globals-only) as a second snapshot linked to the first.
On a cron schedule in a chosen time zone, or on demand from the console.

What the host needs

The plugin has no PostgreSQL driver: it checks the connection, version and privileges with psql and dumps with pg_dump. Both come from the agent host’s postgresql-client package.

pg_dump must not be older than the server, and the minimum server version is PostgreSQL 14. The agent signs in as an ordinary role, not a superuser:

CREATE ROLE backup LOGIN PASSWORD '...';
GRANT pg_read_all_data TO backup;

The password never reaches the server

The password is not written to the source config. The config names only a secret, and the value sits in a 0600 file on the agent host. The agent hands the password only to the host and database the host’s operator allowed, whatever the server sends.

printf '%s' 'password of the backup role' | sudo sard-agent secret set pg-app \
  --plugin postgresql --target db1.example.com:5432/app --stdin

Restore

The console restores a database into a new place. Restoring over an existing database is done by hand for now, following the documentation.

Restore verification

A verifier on a separate machine runs the official postgres container of the same major version as the source, with no network and with limits, runs pg_restore and compares the copy’s fingerprint with the source’s. Docker or Podman is required.

Not yet

Clusters and replicas, WAL, pg_basebackup and point-in-time recovery (PITR).