Point-in-Time Recovery (PITR) is a powerful feature in PostgreSQL that allows database administrators to restore a database to a specific moment in time—usually just before a failure, human error, or data corruption event. It’s an essential part of a solid disaster recovery plan.

PITR relies on two key elements:

1. Base Backup – A full copy of the database at a specific point.
2. WAL (Write-Ahead Logging) – All changes after the base backup are written to WAL files, which are replayed during recovery.

Advantages

1. Undoing accidental DROP or DELETE operations.
2. Recovering from logical corruption or bugs.
3. Setting up test environments by cloning data at a specific time.

Example

1. Enable WAL Archiving in postgresql.conf

Edit the following parameters:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Restart PostgreSQL:

sudo systemctl restart postgresql

2. Take a Base Backup

Using pg_basebackup:

pg_basebackup -D /var/lib/postgresql/base_backup -F tar -X fetch -P -U postgres

3. Simulate a Disaster

Assume the table was dropped and now you need recovery.

Stop PostgreSQL:

sudo systemctl stop postgresql

4. Restore from Backup

cd /var/lib/postgresql/data
rm -rf *
tar -xvf /var/lib/postgresql/base_backup/base.tar

5. Create a recovery.conf or set recovery parameters

touch standby.signal

Edit postgresql.conf or create postgresql.auto.conf:

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2025-06-17 12:29:59'

Start PostgreSQL

sudo systemctl start postgresql

PostgreSQL will apply WAL files and stop exactly at the given timestamp.

You can confirm recovery by checking the logs:

cat /var/log/postgresql/postgresql-13-main.log

admin