Lab 17: Take a logical backup, restore it somewhere else, and find what it left behind

Objective

A logical backup is a set of SQL statements that recreates a database. It is portable across versions and architectures, it can be restored selectively, and it is the wrong tool for most production recovery — a distinction Lab 18 onwards is about.

This lab is about using it correctly, and the thing it exists to teach is one specific failure. You will restore a dump into a cluster that has never seen the database before, and the restore will:

• report errors,
• exit non-zero,
• load every single row correctly,
• and leave the database with no permissions at all.

That combination is why “the restore worked, we counted the rows” is not a test, and why so many restore rehearsals pass and so many real restores do not.

Architecture

One source database with a schema, a view, foreign keys, a role and grants; two destination clusters, one of which knows nothing.

flowchart LR
    S["shop on rbpg-lab01\nschema sales, 55,000 rows\nrole shop_reader with GRANTs"] --> D1["pg_dump -Fp / -Fc / -Fd / -Ft"]
    S --> D2["pg_dumpall --globals-only"]
    D1 --> R1["restore into a cluster\nWITHOUT shop_reader\n-> data yes, privileges no"]
    D2 --> R2["globals first, then restore\n-> data and privileges"]
    D1 --> R2

Requirements

• Two PostgreSQL 18 clusters. The lab uses rbpg-lab01 as the source and a second container as the destination, so that the destination genuinely lacks the role.
• Superuser access to both.
• Roughly 100 MB of free disk for the dumps.

Scenario

You are restoring a production database into a newly built server as part of a migration rehearsal. The dump was taken this morning, the restore runs for four minutes, and the row counts match exactly.

The application then fails to start.

Tasks

Task 1 — Build something worth dumping

LAB="$HOME/rbpg-lab-17"
mkdir -p "$LAB"

docker exec -i -u postgres rbpg-lab01 psql -X -c "CREATE DATABASE shop;"
docker exec -i -u postgres rbpg-lab01 psql -X -d shop <<'SQL'
CREATE SCHEMA sales;
CREATE TABLE sales.customers(id int PRIMARY KEY, name text NOT NULL, email text UNIQUE);
CREATE TABLE sales.orders(
  id int PRIMARY KEY,
  customer_id int NOT NULL REFERENCES sales.customers(id),
  total numeric(12,2) NOT NULL CHECK (total >= 0),
  placed timestamptz DEFAULT now());
CREATE INDEX orders_customer_idx ON sales.orders(customer_id);
CREATE VIEW sales.order_totals AS
  SELECT c.name, sum(o.total) AS lifetime
  FROM sales.customers c JOIN sales.orders o ON o.customer_id = c.id
  GROUP BY c.name;

INSERT INTO sales.customers
  SELECT g, 'customer-'||g, 'c'||g||'@example.invalid' FROM generate_series(1,5000) g;
INSERT INTO sales.orders
  SELECT g, 1+(g%5000), (g%400)*2.5, now() - (g||' minutes')::interval
  FROM generate_series(1,50000) g;

CREATE ROLE shop_reader;
GRANT USAGE ON SCHEMA sales TO shop_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO shop_reader;
SQL

docker exec -u postgres rbpg-lab01 psql -X -d shop -c "VACUUM ANALYZE;"
docker exec -u postgres rbpg-lab01 psql -X -d shop -c "
  SELECT pg_size_pretty(pg_database_size('shop')) AS size,
         (SELECT count(*) FROM sales.customers) AS customers,
         (SELECT count(*) FROM sales.orders) AS orders;"

$ a summary query over the new database

 size  | customers | orders 
-------+-----------+--------
13 MB |      5000 |  50000
(1 row)

Task 2 — The four formats

docker exec rbpg-lab01 mkdir -p /backup
docker exec rbpg-lab01 chown postgres:postgres /backup

{
for FMT in p c d t; do
  case $FMT in
    p) NAME=plain;     TARGET=/backup/shop.sql;;
    c) NAME=custom;    TARGET=/backup/shop.dump;;
    d) NAME=directory; TARGET=/backup/shop.dir;;
    t) NAME=tar;       TARGET=/backup/shop.tar;;
  esac
  docker exec rbpg-lab01 rm -rf "$TARGET"
  S=$(date +%s%N)
  docker exec -u postgres rbpg-lab01 pg_dump -F$FMT -f "$TARGET" shop
  E=$(date +%s%N)
  SIZE=$(docker exec rbpg-lab01 du -sh "$TARGET" | cut -f1)
  printf -- "-F%s (%-9s)  %6s  %s ms\n" "$FMT" "$NAME" "$SIZE" "$(( (E-S)/1000000 ))"
