Backups and restore

In plain English

pg_dump is a command-line tool that reads a database and writes its schema and data to a file, giving you a snapshot you can restore later. pg_restore is the tool that reads that dump file and rebuilds the database from it. A custom format dump is a compressed, flexible file that pg_restore can read selectively, unlike a plain text file of SQL statements. Testing a restore means actually loading a backup into a fresh database to prove it works, because a backup you cannot restore is worthless. Retention is your policy for how many old backups to keep and for how long, so you balance safety against storage cost.

Step by step

1. 1Run pg_dump to write the database to a file in the custom format.
2. 2Create a fresh, empty database for the restore test.
3. 3Run pg_restore to load the dump into that test database.
4. 4Query the test database to confirm the rows came back.
5. 5Set a retention policy for how many dumps to keep and for how long.

pg_dump --format=custom --file=myapp.dump myapp

createdb myapp_restore_test
pg_restore --dbname=myapp_restore_test myapp.dump

pg_dump --schema-only --file=schema.sql myapp

Common mistakes

• Storing backups on the same disk as the database means one disk failure can destroy both at once.
• Skipping the restore test leaves you trusting a file you have never proven works.
• Keeping every backup forever wastes storage, so define how long each one is kept.
• Assuming a plain text SQL dump is as flexible as a custom format dump is wrong, because pg_restore can act selectively only on the custom format.