Restore Postgres
From artifact to running database.
View as MarkdownA Postgres artifact is a pg_dump file. Restoring it is pg_restore, and everything before
that is unwrapping.
The short path
sctl restore postgres <artifact-id> --host localhost --database scratch --user postgresThe target is described with flags, not a connection URI, using the same field names the
postgres source type uses. --database is the only required one; the rest default the way
psql would. The CLI downloads, decrypts with your key, decompresses, detects whether the
dump is custom-format or plain SQL, and runs pg_restore or psql accordingly. All of it on
your machine.
The long path, without our CLI
Four commands, none of them ours.
# 1. Get the bytes
sctl artifact download <artifact-id> --output ./artifact
# or, if you deliver to your own bucket:
# aws s3 cp s3://my-bucket/<ws>/<backup>/<run>/artifact ./artifact
# 2. Decrypt, if it is encrypted
gpg --decrypt ./artifact > ./app.dump.gz
# 3. Decompress, if it is compressed. Check before you assume either.
file ./app.dump.gz # "gzip compressed data" means this step is needed
gunzip ./app.dump.gz
# 4. Restore
createdb scratch
pg_restore --dbname scratch ./app.dumpDecrypting is not decompressing. Compression is applied before encryption, so a backup
with both leaves you holding a gzip file after gpg. pg_restore given that file reports a
corrupt or unrecognised archive, which reads like a damaged backup and is not one. Run
file between the two steps.
Custom format or plain SQL
Our postgres source always produces custom format (pg_dump -Fc). A script source
running pg_dump might produce either, so check rather than assume.
head -c 5 ./app.dump # "PGDMP" means custom format| Format | Restore with | Notes |
|---|---|---|
Custom (PGDMP) | pg_restore | Selective restore, parallelism, reordering |
| Plain SQL | psql -f | Executed top to bottom, no selection |
Restore into a scratch database first
Always. Not as a best practice, as the default.
createdb scratch
pg_restore --dbname scratch --no-owner --no-privileges ./app.dump
psql -d scratch -c '\dt'
psql -d scratch -c 'SELECT count(*) FROM users;'A restore into the live database is destructive and irreversible, and it is the wrong first move even during an incident. Restore to scratch, confirm the data is what you expect, then decide how to promote it.
pg_restore --clean drops objects before recreating them. Run it against the wrong
--dbname and you have destroyed a working database with a backup command. Type the target
twice before you press enter.
Useful flags
| Flag | Use |
|---|---|
--no-owner | Do not restore ownership. Needed when the original roles do not exist |
--no-privileges | Skip GRANT/REVOKE |
--jobs=4 | Restore in parallel. Custom format only, and much faster on big dumps |
--single-transaction | All or nothing. The database is untouched if anything fails |
--schema-only / --data-only | Restore one half |
--table=users | One table |
--list | Print the table of contents without restoring anything |
--list is the one to reach for first when you are not sure what is in the file:
pg_restore --list ./app.dump | head -40Restoring one table
pg_restore --dbname scratch --table=users --data-only ./app.dumpThis is the everyday case: somebody deleted rows, and you need yesterday's copy of one table rather than yesterday's copy of the database. Restore it into scratch and copy the rows across with SQL, rather than restoring over the live table.
Roles are not in the dump
Roles and users are cluster-wide, so pg_dump of one database does not include them. A
restore into a fresh cluster fails with role "app" does not exist unless you either create
the roles first or restore with --no-owner --no-privileges.
If you need roles backed up too, that is a
script source running pg_dumpall --roles-only, as a
second backup:
pg_dumpall --roles-only > "$SAVED_OUTPUT"Version skew
| Direction | Works |
|---|---|
| Dump from an older server → newer server | Yes. This is the supported upgrade path |
| Dump from a newer server → older server | No. Expect syntax errors partway through |
Use a pg_restore at least as new as the server that produced the dump. If the restore fails
with unexpected syntax errors partway through, version skew is the first thing to check, not
corruption.
pg_restore --version
psql -d postgres -c 'SHOW server_version;'The schema-drift trap
This is the failure that rehearsal exists to catch, and the one that bites hardest in an incident.
A dump restores the schema as it was. Your application expects the schema as it is. Restore a backup from before three migrations and the database is internally consistent and still wrong for the code running against it.
Two situations, two different answers:
| Situation | What to do |
|---|---|
| Restoring the whole database after a disaster | Restore, then run migrations forward to current |
| Recovering lost rows from an old backup | Restore to scratch, extract just the rows, insert into live |
The second is much more common, and restoring the whole database over a live one to recover one table is how a small incident becomes a large one.
Keep your migrations in version control and runnable from any historical schema state. A backup you can restore but not migrate forward is only half a recovery plan, and the missing half is not ours to supply.
Verifying the restore
Restoring without errors is not the same as restoring correctly.
# Row counts against what you expect
psql -d scratch -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 20;"
# The most recent row, against when the backup ran
psql -d scratch -c "SELECT max(created_at) FROM orders;"That second query is the important one. It tells you the actual recovery point, which is when the dump started rather than when the run finished, and it is the number you report to everyone else during an incident.