Back to articles
DevOps & Systems

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...

DPTCloud Engineering6 min read
Configure PostgreSQL Point-in-Time Recovery (PITR) for Precise Second-by-Second Data Restore

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:

  1. Make a data change.
  2. Recover to a point before the change.
  3. Check if the data matches expectations.

Common issues:

  • No WAL files: Check archive_command and 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:

  1. Create a test database.
  2. Insert sample data.
  3. Take a full backup.
  4. Insert additional data.
  5. Recover to a point before the additional data was inserted.
  6. Verify that the data matches expectations.

Common Troubleshooting Solutions

Common PITR configuration issues include:

  • WAL backup failure: Check directory permissions and ensure archive_command runs 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_size is 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

DPTCloud Tier III Infrastructure

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.

View 22 VPS Plans
ECO · vn-02

VPS-2E

94.600 ₫ / mo

10% VAT included

  • CPU: 2 vCPU
  • RAM: 2 GB
  • SSD: 30 GB NVMe
ECO · vn-02Recommended

VPS-3E

170.500 ₫ / mo

10% VAT included

  • CPU: 2 vCPU
  • RAM: 4 GB
  • SSD: 50 GB NVMe
BASIC · vn-01Recommended

VPS-05

457.600 ₫ / mo

10% VAT included

  • CPU: 4 vCPU
  • RAM: 8 GB
  • SSD: 50 GB NVMe

Related Articles

View all
ZaloZalo SupportDiscord0384 753 478