Database Backup and Recovery

A backup strategy starts with targets, not with a tool #

An effective backup strategy starts not by choosing the right tool but by settling two critical metrics:

• RTO (Recovery Time Objective): how long the system can stay down when the database becomes unreachable — the acceptable downtime.
• RPO (Recovery Point Objective): how much data loss can be tolerated at most in a disaster scenario.

RTO and RPO targets are set by the business units, not by the technical teams. On an e-commerce platform even fifteen minutes of data loss can be unacceptable, while on a reporting database running overnight batch processing a day’s loss may be considered reasonable. Even with the same backup tools, two entirely different architectures are designed for those two scenarios.

The problem we meet most often in the field is that no RTO or RPO target has ever been set. On most systems a standard full backup is taken every night, and nobody knows how long it would take to bring the system back up in an outage. That information usually emerges during the first major outage, at the worst possible moment.

MySQL solutions #

For physical backups we use Percona XtraBackup. The tool lets us take a full backup without locking the running server. For incremental backups, two different strategies can be designed depending on the base backup used as the reference:

• Differential: each backup contains the changes since the last full backup. It consumes more disk space; in return the restore is markedly faster.
• Incremental: each backup contains the changes since the previous backup. It saves disk space, but the backup chain has to be complete at restore time.

Which strategy is chosen depends entirely on the target RTO. For logical backup needs — recovering a single table, or migrating data between versions — mysqldump comes in, and mydumper, which can work in parallel, on large data sets.

To recover a specific moment, we configure binary log archiving as part of a PITR (Point-in-Time Recovery) strategy. Even when the last backup is from 03:00, the binary log records make it possible to return to just before a faulty UPDATE run at 14:37.

PostgreSQL solutions #

On PostgreSQL architectures we prefer pgBackRest. Supporting full, differential and incremental backup types natively, the tool brings compression, retention policies, backup to a remote server, and PITR through WAL (Write-Ahead Logging) archiving together in one place. On multi-server (cluster) architectures it is also possible to take the backup directly from a standby, without adding load to the primary.

On smaller systems, pg_basebackup, which ships with PostgreSQL, is enough. It needs no extra component, and integrated with WAL archiving it provides PITR. Which tool to use is determined by the scale of the system and its retention needs. Trying to manage a large database with pg_basebackup and complicating a small one with pgBackRest both create unnecessary operational cost.

Both tools carry open source licences. Our right to use them and to integrate them into a customer system comes from that; the installation, the configuration and the operational maintenance that follows are carried out by us.

MongoDB solutions #

On the MongoDB side we use Percona Backup for MongoDB. The tool takes consistent backups on both replica sets and sharded clusters; it supports logical and physical backup types, and where oplog archiving is configured it provides point-in-time recovery through the same tool.

On sharded clusters consistency is a problem that has to be handled separately. Taking a separate mongodump from each shard produces copies from different moments; restored together, the cluster does not reach a consistent state. Because Percona Backup for MongoDB coordinates the backup across the whole cluster, that problem disappears.

mongodump and mongorestore are enough on single replica set installations and for collection-level recovery needs. Which tool to use is determined by the data volume, the recovery target, and whether the architecture is sharded.

The tool carries an open source licence; our right to use it and to integrate it into a customer system comes from that.

How can you be sure your backups really work? #

This is the most critical section on the page. A backup mechanism fails silently in one of two ways: either the backup job stops and nobody notices, or the backups keep being taken but the files are corrupt and cannot be restored. The second scenario is far more dangerous, because everything on the monitoring dashboards keeps looking green.

To remove that risk, we integrate automated restore tests into every backup infrastructure we build. The backup files are periodically opened into an isolated test environment, the database is brought up, consistency checks are run, and the duration of the whole process is reported. The time that comes out of the test is your measured, real RTO — not the one committed on paper.

If the measured time is above the target RTO, what exists is not a recovery plan but an assumption — and you find that out when the system goes down.

Related services #

The backup files of an encrypted database have to be encrypted too. The two processes are designed together: Database Security and KVKK Technical Measures

A cluster architecture does not replace backups; replication does not protect against data loss: High Availability and Cluster Solutions

The effect of the backup window on the production load is within the scope of performance work: Performance Tuning and Monitoring