done
} | tee "$LAB/formats.txt"

$ pg_dump in each of the four formats, timed and measured

-Fp (plain    )    2.5M  85 ms
-Fc (custom   )    420K  106 ms
-Fd (directory)    424K  114 ms
-Ft (tar      )    2.5M  95 ms

Custom is a sixth of the size of plain because it compresses by default. Plain is a .sql file you can read, edit and pipe into psql; the other three need pg_restore.

Task 3 — Read the table of contents

docker exec -u postgres rbpg-lab01 pg_restore -l /backup/shop.dump \
  | grep -v "^;" | tee "$LAB/contents.txt"

$ pg_restore -l /backup/shop.dump

6; 2615 16652 SCHEMA - sales postgres
3461; 0 0 ACL - SCHEMA sales postgres
220; 1259 16653 TABLE sales customers postgres
3462; 0 0 ACL sales TABLE customers postgres
221; 1259 16664 TABLE sales orders postgres
3463; 0 0 ACL sales TABLE orders postgres
222; 1259 16680 VIEW sales order_totals postgres
3464; 0 0 ACL sales TABLE order_totals postgres
3453; 0 16653 TABLE DATA sales customers postgres
3454; 0 16664 TABLE DATA sales orders postgres
3298; 2606 16663 CONSTRAINT sales customers customers_email_key postgres
3300; 2606 16661 CONSTRAINT sales customers customers_pkey postgres
3303; 2606 16673 CONSTRAINT sales orders orders_pkey postgres
3301; 1259 16679 INDEX sales orders_customer_idx postgres
3304; 2606 16674 FK CONSTRAINT sales orders orders_customer_id_fkey postgres

The order is not alphabetical; it is the order things must be created in. Schemas, then tables, then the data, then the constraints and indexes — because building an index once at the end is far cheaper than maintaining it through 50,000 inserts, and a foreign key that exists during the load would check every row.

Note the ACL entries. The permissions are in the dump.

Task 4 — What is not in the dump

docker exec -u postgres rbpg-lab01 bash -c "grep -c 'CREATE ROLE' /backup/shop.sql"
docker exec -u postgres rbpg-lab01 bash -c "grep -n 'shop_reader' /backup/shop.sql | head -3"

$ count CREATE ROLE statements in the plain dump, then grep for the role name

0

55136:GRANT USAGE ON SCHEMA sales TO shop_reader;
55143:GRANT SELECT ON TABLE sales.customers TO shop_reader;
55150:GRANT SELECT ON TABLE sales.orders TO shop_reader;

Zero CREATE ROLE statements, and three GRANT statements to a role that does not exist in the dump.

This is correct behaviour and it is the source of the failure. pg_dump dumps one database. Roles, tablespaces and cluster-wide settings live outside any database, so they are not in it.

docker exec -u postgres rbpg-lab01 pg_dumpall --globals-only -f /backup/globals.sql
docker exec rbpg-lab01 grep -E "CREATE ROLE|ALTER ROLE" /backup/globals.sql | head -4
docker exec rbpg-lab01 du -h /backup/globals.sql

$ pg_dumpall --globals-only, then grep it for role statements

CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
CREATE ROLE shop_reader;
ALTER ROLE shop_reader WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;

4.0K	/backup/globals.sql

Task 5 — Restore into a cluster that knows nothing

docker cp rbpg-lab01:/backup/shop.dump   /tmp/shop.dump
docker cp /tmp/shop.dump                 rbpg-base18:/tmp/shop.dump
docker cp rbpg-lab01:/backup/globals.sql /tmp/globals.sql
docker cp /tmp/globals.sql               rbpg-base18:/tmp/globals.sql

docker exec rbpg-base18 psql -U postgres -X -c \
  "SELECT rolname FROM pg_roles WHERE rolname='shop_reader';"
docker exec rbpg-base18 psql -U postgres -X -c "CREATE DATABASE shop;"

docker exec rbpg-base18 pg_restore -U postgres -d shop /tmp/shop.dump
echo "pg_restore exit status: $?"

$ pg_restore into a cluster with no shop_reader role

 rolname 
---------
(0 rows)

CREATE DATABASE

pg_restore: error: could not execute query: ERROR:  role "shop_reader" does not exist
Command was: GRANT USAGE ON SCHEMA sales TO shop_reader;

pg_restore: error: could not execute query: ERROR:  role "shop_reader" does not exist
Command was: GRANT SELECT ON TABLE sales.customers TO shop_reader;

