Backup, Recovery and Replication

Why databases need their own approach

The general strategy (what to back up, how often, how long to keep it, the 3-2-1 rule, recovery point objective (RPO) and recovery time objective (RTO)) is explained in Backup and Recovery; the section on RPO and RTO is the starting point for every database backup plan. This page covers what is different about databases.

The difference is that a running database is never "at rest". It changes its files constantly, keeps recent changes in memory before writing them, and spreads one transaction over several files (data files, index files, the transaction log). Copying those files with an ordinary file copy while the database runs gives you pieces from different moments, which may not fit together. A database backup must be consistent: it must show the database as it was at one single moment, with every transaction either fully in or fully out (the atomicity from Transactions and ACID).

There are two ways to get there: logical and physical backups.

Logical backups

A logical backup (often called a dump) exports the database's contents as SQL statements, or as rows in a compact format, by reading them through the database itself: CREATE TABLE statements for the structure and INSERT statements (or bulk data) for the rows. The tool reads everything inside one transaction, so the result is consistent even while the shop keeps taking orders.

# SQLite: the whole database as SQL text, and back
sqlite3 shop.db .dump > shop-dump.sql
sqlite3 shop-restored.db < shop-dump.sql

# PostgreSQL: custom format (compressed, restore selectively with pg_restore)
pg_dump --format=custom --file=shop.dump shop
createdb shop_restored
pg_restore --dbname=shop_restored shop.dump

# MySQL / MariaDB: --single-transaction gives a consistent dump of InnoDB tables
# (--routines --events also export stored procedures and scheduled events)
mysqldump --single-transaction --routines --events shop > shop-dump.sql
mysql -e "CREATE DATABASE shop_restored"
mysql shop_restored < shop-dump.sql

Strengths and weaknesses:

• Portable: a dump can be restored on the same or a newer version, another operating system, and with some editing on another database product. It is also the usual way to copy one table or one database out of a large server.
• Readable: a plain SQL dump is text you can inspect, search or compare.
• Slow for large databases: restoring means executing every statement again and rebuilding every index. For hundreds of gigabytes that can take many hours, which may not fit the RTO.
• One moment only: a nightly dump can bring back last night, not 14:31 this afternoon.

Physical backups

A physical backup copies the database's files or storage blocks as they are on disk. Restoring is fast, because the files only need to be put back, not rebuilt; the price is that the copy only works on the same database product and (major) version, usually on the same kind of platform.

Ways to make a physical backup that is consistent:

• The database's backup tool, which coordinates with the engine while it runs: pg_basebackup for PostgreSQL, BACKUP DATABASE for SQL Server, MySQL Enterprise Backup or Percona XtraBackup for MySQL, the online backup API (the .backup command) for SQLite.
• A snapshot of the whole volume at one instant, taken by the storage system or cloud provider. Such a snapshot is crash-consistent: the database sees it as if the power had failed and repairs itself from its transaction log when started. That works only if every file of the database (data and log) is in the same snapshot, and it is safer when the database is told about it first.
• A cold backup: stop the database, copy the files, start it again. Simple and consistent, but the database is unavailable meanwhile.

SQL Server's BACKUP DATABASE is a physical backup taken online: it copies data pages while the database runs, plus the part of the transaction log needed to make the result consistent. A full backup plus regular log backups (not run, SQL Server):

-- SQL Server: full backup with page checksums, then log backups every 15 minutes
BACKUP DATABASE shop TO DISK = 'E:\backup\shop-full.bak' WITH CHECKSUM;
BACKUP LOG shop TO DISK = 'E:\backup\shop-log-1415.trn' WITH CHECKSUM;

Making and verifying a backup in Python

Python's sqlite3 module offers both kinds of backup. Connection.backup() uses SQLite's online backup API to copy the database page by page, consistently, while it stays in use; Connection.iterdump() produces the same SQL statements as the .dump command. The example below makes both, restores the dump into an empty database, and verifies the result by comparing row counts:

import sqlite3

# A small shop database, in memory so the example needs no files
shop = sqlite3.connect(":memory:")
shop.executescript("""
CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, price INTEGER NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL);
INSERT INTO products VALUES (1, 'USB-C cable 2 m', 9), (2, 'Wireless mouse', 25), (3, 'Laptop stand', 35);
INSERT INTO orders VALUES (101, 1, 2), (102, 2, 1), (103, 1, 1), (104, 3, 1);
""")

COUNT_QUERIES = {
    "products": "SELECT COUNT(*) FROM products",
    "orders": "SELECT COUNT(*) FROM orders",
}


def row_counts(conn):
    return {table: conn.execute(sql).fetchone()[0] for table, sql in COUNT_QUERIES.items()}


# 1. Online backup: copies the database page by page, consistently,
#    even while other connections keep working. In real life the target
#    is a file: sqlite3.connect("shop-backup.db")
backup = sqlite3.connect(":memory:")
shop.backup(backup)

# 2. Logical backup: the same data as SQL statements (what .dump prints)
dump = list(shop.iterdump())
inserts = [line for line in dump if line.startswith("INSERT")]
print(len(inserts), "INSERT statements, the first:", inserts[0])

# 3. Verify: restore the dump into an empty database, compare row counts
restored = sqlite3.connect(":memory:")
restored.executescript("\n".join(dump))
print("live:    ", row_counts(shop))
print("backup:  ", row_counts(backup))
print("restored:", row_counts(restored))
print("integrity:", backup.execute("PRAGMA integrity_check").fetchone()[0])

# 4. A mistake in the live database does not touch the backup
shop.execute("DELETE FROM orders")
print("after DELETE, live:  ", row_counts(shop))
print("after DELETE, backup:", row_counts(backup))

