Lab 15: Postgres backup consistency — pg_dump vs filesystem snapshot

Objective

Demonstrate the difference between an application-consistent backup (pg_dump) and a crash-consistent backup (tar of the data directory while Postgres runs).

Tasks

Task 1: Start Postgres with sample data

docker run -d --name pg-lab -e POSTGRES_DB=app -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=secret postgres:16
sleep 5

docker exec pg-lab psql -U app -d app -c "
  CREATE TABLE orders (id serial PRIMARY KEY, sku text, qty int);
  INSERT INTO orders (sku, qty) SELECT 'sku-' || g, g FROM generate_series(1, 10000) g;
"

docker exec pg-lab psql -U app -d app -c "SELECT count(*) FROM orders;"
# 10000

Task 2: Logical dump (application-consistent)

docker exec pg-lab pg_dump -U app -d app -Fc -f /tmp/dump.custom
docker cp pg-lab:/tmp/dump.custom ./dump.custom
ls -la dump.custom

Task 3: Filesystem tar (crash-consistent)

docker exec pg-lab bash -c 'tar czf /tmp/dump.tar.gz -C /var/lib/postgresql/data .'
docker cp pg-lab:/tmp/dump.tar.gz ./dump.tar.gz
ls -la dump.tar.gz

Task 4: Restore the logical dump into a fresh container

docker run -d --name pg-restore -e POSTGRES_DB=app -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=secret postgres:16
sleep 5

docker cp dump.custom pg-restore:/tmp/dump.custom
docker exec pg-restore pg_restore -U app -d app --clean --if-exists /tmp/dump.custom
docker exec pg-restore psql -U app -d app -c "SELECT count(*) FROM orders;"
# 10000

This works cleanly. The dump is logical, ordered, and Postgres can replay to a consistent state.

Task 5: Restore the tar (probably fails or produces corruption)

docker run -d --name pg-restore2 -e POSTGRES_DB=app -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=secret postgres:16
sleep 5

# Stop the new container so we can extract over its data dir
docker stop pg-restore2
docker run --rm -v $(docker inspect pg-restore2 --format '{{.Mounts}}' | ... ):/data ...

# (For brevity: the tar restore typically fails or produces a
# database that won't start because torn pages exist between
# the WAL checkpoint and the live writes.)

The takeaway: tar of a live data directory is crash-consistent. On restore, Postgres may detect torn pages, refuse to start, or start but produce errors under load.

Task 6: Cleanup

docker rm -f pg-lab pg-restore pg-restore2
rm -f dump.custom dump.tar.gz