What is it? #
Restoring is the half of backup that actually matters, and it is the half that gets practised least.
Which tool you use depends on the format of the dump. A plain SQL dump is replayed with psql, because it is just a file of SQL statements. A custom or directory format dump needs pg_restore, which can read its table of contents, restore selectively and work in parallel.
The safest restore is always into a fresh, empty database. Restoring over an existing database is where accidents happen, because the options that make it work are the options that delete things.
And a restore is not complete when the command finishes. It is complete when you have verified the data and run ANALYZE — the step most often forgotten, and the one that makes a freshly restored database feel inexplicably slow.
Think of it like this #
Rebuilding a room from your inventory list.
The safe way is to build it in an empty room. Everything from the list goes in, and if something is wrong you simply clear the empty room and try again.
The risky way is rebuilding on top of a room that already has furniture in it. To make the list fit, you first have to throw out whatever is already there — and if you picked the wrong list, or the wrong room, the throwing out has already happened.
Either way, the job is not done until you have walked in and checked the furniture is actually there.
Simple example #
A bad migration has corrupted the orders table. Last night's dump is good.
You do not want to restore the whole database — the other tables have a day of legitimate new data in them. You want that one table back, from that one dump, without touching anything else.
Code #
# ---------- Which tool? It depends on the dump format ----------
file shop.dump
# "PostgreSQL custom database dump" -> use pg_restore
# "ASCII text" -> plain SQL, use psql
# Inspect a custom-format dump without restoring anything:
pg_restore --list shop.dump | head -30# ---------- Plain SQL dump: replay it with psql ----------
createdb -U postgres shop_restored
psql -U postgres -d shop_restored -f shop.sql
# Stop at the first error instead of ploughing on and leaving a half-restored
# database that LOOKS like it worked:
psql -U postgres -d shop_restored \
--single-transaction \
-v \
-f shop.sql
# abort on the first error
# --single-transaction all or nothing: an error rolls back everything
# Without these, psql reports errors and CONTINUES. This is a common
# way people end up with a silently incomplete restore.# ---------- Custom format: pg_restore into a fresh database ----------
createdb -U postgres shop_restored
pg_restore -U postgres -d shop_restored shop.dump
# Faster on multi-core machines — restore in parallel:
pg_restore -U postgres -d shop_restored -j 4 shop.dump
# -j 4 four parallel jobs. Only works with custom or directory format.
# Indexes and constraints are built in parallel, which is most of the time.
# Verbose, and stop on error:
pg_restore -U postgres -d shop_restored --exit-on-error --verbose shop.dump# ---------- Restore the globals FIRST when moving to a new server ----------
# Roles must exist before objects that they own can be restored.
psql -U postgres -f globals.sql
# Then the database:
createdb -U postgres shop
pg_restore -U postgres -d shop shop.dump
# Doing this in the wrong order produces a pile of
# "role \"app_user\" does not exist" errors.# ---------- Restoring ONE table (the realistic emergency) ----------
# 1. See what is in the dump:
pg_restore --list shop.dump | grep -i orders
# 2. Restore just that table into a SCRATCH database — never straight over
# the live one:
createdb -U postgres scratch
pg_restore -U postgres -d scratch -t orders shop.dump
# 3. Look at it. Confirm it is what you expect BEFORE touching production:
psql -U postgres -d scratch -c "SELECT count(*) FROM orders;"
psql -U postgres -d scratch -c "SELECT * FROM orders ORDER BY id DESC LIMIT 5;"
# 4. Move the data across deliberately, inside a transaction:
pg_dump -U postgres -d scratch -t orders --data-only -F c -f orders_data.dump-- 5. In the live database, with a transaction so you can still back out:
BEGIN;
ALTER TABLE orders RENAME TO orders_broken; -- keep the bad data, do not drop it
CREATE TABLE orders (LIKE orders_broken INCLUDING ALL);
-- ... load the recovered data into the new table ...
SELECT count(*) FROM orders; -- sanity-check BEFORE committing
COMMIT; -- or ROLLBACK if it looks wrong
-- Drop orders_broken only once you are certain, days later.
-- Renaming instead of dropping costs disk space and buys you a second chance.# ---------- Restoring over an EXISTING database ----------
# !! DESTRUCTIVE. Read this whole block before running any of it.
# --clean issues DROP statements before recreating each object
# --if-exists avoids errors when an object is not there
# These DELETE THE CURRENT CONTENTS of the target database.
pg_restore -U postgres -d shop --clean --if-exists shop.dump
# SAFER ALTERNATIVE — rebuild the database completely instead:
dropdb -U postgres shop # !! deletes the database
createdb -U postgres shop
pg_restore -U postgres -d shop shop.dump
# SAFEST — restore alongside, verify, then switch:
createdb -U postgres shop_new
pg_restore -U postgres -d shop_new -j 4 shop.dump
# ... verify shop_new thoroughly ...
psql -U postgres -c "ALTER DATABASE shop RENAME TO shop_old;"
psql -U postgres -c "ALTER DATABASE shop_new RENAME TO shop;"
# shop_old is still there if something was missed.# ---------- ALWAYS the last step: ANALYZE ----------
psql -U postgres -d shop_restored -c "ANALYZE;"
# A restored database has NO planner statistics. Until ANALYZE runs, the
# planner is guessing, and queries can be dramatically slower than normal.
# This is the single most commonly forgotten step in a restore, and it
# produces the classic "the restore worked but everything is slow" report.-- ---------- Verifying the restore ----------
-- Do the tables exist, and how big are they?
SELECT relname, n_live_tup AS approx_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
-- Exact counts on the tables that matter:
SELECT 'orders' AS t, count(*) FROM orders
UNION ALL SELECT 'customers', count(*) FROM customers;
-- Does the newest data look right? (Catches a dump that was older
-- than you thought.)
SELECT max(placed_at) FROM orders;
-- Did the constraints and indexes come back?
SELECT conname, contype FROM pg_constraint
WHERE conrelid = 'orders'::regclass;
\di
-- Are the sequences correct? If they are behind, the next INSERT
-- will fail with a duplicate key error:
SELECT last_value FROM orders_id_seq;
SELECT setval('orders_id_seq', (SELECT max(id) FROM orders)); -- fix if needed# ---------- Common restore errors and what they mean ----------
# "role \"app_user\" does not exist"
# -> restore globals.sql first (pg_dumpall --globals-only)
# "database \"shop\" already exists"
# -> the target already exists; use a new name, or drop it deliberately
# "unsupported version (1.15) in file header"
# -> the dump was made by a NEWER pg_dump than your pg_restore.
# Use matching or newer client tools.
# "duplicate key value violates unique constraint"
# -> restoring data into a table that already has rows
# "out of shared memory / max_locks_per_transaction"
# -> restoring very many objects in one transaction; raise
# max_locks_per_transaction, or drop --single-transactionHow it works #
A plain SQL dump is simply a text file of CREATE and INSERT/COPY statements, so psql replays it. The important detail is that psql by default reports errors and carries on. A restore can therefore print a screen of errors, exit successfully, and leave a database that looks populated but is missing tables. ON_ERROR_STOP=1 and --single-transaction turn that into an honest failure.
A custom-format dump is compressed and carries a table of contents, which is what pg_restore uses to work selectively. --list shows the contents without restoring, -t restores a single table, and -j restores in parallel. Most restore time is spent rebuilding indexes and constraints rather than loading rows, and those parallelise well, so -j is often the difference between one hour and fifteen minutes.
Order matters when moving to a new server. Roles live at cluster level and must exist before objects owned by them can be created, so globals are restored first. Getting this backwards produces a flood of "role does not exist" errors that can look alarming but simply mean the sequence was wrong.
Restoring into a fresh database is safer because nothing existing can be lost. The options that allow restoring over a live database — --clean --if-exists — work by dropping objects first, which means a mistake in the target name destroys real data before anything is restored. Restoring alongside under a new name and then renaming is safer still, because the old database survives until you are satisfied.
ANALYZE is not optional. Statistics are not included in a dump, so a restored database starts with none at all. Until ANALYZE runs, the planner estimates blindly and can pick badly wrong plans. This is exactly the scenario from the query-performance lesson, and it is why "the restore worked but the application is unusably slow" is such a common report.
Sequences deserve a check after a data-only restore. If a sequence's current value is behind the maximum id in its table, the next insert collides with an existing row and fails on the primary key. A full restore handles this correctly; partial and data-only restores sometimes do not.
Real-world use #
Practise restores when nothing is wrong. The first time you run pg_restore should not be during an incident, with people waiting. The monthly restore test from the automated-backup lesson exists for exactly this reason: it verifies the backups and rehearses the procedure at the same time.
Know your restore duration before you need it. A 200 GB database may take hours to restore, and that number is a large part of your recovery time objective. Measure it, write it down, and tell whoever is relying on it — an RTO agreed without knowing the real restore time is fiction.
In an emergency, restore to a scratch database first. It is slower by one step and prevents the entire category of disaster where a hurried restore overwrites data that was still fine. Rename rather than drop wherever possible; disk is cheap compared with the alternative.
For recovering a single table, dumping from the scratch database and loading deliberately into production is the controlled path. Take a fresh backup of the current state first, even though it contains the problem — you may need to compare against it, and you certainly do not want it to be the only copy that disappears.
Watch for what a restore does not bring back. A dump contains the database's contents, not the server's configuration, not pg_hba.conf, not extensions installed at the operating-system level, not cron jobs. A full server rebuild needs all of those documented separately, which is what the production checklist covers.
Finally, make the restore procedure a written checklist rather than knowledge in one person's head, and make sure someone else has followed it successfully at least once.
Common mistakes #
- Replaying a plain SQL dump without leaving a silently incomplete database.
- Restoring the database before the globals, so every owned object fails with "role does not exist".
- Forgetting ANALYZE after a restore, leaving the planner with no statistics and queries very slow.
- Using --clean --if-exists against the wrong database, destroying live data before restoring.
- Not checking sequence values after a partial restore, so the next INSERT fails on a duplicate key.
Practice #
Take a custom-format dump of a test database, then restore it three ways and compare. First into a brand-new database with -j 4, timing it. Second, a single table into a scratch database using -t. Third, replay a plain SQL dump with and without ON_ERROR_STOP=1 after deliberately corrupting one line, and observe the difference in behaviour. After each restore, run ANALYZE, verify the row counts, and check the sequence values.