PostgreSQL in Practice #9 Backups, PITR, Upgrades: The Art of Rewinding to Just Before the Incident

The final chapter’s topic is preparation for the worst day. Basics chapter 9’s pg_dump was a good start, but it had the limit of “nothing newer than last night’s dump.” Production databases aim higher: rewinding to just before the incident. The technique is PITR, and the raw material is the WAL we met last chapter.

Replication is not backup #

First, the common misconception. “We have a standby, so backups are covered” is wrong. Replication defends against failures; backups undo mistakes. Commit an accidental DELETE FROM users and that DELETE is faithfully replayed on the standby milliseconds later. Fail over all you want — you now have one more database with the data already gone. If replication is the answer to hardware failure, the answer to human error and software bugs is a backup that can rewind time. You need both.

PITR: base backup + WAL replay #

The mechanism uses the same raw material as the replication from the last chapter, but differently. Streaming replication replays WAL in real time to build “a copy of the present”; PITR replays archived WAL only up to a chosen moment to build “a copy of the past.” Two ingredients:

# 1) periodic base backups (can be taken while running; e.g. nightly)
pg_basebackup -D /backup/base -Ft -z -P

# 2) postgresql.conf: keep copying WAL to an archive
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'   # in production, to remote storage such as S3

Recovery is “unpack the base backup, set the target time, replay WAL.”

restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2026-08-31 14:29:00+09'   # if the incident was at 14:30, stop just before

With this combination the recovery point is no longer “last night” but any moment for which WAL survives. Figuring out the incident’s timestamp (logs, traces in pg_stat_statements) is part of the recovery work. In practice the standard is not wiring all this by hand but managing it with a dedicated tool like pgBackRest — and a managed service’s “point-in-time restore” feature is exactly this structure productized. And basics chapter 9’s sentence still applies: a PITR setup without restore rehearsals is not a setup, it’s a hope. Run real recovery drills periodically.

Major upgrades: three roads #

PostgreSQL ships a major version yearly, each supported for five years. Postpone and you’ll someday have to leap several versions at once, so upgrades belong on the operations calendar as a regular event. The roads are three.

The working default is pg_upgrade. It exploits data-file format compatibility to convert only the catalogs, so even a database of hundreds of GB finishes within minutes of downtime. If even that is too much, last chapter’s logical replication remains: build the new-version server alongside and switch connections. Whichever road, the shared discipline is the same: rehearse the same path on staging, run ANALYZE right after upgrading (statistics do not carry over), and check extension compatibility beforehand.

Closing the track #

That’s the nine practice chapters — eighteen in the track from the basics. From zero-downtime migrations through connections, diagnostics, index strategy, VACUUM, locks, partitioning, replication, and finally PITR. Looking back, one principle kept repeating: understand the two pillars, MVCC and WAL, and the problems of operations explain themselves as consequences. Dead tuples, the rules of locking, replication, PITR — all of them. The next time you meet an unfamiliar symptom, come back to these two pillars and ask: how do the versions accumulate, and where does the log flow? May this track be the map for that question.

Summary #

• Replication is for failures; backups are for mistakes. A DELETE accident replays on the standby too — failover can’t undo it.
• PITR = base backup (pg_basebackup) + WAL archiving + replay to a target time. The recovery point becomes “any moment.”
• The practical setup standard is a tool like pgBackRest, and managed point-in-time restore is the same structure as a product. Rehearsals are mandatory.
• The default for major upgrades is pg_upgrade (minutes of downtime); for zero downtime, switch via logical replication. Don’t forget ANALYZE right after.
• The track’s conclusion is two pillars: understand MVCC and WAL, and operational problems unravel as their consequences.