Backups, and restoring them

The last line of defense

Look back at the safety nets so far. Transactions (Lesson 7) undo a change before you commit it. Constraints (Lesson 6) refuse bad rows. Checks (Lesson 10) catch what the constraints can't. Roles (Lesson 11) stop a program doing more than its job. Every one of them assumes the database itself is fine. None of them helps when:

• someone runs DROP TABLE on the right table in the wrong database, and commits;
• a bug in a script slowly corrupts rows for three weeks before anyone notices;
• the disk fails, the server is deleted, or the company hosting it has a very bad day.

For those, there's only a backup: a copy of the data, kept somewhere else, from before it went wrong. Think of a house fire. Smoke alarms, fire doors and a sprinkler system all help, and you want them. But the photographs are only safe if copies live somewhere else, and you only find out whether those copies work when you need them. That last part, finding out before you need them, is most of this lesson.

The demo is demo13.sh. It runs every step and prints each command before its output. Run it like this, then follow along:

docker start larkspur
./demo13.sh > ~/Apps/sql-lesson13-output.txt 2>&1

What's being backed up

The county, after Lesson 10's fixes:

docker exec larkspur psql -U postgres -d larkspur -c "SELECT (SELECT count(*) FROM towns) AS towns, (SELECT count(*) FROM leaders) AS leaders, (SELECT count(*) FROM meetings) AS meetings;"

 towns | leaders | meetings 
-------+---------+----------
    12 |      20 |       30
(1 row)

Remember those three numbers. Every restore in this lesson gets checked against them.

A plain SQL dump

pg_dump reads a database and writes out everything needed to rebuild it. By default, as plain SQL:

mkdir -p backups
docker exec larkspur pg_dump -U postgres -d larkspur > backups/larkspur.sql

pg_dump runs inside the container and prints the dump; > saves what it prints to a file on your computer, outside the container. That matters: a backup inside the container dies with the container. The whole county is 9.1 KB:

9.1K backups/larkspur.sql

It's a text file, so you can read it. The interesting lines:

grep -n -E "^(CREATE TABLE|COPY|ALTER TABLE ONLY|CREATE VIEW|CREATE INDEX)" backups/larkspur.sql

