Məlumat bazası

Standard daily backups only let you restore to the moment the backup was taken, potentially losing hours of data. Point-in-time recovery (PITR) uses transaction logs to restore to any specific moment, minimizing data loss.

How Point-in-Time Recovery Works

Combine a full backup with continuously logged transactions since that backup. To recover, restore the full backup, then "replay" logged transactions up to the exact moment just before the incident occurred.

MySQL/MariaDB: Enabling Binary Logging

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 604800
max_binlog_size = 100M

sudo systemctl restart mysql

binlog_expire_logs_seconds = 604800 retains 7 days of binary logs before automatic cleanup.

Taking a Full Backup with Binary Log Position

mysqldump -u root -p --all-databases --master-data=2 --flush-logs | gzip > full-backup.sql.gz

--master-data=2 records the exact binary log position at backup time, needed for point-in-time recovery.

Finding the Binary Log Position from the Backup

zcat full-backup.sql.gz | head -30 | grep "CHANGE MASTER"

Performing Point-in-Time Recovery

Step 1 — Restore the full backup:

gunzip -c full-backup.sql.gz | mysql -u root -p

Step 2 — Replay binary logs from the backup's position up to just before the incident:

mysqlbinlog --start-position=12345 --stop-datetime="2026-08-12 14:29:00" \
  /var/log/mysql/mysql-bin.000123 | mysql -u root -p

This replays all transactions from immediately after the backup up until (but not including) the incident moment — e.g. an accidental DROP TABLE at 14:30.

PostgreSQL: Enabling WAL Archiving

sudo nano /etc/postgresql/*/main/postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

sudo systemctl restart postgresql

Taking a PostgreSQL Base Backup

sudo -u postgres pg_basebackup -D /var/backups/pg_base -Ft -z -P

PostgreSQL Point-in-Time Recovery

Restore the base backup, then configure a recovery target time in recovery.signal/postgresql.conf pointing to the WAL archive location and desired recovery timestamp — PostgreSQL will replay WAL files up to that point automatically on startup.

When You Actually Need PITR (vs Standard Daily Backups)

• High-transaction-volume applications where losing even a few hours of data is unacceptable
• Compliance requirements mandating minimal data loss windows
• Applications where accidental data deletion/corruption is a realistic operational risk

Storage Considerations

Binary/WAL logs consume disk space continuously — monitor and set appropriate retention (binlog_expire_logs_seconds for MySQL) to avoid filling the disk.

Common Errors

Binary logs missing for the required time range — retention period was too short, or logs weren't backed up off-server before local cleanup deleted them.

Recovery replay fails partway through — verify the binary log files themselves weren't corrupted or truncated; always back up binary logs alongside the base backup.

Best Practices

• Back up binary/WAL logs off-server, not just the base backup
• Test the full PITR process periodically, not just standard restore
• Set retention long enough to cover your realistic detection window for incidents

Related Articles

• How to Back Up and Restore MySQL/MariaDB Databases
• How to Back Up and Restore PostgreSQL Databases
• How to Create a Disaster Recovery Plan for Your VPS