Backup, Restore and Proving the Restore Works

A backup that has never been restored is a guess. Disks fill up, a dump stops halfway and nobody reads the error, a role is missing on the new server, or the file is corrupted by a copy that used the wrong encoding. You find out on the worst day. This lesson makes two kinds of backup, restores both, and checks the result by comparing it with the original. It runs in the Docker container shop-pg from the setup lesson, as the postgres user. All the commands work the same in PowerShell, bash and zsh.

There are two kinds of backup, and they answer different problems. A logical backup (pg_dump) writes the SQL and data of one database. It restores into any later PostgreSQL version and can restore a single table, and it is slow on a very large database. A physical backup (pg_basebackup) copies the files of the whole server. It is fast to restore, and it is the base for point-in-time recovery, but it only restores on the same major version and the same kind of machine. Most systems that matter need both, and decide two numbers first: how much data you can afford to lose, and how long a restore may take.

Something to back up

docker exec shop-pg psql -U postgres -c "CREATE DATABASE shop_demo"
docker exec shop-pg psql -U postgres -d shop_demo -c "CREATE TABLE orders AS SELECT g AS order_id, (g % 8) + 1 AS customer_id, DATE '2026-01-01' + (g % 365) AS order_date, ((g % 97) + 0.5)::numeric(8,2) AS total FROM generate_series(1, 20000) AS g"
docker exec shop-pg psql -U postgres -d shop_demo -c "ALTER TABLE orders ADD PRIMARY KEY (order_id)"

CREATE DATABASE
SELECT 20000
ALTER TABLE

A logical backup with pg_dump

The custom format (-Fc) is compressed, and pg_restore can pick parts of it. -f writes the file inside the container.

docker exec -u postgres shop-pg pg_dump -Fc -d shop_demo -f /tmp/shop_demo.dump
docker exec shop-pg sh -c "pg_restore --list /tmp/shop_demo.dump | grep -v '^;' | cut -d' ' -f4-"

TABLE public orders postgres
TABLE DATA public orders postgres
CONSTRAINT public orders orders_pkey postgres

The first command prints nothing when it works. The second reads the table of contents of the file without restoring anything: the table, its data, and its primary key. The grep and cut only remove the header and the object numbers, which change from dump to dump.

pg_dump takes one consistent snapshot, so the dump is correct even while people write to the database, and it does not block them. It does not include roles or tablespaces, because those belong to the server and not to one database. pg_dumpall --globals-only writes them. Its output contains password hashes, so keep it as private as the dump.

Restore into a new database and compare

Restore into an empty database, never over the original. --exit-on-error stops at the first problem instead of carrying on.

docker exec shop-pg createdb -U postgres shop_restore
docker exec -u postgres shop-pg pg_restore -d shop_restore --exit-on-error /tmp/shop_demo.dump

No output means no error. Now prove that the two databases hold the same data. A row count is a start, and a total and a checksum of every row is better. The md5 below is computed over all 20,000 rows in order, so a single changed value gives a different result.

docker exec shop-pg psql -U postgres -d shop_demo -At -c "SELECT count(*), sum(total), md5(string_agg(order_id || ':' || total, ',' ORDER BY order_id)) FROM orders"
docker exec shop-pg psql -U postgres -d shop_restore -At -c "SELECT count(*), sum(total), md5(string_agg(order_id || ':' || total, ',' ORDER BY order_id)) FROM orders"

20000|969307.00|c592c3527e6db1f972a10e6aa557cf5b
20000|969307.00|c592c3527e6db1f972a10e6aa557cf5b

The two lines are identical, and that is the proof. Write the check once for your own tables and run it after every restore test. A row count alone would not notice a column that was restored with different values.

PostgreSQL 18 also puts the planner statistics in the dump. In older versions you had to run ANALYZE on every table after a restore, or the first queries ran on guesses. The restored table already knows its row count:

docker exec shop-pg psql -U postgres -d shop_restore -At -c "SELECT reltuples::int FROM pg_class WHERE relname = 'orders'"

20000

The number is the real one, so the planner needs no ANALYZE after this restore. Before PostgreSQL 18, run ANALYZE after every restore.

What a failed restore looks like

Restore the same file again into the same database, and read the errors.

docker exec -u postgres shop-pg pg_restore -d shop_restore /tmp/shop_demo.dump

pg_restore: error: could not execute query: ERROR:  relation "orders" already exists
Command was: CREATE TABLE public.orders (
    order_id integer NOT NULL,
    customer_id integer,
    order_date date,
    total numeric(8,2)
);
pg_restore: error: COPY failed for table "orders": ERROR:  duplicate key value violates unique constraint "orders_pkey"
DETAIL:  Key (order_id)=(1) already exists.
CONTEXT:  COPY orders, line 1
pg_restore: error: could not execute query: ERROR:  multiple primary keys for table "orders" are not allowed
Command was: ALTER TABLE ONLY public.orders
    ADD CONSTRAINT orders_pkey PRIMARY KEY (order_id);
pg_restore: warning: errors ignored on restore: 3

Without --exit-on-error, pg_restore printed three errors and went on. The exit code is 1, so a script can tell. The last line, errors ignored on restore: 3, is the one to look for. Many restore scripts only check that the command ran, and they treat that as success. Check the exit code and the checksums.

A physical backup with pg_basebackup

This copies the whole server, here into /tmp/base. --checkpoint=fast asks the server to start at once. Without it, the command waits for a normal spread-out checkpoint, and on the test container it had not finished after a minute and a half.

docker exec -u postgres shop-pg pg_basebackup -D /tmp/base -Fp --wal-method=stream --checkpoint=fast
docker exec -u postgres shop-pg pg_verifybackup /tmp/base

backup successfully verified

pg_verifybackup checks every file against the manifest that pg_basebackup wrote, so it catches a damaged or missing file. It does not prove that the database starts. The real test is to start a server on the copy and ask it a question. This one runs on port 5433 so it does not touch the original, and -s keeps the start-up messages quiet.

docker exec -u postgres shop-pg pg_ctl -D /tmp/base -o "-p 5433 -c unix_socket_directories=/tmp" -w -s -l /tmp/base.log start
docker exec shop-pg psql -h /tmp -p 5433 -U postgres -d shop_demo -At -c "SELECT count(*), sum(total) FROM orders"
docker exec -u postgres shop-pg pg_ctl -D /tmp/base -w -s stop

20000|969307.00

The copy started, and holds the same 20,000 rows and the same total. That is a physical restore test. A backup that only has files and a checksum is not yet proven.

A physical backup alone gets you back to the moment it was taken. To restore to a point in time, such as the minute before a bad DELETE, you also archive the write-ahead log continuously and replay it. I did not run that part here.

Make the restore test a routine

• Restore the latest backup to a scratch server on a schedule, not once.
• Compare counts and a checksum of the important tables with the source.
• Keep the backup on a different machine from the database, and test restoring from there.
• Write down how long the restore took. That is your real recovery time.
• Alert when the backup job fails, and when it succeeds too quickly or produces a file much smaller than yesterday's.

Common mistakes

• Taking backups for months and never restoring one.
• Writing a binary dump through a shell redirect on Windows.
• Restoring into the production database to "see if it works".
• Treating pg_restore printing nothing, or a zero exit code from an outer script, as proof.
• Forgetting roles, extensions and settings that live outside the database.
• Keeping the only copy on the same disk as the database.

If you skip this, the first restore you ever run will be the one you cannot afford to get wrong. When did you last restore your own backup, and how do you know the answer matches the original?