PostgreSQL Point-In-Time Recovery (PITR)
Guide
Purpose
This document provides a detailed, step-by-step guide for performing Point-
In-Time Recovery (PITR) in PostgreSQL. It is intended for new database
administrators and will serve as an internal policy and reference for future
PITR processes.
Table of Contents
1. Overview
2. Prerequisites
3. Backup Strategy
4. Creating a Base Backup using pg_basebackup
5. Archiving WAL Files
6. Preparing for PITR
7. Performing the PITR
8. Post-Recovery Steps
9. Common Pitfalls and Troubleshooting
1. Overview
Point-In-Time Recovery (PITR) allows restoring a PostgreSQL database to a
specific moment in time by using a base backup and write-ahead log (WAL)
files. This is crucial in cases of accidental data deletion, corruption, or
malicious actions.
2. Prerequisites
PostgreSQL configured for WAL archiving
File system access to PostgreSQL data directories
Sufficient disk space for backups and archived WALs
Basic knowledge of Linux commands
3. Backup Strategy
Key Directories:
Base Backup Directory: Where your pg_basebackup files are stored
WAL Archive Directory: Directory for storing archived WALs
Recommended Configuration in [Link]:
archive_mode = on
archive_command = 'cp %p /path_to_archive/%f'
wal_level = replica
max_wal_senders = 3
4. Creating a Base Backup Using pg_basebackup
Step 1: Run the Base Backup Command
pg_basebackup -h localhost -U postgres -D /path/to/backup_dir \
-F tar -X fetch -P -v --label="base_backup_YYYYMMDD"
-F tar: Create a .tar file
-X fetch: Fetch WALs during backup
-P: Show progress
Step 2: Secure the backup files
Ensure proper permissions (chown postgres:postgres)
Store off-server or in secure backup location
5. Archiving WAL Files
Ensure archived WALs are continuously copied to a secure location.
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
Monitor:
ls -lh /archive/
6. Preparing for PITR
Step 1: Restore the Base Backup
tar -xvf base_backup.tar -C /new/data_directory
Ensure directory ownership:
chown -R postgres:postgres /new/data_directory
Step 2: Create [Link] and [Link]
touch /new/data_directory/[Link]
Step 3: Configure Recovery Settings
Edit [Link] or [Link] in the restored directory:
restore_command = 'cp /archive/%f "%p"'
recovery_target_time = '2025-07-16 22:30:00+03'
Optional targets:
recovery_target_xid
recovery_target_name
recovery_target_lsn
7. Performing the PITR
Step 1: Start PostgreSQL from the New Cluster
pg_ctl -D /new/data_directory start
Step 2: Monitor Logs
Check log file (defined in [Link]) for:
WAL restoration progress
Successful completion of recovery
Step 3: Finalize Recovery
When target time is reached:
LOG: recovery stopping before commit of transaction ...
HINT: Execute pg_wal_replay_resume() to continue.
Run from psql:
SELECT pg_wal_replay_resume();
8. Post-Recovery Steps
Remove [Link] if not needed anymore
Ensure archive_mode and archive_command are revalidated
Test application connectivity and data consistency
Backup the PITR environment
9. Common Pitfalls and Troubleshooting
Issue Cause Solution
Could not locate Corrupted or Ensure full WAL archive exists and
checkpoint record missing WALs restore_command is correct
Database not Wrong Verify ownership and permissions on
starting permissions data directory
PITR overshot Misconfigured Double-check recovery_target_time
target target time format
Hot standby not max_connectio Align [Link] values with
possible ns mismatch primary
Conclusion
PITR is a powerful recovery method in PostgreSQL, provided that proper
backups and WALs are maintained. Follow this documentation closely to
ensure reliable recovery.
Always test your backup and recovery strategy regularly.
References
PostgreSQL PITR Documentation
Internal DBA Backup & Recovery Policy (v1.0)