Day 8: Logical Backups
Goal: You back up databases with
pg_dumpin every format, back up roles separately, restore fully or selectively, and prove with a restore test that the backup is good. · Duration: approx. 60 min · Prerequisite: Day 2 and 3
Briefing
pg_dumpbacks up one database as SQL or as an archive – consistent to a single point in time, while the server is running, without a write lock.- Roles and tablespaces belong to the cluster, not to the database:
for those you use
pg_dumpall --globals-only. - Formats:
plain(SQL text,psql),custom(one file,pg_restore, selective),directory(one directory, parallel backup and restore). - Logical means: no point-in-time recovery, and a restore takes as long as loading the data plus building the indexes. For large databases, Day 9 is coming.
All commands run on pg1 unless they are marked
pg2.
Drill 1: A test database with traps
A database with its own owner, two schemas, privileges and an extension – exactly the things restores fail on:
psql -c 'CREATE ROLE tag08_owner' -c 'CREATE ROLE tag08_reader'
createdb -O tag08_owner tag08
psql -d tag08 <<'EOF'
SET ROLE tag08_owner;
CREATE SCHEMA app;
CREATE SCHEMA audit;
CREATE TABLE app.notes (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
text text NOT NULL, created_at timestamptz NOT NULL DEFAULT now());
INSERT INTO app.notes (text) SELECT 'Notiz ' || i FROM generate_series(1, 200000) i;
CREATE TABLE app.tags (note_id bigint REFERENCES app.notes, tag text);
INSERT INTO app.tags SELECT i, 'tag' || (i % 10) FROM generate_series(1, 200000) i;
CREATE TABLE audit.log (at timestamptz DEFAULT now(), msg text);
INSERT INTO audit.log (msg) SELECT 'Ereignis ' || i FROM generate_series(1, 50000) i;
GRANT USAGE ON SCHEMA app TO tag08_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO tag08_reader;
RESET ROLE;
CREATE EXTENSION pgcrypto;
EOF
psql -d tag08 -c '\dt (app|audit).*'Expected (abridged):
Schema | Name | Type | Owner
--------+-------+-------+-------------
app | notes | table | tag08_owner
app | tags | table | tag08_owner
audit | log | table | tag08_owner
(3 rows)
Today this query serves as a fingerprint – it must return the same result after every restore:
Q="SELECT (SELECT count(*) FROM app.notes) AS notes,
(SELECT count(*) FROM app.tags) AS tags, (SELECT count(*) FROM audit.log) AS log"
psql -d tag08 -c "$Q"Expected:
notes | tags | log
--------+--------+-------
200000 | 200000 | 50000
(1 row)
Save your progress? Sign up for free
Drill 2: Three formats
mkdir -p ~/tag08 && cd ~/tag08
pg_dump -d tag08 -f tag08.sql
pg_dump -d tag08 -Fc -f tag08.dump
pg_dump -d tag08 -Fd -j 4 -f tag08.dir
pg_dump -d tag08 -Fc -Z zstd -f tag08-zstd.dump
du -sh tag08.sql tag08.dump tag08.dir tag08-zstd.dump
ls tag08.dirExpected:
14M tag08.sql
1.6M tag08.dump
1.6M tag08.dir
336K tag08-zstd.dump
3472.dat.gz
3473.dat.gz
3474.dat.gz
toc.dat
custom and directory compress with gzip by
default; -Z zstd is faster and smaller. -j
(parallel) only works for backups in directory format: each
table becomes its own file. A look into the SQL file:
grep -vE '^(--|$)' tag08.sql | head -4
grep -nE '^\\(un)?restrict' tag08.sqlExpected (abridged):
\restrict piY0FMd7hZhUG8rgI1XZ9SBmmdcc46cGhausxDDS9tEir0l9SJjNneYsXyPyyZR
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
5:\restrict piY0FMd7hZhUG8rgI1XZ9SBmmdcc46cGhausxDDS9tEir0l9SJjNneYsXyPyyZR
450181:\unrestrict piY0FMd7hZhUG8rgI1XZ9SBmmdcc46cGhausxDDS9tEir0l9SJjNneYsXyPyyZR
\restrict has existed since the minor releases of August
2025 (18.0 already has it): it blocks psql meta-commands inside the
dump, so a tampered database cannot run shell commands while the dump is
being loaded. The SQL file needs a psql client at least at
that level.
Tip:
pg_dump18 only backs up statistics with--statistics. Without them, runANALYZEfirst after every restore, otherwise the server plans blind.
Save your progress? Sign up for free
Drill 3: pg_restore – contents and a new database
An archive has a table of contents (TOC):
pg_restore -l tag08.dump | grep -v '^;'Expected:
8; 2615 16621 SCHEMA - app tag08_owner
3481; 0 0 ACL - SCHEMA app tag08_owner
7; 2615 16622 SCHEMA - audit tag08_owner
2; 3079 16651 EXTENSION - pgcrypto
3482; 0 0 COMMENT - EXTENSION pgcrypto
223; 1259 16624 TABLE app notes tag08_owner
3483; 0 0 ACL app TABLE notes tag08_owner
222; 1259 16623 SEQUENCE app notes_id_seq tag08_owner
224; 1259 16635 TABLE app tags tag08_owner
3484; 0 0 ACL app TABLE tags tag08_owner
225; 1259 16645 TABLE audit log tag08_owner
3472; 0 16624 TABLE DATA app notes tag08_owner
3473; 0 16635 TABLE DATA app tags tag08_owner
3474; 0 16645 TABLE DATA audit log tag08_owner
3485; 0 0 SEQUENCE SET app notes_id_seq tag08_owner
3322; 2606 16634 CONSTRAINT app notes notes_pkey tag08_owner
3323; 2606 16640 FK CONSTRAINT app tags tags_note_id_fkey tag08_owner
Order: first structure, then data, then indexes and constraints –
that way COPY loads without index maintenance. Restore into
a new database, in parallel. The target database is
created from template0 (Day 2) – so no change to
template1 brings along objects that collide with the
dump:
createdb -T template0 tag08_restore
pg_restore -d tag08_restore -j 4 tag08.dump; echo rc=$?
psql -d tag08_restore -c "$Q"
psql -l | grep tag08Expected (abridged):
rc=0
notes | tags | log
--------+--------+-------
200000 | 200000 | 50000
(1 row)
tag08 | tag08_owner | UTF8 | libc | C.UTF-8 | C.UTF-8 | ...
tag08_restore | ubuntu | UTF8 | libc | C.UTF-8 | C.UTF-8 | ...
The data is correct, but the new database belongs to
ubuntu: properties of the database itself (owner,
ALTER DATABASE … SET, privileges on it) only end up in the
restore with pg_dump -C or pg_restore -C. The
SQL file is loaded with psql:
createdb -T template0 tag08_plain
psql -X -q -v ON_ERROR_STOP=1 -d tag08_plain -f tag08.sql >/dev/null; echo rc=$?
createdb -T template0 tag08_dir
pg_restore -d tag08_dir -j 4 tag08.dir; echo rc=$?Expected:
rc=0
rc=0
ON_ERROR_STOP=1 is mandatory: without it,
psql does not stop on errors and still reports success at
the end.
Save your progress? Sign up for free
Drill 4: Restore selectively
Someone dropped a table in tag08_restore. Bring back
only that one:
psql -d tag08_restore -c 'DROP TABLE audit.log'
pg_restore -d tag08_restore -n audit -t log tag08.dump; echo rc=$?
psql -d tag08_restore -Atc 'SELECT count(*) FROM audit.log'Expected:
DROP TABLE
rc=0
50000
-t alone matches every table log in every
schema, which is why you add -n. For finer control, use an
edited TOC list (-L) – here deliberately including the
schema line:
psql -d tag08_restore -c 'DROP TABLE audit.log'
pg_restore -l tag08.dump | grep -E 'SCHEMA - audit|audit log' > audit.list
cat audit.list
pg_restore -d tag08_restore -L audit.list tag08.dump; echo rc=$?
psql -d tag08_restore -Atc 'SELECT count(*) FROM audit.log'Expected (abridged):
7; 2615 16622 SCHEMA - audit tag08_owner
225; 1259 16645 TABLE audit log tag08_owner
3474; 0 16645 TABLE DATA audit log tag08_owner
pg_restore: error: could not execute query: ERROR: schema "audit" already exists
Command was: CREATE SCHEMA audit;
pg_restore: warning: errors ignored on restore: 1
rc=1
50000
By default, pg_restore carries on after errors and
reports rc=1 at the end. The table is back; the schema
already existed. For serious restores: -e
(--exit-on-error) stops at the first error, -1
(--single-transaction) makes it all or nothing.
Save your progress? Sign up for free
Drill 5: Overwrite with –clean
After the backup, rows were deleted and inserted in
tag08_restore. A restore into the existing database fails
because of the objects already there:
psql -d tag08_restore -c 'DELETE FROM app.tags' \
-c "INSERT INTO app.notes (text) VALUES ('nach dem Backup')"
pg_restore -d tag08_restore tag08.dump 2>&1 | head -2Expected:
DELETE 200000
INSERT 0 1
pg_restore: error: could not execute query: ERROR: schema "app" already exists
Command was: CREATE SCHEMA app;
--clean drops every object before recreating it;
--if-exists prevents errors for objects that are
missing:
pg_restore -d tag08_restore --clean --if-exists -j 4 tag08.dump; echo rc=$?
psql -d tag08_restore -c "$Q"
psql -d tag08_restore -Atc "SELECT count(*) FROM app.notes
WHERE text = 'nach dem Backup'"Expected:
rc=0
notes | tags | log
--------+--------+-------
200000 | 200000 | 50000
(1 row)
0
The state after the backup is gone – exactly what a restore does.
Objects that do not exist in the backup are left alone by
--clean.
Save your progress? Sign up for free
Drill 6: Back up roles and the owner trap
pg_dumpall --globals-only -f globals.sql
grep -E '^(CREATE|ALTER) ROLE (notes_app|tag08)' globals.sql | cut -c1-80Expected:
CREATE ROLE notes_app;
ALTER ROLE notes_app WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLI
CREATE ROLE tag08_owner;
ALTER ROLE tag08_owner WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOR
CREATE ROLE tag08_reader;
ALTER ROLE tag08_reader WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NO
The file contains the SCRAM hashes of the passwords – it needs as
much protection as the passwords themselves
(--no-role-passwords leaves them out). Now the move to
pg2, where these roles do not exist. Copy from your
machine:
ssh [email protected] 'cat tag08/tag08.dump' \
| ssh [email protected] 'mkdir -p ~/tag08 && cat > ~/tag08/tag08.dump'
ssh [email protected] 'cat tag08/globals.sql' \
| ssh [email protected] 'cat > ~/tag08/globals.sql'On pg2 (only the role postgres exists
there). pg_restore reads the archive from stdin – that way
postgres needs no read access to your home directory:
sudo -u postgres createdb tag08
sudo -u postgres pg_restore -d tag08 < ~/tag08/tag08.dump 2>&1 | grep '^pg_restore' \
| sort | uniq -c
sudo -u postgres psql -d tag08 -c '\dn'Expected (abridged):
5 pg_restore: error: could not execute query: ERROR: role "tag08_owner" ...
3 pg_restore: error: could not execute query: ERROR: role "tag08_reader" ...
1 pg_restore: warning: errors ignored on restore: 8
Name | Owner
--------+-------------------
app | postgres
audit | postgres
public | pg_database_owner
The data is there, but everything belongs to postgres
and the read privileges are missing. Two ways out:
--no-owner --no-acl (deliberately without privileges) or
create the roles first. Here only the two we need:
sudo -u postgres dropdb tag08
grep -E 'ROLE tag08_' ~/tag08/globals.sql | sudo -u postgres psql -X
sudo -u postgres createdb -O tag08_owner tag08
sudo -u postgres pg_restore -d tag08 < ~/tag08/tag08.dump; echo rc=$?
sudo -u postgres psql -d tag08 -c '\dn' -c '\dp app.notes'Expected (abridged):
CREATE ROLE
ALTER ROLE
CREATE ROLE
ALTER ROLE
rc=0
Name | Owner
--------+-------------------
app | tag08_owner
audit | tag08_owner
public | pg_database_owner
Schema | Name | Type | Access privileges | ...
--------+-------+-------+----------------------------------+----
app | notes | table | tag08_owner=arwdDxtm/tag08_owner+| ...
| | | tag08_reader=r/tag08_owner | ...
The extension pgcrypto came along because
postgresql-18 is installed on pg2. If the
package for an extension (PostGIS, say) is missing on the target, the
restore fails at CREATE EXTENSION – install the packages
first.
Save your progress? Sign up for free
Drill 7: Backup over the network and a restore test
On pg2: pg_dump runs on the backup
server and fetches the data over a normal connection – with the lab role
from Day 0 and verified TLS from Day 3:
cd ~/tag08
pg_dump --version
export PGPASSWORD=lab-notes-123
C="host=172.16.0.182 dbname=notes user=notes_app sslmode=verify-full"
pg_dump "$C" -Fc -f notes.dump; echo rc=$?
pg_restore -l notes.dump | grep -v '^;'Expected:
pg_dump (PostgreSQL) 18.7 (Ubuntu 18.7-1.pgdg24.04+2)
rc=0
219; 1259 16388 TABLE public notes ubuntu
3428; 0 0 ACL public TABLE notes ubuntu
3421; 0 16388 TABLE DATA public notes ubuntu
3273; 2606 16399 CONSTRAINT public notes notes_pkey ubuntu
notes_app may read everything in notes –
that is enough here. pg_dump must be at least the major
version of the server; an older client refuses to work. A backup without
a restore test is a hope. So restore it and compare the fingerprint:
sudo -u postgres createdb tag08_notes
sudo -u postgres pg_restore -d tag08_notes --no-owner --no-acl < notes.dump
echo rc=$?
sudo -u postgres psql -d tag08_notes -Atc 'SELECT count(*), max(created_at) FROM notes'
psql "$C" -Atc 'SELECT count(*), max(created_at) FROM notes'
unset PGPASSWORDExpected:
rc=0
10004|2026-11-19 04:40:32.889006+00
10004|2026-11-19 04:40:32.889006+00
Exit code 0, same row count, same latest timestamp. In production, exactly this runs automatically after every backup, and an alert fires if it fails.
Save your progress? Sign up for free
Exercise: Speed Drills
- Back up the database
tag08in directory format with 4 jobs to/tmp/t8. Target time: 30 s - Show only the table data entries in the archive
tag08.dump. Target time: 20 s - Back up only the cluster’s roles, without passwords. Target time: 20 s
- Load
tag08.sqlso that it stops at the first error. Target time: 30 s
Solution
pg_dump -d tag08 -Fd -j 4 -f /tmp/t8
pg_restore -l ~/tag08/tag08.dump | grep 'TABLE DATA'
pg_dumpall --roles-only --no-role-passwords -f /tmp/roles.sql
createdb tag08_speed
psql -X -v ON_ERROR_STOP=1 -d tag08_speed -f ~/tag08/tag08.sql >/dev/null; echo rc=$?Expected (abridged):
3472; 0 16624 TABLE DATA app notes tag08_owner
3473; 0 16635 TABLE DATA app tags tag08_owner
3474; 0 16645 TABLE DATA audit log tag08_owner
rc=0
Save your progress? Sign up for free
Checkpoint
pg_restore -l ~/tag08/tag08.dump | grep -c 'TABLE DATA'Expected: 3
psql -d tag08_restore -c "$Q"Expected: 200000 | 200000 | 50000
grep -c 'ROLE tag08_' ~/tag08/globals.sqlExpected: 4 (one CREATE
and one ALTER for each of the two roles).
On pg2:
sudo -u postgres psql -d tag08_notes -Atc 'SELECT count(*) FROM notes'Expected: the same number as
SELECT count(*) FROM notes on pg1 (here
10004).
Challenge
From tag08.dump, restore only the
structure of the schema app (no data, no
audit) into an empty database
tag08_schema.
Hint: pg_restore -n app does create the
tables, but not the schema itself – try it and read the error
message.
Solution
createdb tag08_schema
pg_restore -d tag08_schema -s -n app tag08.dump 2>&1 | grep -m1 ERROR
pg_restore -l tag08.dump | grep -E ' (SCHEMA )?app ' > app.list
pg_restore -d tag08_schema -s -L app.list tag08.dump; echo rc=$?
psql -d tag08_schema -Atc '\dt app.*' -c 'SELECT count(*) FROM app.notes'Expected:
pg_restore: error: could not execute query: ERROR: schema "app" does not exist
rc=0
app|notes|table|tag08_owner
app|tags|table|tag08_owner
0
Unlike pg_dump -n, pg_restore -n does not
include the SCHEMA entry. The TOC list contains it, and
-s still filters out the TABLE DATA lines.
Save your progress? Sign up for free
Cleanup
On pg1:
dropdb --if-exists tag08
dropdb --if-exists tag08_restore
dropdb --if-exists tag08_plain
dropdb --if-exists tag08_dir
dropdb --if-exists tag08_schema
dropdb --if-exists tag08_speed
psql -c 'DROP ROLE tag08_owner' -c 'DROP ROLE tag08_reader'
rm -rf ~/tag08 /tmp/t8 /tmp/roles.sqlOn pg2:
sudo -u postgres dropdb tag08
sudo -u postgres dropdb tag08_notes
sudo -u postgres psql -c 'DROP ROLE tag08_owner' -c 'DROP ROLE tag08_reader'
rm -rf ~/tag08Interview Note
- “pg_dump or pg_basebackup?” –
pg_dumpfor individual databases, migrations, version changes and selective restores;pg_basebackupplus WAL for the whole cluster with point-in-time recovery and fast restores of large data volumes. - “Why are privileges missing after the restore?” – Roles are global
and not in the dump; load
pg_dumpall --globals-onlyfirst, or deliberately use--no-owner --no-acl. - “Is a pg_dump consistent?” – Yes, it runs in one transaction with
one snapshot (
REPEATABLE READ); parallel jobs share this snapshot.
Enterprise Note
- Store dumps encrypted and off the server (object storage with
retention rules and Object Lock);
globals.sqlis part of it. - A dedicated backup role with
pg_read_all_datainstead of a superuser or the application role. - An automated restore test into a throwaway instance with check queries and an alert – without it, a backup is not proven.
- Large databases:
-Fd -j Nwith zstd; from a few hundred GB on, physical backups (Day 9) are the better choice, because otherwise the restore takes hours. pg_dumplocks tables withACCESS SHARE: anALTER TABLEthen waits until the dump ends – mind maintenance windows andlock_timeout.