How to Use Point in Time Recovery in PostgreSQL

To use point in time recovery (PITR) in PostgreSQL, enable WAL archiving and take a base backup. Then, restore the base backup and replay WAL files up to the desired recovery time by setting recovery_target_time in the recovery configuration.

Syntax

Point in time recovery in PostgreSQL involves configuring recovery settings in a recovery.conf or postgresql.conf file during restore. Key parameters include:

• restore_command: Shell command to fetch WAL files.
• recovery_target_time: Timestamp to stop recovery.
• recovery_target_action: Action after recovery (usually pause or promote).

These settings tell PostgreSQL how to replay WAL logs to reach the exact point in time.

conf

restore_command = 'cp /path_to_wal_archive/%f %p'
recovery_target_time = '2024-06-01 15:30:00'
recovery_target_action = 'promote'

Example

This example shows how to perform PITR by restoring a base backup and replaying WAL files up to a specific time.

bash

# Step 1: Take base backup (run on primary server)
pg_basebackup -D /var/lib/postgresql/backup -F tar -z -P

# Step 2: Enable WAL archiving in postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /path_to_wal_archive/%f'

# Step 3: Restore base backup on recovery server
rm -rf /var/lib/postgresql/data/*
tar -xzf /var/lib/postgresql/backup/base.tar.gz -C /var/lib/postgresql/data/

# Step 4: Create recovery.conf with recovery target time
cat > /var/lib/postgresql/data/recovery.conf <<EOF
restore_command = 'cp /path_to_wal_archive/%f %p'
recovery_target_time = '2024-06-01 15:30:00'
recovery_target_action = 'promote'
EOF

# Step 5: Start PostgreSQL on recovery server
pg_ctl start -D /var/lib/postgresql/data

Output

waiting for server to start... done
server started
recovery in progress
recovery complete
server promoted to primary

Common Pitfalls

Common mistakes when using PITR include:

• Not enabling archive_mode and archive_command before failure, so WAL files are missing.
• Incorrect restore_command causing WAL files not to be found.
• Setting recovery_target_time outside the range of available WAL files.
• Forgetting to remove recovery.conf after recovery, which can cause the server to stay in recovery mode.

conf

## Wrong restore_command example
restore_command = 'cp /wrong_path/%f %p'

## Correct restore_command example
restore_command = 'cp /path_to_wal_archive/%f %p'

Quick Reference

Parameter	Description	Example Value
restore_command	Command to fetch WAL files during recovery	'cp /wal_archive/%f %p'
recovery_target_time	Timestamp to stop recovery	'2024-06-01 15:30:00'
recovery_target_action	Action after recovery completes	'promote' or 'pause'
archive_mode	Enable WAL archiving	on
archive_command	Command to archive WAL files	'cp %p /wal_archive/%f'

Key Takeaways

Enable WAL archiving before failure to use point in time recovery.

Use a base backup combined with WAL replay to restore to a specific time.

Set recovery_target_time and restore_command correctly in recovery configuration.

Verify WAL files exist for the recovery period to avoid incomplete recovery.

Remove recovery.conf after recovery to resume normal database operation.