Back up and restore PostgreSQL

A backup you have never restored is a hope, not a backup. The file can be incomplete, the schema can depend on roles that no longer exist, the application can choke on what comes back. So this lesson does both halves: dump, then prove the dump works.

Create a custom-format archive:

pg_dump --format=custom --file=notes.dump notes_app

pg_dump takes a consistent snapshot of one database while it stays online; readers and writers keep working during the dump. The custom format is compressed and, unlike a plain SQL file, lets pg_restore restore selectively: one table, schema only, data only.

Peek inside the archive without touching any database:

pg_restore --list notes.dump

A table of contents listing your schemas, tables, and indexes tells you the file is a readable archive. It does not yet prove the data restores.

Restore into a clean database

Restore it into a clean database, never over the source:

createdb --template=template0 notes_restore
pg_restore --exit-on-error --no-owner \
  --dbname=notes_restore notes.dump

template0 gives you a genuinely empty database. --exit-on-error stops at the first failure instead of plowing through and leaving a half-restored database that looks fine. --no-owner skips restoring object ownership, which otherwise fails with role "notes_owner" does not exist when the target cluster lacks the original roles; everything becomes owned by the connecting role instead.

Verify the restored data:

psql notes_restore -c 'SELECT count(*) FROM app.notes'

Compare the count against the source. Better still, point a test instance of your application at notes_restore and click around. A database can restore perfectly and still not serve the application, for example when the dump predates a migration the code now requires.

What pg_dump does not cover

pg_dump backs up one database. Roles and other cluster-global objects are outside it. Back them up separately when you operate the cluster:

pg_dumpall --globals-only > globals.sql

Inspect and protect that file. It contains role definitions and can contain password hashes, so it deserves the same secrecy as any credential.

A successful dump job is not enough: restore it and run the application against the result. Put that restore drill on a schedule, because the failure mode of backups is silent, and you want to find it on a quiet afternoon rather than during the incident.