Back Up With pg_dump and Prove the Restore Works

A backup file proves nothing until someone has restored it. This page is a short runbook for a logical backup of one database with pg_dump: take the dump, restore it into a scratch database, compare the data with the original, and clean up. Every command runs inside a Docker container named shop-pg, started from postgres:18.6, so nothing is installed on your computer. If you do not have the container yet, start one with docker run -d --name shop-pg -e POSTGRES_PASSWORD=demo postgres:18.6.

Step 1: a database worth backing up

Create a database with two tables, one of them with a serial column, so that the restore has a sequence to bring back too.

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 customers (customer_id serial PRIMARY KEY, name text NOT NULL)" -c "INSERT INTO customers (name) SELECT 'customer ' || g FROM generate_series(1, 500) AS g"
docker exec shop-pg psql -U postgres -d shop_demo -c "CREATE TABLE orders AS SELECT g AS order_id, (g % 500) + 1 AS customer_id, ((g % 97) + 0.5)::numeric(8,2) AS total FROM generate_series(1, 20000) AS g" -c "ALTER TABLE orders ADD PRIMARY KEY (order_id)"

CREATE DATABASE
CREATE TABLE
INSERT 0 500
SELECT 20000
ALTER TABLE

Step 2: take the dump

-Fc is the custom format: compressed, and restorable one object at a time with pg_restore. -f writes to a file inside the container. The -u postgres makes the command run as the database's operating system user.

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 'TABLE DATA' | cut -d' ' -f4-"

TABLE DATA public customers postgres
TABLE DATA public orders postgres

The list shows what is inside the file, and the two TABLE DATA lines prove that both tables' rows are in it. To get the file out of the container, use docker cp shop-pg:/tmp/shop_demo.dump .. Do not redirect the output of docker exec to a file with > in PowerShell, because PowerShell rewrites the bytes of a binary stream and the dump is damaged.

Step 3: restore into a scratch database

Never restore over the original to test it. Create a new, empty database and restore there. --exit-on-error stops at the first problem instead of printing it and going on, so that a successful run means something.

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

No output means no errors.

Step 4: compare the two databases

A restore that finished is not yet a restore that is right. Count the rows of every table in both databases. The query builds a SELECT count(*) for each table, runs it with query_to_xml, and reads the number from the result. It needs no list of table names, so it still works when someone adds a table next month.

docker exec shop-pg psql -U postgres -d shop_demo -At -c "SELECT table_name || ': ' || (xpath('/row/n/text()', query_to_xml(format('SELECT count(*) AS n FROM %I.%I', table_schema, table_name), false, true, '')))[1]::text FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE' ORDER BY table_name"
docker exec shop-pg psql -U postgres -d shop_check -At -c "SELECT table_name || ': ' || (xpath('/row/n/text()', query_to_xml(format('SELECT count(*) AS n FROM %I.%I', table_schema, table_name), false, true, '')))[1]::text FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE' ORDER BY table_name"

customers: 500
orders: 20000
customers: 500
orders: 20000

The two lists are the same. Counts catch a missing table or a table that restored empty. For tables where a silent difference would cost you, compare a checksum as well, such as md5(string_agg(order_id || ':' || total, ',' ORDER BY order_id)). Check the sequence too, because a serial column that restarts at 1 breaks the first insert after a restore.

docker exec shop-pg psql -U postgres -d shop_check -At -c "SELECT last_value FROM customers_customer_id_seq"

500

Step 5: see what a bad backup looks like

Cut the dump file to its first 100 bytes, which is what an interrupted copy leaves behind, and try to restore it.

docker exec shop-pg sh -c "head -c 100 /tmp/shop_demo.dump > /tmp/broken.dump"
docker exec -u postgres shop-pg pg_restore --list /tmp/broken.dump

pg_restore: error: could not read from input file: end of file

The error is immediate and clear, and this is the point of the routine: the problem showed up on a scratch database on a quiet afternoon, and not during an outage.

Step 6: clean up

docker exec shop-pg dropdb -U postgres shop_check
docker exec shop-pg dropdb -U postgres shop_demo
docker exec shop-pg sh -c "rm -f /tmp/shop_demo.dump /tmp/broken.dump"

Put the same five steps in a script and run it on a schedule against yesterday's real dump. A dump that is restored every night is one you can trust.

Related course: SQL Advanced.