pg_restore: error: could not execute query: ERROR:  role "shop_reader" does not exist
Command was: GRANT SELECT ON TABLE sales.orders TO shop_reader;

pg_restore exit status: 1

Task 6 — Count the rows, and be reassured for the wrong reason

docker exec rbpg-base18 psql -U postgres -X -d shop -c "SELECT count(*) AS customers FROM sales.customers;"
docker exec rbpg-base18 psql -U postgres -X -d shop -c "SELECT count(*) AS orders FROM sales.orders;"
docker exec rbpg-base18 psql -U postgres -X -d shop -c "\dp sales.customers"

$ row counts, then \\dp on the restored table

 customers 
-----------
    5000
(1 row)

orders 
--------
50000
(1 row)

                             Access privileges
Schema |   Name    | Type  | Access privileges | Column privileges | Policies 
--------+-----------+-------+-------------------+-------------------+----------
sales  | customers | table |                   |                   | 
(1 row)

Row counts match exactly. The Access privileges column is empty.

An application connecting as shop_reader gets permission denied for schema sales, from a database that a row-count check has just certified as a good restore.

Task 7 — Do it in the right order

docker exec rbpg-base18 psql -U postgres -X -c "DROP DATABASE shop;"

docker exec rbpg-base18 psql -U postgres -X -f /tmp/globals.sql
docker exec rbpg-base18 psql -U postgres -X -c "CREATE DATABASE shop;"
docker exec rbpg-base18 pg_restore -U postgres -d shop /tmp/shop.dump
echo "pg_restore exit status: $?"
docker exec rbpg-base18 psql -U postgres -X -d shop -c "\dp sales.customers"

$ restore globals, create the database, restore it, then check privileges

psql:/tmp/globals.sql:16: ERROR:  role "postgres" already exists
ALTER ROLE
CREATE ROLE
ALTER ROLE

pg_restore exit status: 0

                                 Access privileges
Schema |   Name    | Type  |     Access privileges      | Column privileges | Policies 
--------+-----------+-------+----------------------------+-------------------+----------
sales  | customers | table | postgres=arwdDxtm/postgres+|                   | 
      |           |       | shop_reader=r/postgres     |                   | 
(1 row)

Exit status 0, and shop_reader=r/postgres — read permission, granted by postgres. The restore is now genuinely complete.

The role "postgres" already exists error is expected: the globals file describes every role in the source cluster, including ones the destination already has. It is harmless, and it is also why you cannot simply check the globals restore for zero errors.

Task 8 — Parallelism and sections

# Parallel dump needs directory format:
docker exec -u postgres rbpg-lab01 pg_dump -Fc -j 4 -f /backup/x.dump shop

$ pg_dump -Fc -j 4

pg_dump: error: parallel backup only supported by the directory format

docker exec rbpg-lab01 rm -rf /backup/shop.pdir
docker exec -u postgres rbpg-lab01 pg_dump -Fd -j 4 -f /backup/shop.pdir shop
docker exec rbpg-lab01 ls /backup/shop.pdir

$ pg_dump -Fd -j 4, then list the directory

3453.dat.gz
3454.dat.gz
toc.dat

The numbers match the TABLE DATA entries from Task 3 — one file per table, which is what makes parallelism possible.

Finally, the three sections a dump is divided into:

docker exec -u postgres rbpg-lab01 pg_restore --section=post-data -f - /backup/shop.dump \
  | grep -E "^(ALTER TABLE|CREATE INDEX)" | head -5

$ pg_restore --section=post-data -f - and filter for index and constraint statements

ALTER TABLE ONLY sales.customers
ALTER TABLE ONLY sales.customers
ALTER TABLE ONLY sales.orders
CREATE INDEX orders_customer_idx ON sales.orders USING btree (customer_id);
ALTER TABLE ONLY sales.orders

pre-data is schemas, tables and functions; data is the rows; post-data is indexes, constraints and triggers. Restoring pre-data, loading data by some other route, and then restoring post-data is the standard shape of a large migration, and --section is how you drive it.

Validation

test -s "$LAB/formats.txt"         && echo "OK formats"
test -s "$LAB/contents.txt"        && echo "OK contents"
test -s "$LAB/failed-restore.txt"  && echo "OK failed-restore"
test -s "$LAB/correct-restore.txt" && echo "OK correct-restore"

grep -q "TABLE DATA" "$LAB/contents.txt" && echo "OK table of contents captured"

