Database operations: PostgreSQL backups, restores and migrations
For systems administrators, DevOps engineers and developers who run PostgreSQL servers and need backups they can actually restore. You should be comfortable with basic SQL and the psql client (SQL fundamentals is a good start) and with the Linux command line. The course covers choosing between logical dumps, base backups and continuous WAL archiving, writing pg_dump, pg_dumpall and pg_restore commands, planning a point-in-time recovery, proving a backup by test-restoring it, and planning schema migrations and version upgrades that don't lock out users. It follows the PostgreSQL 18 documentation; check the documentation for your version. Your data protection rules and change process decide who may hold copies of production data and who approves restores and migrations. Ends with a supervisor-graded recovery scenario.
- Level
- Intermediate
- Length
- About 105 minutes
- Contents
- 5 lessons · final exam
- Status
- Published · updated 10 Oct 2026
Skills you'll practise
- Choose between a logical dump, a base backup and continuous WAL archiving for a recovery or migration requirement
- Write pg_dump, pg_dumpall and pg_restore commands for custom and directory formats, including parallel jobs and selective restores
- Plan a point-in-time recovery from a base backup and archived WAL, including the recovery target and the checks before users reconnect
- Verify that a backup can be restored by test-restoring it, checking the result and monitoring WAL archiving
- Plan a schema migration that limits locking, using CREATE INDEX CONCURRENTLY, NOT VALID constraints and lock_timeout, with a way back
Course outline
- 1.Choose a backup method: logical dump, base backup or continuous archivingLesson · 16 min
- 2.Write pg_dump, pg_dumpall and pg_restore commandsLesson · 18 min
- 3.Plan a point-in-time recovery from a base backup and archived WALLesson · 18 min
- 4.Verify a backup by test-restoring it and monitoring archivingLesson · 14 min
- 5.Plan a schema migration that limits locking and has a way backLesson · 16 min
- 6.Database operations: PostgreSQL backups, restores and migrations: knowledge checkKnowledge check · 15 questions
- 7.Database operations: PostgreSQL backups, restores and migrations: practical exerciseKnowledge check · 1 question
- 8.Final exam10 questions · passing it completes the course, so people who already know the material can test out
Sources it draws on
The lessons and questions are written from these references, so learners can go back to the original.
- PostgreSQL 18 documentation, Chapter 25: Backup and Restore (three approaches)
- PostgreSQL 18 documentation, 25.1 SQL Dump (pg_dump, restoring, pg_dumpall, large databases)
- PostgreSQL 18 documentation, 25.3 Continuous Archiving and Point-in-Time Recovery
- PostgreSQL 18 documentation: pg_dump
- PostgreSQL 18 documentation: pg_restore
- PostgreSQL 18 documentation: pg_basebackup
- PostgreSQL 18 documentation: pg_verifybackup
- PostgreSQL 18 documentation, 18.6 Upgrading a PostgreSQL Cluster
- PostgreSQL 18 documentation, 13.3 Explicit Locking (table-level lock modes)
- PostgreSQL 18 documentation: CREATE INDEX (building indexes concurrently)
- PostgreSQL 18 documentation: ALTER TABLE (NOT VALID, VALIDATE CONSTRAINT, rewrites)
- PostgreSQL 18 documentation, 19.11 Client Connection Defaults (lock_timeout)
See it with your own jobs and topics
Tell us about your team and we'll walk you through setup, from choosing jobs to your first skills check.