Restore a PostgreSQL logical backup

1 · Prerequisites

Confirm every item is in place before any state change.

• The dump file, plus the three facts a plan needs about it: which database it came from, which cluster and major version wrote it, and which format it is in. pg_restore reads the custom and directory formats; a plain SQL script is replayed with psql and can be neither listed nor restored selectively.
• The business invariant recorded on the source before the incident — a row count and a sum over a money column, a ledger balance, an order total — and the timestamp it was taken at. Without one, the restore can only be judged by exit status, which is the failure this runbook exists to prevent.
• The incident timeline: when the damage was done, when it was noticed, and what has been written since. The recovery point is read off that timeline, never off the modification time of the newest file on the share.
• A target cluster whose major version is at least the source cluster's, with every extension the dump names already installed. A dump carries CREATE EXTENSION, not the extension's code.
• The globals — the output of pg_dumpall --globals-only, or a written list of the roles the dump grants to. Roles, role memberships and tablespace definitions are not in a database-level dump and exist nowhere else in it.
• Free space on the target measured with df, not assumed: the restored heap, its indexes, and the WAL the restore itself generates.
• A named person who owns the data and can accept the recovery point, because restoring this dump means accepting the loss of everything committed after it was taken.
• Read access to the damaged database if it still exists. It is the evidence and it is the fallback, and no step in this runbook writes to it.

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

• · Fix the incident timeline before you open the dump directory. Write down when the damage was done, when it was noticed, and what has been written since. Every decision below — which dump, beside or in place, whether the recovery point is acceptable — is read off that timeline, and it cannot be reconstructed after a restore has overwritten the evidence.
• · Build the dump inventory and read it, instead of reaching for the newest file. List the candidates with their modification times, then read each candidate's table of contents. The newest dump is the correct one only when the damage is newer than the dump, which is precisely the question the timeline has just answered.
• · Prove the candidate is a readable archive before planning around it. pg_restore -l against the file, with the exit status read on its own line rather than through a pipe. A file that is the right size on the right schedule and is not a readable archive is a discovery worth making now rather than forty minutes into the incident.
• · Read the recorded invariant and confirm it was computed on the source before the incident. An invariant computed after the damage, or computed from the restored copy, validates nothing whatsoever. If none exists, record one now from whatever survives and state plainly in the incident notes that this restore will be unvalidated.
• · Inspect the restore target and account for everything already in it. ABORT if you cannot. psql -l, then row counts over pg_stat_user_tables in the candidate target. A database holding data whose origin the responder cannot explain is not a restore target: loading a dump into it merges two datasets and destroys the evidence of both.
• · Check the target's major version and installed extensions against the dump's table of contents. SELECT version(); and SELECT extname FROM pg_extension; on the target, compared with the CREATE EXTENSION entries in the listing. A missing extension fails the restore late, after the schema and usually after the data.
• · Measure free space on the target volume, and compare it with the size of the source database if the source is still reachable. Running out of space part way through leaves a partly loaded database, which is the most awkward state this runbook has to roll back from.
• · Confirm who will accept the recovery point, by name. The interval between the dump and the damage is data that is not coming back. That is a business decision taken before the restore, not a fact discovered after it.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1. 1Select the recovery point against the dump inventory and the timeline, and write down which file you chose and why. The rule is the newest dump taken strictly before the damage — not simply the newest dump. A dump written after an unqualified DELETE contains the result of that DELETE, restores cleanly, exits 0, and hands the damage back to you.
2. 2Compute the recovery point you are about to pay for, and state it in wall-clock terms. It is the interval between the dump and the moment of damage, and everything committed inside it is gone. Say "we lose the orders taken between 02:15 and 09:41", not "the RPO is 26,160 seconds", because the first sentence is the one a data owner can accept or refuse.
3. 3Get that recovery point accepted by the person who owns the data, and record the acceptance. This is the last moment at which the answer can still be "no — go and look for a later dump, or for a WAL archive that covers the gap".
4. 4Dump the current state of the damaged database before changing anything, if it still exists. It is the only copy of the evidence and the only fallback if the restored copy turns out to be worse than what you have. Store it beside the incident notes, not on the share the restore reads from.
5. 5Copy the chosen dump to the target host and confirm it arrived intact. Compare size and checksum against the source copy. A truncated transfer produces a failure some way into the restore, with a half-loaded database behind it.
6. 6Restore the globals into the target cluster if the roles the dump grants to are absent. psql -f globals.sql. Errors about roles that already exist are benign; every other error is not. Skipping this step makes the restore fail at the first GRANT, which is late and expensive.
7. 7Create a NEW database as the restore target. That is the default and it requires no justification. createdb rbdr_shop_restored. The original stays untouched and available while you judge the copy, and the decision to swap is then made deliberately on evidence rather than implied by the restore command you happened to type.
8. 8**Restore into it with --exit-on-error, sending stderr to a file.** Without --exit-on-error, pg_restore continues past errors and produces a database that is missing objects, plus a log that nobody reads. With it, the restore stops where the first thing went wrong, which is where you want to be looking.
9. 9Read the error file, not only the exit status. An empty error file alongside a zero exit is the check. A zero exit with a populated error file, or a non-zero exit with an empty one, both mean something happened that the exit code did not describe.
10. 10Supply what the dump did not carry: ownership, role grants, and extension availability. Objects end up owned by the role that ran the restore, rather than by the role the dump named, whenever the ownership statements were suppressed with --no-owner or failed because the role did not exist. Verify ownership and the grants the application depends on by connecting as the application role and reading a table, because an application that cannot read its own tables has not been recovered.
11. 11Recompute the invariant on the restored database and compare it against the recorded value character by character. Not "about right", not "it looks populated". Write both to files — /tmp/rbdr-before.txt from the recorded value, /tmp/rbdr-after.txt from the restored database — and let diff decide, so the comparison has an exit status instead of an opinion.
12. 12DECISION POINT — restore beside and swap, or restore in place. Restore beside and swap when the original still exists, when other databases share the cluster, or when the cause of the damage is not yet understood: this is the default. Restore in place only when the original is already gone, the cluster holds nothing else that matters, and the step-4 dump exists; it saves the length of one rename and costs you the fallback.
13. 13If you are swapping, swap by renaming rather than by dropping. Stop the application, rename the damaged database to a name that says what it is, rename the restored database into its place, and start the application. The damaged copy stays on disk, and the swap is reversible with two more statements instead of another restore.
14. 14Validate through the application, not through psql. Confirm the invariant through the path the business actually uses — the report that shows the order total, the endpoint that returns the balance — with the service started and a real request served. "The database accepts connections" and "the service is correct" are different claims, and only the second one ends an incident.
15. 15Record the four numbers this restore produced: the dump chosen and its timestamp, the recovery point paid in wall-clock terms and who accepted it, the measured restore duration beside the dataset size, and the invariant comparison. The measured duration is the only honest input to a recovery time objective for this dataset, and it is captured here or nowhere.