# The check that actually matters: connect as the application role and query.
docker exec rbpg-base18 psql -U postgres -X -d shop -c \
  "SET ROLE shop_reader; SELECT count(*) FROM sales.orders;"

That last command is the real validation. It fails on the Task 5 restore and succeeds on the Task 7 one, and no row count distinguishes them.

Questions to answer without looking anything up:

1. Which two commands make a complete logical backup of one database, and why is one not enough?
2. pg_restore printed errors and every row is present. What is most likely missing?
3. Your restore script runs pg_restore ... | tee restore.log and checks $?. What is it actually checking?
4. Why does a dump restore data before creating indexes and foreign keys?
5. A four-hour pg_dump runs nightly and tables are bloating. What is the connection?

Expected Outcome

You have compared the four formats, read a dump’s table of contents, and produced the specific failure that makes logical restores untrustworthy: complete data, correct row counts, non-zero exit status that a pipe concealed, and no permissions at all.

The procedure to carry away:

# Backup: two commands, always.
pg_dumpall --globals-only -f globals.sql
pg_dump -Fd -j 4 -f shop.dir shop

# Restore: globals first, and check the status of the right command.
psql -f globals.sql
createdb shop
pg_restore -d shop -j 4 shop.dir
rc=$?; [ "$rc" -eq 0 ] || echo "RESTORE FAILED: $rc"

# Verify as the application, not as a superuser counting rows.
psql -d shop -c "SET ROLE app_role; SELECT count(*) FROM sales.orders;"

Troubleshooting

pg_dump reported success and the restore is missing roles, permissions or tablespaces. They are not in a pg_dump output at all. pg_dumpall --globals-only is a second, separate command, and Task 4 exists so that you meet this in a lab rather than during a restore.

pg_dump ... | gzip > out.gz exited zero and the dump is truncated. $? after a pipeline reports the last command’s status, so gzip succeeding masks pg_dump failing. This was found by executing the lab rather than by reading it. Use ${PIPESTATUS[0]} in bash, or set -o pipefail, or — best — write to a file with -f and check that command’s status directly.

The restored database has the right row counts and the application cannot use it. You verified as a superuser. Row counts prove the data arrived; they prove nothing about privileges. Verify with SET ROLE to the application’s own role, as the Expected Outcome procedure does.

pg_restore -j fails on a plain-format dump. Parallel restore requires the directory or custom format. Plain SQL is replayed by psql, in one stream.

pg_restore reports errors about existing objects. The target database is not empty. Restore into a freshly created database, or use --clean --if-exists deliberately — knowing that it drops things.

Foreign key or ownership errors during restore. Globals were not restored first, so the roles the dump references do not exist. Order matters: globals, then createdb, then pg_restore.

Cleanup

docker exec rbpg-base18 psql -U postgres -X -c "DROP DATABASE IF EXISTS shop;"
docker exec rbpg-base18 psql -U postgres -X -c "DROP ROLE IF EXISTS shop_reader;"
docker exec rbpg-base18 rm -f /tmp/shop.dump /tmp/globals.sql
docker exec rbpg-lab01 rm -rf /backup
docker exec -u postgres rbpg-lab01 psql -X -c "DROP DATABASE IF EXISTS shop;"
docker exec -u postgres rbpg-lab01 psql -X -c "DROP ROLE IF EXISTS shop_reader;"
rm -f /tmp/shop.dump /tmp/globals.sql

Production notes

• Two commands make a logical backup, not one. pg_dumpall --globals-only for roles, passwords and tablespaces, and pg_dump per database for the contents.
• Never check the exit status of a pipeline and believe it. Write to a file and check the producing command, or set pipefail. This is the single most common way a backup job reports success while producing nothing usable.
• Verify a restore as the application role. A superuser can read a database with no grants at all, which is exactly the condition that makes the restore useless.
• Treat the globals dump as a credential file. It contains role password verifiers, and it is the file most often committed to a repository by accident.
• Directory format with -j is the default choice for anything large: it restores in parallel, and its table of contents lets you restore a subset without unpacking everything.

What You Learned

• Four formats, and only two restore in parallel. Directory and custom carry a table of contents; plain and tar do not.
• pg_dump does not contain roles, passwords, tablespaces or cluster-wide settings. pg_dumpall --globals-only does.
• $? after a pipe is the pipe’s status, which is how a truncated dump reports success.
• Row counts are not verification. A restore can carry every row and no privilege, and the application will fail on the first statement.
• The order is globals, create, restore, and getting it wrong produces ownership errors that look like data errors.
• pg_restore can restore a subset from the table of contents, which is what makes the directory format worth the extra file.