pg_dump command builder

Pick a format and where the backup goes, and copy the pg_dump command together with the pg_restore or psql command that loads its output. The pg_dump, pg_restore and psql lines for each format and compression setting were run against PostgreSQL 18 and restored to the same row count.

pg_dump --format=custom "$DATABASE_URL" --file=app.dump

pg_restore --exit-on-error --single-transaction --dbname "$TARGET_URL" app.dump

Why these defaults

• Custom format. One compressed file that pg_restore can list, filter and restore table by table. Plain SQL can only be replayed whole.
• The password stays out of the command. The connection URL comes from an environment variable, so it never shows up in ps or in shell history.
• The restore stops on the first error. --exit-on-error and ON_ERROR_STOP make a failed restore fail, and --single-transaction leaves nothing behind when it does. A restore that carries on past errors and reports success is the failure you are testing for.
• set -o pipefail on every pipe. Without it, a pg_dump that dies halfway still ends in a successful upload of half a backup.
• Directory format for big databases. It is the only format that dumps and restores tables in parallel, but it is a directory, so it cannot stream into S3.

Use a pg_dump at least as new as the server, and restore into the same major version or a newer one. To check a dump you already have, use the backup file checker. For the bucket side, with a lock that stops anyone deleting the file, follow How to back up PostgreSQL to S3 with pg_dump.

SafeGrd runs this for you: it backs up on a schedule, encrypts and locks each backup, and restores it to count every row (PostgreSQL backup).

Run your first Fire Drill More free tools