Project 5 — Backup, Monitoring, and Deployment

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Project 5 — Backup, Monitoring, and Deployment
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

A backup is recoverable only after restore testing. Logical `pg_dump` is portable and useful for migrations, while large disaster-recovery plans may need base backups plus WAL and point-in-time recovery. Monitoring must include slow queries, locks, replication lag, disk, connection saturation, and autovacuum health—not just uptime.

Connect it to a real scenario

Create a custom-format dump, restore into a fresh database, and verify row counts, checksums, and smoke queries. Document supported-major/current-minor policy, secret rotation, maintenance windows, RPO/RTO, and incident contacts.

Verify backups through restore tests
Set RPO/RTO with the business
Combine version policy, metrics, alerts, and runbooks

Try the working example

bash

# Logical backup
pg_dump --format=custom --no-owner \
  --dbname="$DATABASE_URL" \
  --file=tutorial_platform.dump

# Restore drill into a fresh database
createdb tutorial_platform_restore_test
pg_restore --exit-on-error --single-transaction \
  --dbname=tutorial_platform_restore_test \
  tutorial_platform.dump

psql tutorial_platform_restore_test -c "SELECT count(*) FROM app.tutorials;"

You should see

A fresh database restore succeeds and passes verification queries.

5-minute try-it

Design backup/PITR, alerting, and restore-drill schedules for RPO 15 minutes and RTO 60 minutes.

One important caution

Avoid backups on the same failure domain, untested restores, and treating a successful backup-job notification as proof of recovery.

PostgreSQL — Backup and Restore — PostgreSQL Global Development Group

Easy traps

Avoid backups on the same failure domain, untested restores, and treating a successful backup-job notification as proof of recovery.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Design backup/PITR, alerting, and restore-drill schedules for RPO 15 minutes and RTO 60 minutes.

You'll know it worked when: A fresh database restore succeeds and passes verification queries.