29:CREATE TABLE public.leaders (
45:CREATE TABLE public.meetings (
61:CREATE TABLE public.towns (
78:CREATE VIEW public.problems AS
156:COPY public.leaders (id, town_id, name, role, started, ended) FROM stdin;
184:COPY public.meetings (id, town_id, held_on, body, attendance, minutes_url) FROM stdin;
222:COPY public.towns (id, name, kind, population, founded, website, phone) FROM stdin;
242:ALTER TABLE ONLY public.leaders
250:ALTER TABLE ONLY public.meetings
258:ALTER TABLE ONLY public.towns
266:ALTER TABLE ONLY public.leaders
274:ALTER TABLE ONLY public.meetings

The order is the recipe for rebuilding:

1. CREATE TABLE for each table, and CREATE VIEW for Lesson 10's problems view: the shapes, empty.
2. COPY for each table: the rows. COPY is Postgres's fast bulk loader, much quicker than one INSERT per row.
3. ALTER TABLE ONLY: the primary keys, then the foreign keys, added last. Loading rows into a table without its keys and indexes, then adding them at the end, is much faster than checking every row on the way in.

Here's what the rows look like:

COPY public.towns (id, name, kind, population, founded, website, phone) FROM stdin;
1	Ashford	city	48210	1851	https://www.ashfordcity.gov	(555) 010-1100
2	Ashford	village	1904	1888	\N	(555) 010-2200
3	Brennan Falls	city	21544	1867	https://www.brennanfalls.gov	(555) 010-3300
6	Elk Ridge	village	2710	1911	https://www.elkridge.gov	(555) 010-6600

One line per row, columns separated by tabs, and \N for NULL: the Ashford village's missing website, kept as missing, not as empty text. (Rows come out in storage order, not by id: updates in earlier lessons moved some.) A plain dump is easy to read, search and even edit, and you restore it by running it through psql, like larkspur.sql itself.

A custom-format dump

-Fc writes Postgres's own custom format instead: compressed, and with a table of contents.

docker exec larkspur pg_dump -U postgres -Fc -d larkspur > backups/larkspur.dump

8.3K larkspur.dump
9.1K larkspur.sql

Only a little smaller here: most of a file this tiny is the overhead of saying what's in it. On a real database, mostly rows, the compression pays off far more. The bigger win is the table of contents. pg_restore -l lists it:

docker exec larkspur pg_restore -l /practice/backups/larkspur.dump | grep -v "^;"

216; 1259 24705 TABLE public leaders postgres
217; 1259 24717 TABLE public meetings postgres
215; 1259 24698 TABLE public towns postgres
218; 1259 24729 VIEW public problems postgres
3431; 0 24705 TABLE DATA public leaders postgres
3432; 0 24717 TABLE DATA public meetings postgres
3430; 0 24698 TABLE DATA public towns postgres
3281; 2606 24711 CONSTRAINT public leaders leaders_pkey postgres
3283; 2606 24723 CONSTRAINT public meetings meetings_pkey postgres
3279; 2606 24704 CONSTRAINT public towns towns_pkey postgres
3284; 2606 24712 FK CONSTRAINT public leaders leaders_town_id_fkey postgres
3285; 2606 24724 FK CONSTRAINT public meetings meetings_town_id_fkey postgres

(The path is /practice/backups/... because pg_restore runs inside the container, where your sql-practice folder is /practice.) Twelve separate pieces: each table's shape, each table's rows, each key. Because they're separate, pg_restore can restore some of them and not others, and that's the custom format's real advantage. Hold on to this list: it explains a surprise in a minute.

Which to use? Custom format for backups: smaller, and you can restore just part of it. Plain SQL when a person needs to read it, or when it has to go into something that only understands SQL.

Restore it, and prove it

A backup you've never restored is a hope, not a backup. Files get cut short, the wrong database gets dumped, a setting leaves something out, and you find out the day you need it. So restore it, now, somewhere harmless: a new, empty database.

docker exec larkspur createdb -U postgres larkspur_restored
time docker exec larkspur pg_restore -U postgres -d larkspur_restored /practice/backups/larkspur.dump

real	0m0.060s
user	0m0.014s
sys	0m0.007s

createdb makes an empty database, the same as CREATE DATABASE. pg_restore -d larkspur_restored loads the dump into it. Sixty milliseconds, and silent, which for a Unix tool means it worked. time measures how long it took; on a real database, knowing that number in advance tells you how long you'll be down if you ever need to do it for real.

"It didn't complain" isn't proof. Count:

docker exec larkspur psql -U postgres -d larkspur_restored -c "SELECT (SELECT count(*) FROM towns) AS towns, (SELECT count(*) FROM leaders) AS leaders, (SELECT count(*) FROM meetings) AS meetings;"

 towns | leaders | meetings 
-------+---------+----------
    12 |      20 |       30
(1 row)

12, 20, 30: the same as the original. And since the problems view came back too, run Lesson 10's checks against the copy:

docker exec larkspur psql -U postgres -d larkspur_restored -c "SELECT severity, count(*) FROM problems GROUP BY severity ORDER BY severity;"

 severity | count 
----------+-------
 warn     |     6
(1 row)

No errors and the same six warnings as the original. The copy isn't just the right size; it has the same data, problems and all. Counts plus checks is a solid, cheap test of any restore.

Disaster: a table dropped for real

Now the bad day. Someone drops meetings in the real county, with no transaction to save them:

docker exec larkspur psql -U postgres -d larkspur -c "DROP TABLE meetings;"
docker exec larkspur psql -U postgres -d larkspur -c "SELECT count(*) FROM meetings;"

DROP TABLE
ERROR:  relation "meetings" does not exist
LINE 1: SELECT count(*) FROM meetings;
                             ^

Gone. Everything else in the county is fine, and you don't want to restore the whole database over it, which would undo any changes made to the other tables since the backup. You want just this table back.

The obvious way: pg_restore -t, restore one table.

docker exec larkspur pg_restore -U postgres -d larkspur -t meetings /practice/backups/larkspur.dump
docker exec larkspur psql -U postgres -d larkspur -c "SELECT count(*) FROM meetings;"

 count 
-------
    30
(1 row)

All 30 meetings back. Done? Look at the table:

                Table "public.meetings"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 id          | integer |           | not null | 
 town_id     | integer |           |          | 
 held_on     | date    |           |          | 
 body        | text    |           |          | 
 attendance  | integer |           |          | 
 minutes_url | text    |           |          | 

No primary key, no foreign key. Compare the same table after the next fix, below: Indexes: and Foreign-key constraints: are missing here. Look back at the table of contents: the table, its data, its primary key and its foreign key are four separate entries, and -t meetings restored the first two only. The rows are back, but nothing stops two meetings sharing an id now, or a meeting pointing at a town that doesn't exist. Every rule Lesson 6 was about, silently gone, and the count says everything's fine.

The way that brings back everything: take the table from the restored copy with pg_dump -t, which does include a table's keys and indexes, and pipe it straight into the real county.

docker exec larkspur psql -U postgres -d larkspur -c "DROP TABLE meetings;"
docker exec larkspur sh -c "pg_dump -U postgres -d larkspur_restored -t meetings | psql -q -U postgres -d larkspur"

DROP TABLE
 set_config 
------------
 
(1 row)

(The keyless copy is dropped first. The set_config output is harmless: every dump starts by setting a few options, and one of them prints a result.) Now:

                Table "public.meetings"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 id          | integer |           | not null | 
 town_id     | integer |           |          | 
 held_on     | date    |           |          | 
 body        | text    |           |          | 
 attendance  | integer |           |          | 
 minutes_url | text    |           |          | 
Indexes:
    "meetings_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "meetings_town_id_fkey" FOREIGN KEY (town_id) REFERENCES towns(id)

 count 
-------
    30
(1 row)

The table, its 30 rows, and both keys. This is the standard move for "get one thing back": restore the whole backup somewhere harmless, then copy across just what you need. It's also why the restore-to-a-new-database habit pays off twice: it proves the backup, and it's the first step of most real recoveries.

(You can also make pg_restore bring back the keys, by saving its -l list to a file, keeping only the meetings lines, and passing it back with -L. It works, and it's fiddly at three in the morning.)

What pg_dump leaves out

A dump is one database. Roles belong to the whole server (Lesson 11), so pg_dump doesn't include them. Make one, and look for it:

docker exec larkspur psql -U postgres -c "CREATE ROLE backup_test_role LOGIN;"
docker exec larkspur pg_dump -U postgres -d larkspur | grep -c backup_test_role

CREATE ROLE
0

Not there. pg_dumpall --globals-only dumps the server-wide things, roles included:

docker exec larkspur pg_dumpall -U postgres --globals-only | grep -E "^CREATE ROLE"

CREATE ROLE backup_test_role;
CREATE ROLE postgres;

Restore a database to a brand new server without its roles, and every GRANT to larkspur_api or reader fails, because those roles don't exist there. Back up the globals alongside the database dumps. (Passwords come out of pg_dumpall as hashes, so treat that file as a secret.) The test role is dropped again straight after.

Moving to a newer Postgres

Lesson 2's Spot the problem: a Postgres 17 server won't open a Postgres 16 data directory. A dump doesn't have that problem, because it isn't a data directory; it's instructions. Start a throwaway Postgres 17 container, and restore the same dump into it:

docker run -d --name larkspur17 -e POSTGRES_PASSWORD=practice -v "$PWD":/practice postgres:17
docker exec larkspur17 createdb -U postgres larkspur
docker exec larkspur17 pg_restore -U postgres -d larkspur /practice/backups/larkspur.dump
docker exec larkspur17 psql -U postgres -d larkspur -c "SHOW server_version;"

         server_version          
---------------------------------
 17.11 (Debian 17.11-1.pgdg13+2)
(1 row)

 towns | leaders | meetings 
-------+---------+----------
    12 |      20 |       30
(1 row)

The county, on Postgres 17, with every row. That's how major upgrades are done for databases small enough to dump and restore: dump the old one, restore into the new one, check, switch over. (Very large databases use pg_upgrade instead, which converts the files in place, but the dump is the simple way, and it's the one to know.) The other direction is the one that fails: an older pg_restore can't read a dump made by a newer pg_dump. Remove the test container, and its unnamed volume, with -v:

docker rm -f -v larkspur17

A backup you can trust

Everything above makes a backup and proves it once. A backup you can rely on needs four more things.

It has to live somewhere else. backups/larkspur.dump is on the same laptop as the database. A dead disk, a stolen laptop or a bad rm takes both. The usual rule is 3-2-1: three copies of the data, on two different kinds of storage, one of them in another place. For a server, that means copying each dump to storage run by someone else: an object store like S3 or DigitalOcean Spaces, in a different region.

It has to be on a schedule. A backup from six months ago is a history lesson. A dump made by a scheduled job every night, a cron job or a Kubernetes CronJob, means you never lose more than a day. How much you could stand to lose decides how often: that's your recovery point objective.

You have to know it's whole. A checksum is a fingerprint of a file: change one byte, and it changes completely.

shasum -a 256 backups/larkspur.dump

9d0c75caf009267729368076f324fbf62830010a87ec9404e1543b9d6ff4ed2a  backups/larkspur.dump

Record it when the dump is made, check it after copying, and you know the copy is exactly what was dumped. And the dump describes itself:

docker exec larkspur pg_restore -l /practice/backups/larkspur.dump | head -12

;
; Archive created at 2026-10-31 19:38:49 UTC
;     dbname: larkspur
;     TOC Entries: 16
;     Compression: gzip
;     Dump Version: 1.15-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 16.15 (Debian 16.15-1.pgdg13+2)
;     Dumped by pg_dump version: 16.15 (Debian 16.15-1.pgdg13+2)
;

When it was made, from which database, by which version. If pg_restore -l can read a file, it's at least a complete custom-format dump; one cut short partway through fails here.

And it has to be restored, regularly. Not once when it's set up: on a schedule, automatically, the way this lesson did it. Restore the latest dump into a scratch database, count the rows, run the checks, and alert if anything fails. That's the only test that tells you the backup will work on the day you need it.

Tidy up the restored copy:

docker exec larkspur dropdb -U postgres larkspur_restored

Spot the problem

Work out what's wrong before you read the explanation.

The backups that weren't. A team sets up a nightly job: pg_dump -U app -d production > /backups/prod.sql. It runs every night for a year, and the folder fills with files, one a night. Then the database is lost, and they go to restore. Every file is 0 bytes. Why didn't anyone notice? The job's password expired, or a permission changed, a year ago, so pg_dump failed every night with an error. But > creates the file before the command runs, so a file appeared every night anyway, and nobody looked inside. The job never checked pg_dump's exit code, the files' sizes were never compared, and no one ever tried a restore. Any one of those would have caught it on the first night. The test that matters is the restore.

Where you are now

You can back up a database, restore it, prove the copy is whole, get one table back with its keys, carry the roles along, and move to a newer Postgres. That's the end of the SQL itself. Lesson 14 steps outside the table altogether: the other kinds of databases, like Redis, and when they're the better tool.

Check yourself

Key terms

• Backup: a copy of the data, kept somewhere else, from which the database can be rebuilt.
• pg_dump: writes out everything needed to rebuild one database, as plain SQL or in custom format.
• Custom format: Postgres's own dump format, -Fc: compressed, with a table of contents, for partial restores.
• pg_restore: loads a custom-format dump into a database, all of it or part.
• Globals: server-wide things a database dump doesn't include, like roles. Backed up with pg_dumpall --globals-only.
• 3-2-1: three copies, two kinds of storage, one somewhere else.
• Checksum: a fingerprint of a file, to prove a copy is exact.
• Restore test: restoring a backup and checking it, on a schedule. The only proof a backup works.

Go deeper

• The PostgreSQL manual: Backup and Restore (opens in a new tab), and the pg_dump (opens in a new tab) page.
• Try: write restore-test.sh: dump the county, restore it into a new database, compare the three counts with the original, and exit 1 if any differ. Then make it delete the copy.
• Try: restore just the meetings table's keys with pg_restore -l and -L, without pg_dump.