7 INSERT statements, the first: INSERT INTO "orders" VALUES(101,1,2);
live:     {'products': 3, 'orders': 4}
backup:   {'products': 3, 'orders': 4}
restored: {'products': 3, 'orders': 4}
integrity: ok
after DELETE, live:   {'products': 3, 'orders': 0}
after DELETE, backup: {'products': 3, 'orders': 4}

Row counts and PRAGMA integrity_check are quick sanity checks, not proof that every value is right; a real verification also runs a few queries the business cares about (the total of last month's orders, say) on the restored copy and compares them with the live database.

Transaction logs

Every serious database writes each change to a transaction log before it changes the data files themselves: an append-only record of what each transaction did. The engine needs it for itself: after a crash it replays committed changes that had not yet reached the data files and undoes unfinished ones, which is how committed data survives a power failure (the durability in ACID). The same log is the key to recovering to any moment in time.

Point-in-time recovery

Point-in-time recovery (PITR) restores a database to a chosen moment by combining two things: a base backup (a full physical backup) and the archived transaction log since that backup. You restore the base backup, then let the database replay the log up to the target moment and stop there. With a nightly base backup and a continuously archived log, the RPO shrinks from a day to minutes or less.

In PostgreSQL the settings look like this (not run). On the running server, WAL archiving copies every completed log segment to a safe place:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

To recover, you restore the base backup made with pg_basebackup to a new data directory, tell PostgreSQL where the archived log is and where to stop, and create an empty file named recovery.signal in the data directory (PostgreSQL 12 and later):

restore_command = 'cp /mnt/server/archivedir/%f %p'
recovery_target_time = '2026-09-30 14:31:00+00'

When the target is reached, PostgreSQL pauses (the default, recovery_target_action = 'pause'): check the data with read-only queries, then call pg_wal_replay_resume() to finish recovery and open the database for writes. Setting recovery_target_action = 'promote' skips the pause and finishes recovery at once.

SQL Server does the same with RESTORE DATABASE of the full backup followed by RESTORE LOG ... WITH STOPAT; MySQL restores a full backup and pipes the binary log through mysqlbinlog --stop-datetime. Managed cloud databases hide the mechanics behind a point-in-time restore option with a retention period you choose.

Restore testing

Backups fail silently: a job that "succeeds" but writes an empty file, a dump of the wrong database, a log archive with a gap, an encryption key nobody can find. The only proof that a backup works is a restore. A restore test should:

1. Restore the latest backup (and, for PITR, the log up to a chosen moment) to a separate server, on a schedule, ideally automatically.
2. Run checks on the result: the database starts, integrity checks pass, row counts and a few business totals match what you expect.
3. Measure how long the whole restore took. That number, not a guess, is what you compare with the RTO.
4. Write down the steps. During a real incident, under pressure, a tested runbook is worth more than expertise from memory.

Replication

Database replication continuously copies changes from one database server to one or more others, so that several servers hold the same data. Where a backup protects against loss over time ("bring back yesterday"), replication protects against the loss of a server ("keep running now").

Primary and replicas

In the most common setup, one server is the primary: it accepts all writes. Each replica (also called a standby or secondary) receives the primary's transaction log and applies it, so it follows the primary closely. Replicas are normally read-only (PostgreSQL standbys always are; on MySQL you set super_read_only). PostgreSQL calls this streaming replication, MySQL replicates through the binary log, and SQL Server uses Always On availability groups.

Replicas are used for two different goals:

• Availability: if the primary fails, a replica takes over (failover, below).
• Read scaling: reports, searches and other read-only queries go to replicas, leaving the primary's capacity for writes. Managed cloud databases offer this as read replicas.

Synchronous or asynchronous

The big design choice is when the primary tells the application "committed":

• Synchronous: only after at least one replica has confirmed that it has received (or applied) the change. A committed transaction then exists on two servers, so a failover loses nothing (RPO of zero). The price is latency on every commit, especially over long distances, and if the synchronous replica becomes unreachable, commits wait until it returns or another replica takes its place.
• Asynchronous: immediately, and the change is sent to the replicas afterward. Commits are fast and replicas can be far away, but replicas run slightly behind (the replication lag). If the primary dies, the transactions that had not yet reached a replica are lost: the data loss window equals the lag at that moment.

Many setups combine both: one synchronous replica nearby (no data loss) and asynchronous replicas farther away (disaster recovery and reads). A read from an asynchronous replica can return slightly old data, so reads that must see the user's own latest change go to the primary.

Failover

Failover is switching the primary role to a replica when the primary fails: the replica is promoted, starts accepting writes, and applications are pointed at it (through a DNS name, a virtual IP address or a proxy).

• Manual failover: an administrator decides and promotes the replica (in PostgreSQL with pg_ctl promote or the function pg_promote()). Slower, but a human checks first that the primary is really gone.
• Automatic failover: cluster software (Patroni for PostgreSQL, the cluster manager behind SQL Server availability groups, or a managed cloud service) detects the failure with health checks and promotes a replica within seconds to minutes.

The danger of automatic failover is split brain: the old primary was not dead but only unreachable from the cluster software, so after the promotion two servers accept writes and the data diverges. Clusters prevent this with a quorum (a majority of nodes, or a witness, must agree before a promotion) and with fencing (making sure the old primary can no longer accept writes, if necessary by cutting its power or network). After any failover, the old primary must be rebuilt as a replica before it rejoins.

Replication is not a backup

A replica copies every change, including the wrong ones. Someone runs DELETE FROM orders; on the primary, and a fraction of a second later the orders are gone on every replica as well. The same goes for a bad migration, an application bug that overwrites prices, or ransomware encrypting rows through the application. Replication protects against a failed server, not against a failed action.