Point-in-Time Recovery (PITR) Database Setup

Imagine: at 14:37 someone accidentally drops the orders table, or at 09:15 a mass incorrect UPDATE hits a million rows. Without PITR, recovery is only to the last backup — all data after that is lost. We solve this by configuring Point-in-Time Recovery for PostgreSQL and MySQL, so you can roll back to any second. Standard backups protect against full failure, but not logical errors. PITR enables recovery with transaction-level precision. Key metrics: RPO (Recovery Point Objective) — maximum data loss. With archive_timeout=300, RPO ≤ 5 minutes. RTO (Recovery Time Objective) — recovery time. For a 100 GB database, 15–40 minutes. Without PITR, recovery can take days instead of hours, and lost data is often unrecoverable. According to PostgreSQL documentation, WAL archiving is the foundation of PITR. Savings from a single data deletion incident can exceed $5,000.

Why PITR Is a Necessity, Not an Option

Without PITR, you lose all changes after the last backup. This can cost hours of work and reputation damage. With PITR, you recover to the moment just before the error, losing only a few minutes. Time savings during rollback are 10x faster than re-entering data.

Supported Databases

• PostgreSQL — pgBackRest with replication to S3.
• MySQL — binary logs with binlog_format=ROW.

How PITR Works

It requires two components: a base snapshot (full backup) and a continuous stream of transaction logs from the snapshot to the current time (WAL for PostgreSQL, binlog for MySQL). Recovery = base snapshot + replay of logs up to the desired point. Storage cost for WAL archives in S3 for a 100 GB database is about $15/month. Each WAL segment is 16 MB and has a unique Log Sequence Number (LSN), allowing precisely targeted recovery.

PITR Setup for PostgreSQL

WAL Archiving Configuration

In postgresql.conf:

wal_level = replica archive_mode = on archive_command = 'pgbackrest --stanza=myapp archive-push %p' archive_timeout = 300 

A PostgreSQL restart is required after changing wal_level.

pgBackRest: Complete PITR Configuration

# /etc/pgbackrest/pgbackrest.conf [global] repo1-path=/mnt/backup-storage/pgbackrest repo1-retention-full=3 repo1-retention-archive=14 repo2-type=s3 repo2-path=/pgbackrest repo2-s3-bucket=company-db-backups repo2-s3-region=eu-west-1 repo2-retention-full=2 [myapp] pg1-path=/var/lib/postgresql/14/main pg1-port=5432 

Point-in-Time Restore

systemctl stop postgresql pgbackrest --stanza=myapp restore \ --target="YYYY-MM-DD HH:MM:SS" \ --target-action=promote \ --delta systemctl start postgresql 

Replace --target with the desired timestamp. --delta restores only changed files, speeding up the process.

PITR Setup for MySQL via binlog

Binary Log Configuration

# /etc/mysql/mysql.conf.d/mysqld.cnf server_id = 1 log_bin = /var/log/mysql/mysql-bin.log binlog_format = ROW expire_logs_days = 14 max_binlog_size = 500M binlog_row_image = FULL 

Recovery Using mysqlbinlog

mysql -u root myapp < full_backup_YYYYMMDD.sql mysqlbinlog \ --stop-datetime="YYYY-MM-DD HH:MM:SS" \ /var/log/mysql/mysql-bin.000040 \ /var/log/mysql/mysql-bin.000041 \ /var/log/mysql/mysql-bin.000042 | mysql -u root myapp 

To skip a problematic transaction, specify --start-position and --stop-position in mysqlbinlog to exclude the corrupt event.

Choosing Between PITR on PostgreSQL and MySQL

PostgreSQL offers more flexibility (LSN recovery, parallel apply) but requires more careful tuning. MySQL is simpler to configure, but binlog files can consume significant space with ROW format.

Local vs S3 Storage for WAL Archive

Our PITR Setup Process

1. Audit current infrastructure — determine database size, change frequency, RTO/RPO requirements.
2. Configure archiving — set up WAL/binlog, choose repository (local disk + S3).
3. Test restore — verify recovery to a random point in an isolated environment.
4. Documentation — record procedures, create a runbook for on-call engineers.
5. Training — conduct a workshop for your team on manual and automated recovery.