Point-in-Time Recovery PITR database setup

Setting Up Point-in-Time Recovery (PITR) for Databases

Point-in-Time Recovery allows restoring a database to any moment in the past — not just the last backup point. Critical for scenarios: "at 14:37 someone deleted a table", "at 09:15 a wrong UPDATE of a million records happened".

How PITR works

PITR requires two components:

1. Base snapshot (full backup) — starting point
2. WAL/binlog archive — continuous stream of transaction logs from snapshot to now

Recovery = apply base snapshot + replay WAL logs to desired moment.

PostgreSQL PITR

WAL archiving setup

In postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=myapp archive-push %p'
archive_timeout = 300  # archive WAL no less than every 5 minutes

PostgreSQL restart is mandatory 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

# Replication to S3
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

Base backup (execute weekly):

pgbackrest --stanza=myapp --type=full backup

Restore to specific moment

# Stop PostgreSQL
systemctl stop postgresql

# Restore to exact timestamp
pgbackrest --stanza=myapp restore \
  --target="2024-03-15 14:30:00" \
  --target-action=promote \
  --delta

# Start PostgreSQL
systemctl start postgresql

Option --delta restores only changed files — significantly speeds up process.

Restore to LSN (Log Sequence Number):

pgbackrest --stanza=myapp restore \
  --target="0/5000000" \
  --target-action=promote

Check available PITR range

-- Minimum recovery point
SELECT pg_walfile_name(pg_current_wal_lsn());

-- Time of first available WAL entry
SELECT min(last_modified) FROM pgbackrest.archive_list;

MySQL PITR via binlog

Binary log setup

# /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 via mysqlbinlog

# First restore full backup
mysql -u root myapp < full_backup_20240310.sql

# Find desired moment in binlog
mysqlbinlog --base64-output=DECODE-ROWS -v \
  /var/log/mysql/mysql-bin.000042 | grep -A 5 "14:37"

# Apply binlog to desired moment
mysqlbinlog \
  --stop-datetime="2024-03-15 14:36:59" \
  /var/log/mysql/mysql-bin.000040 \
  /var/log/mysql/mysql-bin.000041 \
  /var/log/mysql/mysql-bin.000042 | mysql -u root myapp

Skip problematic transaction (not entire range):

mysqlbinlog \
  --start-position=4 --stop-position=1234 \
  mysql-bin.000042 | mysql -u root myapp

# Skip positions 1234-5678 (DROP TABLE)

mysqlbinlog \
  --start-position=5679 \
  mysql-bin.000042 | mysql -u root myapp

Validate PITR

Regular recovery drills — minimum quarterly:

#!/bin/bash
# Test PITR in isolated environment
TARGET_TIME=$(date -d "2 hours ago" "+%Y-%m-%d %H:%M:%S")

# Restore to test installation
pgbackrest --stanza=myapp restore \
  --target="$TARGET_TIME" \
  --target-action=promote \
  --pg1-path=/var/lib/postgresql/14/pitr-test \
  --pg1-port=5434

# Check: data exists at desired moment
psql -p 5434 -U postgres myapp -c \
  "SELECT COUNT(*) FROM orders WHERE created_at < '${TARGET_TIME}';"

Metrics and monitoring

• RPO (Recovery Point Objective) — how fresh data can be recovered. With archive_timeout=300 RPO ≤ 5 minutes.
• RTO (Recovery Time Objective) — recovery time. For 100GB database with pgBackRest — 15–40 minutes.

Monitor WAL archiving lag:

SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

Alert if WAL archiving lag exceeds 15 minutes indicates problem.

Timeline

Setup of PITR for PostgreSQL with pgBackRest (including S3 repository and restore test) — 2–3 business days. MySQL binlog PITR — 2 days.