PostgreSQL

backupd backs up PostgreSQL 9.2 through 18 with pg_dump in its custom format, from a single consistent snapshot. It works with managed Postgres services as long as the database is reachable from the internet over TLS.

Connection stringCopy Anchor Link

postgresql://user:password@db.example.com:5432/app?sslmode=require

• The scheme is postgresql:// or postgres://.
• The port defaults to 5432.
• The path is the database to back up. Without one, backupd uses the database named after the user, then postgres.
• Percent-encode special characters in the user or password, for example @ as %40.

TLSCopy Anchor Link

backupd always connects with TLS.

PermissionsCopy Anchor Link

A read-only user is enough. It needs to read every table, sequence and schema you want in the backup. On PostgreSQL 14 and later the simplest option is the built-in pg_read_all_data role:

CREATE ROLE backupd LOGIN PASSWORD 'choose-a-strong-password';
GRANT pg_read_all_data TO backupd;

On older versions, grant CONNECT on the database, USAGE on each schema, and SELECT on all tables and sequences in those schemas.

To restore from the app, the target needs a user that can create and replace the objects in the backup.

What is backed upCopy Anchor Link

• One database per connection: every schema, table, view, function, sequence and its data, as pg_dump sees it.
• Cluster-wide objects such as roles and tablespaces are not part of a pg_dump backup.
• On a primary, backupd opens a read-only transaction and exports its snapshot, so the dump and the row counts see the same moment.

Files are named with the .pgdump.zst.age extension: a pg_dump custom-format archive, compressed with zstd and encrypted with age.

Row counts and restore testsCopy Anchor Link

backupd counts each table's rows while it backs up, and the restore test compares them with what comes back. See Integrity checks and restore tests.

• Counts are exact when the database has up to 25 million rows in total and a snapshot can be exported, which needs a primary.
• On a standby, or above 25 million rows, counts are estimates and those tables are not compared.
• Counts cover the 1,000 largest tables.

RestoringCopy Anchor Link

In-app restores use pg_restore without owners or privileges, in a single transaction: if anything fails, the target is left as it was. backupd uses pg_dump 18, which can dump every server from 9.2 to 18. Restores into servers older than 17 are handled automatically.

To restore yourself, see Restore without backupd.

LimitationsCopy Anchor Link

• No point-in-time recovery or incremental backups. Every backup is a full copy.
• No private networks or SSH tunnels.
• A dump that sends no data for 5 minutes is stopped, so a long lock doesn't keep a snapshot open on your database.