A nightly backup lets you go back to last night. Point-in-time recovery (PITR) lets you go back to any second since then, for example to the moment just before someone ran DELETE FROM orders without a WHERE clause. PITR combines a full base backup with a continuous log of every change: MySQL's binary logs or PostgreSQL's write-ahead log (WAL). To recover, you restore the base backup and replay the log up to the chosen point. In this tutorial you will set up and test PITR for MySQL 8.0 and PostgreSQL 16 on Ubuntu 24.04.
Prerequisites
To follow this guide you need:
- A server running Ubuntu 24.04 LTS with a non-root sudo user, for example a CubePath VPS.
- MySQL 8.0 (
sudo apt install mysql-server) for Part 1, PostgreSQL 16 (sudo apt install postgresql) for Part 2, or both. - Enough disk space for a full backup plus the logs you keep. In production, copy backups and logs to a different server or object storage: PITR is useless if the logs die with the database server.
The two parts are independent; follow the one for your database.
How PITR works
| Concept | MySQL | PostgreSQL |
|---|---|---|
| Change log | Binary log (binlog.000001...) | WAL segments (16 MB files) |
| Base backup | mysqldump with the binlog position | pg_basebackup |
| Log shipping | Copy closed binlog files | archive_command copies each WAL segment |
| Replay tool | mysqlbinlog piped into mysql | Server replays WAL using restore_command |
| Stop point | --stop-datetime or --stop-position | recovery_target_time |
Your recovery window starts at the oldest base backup you keep and ends at the last log you archived.
Part 1: MySQL point-in-time recovery
Step 1 - Checking binary logging
MySQL 8.0 enables binary logging by default, in row format, and keeps binlogs for 30 days. Confirm it:
sudo mysql -e "SELECT @@log_bin, @@binlog_format, @@binlog_expire_logs_seconds, @@log_bin_basename;"
+-----------+-----------------+-------------------------------+----------------------+
| @@log_bin | @@binlog_format | @@binlog_expire_logs_seconds | @@log_bin_basename |
+-----------+-----------------+-------------------------------+----------------------+
| 1 | ROW | 2592000 | /var/lib/mysql/binlog|
+-----------+-----------------+-------------------------------+----------------------+
If @@log_bin is 0, someone disabled it with skip-log-bin or disable-log-bin in /etc/mysql/mysql.conf.d/mysqld.cnf; remove that line and restart MySQL. Make sure binlogs are kept at least as long as the interval between two base backups plus a safety margin.
Step 2 - Taking a base backup with the binlog position
Create a directory for backups:
sudo install -d -m 0700 /var/backups/mysql
Take a consistent dump of all databases. --single-transaction avoids locking InnoDB tables, and --source-data=2 records the binary log position of the snapshot as a comment in the dump:
sudo mysqldump --single-transaction --source-data=2 --all-databases \
--routines --triggers --events | gzip | sudo tee /var/backups/mysql/full-$(date +%F).sql.gz > /dev/null
Check the recorded position. Every change after this point is in the binary logs:
sudo zcat /var/backups/mysql/full-$(date +%F).sql.gz | grep -m1 "CHANGE REPLICATION SOURCE"
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000012', SOURCE_LOG_POS=1571;
In production, run this dump daily from a script or systemd timer and copy the binlog files in /var/lib/mysql/ to backup storage regularly (for example every 15 minutes with rsync). The binlog currently being written is still open; run sudo mysql -e "FLUSH BINARY LOGS;" before copying to close it and start a new one.
Step 3 - Simulating an accident
Create a test database with some rows:
sudo mysql -e "CREATE DATABASE shop; CREATE TABLE shop.orders (id INT PRIMARY KEY AUTO_INCREMENT, item VARCHAR(50), created_at DATETIME DEFAULT NOW()); INSERT INTO shop.orders (item) VALUES ('keyboard'), ('mouse'), ('monitor');"
Take the base backup from Step 2 now, so the database is included. Then add a row after the backup, which only the binary log contains:
sudo mysql -e "INSERT INTO shop.orders (item) VALUES ('headset');"
Now simulate the mistake:
sudo mysql -e "DELETE FROM shop.orders;"
The goal is to recover all four rows, including the one added after the backup.
Step 4 - Finding the stop position
Stop the application from writing more data. Then copy the binary logs to a safe location before touching anything else, so the restore cannot overwrite or purge them:
sudo mkdir -p /root/pitr
sudo cp /var/lib/mysql/binlog.0* /root/pitr/
Search the binlogs written since the backup for the bad statement. -v with --base64-output=DECODE-ROWS shows row events as readable pseudo-SQL:
sudo mysqlbinlog --base64-output=DECODE-ROWS -v /root/pitr/binlog.000012 | grep -B 30 "### DELETE FROM \`shop\`.\`orders\`" | head -40
Look for the # at line that starts the transaction containing the DELETE:
...
# at 2462
#260925 11:48:02 server id 1 end_log_pos 2541 CRC32 0x5e1a07f2 Anonymous_GTID ...
...
### DELETE FROM `shop`.`orders`
Position 2462 is where the bad transaction begins. Replaying up to (but not including) that position restores everything before the mistake. If you only know the approximate time, you can use --stop-datetime="2026-09-25 11:48:00" instead, but positions are exact.
Step 5 - Restoring and replaying
Restore the base backup. This recreates every database as it was at backup time:
sudo zcat /var/backups/mysql/full-2026-09-25.sql.gz | sudo mysql
Now replay the binary logs from the position recorded in the dump up to the position of the bad transaction. --disable-log-bin keeps the replayed events out of the server's own binary log:
sudo mysqlbinlog --disable-log-bin --start-position=1571 --stop-position=2462 /root/pitr/binlog.000012 | sudo mysql
If the changes span several files, list them all in order in the same command: --start-position applies to the first file and --stop-position to the last one.
Verify the result:
sudo mysql -e "SELECT id, item FROM shop.orders;"
+----+----------+
| id | item |
+----+----------+
| 1 | keyboard |
| 2 | mouse |
| 3 | monitor |
| 4 | headset |
+----+----------+
All four rows are back, including headset, which existed only in the binary log.
ImportantThe procedure above rolls the whole server back to the stop position. Any legitimate writes made after the mistake are lost. To recover a single table without rolling back everything, restore the backup and binlogs on a separate test server and copy only the affected rows back to production.
Part 2: PostgreSQL point-in-time recovery
Step 1 - Enabling WAL archiving
PostgreSQL writes every change to WAL segment files. With archiving enabled, it hands each completed segment to archive_command, which copies it to the archive. Create the archive directory:
sudo install -d -o postgres -g postgres -m 0700 /var/lib/postgresql/wal_archive
Open the cluster configuration file:
sudo nano /etc/postgresql/16/main/postgresql.conf
Set these parameters (they already exist, commented out):
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'
archive_timeout = 300
test ! -f refuses to overwrite an existing archived file, as the PostgreSQL documentation recommends. archive_timeout = 300 forces a segment switch at least every 5 minutes, so a quiet database never leaves more than 5 minutes of changes unarchived. In production, archive to another server, for example with a tool such as pgBackRest or WAL-G.
Changing archive_mode requires a restart:
sudo systemctl restart postgresql@16-main
Force a segment switch and check that it was archived:
sudo -u postgres psql -c "SELECT pg_switch_wal();"
sudo -u postgres psql -c "SELECT archived_count, last_archived_wal, failed_count FROM pg_stat_archiver;"
archived_count | last_archived_wal | failed_count
----------------+--------------------------+--------------
1 | 000000010000000000000001 | 0
failed_count must stay at 0. If it grows, the archive command fails; check /var/log/postgresql/postgresql-16-main.log.
Step 2 - Taking a base backup
pg_basebackup copies the running cluster. With -Ft -z it writes compressed tar files, and -Xs streams the WAL needed to make the backup consistent:
sudo -u postgres pg_basebackup -D /var/lib/postgresql/basebackup/$(date +%F) -Ft -z -Xs -P
31245/31245 kB (100%), 1/1 tablespace
List the result:
sudo ls -lh /var/lib/postgresql/basebackup/$(date +%F)
-rw------- 1 postgres postgres 180K Sep 25 12:05 backup_manifest
-rw------- 1 postgres postgres 4.1M Sep 25 12:05 base.tar.gz
-rw------- 1 postgres postgres 17K Sep 25 12:05 pg_wal.tar.gz
Step 3 - Simulating an accident
Create test data, note the time, then delete it:
sudo -u postgres psql -c "CREATE TABLE orders (id serial PRIMARY KEY, item text, created_at timestamptz DEFAULT now());"
sudo -u postgres psql -c "INSERT INTO orders (item) VALUES ('keyboard'), ('mouse'), ('monitor');"
sudo -u postgres psql -Atc "SELECT now();"
2026-09-25 12:10:41.283712+00
Wait a few seconds, then run the mistake and force the WAL to the archive:
sudo -u postgres psql -c "DELETE FROM orders;"
sudo -u postgres psql -c "SELECT pg_switch_wal();"
The target is 2026-09-25 12:10:41+00, after the inserts and before the delete. In a real incident, find the time in the application or PostgreSQL logs and choose a moment slightly before the mistake.
Step 4 - Restoring the base backup
Stop PostgreSQL:
sudo systemctl stop postgresql@16-main
Keep the damaged data directory instead of deleting it. It still contains WAL that may not have been archived yet:
sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.broken
sudo install -d -o postgres -g postgres -m 0700 /var/lib/postgresql/16/main
Extract the base backup and its WAL into the new data directory:
sudo -u postgres tar -xzf /var/lib/postgresql/basebackup/2026-09-25/base.tar.gz -C /var/lib/postgresql/16/main
sudo -u postgres tar -xzf /var/lib/postgresql/basebackup/2026-09-25/pg_wal.tar.gz -C /var/lib/postgresql/16/main/pg_wal
Step 5 - Configuring and running recovery
Open the configuration file again:
sudo nano /etc/postgresql/16/main/postgresql.conf
Add the recovery settings at the end of the file:
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-09-25 12:10:41+00'
recovery_target_action = 'promote'
restore_command fetches archived WAL segments, recovery_target_time sets where replay stops, and promote opens the database for writes once the target is reached. Create the recovery.signal file, which tells PostgreSQL to start in recovery mode:
sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal
Start PostgreSQL:
sudo systemctl start postgresql@16-main
Watch the log. You should see recovery stop before the delete transaction:
sudo tail -n 20 /var/log/postgresql/postgresql-16-main.log
LOG: starting point-in-time recovery to 2026-09-25 12:10:41+00
LOG: restored log file "000000010000000000000003" from archive
LOG: recovery stopping before commit of transaction 745, time 2026-09-25 12:10:52.104311+00
LOG: selected new timeline ID: 2
LOG: archive recovery complete
LOG: database system is ready to accept connections
Step 6 - Verifying and cleaning up
Check the data:
sudo -u postgres psql -c "SELECT id, item FROM orders;"
id | item
----+----------
1 | keyboard
2 | mouse
3 | monitor
(3 rows)
The rows are back. PostgreSQL switched to a new timeline (2), so the new history does not collide with the archived WAL of the old one, and it removed recovery.signal automatically.
Remove the three recovery lines from postgresql.conf so they do not confuse a future recovery, then take a fresh base backup right away, since it is now your starting point for the next PITR:
sudo -u postgres pg_basebackup -D /var/lib/postgresql/basebackup/$(date +%F)-after-pitr -Ft -z -Xs -P
Once you have confirmed everything works, delete /var/lib/postgresql/16/main.broken.
Troubleshooting
MySQL: mysqlbinlog replay fails with a duplicate key error. The start position is wrong, so events already in the dump are applied twice. Use exactly the SOURCE_LOG_FILE and SOURCE_LOG_POS recorded in the dump.
MySQL: the binlogs you need are gone. They were purged by binlog_expire_logs_seconds. Increase it, and copy binlogs off the server regularly.
PostgreSQL: recovery ended before configured recovery target was reached. The archive does not contain WAL up to the target time. Copy any missing segments from main.broken/pg_wal/ into the archive directory and start again from Step 4.
PostgreSQL: failed_count keeps growing in pg_stat_archiver. The archive directory is missing, full, or not writable by postgres. Fix it quickly: WAL accumulates in pg_wal until archiving succeeds and can fill the disk.
Conclusion
You configured point-in-time recovery for MySQL with binary logs and for PostgreSQL with WAL archiving, and used both to undo an accidental delete to the exact moment before it happened. The procedure only works if the logs survive the incident, so ship binlogs and WAL to another server or object storage and test a recovery regularly. As next steps, consider pgBackRest or WAL-G for PostgreSQL and Percona XtraBackup for large MySQL databases, which automate base backups, log shipping and retention.