Configure PostgreSQL Point-in-Time Recovery (PITR) for Precise Second-by-Second Data Restore
Detailed guide on configuring PostgreSQL Point-in-Time Recovery (PITR) to restore data accurately to the second, including WAL setup, periodic backups, and recovery at specific...
Image credit: Alexas_Fotos · Pixabay Content License
Introduction to Point-in-Time Recovery (PITR) in PostgreSQL
Point-in-Time Recovery (PITR) is a powerful feature in PostgreSQL that enables restoring a database to a specific state at a particular moment. This is especially useful when dealing with user errors, software bugs, or unintended data deletion. With PITR, you can recover data down to the second, rather than relying solely on full backups.
Requirements and Prerequisites
- PostgreSQL version 9.6 or later (preferably 13+)
- Root or sudo access on the server
- WAL (Write-Ahead Logging) setup and periodic backups configured
- Sufficient storage space for WAL files and backups
Configuring PostgreSQL for PITR
To enable PITR, edit the postgresql.conf file:
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal/%f'
max_wal_senders = 3
wal_keep_size = 1GB
Explanation:
wal_level = replica: Enables full WAL logging.archive_mode = on: Activates WAL archiving to external storage.archive_command: Copies WAL files to the archive directory.max_wal_senders: Allows multiple WAL sender connections.wal_keep_size: Maintains a specified amount of WAL files for recovery.
Performing Backups and Recovery
Run the following command to create a full backup:
pg_basebackup -h localhost -U postgres -D /backup/postgres_backup -Ft -P -v
To recover to a specific time, use:
pg_restore -h localhost -U postgres -d mydb /backup/postgres_backup/backup.tar --point-in-time='2023-10-01 12:00:00'
Replace the timestamp with the desired recovery point.
Verification and Troubleshooting
To verify PITR effectiveness:
- Make a data change.
- Recover to a point before the change.
- Check if the data matches expectations.
Common issues:
- No WAL files: Check
archive_commandand directory permissions. - Recovery failure: Ensure full backup and required WAL files are available.
Managing Periodic Backups and WAL
To ensure PITR works effectively, set up a scheduled backup process combined with WAL storage. Use pg_basebackup for regular full backups (e.g., daily). Periodically verify that WAL files are correctly archived to /var/lib/postgresql/wal/. If using cloud storage or external servers, ensure stable connectivity and sufficient storage capacity.
Restoring Data at a Specific Time
To restore data to a specific time, create a recovery.conf file in the PostgreSQL data directory. Include the following settings:
restore_command = 'cp /var/lib/postgresql/wal/%f %p'
recovery_target_time = '2023-10-01 12:00:00'
recovery_target_inclusive = true
Explanation:
restore_command: Specifies how to retrieve WAL files from external storage.recovery_target_time: The target recovery time (must match YYYY-MM-DD HH:MM:SS format).recovery_target_inclusive: If true, the target time is included in recovery.
Automating Backups and Data Lifecycle Management
To optimize the process, write a bash script or use tools like pgbackrest or wal-g to automate backups and delete old WAL files. For example, with pgbackrest, set options like backup-retention-full and backup-retention-diff to control how many backups are retained. This saves space and prevents storage overload.
Testing and Validating Recovery
Before deploying in production, test PITR in a development environment. Follow these steps:
- Create a test database.
- Insert sample data.
- Take a full backup.
- Insert additional data.
- Recover to a point before the additional data was inserted.
- Verify that the data matches expectations.
Common Troubleshooting Solutions
Common PITR configuration issues include:
- WAL backup failure: Check directory permissions and ensure
archive_commandruns correctly. - Recovery failure: Confirm that both the full backup and required WAL files are present and intact.
- Slow recovery time: Check I/O performance and use NVMe drives if possible to improve read/write speeds.
- WAL files deleted prematurely: Verify
wal_keep_sizeis set high enough to retain necessary WAL files.
Performance Analysis and Optimization
To optimize PITR performance, monitor metrics such as:
- Full backup duration.
- Recovery time.
- WAL and backup storage costs.
- Drive I/O performance.
Adjust parameters like checkpoint_timeout and max_wal_size to tune checkpoint frequency and WAL size. This balances performance and recovery capabilities.
Introduction to Supporting Tools
Besides manual configuration, use tools like pgbackrest, wal-g, or Barman for automated backup and recovery. These tools offer features such as:
- Automatic backups and data lifecycle management.
- Fast and accurate recovery.
- Error analysis and alerts.
- Integration with monitoring systems.
Best Practices for PITR
To ensure effective PITR, follow these best practices:
- Regularly test the recovery process.
- Set up alerts for backup and recovery failures.
- Use reliable external storage for WAL and backups.
- Maintain detailed logs of data changes and recovery events.
- Optimize configuration based on specific application needs.
Related infrastructure & engineering guides: NVMe Cloud VPS for Docker, Nginx & Databases · Configure Envoy Proxy as API Gateway for GRPC and HTTP3 Traffic Routing in Microservices
Recommended NVMe Cloud VPS Plans to Deploy This Setup
Enterprise RAID 10 NVMe SSDs, CMC & FPT District 9 uplinks with Anycast Anti-DDoS and 99.99% SLA.
VPS-3E
10% VAT included
- CPU: 2 vCPU
- RAM: 4 GB
- SSD: 50 GB NVMe
