How do I set up point-in-time recovery in PostgreSQL?

Point-in-time recovery (PITR) in PostgreSQL lets you restore a database to a specific moment after a failure or mistake by combining a base backup with the WAL (Write-Ahead Log) records generated afterward.

What you need

• A full base backup
• Archiving of WAL files
• A restore process that can replay WAL up to a target time, transaction ID, LSN, or restore point

---

1) Configure PostgreSQL for WAL archiving

Edit postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

Recommended additional settings

max_wal_senders = 3
wal_keep_size = 1GB
archive_timeout = 60s

Notes:

• archive_command should copy each completed WAL segment to durable storage.
• Replace /archive/ with your backup target location or object storage script.
• Make sure the PostgreSQL service user can write there.

Restart PostgreSQL after changing settings.

---

2) Take a base backup

Use pg_basebackup:

pg_basebackup -D /backups/base_$(date +%F_%H%M) -Fp -Xs -P -U backupuser

Common options:

• -D: destination directory
• -Fp: plain format
• -Xs: include WAL during backup
• -P: progress
• -U: replication/backup user

You can also use -R to write recovery settings for standby use, though PITR restore usually needs custom recovery config.

---

3) Keep WAL files archived

After the base backup, PostgreSQL will keep archiving WAL segments via archive_command.

Verify archiving works:

SELECT * FROM pg_stat_archiver;

Look for increasing:

• archived_count
• no persistent failed_count

Also test that WAL files appear in your archive directory.

---

4) Restore for PITR

When you need to recover:

1. Stop PostgreSQL
2. Move aside the broken data directory
3. Restore the base backup into the data directory
4. Configure recovery
5. Start PostgreSQL

PostgreSQL 12+ recovery config

Create a file in the data directory:

restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-10-07 10:15:00'
recovery_target_action = 'pause'

Then create an empty file:

touch /var/lib/postgresql/data/recovery.signal

For older versions, use recovery.conf instead.

Restore example

cp -a /backups/base_2026-10-07_0900/* /var/lib/postgresql/data/
touch /var/lib/postgresql/data/recovery.signal

Start PostgreSQL and it will:

• restore the base backup
• fetch archived WAL files using restore_command
• replay WAL until the target time/LSN/transaction/restore point

---

5) Choose a recovery target

You can recover to:

• time: recovery_target_time
• transaction ID: recovery_target_xid
• LSN: recovery_target_lsn
• named restore point: recovery_target_name

Example restore point

Before a risky change:

SELECT pg_create_restore_point('before_migration');

Then recover to it:

recovery_target_name = 'before_migration'
recovery_target_action = 'pause'

---

6) After recovery

Once PostgreSQL reaches the target:

• it will pause, stop, or promote depending on recovery_target_action
• if you’re satisfied, promote the instance:

SELECT pg_promote();

Or let it auto-promote if configured accordingly.

---

Best practices

• Store backups and WAL in separate durable storage
• Test restores regularly
• Automate backup/archiving with scripts or tools like:
  • pgBackRest
  • Barman
  • WAL-G
• Keep enough WAL retention to cover your recovery window
• Monitor archiving failures closely

---

Strong recommendation

For production, use a backup tool like pgBackRest or Barman rather than rolling your own scripts. They handle:

• incremental backups
• WAL management
• verification
• retention policies
• easier PITR restores

If you want, I can give you:

1. a minimal manual PITR setup, or
2. a pgBackRest/Barman production setup.