postgresql point in time restore

PostgreSQL “point-in-time restore” (PITR) is the process of restoring a database to an exact moment in time by:

1. Restoring a base backup
2. Replaying archived WAL (Write-Ahead Log) records up to a target time/LSN/transaction

What you need

• A base backup of the cluster
• WAL archiving enabled and working
• Access to the backup and archived WAL files
• A separate restore environment/server

---

Typical setup for PITR

1) Enable WAL archiving

In postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'

Make sure /archive is durable and backed up.

2) Take a base backup

Example:

pg_basebackup -h primary-host -U replicator -D /backups/base -Fp -Xs -P

This captures the starting state for recovery.

3) Keep archiving WAL

PostgreSQL will continuously archive WAL segments. Those are what let you “replay” changes to a specific point.

---

Restoring to a point in time

1) Stop PostgreSQL on the restore host

systemctl stop postgresql

2) Replace data directory with base backup

Remove or move the current data directory, then restore the base backup contents into the PostgreSQL data directory.

3) Configure recovery

Newer PostgreSQL versions use postgresql.conf plus restore_command, and optionally recovery.signal.

Example postgresql.conf:

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

Then create the signal file:

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

Or for standby mode:

touch standby.signal

4) Start PostgreSQL

systemctl start postgresql

PostgreSQL will:

• Restore the base backup
• Pull archived WAL files
• Stop when it reaches the requested time

---

Recovery targets you can use

You can restore to:

• A specific time

  recovery_target_time = '2026-10-07 14:30:00 UTC'
  

• A specific transaction ID

  recovery_target_xid = '123456'
  

• A named restore point

  recovery_target_name = 'before_deployment'
  

• A specific LSN

  recovery_target_lsn = '0/7000000'
  

---

Common pitfalls

• No WAL archive → PITR won’t work
• Missing WAL files → you can only restore as far as available WAL
• Wrong time zone in recovery_target_time
• Base backup not consistent
• restore_command incorrect
• Permissions on archive directory

---

Quick example workflow

1. Enable WAL archiving
2. Take base backup
3. Archive WAL continuously
4. When disaster happens:
   • Stop server
   • Restore base backup
   • Set restore_command
   • Set recovery target
   • Start server
5. Verify data and promote if needed

---

If you want the simplest operational tool

Consider using backup tools like:

• pgBackRest
• Barman
• WAL-G

They automate base backups, WAL archiving, and PITR.

If you want, I can give you:

• a step-by-step PITR guide for PostgreSQL 14/15/16
• a pgBackRest-based setup
• or a real example restoring to a specific timestamp