4 · Verification

Confirm the procedure actually fixed the problem.

• ✓The invariant is identical, not close. psql -d rbdr_shop_restored -Atc "SELECT count(*), sum(amount) FROM rbdr_orders" > /tmp/rbdr-after.txt, then diff /tmp/rbdr-before.txt /tmp/rbdr-after.txt. Expected: diff prints nothing and exits 0. Any output at all is the finding, and it is compared with diff rather than by eye because "about right" is how a short restore gets signed off.
• ✓The exit status and the error file agree. echo ">>> pg_restore exit: $?" on its own line immediately after the restore, expected >>> pg_restore exit: 0; then wc -l < /tmp/rbdr-restore.err, expected 0. Neither one alone is the check, because the two disagree often enough to matter.
• ✓Every table the archive carries data for arrived. grep -c "TABLE DATA" /tmp/rbdr-toc.txt against psql -d rbdr_shop_restored -Atc "SELECT count(*) FROM pg_stat_user_tables". Expected: the same number from both, exit 0 from both. A restore that produced the schema and none of the rows exits 0 and is caught only here.
• ✓No sequence is behind the column it feeds. For each one, psql -d rbdr_shop_restored -Atc "SELECT last_value FROM rbdr_orders_id_seq" against SELECT max(id) FROM rbdr_orders. Expected: last_value at or above max(id), exit 0. A dump written by pg_dump normally carries the sequence position; this check is what catches the case where the data arrived by some other route, and its untested signature is a duplicate key error on the application's first insert.
• ✓Ownership and grants are correct from the application's side, not the superuser's. Connect as the application role — psql "service=rbdr_app" -Atc "SELECT count(*) FROM rbdr_orders" — expected: a row count and exit 0. A permission error here is the finding. Reading the catalogue as a superuser proves nothing, because a superuser reads everything regardless of the grants.
• ✓The application returns the invariant through its own read path. With the service started, curl -fsS "$RBDR_REPORT_URL" — expected: exit 0 and a body carrying the same total the invariant names. -f makes curl exit non-zero on an HTTP error status instead of printing the error page and exiting 0, which is the difference between checking and appearing to check.
• ✓The recovery point actually paid is in the incident record, in wall-clock terms, with the name of the person who accepted it. Absent that line, the restore has no accepted scope and the check fails.
• ✓The measured restore duration is recorded beside the dataset size and the date — /usr/bin/time -f "%e s" pg_restore ... or the wall-clock interval either side of the command. It is the input to the next estimate anybody makes for this database, and it is captured here or nowhere.

