--- title: "Point-in-Time Recovery for MySQL and PostgreSQL on Ubuntu" description: "Set up point-in-time recovery on Ubuntu 24.04 with MySQL binary logs and PostgreSQL WAL archiving, then restore a database to the moment before a bad query." category: "Migration and Disaster Recovery" language: en source: https://cubepath.com/docs/migration-and-disaster-recovery/point-in-time-recovery-for-databases updated: 2026-09-25 --- # Point-in-Time Recovery for MySQL and PostgreSQL on Ubuntu 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: ```bash sudo mysql -e "SELECT @@log_bin, @@binlog_format, @@binlog_expire_logs_seconds, @@log_bin_basename;" ``` ```output +-----------+-----------------+-------------------------------+----------------------+ | @@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: ```bash 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: ```bash 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: ```bash sudo zcat /var/backups/mysql/full-$(date +%F).sql.gz | grep -m1 "CHANGE REPLICATION SOURCE" ``` ```output -- 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: ```bash 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: ```bash sudo mysql -e "INSERT INTO shop.orders (item) VALUES ('headset');" ``` Now simulate the mistake: ```bash 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: ```bash 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: ```bash 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`: ```output ... # 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: ```bash 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: ```bash 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: ```bash sudo mysql -e "SELECT id, item FROM shop.orders;" ``` ```output +----+----------+ | id | item | +----+----------+ | 1 | keyboard | | 2 | mouse | | 3 | monitor | | 4 | headset | +----+----------+ ``` All four rows are back, including `headset`, which existed only in the binary log. > Important: The 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: ```bash sudo install -d -o postgres -g postgres -m 0700 /var/lib/postgresql/wal_archive ``` Open the cluster configuration file: ```bash sudo nano /etc/postgresql/16/main/postgresql.conf ``` Set these parameters (they already exist, commented out): ```ini 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: ```bash sudo systemctl restart postgresql@16-main ``` Force a segment switch and check that it was archived: ```bash 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;" ``` ```output 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: ```bash sudo -u postgres pg_basebackup -D /var/lib/postgresql/basebackup/$(date +%F) -Ft -z -Xs -P ``` ```output 31245/31245 kB (100%), 1/1 tablespace ``` List the result: ```bash sudo ls -lh /var/lib/postgresql/basebackup/$(date +%F) ``` ```output -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: ```bash 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();" ``` ```output 2026-09-25 12:10:41.283712+00 ``` Wait a few seconds, then run the mistake and force the WAL to the archive: ```bash 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: ```bash 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: ```bash 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: ```bash 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: ```bash sudo nano /etc/postgresql/16/main/postgresql.conf ``` Add the recovery settings at the end of the file: ```ini 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: ```bash sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal ``` Start PostgreSQL: ```bash sudo systemctl start postgresql@16-main ``` Watch the log. You should see recovery stop before the delete transaction: ```bash sudo tail -n 20 /var/log/postgresql/postgresql-16-main.log ``` ```output 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: ```bash sudo -u postgres psql -c "SELECT id, item FROM orders;" ``` ```output 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: ```bash 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.