Free tools / pg_dump command builder

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.

Backup

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

Restore into an empty database

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

Why these defaults

Choosing a pg_dump format. For a big database where you want a parallel dump and restore, use the directory format, -Fd with -j; it is a folder, so it cannot stream to S3. Otherwise, if you need to read or edit the SQL, use plain SQL, -Fp, loaded with psql and replayed whole. If not, use the custom format, -Fc: one file that pg_restore can restore table by table. A big database, and you want a parallel dump and restore? yes no Directory -Fd, parallel with -j a folder, so no S3 stream Do you need to read or edit the SQL? yes no Plain SQL -Fp, load with psql replayed whole Custom -Fc, one file restores one table
Custom is the builder’s default. Tar works like custom without compression.

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