Backup and PITR with pgBackRest

Full / differential / incremental backups with pgBackRest, continuous WAL archiving and point-in-time recovery (PITR). For a single server, streaming replication (primary + standbys) or a Patroni cluster; the repository can live on the same server, on a separate backup server (SSH) or in S3-compatible object storage. Covers encryption, compression, retention policy, cron/systemd scheduling and a test restore into a separate directory; the same guide also generates the in-place restore runbook for a disaster (including rebuilding a Patroni cluster from backup). How it differs from the alternatives: this is the physical backup tool recommended for production; for physical backups and PITR without installing an extra tool use the pg_basebackup + WAL archiving guide, and for logical backups (table/database dumps, moving between major versions) use the pg_dump / pg_restore guide.

Topology: 1 server, primary + standbys or a Patroni cluster; optional backup server

Scenarios

• pgBackRest installation + first backup + test restore — pgBackRest is not in place yet: software, repository, WAL archiving, first backup, scheduling and a test restore.
• Disaster: in-place restore (to the latest state or to a point in time) — Return the database, in place, to a moment covered by the backup and the archive: the latest point, a time, an xid, an LSN or a restore point. The cluster is shut down; changes made after the target are lost.
• Corrupt or deleted files in the data directory (repair with delta restore) — The server is intact but some data files cannot be read or have been deleted: --delta rewrites only the files that differ from the backup and replays to the end of the archive. The cluster is shut down.
• Restore a single database only (--db-include) — The cluster stays up: the single database is restored to a temporary instance (separate directory and port), dumped with pg_dump and loaded into the live server under a new name. Use this card for a single dropped table too (with pg_dump -t from there).
• Restore the backup to another server (clone / test / reporting) — The live server is not touched. The backup is opened on a new server; WAL archiving is disabled so the clone does not write to the live repository. An encrypted repository requires the key.
• The server is lost completely: open the backup on a new server and replace it — Disk or server loss: the new server is opened from the backup and becomes the new primary; WAL archiving and backups continue in the same stanza. The old server must stay shut down.
• Build a new standby from the backup (--type=standby) — A new standby without putting copy load on the primary: the files come from the backup, and the missing WAL is filled in from the archive and via streaming. The primary stays up.
• Are the backups really sound? (info / check / verify and a restore drill) — Read-only checks and a restore drill into a separate directory that does not touch the live server. A monthly verification job (cron) is set up as well.
• Change the retention period and remove old backups (expire) — Changes the repo1-retention-full/diff settings, first shows with --dry-run what would be removed, then runs expire. A removed backup cannot be brought back.

Before you start

• PostgreSQL … is installed and running (pg-install guide)…; this server is the primary…. Data directory ….
• Patroni cluster … is running (Patroni installation): one Leader, the others streaming replicas. On all nodes the data directory is …, the Patroni configuration …, and the REST API password is in /etc/patroni/patroni.env.
• No other archiving/backup tool (Barman, WAL-G, a hand-written archive_command) is configured; if there is one, remove it first. If two tools move the same WAL to different places, both end up incomplete.… With Patroni the current value is in the output of patronictl show-config.…
• The database size is known. The repository needs roughly (number of fulls + 1) × compressed size + WAL archive (with zst most databases compress to 1/3–1/5).
• The repository directory … is on a disk/NFS share separate from the database and mounted persistently (fstab).
• A separate disk with enough space for … is mounted on the backup server. The backup server runs the same operating system family as the database.
• The servers resolve each other's names (…, …) and 22/tcp (SSH) is open between them.
• The standbys (………, ……) are streaming standbys receiving WAL from the primary and use the same PGDATA path as the primary (…).
• Bucket … has been created; an access key (access key + secret key) with read/write/delete permissions on this bucket only is ready. The bucket has no lifecycle rule that deletes objects (pgBackRest manages retention). The server can reach the endpoint.
• Clocks are synchronized (NTP). The PITR target time and S3 signatures depend on correct time.
• The servers can reach the PGDG and EPEL repositories (or an internal mirror); pgBackRest's dependency libssh2 comes from EPEL.
• A place to store the encryption key (password vault) is ready. If the key is not stored off the server, the backups cannot be opened after a server loss.

Phases of the guide

• Installing the pgBackRest software — Add the PGDG repository (backup server) · Add the PGDG repository (backup server) · Enable the EPEL repository · Install pgBackRest · Create the pgbackrest user · Remove the old configuration file
• Repository preparation — Create the repository directory · Create the repository directory · Generate an SSH key (backup server) · Generate an SSH key (database servers) · Authorize the backup server's key · Authorize the database servers' keys …
• pgBackRest configuration — Write the configuration file · Write the configuration file (backup server) · Write the configuration file (database servers) · Enter the S3 access keys · Generate the encryption key · Write the encryption key on server … …
• Stanza and first backup — Create the stanza · Create the stanza · Verify the configuration (check) · Take the first full backup · Take the first full backup · Show the repository status (info) …
• Backup scheduling — Verify the path of the pgbackrest command · Write the backup script · Set the script's ownership and permissions · Create the systemd service and timers · Test the schedule (trigger manually) · Prepare the cron service …
• Verification and recovery test — Check that WAL archiving works · Write test data and record the target time · Prepare the test restore directory · Restore into a separate directory up to the target time · Start the test cluster on a separate port · Verify the recovered data …
• Monitoring and maintenance — Verify repository integrity (verify) · Verify repository integrity (verify) · Monitor the age of the last backup · Monitor the age of the last backup · Second copy and key storage
• Diagnosis: corrupt or missing file? — Find the error and the affected file in the log · Identify the corrupt object · Verify that a sound backup and archive exist
• Pre-restore checks — List the backups · Check the application connections · Stop the standbys · Stop PostgreSQL · Copy the current data directory (if there is room)
• In-place restore — Restore (--delta) · Start PostgreSQL and wait for recovery to finish · Check the data at the target (pause) · End recovery and promote
• Verification — Clean up the restore settings · Check the cluster state · Check the application data · Verify that the corruption is gone (amcheck) · Verify that archiving continues · Take a new full backup …
• Before the restore: stop the Patroni cluster — List the backups · Save the cluster state and the dynamic configuration · Stop the applications · Stop Patroni on the replicas · Stop Patroni on the leader · Copy the current data directory (if there is room)
• Verification and cleanup (Patroni) — Verify that archiving continues · Take a new full backup · Check the cluster and application access · Remove the moved-aside data directories
• Patroni: rebuild the damaged node (reinit) — Find which node has the corruption · If the damaged node is the leader, hand over leadership · Rebuild the damaged replica (reinit)
• Backup verification and restore drill — Backup state and the age of the last backup (info) · Verify that archiving is working right now (check) · Verify repository integrity (verify) · Prepare the drill directory · Restore the latest backup into the drill directory · Start the drill instance and wait for recovery …
• Monthly verification schedule — Write the monthly verification script · Set the script's permissions and try it with cron's environment · Write the monthly cron job · Set the cron file permissions
• Change the retention policy — Current retention setting and backups · Change the retention setting · Show what would be removed (dry-run) · Remove old backups (expire) · Check the repository usage and the scheduling