Database Management · Advanced level

Advanced level · Module 1: PostgreSQL Administration · Lesson 3 of 4

Backups and Restores in PostgreSQL

Choose between logical and physical backups, automate them and practise restores.

In this lesson you will

  • Create logical backups with pg_dump and restore with pg_restore
  • Explain physical backups and point-in-time recovery

Key concepts

pg_dump / pg_restorephysical base backupWAL archivingpoint-in-time recovery

The lesson

pg_dump creates a logical backup of one database — the SQL needed to rebuild it — which is portable and good for smaller databases. pg_restore loads it back, optionally selecting tables.

Physical backups copy the data files. Combined with archived write-ahead logs (WAL), they allow point-in-time recovery: restoring the database to how it was at, say, 10:14 just before a mistaken DELETE.

Automate backups, keep several generations, store copies off-site and encrypted, and run restore drills on a schedule. Measure how long a restore takes.

Step by step

  1. Back up the database with pg_dump.
  2. Restore it into a new database with pg_restore.
  3. Schedule a daily backup job.
  4. Run and time a restore drill.
Workflow

A backup routine you can actually rely on

The cycle that turns 'we take backups' into 'we can recover': back up, verify, store away, test the restore.

  1. Back up

    pg_dump (or a physical base backup) runs automatically on a schedule that meets the RPO.

    scheduler

  2. Encrypt and verify

    The backup file is encrypted and checked: non-zero size, completes without errors, checksum recorded.

    backup script

  3. Store away

    One copy stays nearby for quick restores; another goes off-site or to cloud storage.

    administrator

  4. Keep generations

    Daily copies for two weeks and monthly copies for a year, so an old mistake can still be undone.

    retention policy

  5. Test the restore

    Restore into a separate database, check row counts and key reports, and record how long it took.

    administrator

Repeat automatically; run a restore test at least monthly and after any change to the backup setup.

Why it works

Replication is not a backup: a mistaken DELETE is copied to the replica within seconds.

Practice activity

Automate the health programme's backups and complete a timed restore drill.

Expected result: Backups run automatically and a restore completes, with its duration recorded.

Check your understanding

  1. What does point-in-time recovery allow?

    Show answer

    Restoring the database to its state at a specific moment, using archived WAL.

  2. Why time restore drills?

    Show answer

    To know how long recovery really takes and whether it meets targets.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.