PostgreSQL backup and recovery
You set up PostgreSQL backups with pg_dump formats, pg_basebackup, WAL archiving and pgBackRest, recover to a point in time and prove restores with regular drills.
Level 301 · 15 lessons · 12 practice labs
Lessons
Logical backups
- pg_dump formats: plain, custom, directory
- Selective restore with pg_restore
- pg_dumpall and cluster-wide objects
- Parallel dump and restore
- Short task: restore one table from yesterday’s backup
Physical backups and WAL
- Physical backups with pg_basebackup
- Setting up WAL archiving
- Recovery targets: time, transaction and name
- Setting up pgBackRest and its repository
- Full, differential and incremental backups
- Short task: recover to just before a bad UPDATE
Assurance
- Verifying backups: checksums and test restores
- Retention policy and encryption
- Measuring restore time
- Capstone: a disaster recovery drill
Practice labs
- Restore one table from yesterday's backup: Bring back only the deleted campaign table from a custom-format backup with pg_restore.
- Back up roles and tablespaces too: Notice missing roles on a database moved to a new server and add global objects with pg_dumpall.
- Dump and restore a large database in parallel: Halve backup and restore time with the directory format and parallel jobs.
- Take a physical backup with pg_basebackup: Take a physical backup of the running cluster and verify it by starting it on a separate port.
- Turn on WAL archiving and verify it: Set archive_command, force a WAL switch and check the file lands in the archive.
- Fix a failing archive command: Diagnose a failing archive that fills pg_wal from the statistics view and fix it safely.
- Recover to a point in time: Recover the cluster to a given time from a base backup and the WAL archive.
- Return to just before a bad UPDATE: Find the transaction ID of an UPDATE that zeroed all prices and stop recovery just before it.
- Recover to a named restore point: Create a restore point before a risky migration, break the migration and recover to that point to compare data.
- Set up a backup repository and full backup: Configure the backup tool's repository, take the first full backup and read its info output.
- Set retention with differential and incremental backups: Set up a weekly full, daily differential and hourly incremental schedule with retention rules.
- Prove a backup really restores: Verify backup integrity with checksum checks and a test restore and report the recovery time.
About this course
You set up PostgreSQL backups with pg_dump formats, pg_basebackup, WAL archiving and pgBackRest, recover to a point in time and prove restores with regular drills.
What you will learn
- Logical backups
- Physical backups and WAL
- Assurance
Prerequisites
You should be comfortable with the level 101 and 201 topics of the same path.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 301; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.