Alright, let’s get serious for a moment. You’ve been taking pg_dump backups like a responsible human, and that’s great. But let’s be honest: if your main database server decides to have a catastrophic meltdown right now, how much data are you willing to lose? The time between your last pg_dump and the moment of failure? That could be hours, or even days. Unacceptable. We need a better safety net. Enter Continuous Archiving and Point-in-Time Recovery (PITR). This isn’t just a backup; it’s a time machine for your database.

The core idea is brilliantly simple, almost cheating. PostgreSQL writes all changes to its data files only after first writing a record of the change to a Write-Ahead Log (WAL). We’re going to continuously save copies of these WAL files to a secure, separate location (this is the “continuous archiving” part). Then, if everything goes pear-shaped, we can take a base backup of the data directory, feed it all the archived WAL files, and literally replay your database’s entire history right up to the exact second before the disaster. That’s the “point-in-time recovery.” You’re not restoring a backup; you’re replaying every transaction.

The Three Pillars of PITR

For this to work, you need three things configured perfectly. Get one wrong, and your beautiful time machine becomes a very expensive paperweight.

  1. wal_level = replica (or higher): This is the minimum. It ensures the WAL files contain enough information to not just crash-recover, but to be replayed on a completely different server. Don’t even think about trying this with wal_level = minimal; it’s like trying to rebuild a novel from nothing but the chapter titles.
  2. archive_mode = on: This tells PostgreSQL, “Hey, I want to save these WAL files somewhere else after you’re done with them.” It’s the master switch.
  3. archive_command: This is the heart of the operation. It’s a shell command PostgreSQL executes to ship each finished WAL segment to your archive. This is where you define your storage strategy: scp, rsync, aws s3 cp, cp to an NFS share—you name it. The command must return a zero exit status on success.

Here’s a realistic example using rsync to a remote server. You’d put this in your postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/archive/wal/%f && rsync -a %p postgres@backup-server:/mnt/archive/wal/%f'

Let’s crack that archive_command open. %p is the full path to the WAL file PostgreSQL just finished with. %f is just the filename. The whole command reads: “If the file doesn’t already exist in the archive (a cheap safeguard against duplicates), rsync it to the backup server.” The test ! -f part is a classic pitfall—without it, a poorly timed restart could cause PostgreSQL to try and archive the same segment twice, which would make rsync fail and cause a panic.

Taking a Base Backup

A pg_dump won’t cut it here. You need a physical file-system level copy of the entire data directory, but it has to be taken in a way that’s consistent with the WAL files we’re archiving. The workhorse for this is pg_basebackup. It connects to the server and pulls down the entire data directory over a PostgreSQL connection.

# This is a typical command. Run it on your backup server, targeting your primary.
pg_basebackup -h primary-db-host -U postgres -D /var/lib/postgresql/14/backup_instance -Fp -P -v -R -X stream

The flags are important: -Fp for plain format (a copy of the files, not a tarball), -P for progress, -v for verbose. The two critical ones are:

  • -X stream: This forces the backup to stream the WAL files needed for consistency during the backup. This is non-negotiable. Without it, your base backup might be internally inconsistent and useless for recovery.
  • -R: This is a godsend. It automatically creates a standby.signal file and populates postgresql.auto.conf with the primary connection info. This is what will allow this backup to seamlessly become a replica later, which is the entire basis for replication. For now, it also sets up the restore_command for recovery.

The Recovery Configuration

Now for the magic trick. Let’s say your primary server exploded. You have your base backup on a new machine and all your WAL archives. You need to tell PostgreSQL how to perform the recovery.

The key file is recovery.conf (PostgreSQL 12 and above: it’s now postgresql.conf and a standby.signal file, but the settings are the same). You place this in the data directory of your restored base backup. The most critical setting is restore_command: it’s the mirror of archive_command. It’s the command PostgreSQL will use to fetch WAL files from your archive when it needs them during recovery.

# This would be your restore_command in recovery.conf
restore_command = 'cp /mnt/archive/wal/%f %p'

# Or if you're using S3, something like:
restore_command = 'aws s3 cp s3://my-wal-bucket/wal/%f %p'

To recover to a specific point in time, you use recovery_target. Want to stop right before that idiot dropped the production table?

recovery_target_time = '2023-10-27 14:45:00 EST'
recovery_target_inclusive = false

Setting inclusive = false means “stop right before this timestamp,” which is usually what you want. You can also recover to a specific transaction ID (recovery_target_xid) or a named restore point you created earlier with pg_create_restore_point('before_that_dangerous_migration').

The Single Biggest Gotcha

Here’s the thing they don’t tell you in the cute tutorials: Your base backup is useless without every single WAL file generated from the moment the backup started until the moment you want to recover. The backup is the first chapter of the book. The WAL files are every page after that. Lose one page, and the story ends in a cliffhanger. This is why monitoring your archive destination is absolutely critical. If your archive_command starts failing and gaps develop in your WAL sequence, your ability to perform PITR is compromised from that point forward. You’re back to only being able to recover to your last good base backup. Set up alerts on the archive destination. Test the restore process. Regularly. Your job depends on it.