5 · Rollback

If verification fails, undo the procedure in reverse order.

• ↶A partly loaded database is not repaired, it is dropped and reloaded from the beginning. dropdb rbdr_shop_restored, then start again from the chosen dump. Loading the remainder of a dump on top of a failed restore produces duplicate key errors, orphaned rows, and a database whose state nobody can describe.
• ↶If the restore went beside the original — the default — there is nothing to undo on the source. Drop the restore target and the estate is exactly where it was, minus some disk space and some time.
• ↶If a dump was loaded into a database that already held data, stop and treat it as a second incident rather than as a step to continue from: evidence and fallback are now mixed together in one place. The way back is the pre-restore dump from step 4, restored beside.
• ↶If the swap has already happened and the restored copy turns out to be wrong, reverse the two renames. The damaged database is still on disk under its renamed identity precisely so that this costs two statements rather than another restore.
• ↶Remove every copy of the dump from every host it was copied to during the incident. A dump is a complete copy of the data carrying none of the database's access controls, and the scratch copy on a jump host reliably outlives the incident that created it.
• ↶Undo the changes made by reflex while the pressure was on: the alert silence, the paused backup schedule, the temporary superuser grant taken to get pg_restore moving, the connection limit set to zero to keep the application out. Each one is a change, and each one is invisible to the next shift.
• ↶If globals were restored into a shared non-production cluster, review the roles that appeared. pg_dumpall --globals-only carries every role in the source cluster, including ones with no business existing on the target.

6 · Escalation

When the runbook isn't enough, contact:

• · The restore target holds data the responder cannot account for: abort before running pg_restore and escalate. Merging two datasets is not recoverable by any step below it in this runbook.
• · No dump exists that predates the damage: escalate to the data owner and to whoever owns the WAL archive, immediately. Restoring the newest dump anyway, because it is the one that exists, restores the damage and spends the last decision point you had.
• · The invariant does not match, and the difference is not explained by the recovery point interval: escalate rather than accepting the restore. A restore that is quietly wrong costs more than one that visibly failed, because it ends the incident and is discovered weeks later.
• · The candidate dump cannot be listed by pg_restore -l: escalate to the owner of the backup job. This is a monitoring failure as much as a recovery one, and the other dumps in the inventory are now also in question.
• · The target cluster is missing an extension the dump names, or is at a lower major version than the source: escalate to the platform owner. Neither is fixable inside the restore window by the person running the restore.
• · The measured restore duration is going to exceed the recovery time objective: escalate to the service owner with the number while the restore is still running, not afterwards. Whether to wait, fail over, or serve degraded is their decision and it needs the number early.
• · You are about to request superuser access to get past an ownership or permission error: escalate instead. Restoring as a superuser to work around a missing role changes who owns every object in the database, and the application discovers that later, from a user.

References

1. PostgreSQL documentation, SQL Dump and restoring the dump
2. PostgreSQL documentation, pg_restore
3. PostgreSQL documentation, pg_dump
4. PostgreSQL documentation, pg_dumpall
5. PostgreSQL documentation, ALTER DATABASE