Backups & Recovery

Backups are copies you can use to get data back after deletion, corruption, bad deploys, hardware loss, or operator mistakes. Recovery is the practiced path from "something is wrong" to "the right data is serving users again."

The beginner mistake is thinking the backup file is the finish line. The finish line is a restore that works, under pressure, with a known amount of acceptable data loss and downtime.

Logical Backups: SQL-Level Copies

A logical backup captures database objects and data as SQL-level content: schemas, tables, indexes, constraints, and rows. In PostgreSQL, the usual tool is pg_dump. It connects like a client, reads a consistent snapshot, and writes a dump file that can be restored into another database.

The browser lab cannot run pg_dump because pg_dump is an external PostgreSQL command-line client, not SQL. A real terminal command looks like this:

pg_dump \
  --format=custom \
  --file=backups/appdb-2026-07-10.dump \
  --dbname="$DATABASE_URL"

The custom format is meant for pg_restore, which can list contents, choose what to restore, and restore in a controlled way:

createdb appdb_restore_drill
 
pg_restore \
  --dbname=appdb_restore_drill \
  --clean \
  --if-exists \
  backups/appdb-2026-07-10.dump

Logical backups are portable and easy to inspect. They are good for moving a database between environments, restoring one database, or keeping a long-lived archive. They are not the whole recovery story for a busy production server, because restoring a large logical dump can take longer than the outage window allows, and it restores to the moment the dump was taken.

You can still practice the shape here. This tiny live script creates current data, creates a dump-shaped restore script, "restores" into a new table, and compares row counts and totals.

CREATE TABLE live_orders (
  order_id integer PRIMARY KEY,
  customer_email text NOT NULL,
  amount numeric(8, 2) NOT NULL
);
 
INSERT INTO live_orders (order_id, customer_email, amount)
VALUES
  (1, 'ada@example.com', 42.00),
  (2, 'ken@example.com', 18.50),
  (3, 'priya@example.com', 71.25);
 
CREATE TABLE restored_orders (
  order_id integer PRIMARY KEY,
  customer_email text NOT NULL,
  amount numeric(8, 2) NOT NULL
);
 
INSERT INTO restored_orders (order_id, customer_email, amount)
VALUES
  (1, 'ada@example.com', 42.00),
  (2, 'ken@example.com', 18.50),
  (3, 'priya@example.com', 71.25);
 
SELECT 'live' AS source,
       count(*) AS row_count,
       sum(o.amount) AS total_amount
FROM live_orders o
UNION ALL
SELECT 'restored' AS source,
       count(*) AS row_count,
       sum(o.amount) AS total_amount
FROM restored_orders o
ORDER BY source;

CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 3
  source  | row_count | total_amount
----------+-----------+--------------
 live     |         3 |       131.75
 restored |         3 |       131.75
(2 rows)

The point is not that this is a real backup. It is the review habit: restore somewhere separate, then prove the restored data matches what you expect.

Physical Backups And Point-In-Time Recovery

A physical backup copies the database cluster's files: table storage, indexes, transaction logs, and the server state needed to start PostgreSQL again. It is lower-level than pg_dump. Physical backup tooling is how large PostgreSQL systems usually recover quickly to a new server or storage volume.

The missing word is WAL: the write-ahead log. PostgreSQL records changes in WAL before data files are finally written. With a base physical backup plus archived WAL files, PostgreSQL can replay changes after the backup.

That enables point-in-time recovery (PITR): restore a base backup, replay WAL, and stop at a chosen time before the mistake.

# transcript shape only: paths and service commands differ by environment
tar -xf basebackups/appdb-base-2026-07-10.tar \
  -C /var/lib/postgresql/data
 
cat >> /var/lib/postgresql/data/postgresql.auto.conf <<'EOF'
restore_command = 'cp /backups/wal/%f %p'
recovery_target_time = '2026-07-10 14:03:00+00'
recovery_target_action = 'pause'
EOF
 
touch /var/lib/postgresql/data/recovery.signal
pg_ctl start -D /var/lib/postgresql/data

That is intentionally a transcript shape, not a recipe to paste into your laptop. Real PITR depends on where your data directory lives, how WAL is archived, how the service is managed, and whether your database is self-hosted or managed. The invariant is stable: base backup plus WAL archive plus chosen recovery target.

The Restore Drill Trap

A small restore drill transcript might look like this:

createdb appdb_restore_drill
 
pg_restore \
  --dbname=appdb_restore_drill \
  --clean \
  --if-exists \
  backups/appdb-2026-07-10.dump
 
psql \
  --dbname=appdb_restore_drill \
  --file=checks/restore_smoke_checks.sql

The smoke checks are ordinary SQL kept under review with the application:

CREATE TABLE restored_order_checks (
  check_name text PRIMARY KEY,
  expected_value numeric NOT NULL,
  actual_value numeric NOT NULL
);
 
INSERT INTO restored_order_checks (check_name, expected_value, actual_value)
VALUES
  ('orders row count', 3, 3),
  ('orders total amount', 131.75, 131.75);
 
SELECT c.check_name,
       c.expected_value,
       c.actual_value,
       c.expected_value = c.actual_value AS passed
FROM restored_order_checks c
ORDER BY c.check_name;

CREATE TABLE
INSERT 0 2
     check_name      | expected_value | actual_value | passed
---------------------+----------------+--------------+--------
 orders row count    |              3 |            3 | t
 orders total amount |         131.75 |       131.75 | t
(2 rows)

Good checks are boring and specific: row counts for critical tables, totals that catch partial restores, required extensions, app roles, and a smoke test that the application can connect with its normal least-privilege role from 19.1.

Retention, Encryption, And Off-Site Copies

Retention is how long you keep backups. One backup from last night is not a policy. A practical policy usually has layers: recent backups for fast recovery, older backups for accidental deletion discovered late, and longer archives when law or the business requires them.

Encryption means backups are unreadable without keys. It protects you when a backup file or storage account leaks, but it adds one hard requirement: the restore team must be able to access the keys during an incident. A perfectly encrypted backup with a lost key is just expensive noise.

Off-site copies protect against losing the place where the database lives. If the database and every backup sit in the same account, disk, region, or building, one failure domain can take them all. Off-site does not have to mean a specific vendor. It means a separate place, separate credentials where possible, and a restore path that has been tested.

Review backup policy with plain questions:

• Which databases are backed up?
• How often are base backups and logical dumps taken?
• Where are WAL archives stored?
• How long is each backup kept?
• Who can decrypt and restore them?
• When was the last successful restore drill?

RPO And RTO In Plain Words

Two recovery questions turn vague fear into an engineering target.

Recovery point objective (RPO) means "how much data can we afford to lose?" If backups run every 24 hours and there is no WAL archive, a failure right before the next backup could lose almost a day of writes. If WAL is archived continuously and restorable, the RPO can be much smaller.

Recovery time objective (RTO) means "how long can the system be down or degraded?" A logical dump that takes hours to restore might be fine for an internal reporting database and unacceptable for checkout. A physical backup plus WAL may restore faster, but only if the drill proves the full path works.

RPO and RTO are not just database settings. They are product and business decisions translated into backup frequency, WAL archiving, restore automation, runbooks, permissions, storage, monitoring, and drills.

Backups are the promise; recovery is the proof. Next, you will learn how to notice trouble before restore is the only option: monitoring, slow-query hunting, locks, and the dashboards that matter.