Database Backup Strategy: Postgres, MySQL and Redis

Ask what happens to the orders taken since midnight and most backup plans go quiet, because a scheduled dump answers a different question from the one an incident asks. Getting the window down to minutes is a per-engine exercise with a prerequisite in each case that only announces itself during recovery. This covers all three, and the consistency problem that appears once an application spans them.

A nightly dump is a backup of last night. It is not a backup of five minutes before the incident, and the difference between those two is usually the whole conversation about acceptable data loss. Closing that gap needs a different mechanism per engine, and each one has a prerequisite people discover too late.

Logical and Physical Are Not Interchangeable

Every database offers two kinds of backup and they solve different problems.

A logical backup is a description of the data: SQL statements or a portable archive. It restores into a different version, a different platform, sometimes a different engine, and you can extract one table from it. It is slow to produce and slow to restore, and it captures the database as of when it started.

A physical backup is a copy of the data files. Fast for large databases, restores as a whole, and requires a compatible version and architecture. Crucially, it is the foundation for point-in-time recovery, which logical backups cannot provide.

Most production systems need both, and that is not redundancy. A weekly logical dump plus continuous physical archiving covers the two failure modes that actually occur: somebody deleted something, and something corrupted everything.

Postgres: the WAL Is the Backup

Postgres writes every change to a write-ahead log before applying it. Archive those segments alongside a base backup and you can replay to any moment in between, which is what point-in-time recovery means.

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_timeout = 300

pg_basebackup -D /backup/base-$(date +%F) -Ft -z -Xs -P -U replicator
# recovery: restore the base, then in postgresql.conf
#   restore_command = 'cp /archive/%f %p'
#   recovery_target_time = '2026-09-09 14:32:00'
# and touch recovery.signal before starting

Two details decide whether this works. archive_timeout forces a segment to be archived even when the database is quiet, which bounds your exposure on a low-traffic system; without it a mostly-idle database can hold hours of changes in an unarchived segment. And an archive_command that fails silently is the classic disaster: Postgres retries, the log directory fills, and eventually the database stops accepting writes. Monitor whether archiving is succeeding, not just whether the database is up.

For anything beyond a single instance, use a purpose-built tool rather than shell commands. pgBackRest and Barman handle retention, parallel compression, verification and the archive bookkeeping that hand-rolled scripts get wrong.

MySQL: Binary Logs and a Consistent Start

The MySQL equivalent is the binary log, and the same pattern applies: a full backup plus the logs since it.

# my.cnf
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 1209600
sync_binlog = 1

mysqldump --single-transaction --source-data=2 --all-databases \
  --routines --events --triggers | gzip > /backup/all-$(date +%F).sql.gz

mysqlbinlog --start-datetime="2026-09-09 02:00:00" \
            --stop-datetime="2026-09-09 14:32:00" \
            mysql-bin.000031 | mysql

--single-transaction is what makes the dump consistent without locking every table, and it only works for InnoDB. A mixed-engine database with MyISAM tables gets an inconsistent dump from that flag and needs --lock-all-tables instead, which does block writes. Check your engines before trusting the flag.

--source-data=2 records the binary log position in the dump as a comment, which is what tells you where to start replaying. Without it you are guessing at the join point.

Three things routinely missing from MySQL dumps: routines, events and triggers. They are not included by default, and a restore without them produces a schema that looks complete and behaves wrongly.

Redis: Durability Is a Choice

Redis is often treated as a cache that needs no backup, right up to the moment it is holding sessions, queues or rate-limit state that nobody can reconstruct. Decide which it is, explicitly.

RDB is a periodic point-in-time snapshot. Compact, fast to load, and you lose everything since the last one.

AOF logs every write. Much smaller loss window, larger files, slower restart. With appendfsync everysec the exposure is about a second, which is the sensible default; always is durable and slow.

save 900 1
appendonly yes
appendfsync everysec
dir /var/lib/redis

Run both. RDB gives you a compact file to copy off the host, AOF bounds the loss. Our guides to installing Redis on Ubuntu and installing MySQL or MariaDB cover the surrounding configuration.

The Consistency Problem Nobody Plans For

An application with a Postgres database, a Redis instance and files on disk has three backups taken at three different moments. Restore all three and you have a state that never existed: orders in the database whose queue entries are gone, or file references pointing at uploads that were not yet captured.

Three practical responses, in descending order of effort. Quiesce the application briefly and back everything up inside that window, which is the only genuinely consistent option. Or make the application tolerate the skew, by treating the database as the source of truth and rebuilding derived state on start. Or accept the inconsistency and document exactly what reconciliation is needed after a restore.

Pick one deliberately. The failure mode is choosing none and discovering the reconciliation requirement during recovery.

A Replica Is Not a Backup

Streaming replication protects against a server failing. It does not protect against anything else, because a DROP TABLE replicates faithfully and in milliseconds.

Replication is a high-availability mechanism. Backups are a recovery mechanism. A system with replicas and no backups has excellent uptime and no way back from a mistake, which is a common and expensive configuration.

A delayed replica sits usefully between them: replication held deliberately an hour behind, so a destructive statement can be caught before it applies. Cheap, and it shortens recovery for the most common incident considerably. It still is not a backup.

What Makes It Real

Everything above is configuration. What turns it into a recovery capability is restoring it on a schedule, on a host that does not already have the data, and timing it.

Two checks specific to databases. Verify the backup restores into a running instance and answers a query, rather than verifying the archive is readable. And test the point-in-time path specifically: restoring a base backup is one procedure, and replaying logs to a chosen timestamp is a different one that almost nobody rehearses until they need it. Our guide to running recovery drills covers structuring those rehearsals, and backup encryption and key recovery covers the key material that makes the rest moot if lost.

Where the Copies Belong

Archived logs and base backups on the same volume as the database is the arrangement that fails on the one occasion it is needed.

MassiveGRID's backup services use block-level incremental backups with AES-256 encryption at $0.01 per GB, stored on RAID10 in Tier-3 and Tier-4 datacenters with destinations in the US and EU, plus two free VPS snapshots per instance. Underneath, Proxmox high-availability clustering with automatic failover over Ceph storage replicating every block three times across independent NVMe drives handles the hardware failure that replication would otherwise be carrying alone, which lets your replicas do the job they are good at. A restore target is cheap on per-resource pricing at $2.87 per CPU core, $0.80 per GB of RAM and $0.01 per GB of SSD per month, which is what makes the drill above affordable.

Databases and backup destinations can be ordered across a partner footprint of more than 700 datacenters in 85 metros, 30 countries and six continents, with auto-provisioning in New York, London, Frankfurt and Singapore.