Skip to content
Hands-on IT IT Drills

Day 8: Logical Backups

Goal: You back up databases with pg_dump in 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_dump backs 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.dir

Expected:

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.sql

Expected (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_dump 18 only backs up statistics with --statistics. Without them, run ANALYZE first 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 tag08

Expected (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 -2

Expected:

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-80

Expected:

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 PGPASSWORD

Expected:

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

  1. Back up the database tag08 in directory format with 4 jobs to /tmp/t8. Target time: 30 s
  2. Show only the table data entries in the archive tag08.dump. Target time: 20 s
  3. Back up only the cluster’s roles, without passwords. Target time: 20 s
  4. Load tag08.sql so 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.sql

Expected: 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.sql

On 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 ~/tag08

Interview Note

  • “pg_dump or pg_basebackup?” – pg_dump for individual databases, migrations, version changes and selective restores; pg_basebackup plus 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-only first, 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.sql is part of it.
  • A dedicated backup role with pg_read_all_data instead 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 N with zstd; from a few hundred GB on, physical backups (Day 9) are the better choice, because otherwise the restore takes hours.
  • pg_dump locks tables with ACCESS SHARE: an ALTER TABLE then waits until the dump ends – mind maintenance windows and lock_timeout.