Server backups that actually restore: 3-2-1, snapshots and dumps

On 31 January 2017 an engineer at GitLab, trying to get a lagging database replica back in sync, ran the command that empties a PostgreSQL data directory. It ran on the primary, not the replica. The engineer stopped it a second or two later, and by then about 300 GB of data was gone.¹ The team went for the backups. The daily pg_dump job had been failing, because it ran version 9.2 of pg_dump against a 9.6 database, and the emails reporting the failure were being rejected by the receiving mail server. The storage bucket meant to hold the dumps was empty. What saved most of the data was an LVM snapshot an engineer had taken by hand six hours earlier to refresh a staging database.¹

GitLab lost the changes made between 17:20 and midnight UTC, roughly 5,000 projects, 5,000 comments and 700 new user accounts, and copying the data back took around 18 hours because the disks involved were throttled to about 60 Mbps.¹ It did not lack copies. It had several kinds, and the one that worked was the one nobody had planned as a backup. A backup is real only once you have restored from it.

Replica, snapshot, backup: three copies that fail differently

A replica is a second live database on another server that applies every change the primary makes. PostgreSQL's documentation states the default plainly: a standby replays the primary's log as fast as it can. It offers a setting to hold changes back on purpose, since a standby that runs behind leaves you time to catch a mistake before the standby applies it too.³ That is the whole story of a replica. It protects you from a dead disk or a dead server, because it can take over, and from nothing that arrives as a change. A DROP TABLE, a bad migration or an attacker's DELETE is a change like any other, and the replica applies it within seconds.

A snapshot at the storage level, the kind LVM or ZFS takes, freezes a volume as it was at one instant, and it is quick because it copies almost nothing. In the Linux device-mapper's snapshot target, only the chunks that change after the snapshot are copied to a separate copy-on-write device, and reads of everything else come from the original disk.⁴ MySQL's manual describes the snapshots that LVM or ZFS take the same way: a logical copy at a point in time, without a physical copy of the whole file system.⁵ Such a snapshot is a view of the original, not a second copy of it. If the disk underneath fails, or the server or account it lives in is lost, the snapshot goes with it.

Some providers copy a snapshot to separate storage, and then it survives a dead disk, but it still lives in the same account with the same provider. GitLab's snapshot helped because it had been copied to a staging host, and the restore came from that copy.¹ Ask where yours are kept. What a snapshot does well is undo something recent on a machine that still exists: a failed upgrade, a deleted directory.

A backup, in CISA's words, is "a secure copy of your business's critical data, stored separately from your primary systems".² Separately is the word that matters: another machine, other storage, ideally another place. A useful backup also has history, many restore points rather than one, so you can go back to before a problem you noticed late.

The grid at the top of this page sets the copies against the five ways data gets lost. Two rows deserve a second look. A backup kept on the same server dies with the server and can be deleted by anyone who gets root on it. And an off-site backup is only as safe as the credentials the server holds for it: CISA's ransomware guide warns that many ransomware variants look for reachable backups and delete or encrypt them, so the ransom becomes the only way back.⁶

The 3-2-1 rule, translated for a server

CISA's version of the rule is three copies of important files, on two different types of storage media, with one copy stored off site.² CISA's example of two media is a hard drive and the cloud. On a server the rule maps like this.

Copy one is the live data on the server's disk: the database, uploaded files, configuration. Snapshots on that disk do not add a copy, because they share its blocks, and even a snapshot the provider keeps on separate storage sits in the same account as the server, so it cannot be the off-site copy. Copy two is a backup repository on storage that is not that disk: a second machine, a storage server, object storage. The point of "two kinds of media" is that no single failure takes out both. Copy three is off site: another region, and preferably another provider and another account. CISA suggests spreading cloud backups over more than one vendor in case every account with one vendor is affected at once.⁶

The last detail in the drawing is the easiest to get wrong. If the server holds credentials that can delete the off-site copy, an attacker who takes the server holds them too. CISA's guidance is offline, encrypted backups, and delete protection or object lock on the storage that holds them.⁶

Why a copy of a running database can be useless

A database's state at any instant is spread across several files and some memory. One sale changes an order page and a stock page in memory, writes a record of both to the write-ahead log, and commits once that record is on disk. The changed pages reach their data files whenever the database flushes them. The only rule is that their log records get there first,¹² so a page can land before the commit or long after it. A copy is consistent only if every part of it comes from the same instant, or if the database can repair it back to one.

Copying the files one by one, with cp, tar or rsync, breaks that. PostgreSQL's manual says a file-level backup needs the server shut down, and that even refusing all connections is not enough, partly because tar and similar tools do not take an atomic snapshot of the file system and partly because the server buffers data internally.⁷ SQLite's documentation describes the same failure for its single-file database: a copy made while a transaction is under way can end up holding part of the change and not the rest, which leaves the file corrupt.⁸

Crash-consistent: the power-cut copy

A snapshot of the whole volume taken at one instant is a different thing. PostgreSQL's manual accepts a frozen snapshot of a running server's volume, with conditions: the restored database will think the previous server crashed and will replay its write-ahead log, so the log has to be inside the snapshot, and if the data and the log sit on different disks the snapshots must be simultaneous, which may not be possible.⁷ This is called crash-consistent: the copy is as good as the disk after a power cut, which a well-built database is designed to survive. It is not as good when the data spans volumes that were not frozen together, or when the application keeps related state in two places, say the database on one disk and uploaded files on another.

Application-consistent: the database takes part

The stronger kind involves the application. Either the database writes the copy itself, which is what a dump is, or it is told to flush and hold still before the snapshot. On KVM hosts the QEMU guest agent inside the virtual machine has a command that flushes the guest's local file systems to disk and pauses writes to them,¹⁰ and it can run a hook script with a freeze or thaw argument,¹¹ which is where a database gets told to checkpoint first. Whether a provider's snapshots go through that step is a question to ask, not something to assume. The dependable route for a database is a dump that you schedule yourself.

Dumps: let the database copy itself

A dump asks the database for its contents through the same interface your application uses, inside one transaction. PostgreSQL's pg_dump produces an internally consistent dump that represents the database at the moment the dump began, and it does not block other work while it runs, apart from operations that need an exclusive lock, such as most forms of ALTER TABLE.¹³ Its output can generally be loaded into newer PostgreSQL versions, whereas file-level copies and archived logs are tied to the server's version,¹³ in practice to its major version: minor releases never change the storage format, and major releases may.¹⁴

Two details catch people. pg_dump covers one database and leaves out roles and tablespaces, because those belong to the whole cluster,¹³ so dump them separately with pg_dumpall --globals-only.¹⁶ Without them, a restore on a fresh server cannot give objects their original owners and permissions, and the application's login does not exist either.¹³ And the custom format, -Fc, compresses as it writes and lets pg_restore bring back single tables, which a plain SQL file cannot.¹³

PostgreSQL's own reference page adds a warning worth taking seriously: except in simple cases, pg_dump is generally not the right choice for regular backups of production databases.¹⁵ The alternative is a base backup of the data files plus continuous archiving of the write-ahead log. Replaying that log can stop at any point, which gives a consistent database as it was at that moment: point-in-time recovery.¹⁷ A nightly dump is a sound start for a small shop or a side project. When the dump takes too long to restore, or a day of lost work is too much, that is the next step.

For MySQL, the equivalent is mysqldump --single-transaction. It sets the isolation level to REPEATABLE READ and starts a transaction before dumping, so InnoDB tables come out in the state they had at that moment without blocking applications.¹⁸ The limits are in the same paragraph of the manual: only InnoDB tables are consistent, MyISAM or MEMORY tables can still change during the dump, and no other connection should run ALTER, CREATE, DROP, RENAME or TRUNCATE TABLE while it runs.¹⁸ Stored routines and events are left out unless you ask for them with --routines and --events, and since MySQL 8.4 that holds for --all-databases too.¹⁸

$ pg_dump -Fc -f /var/backups/shop.dump shop$ pg_dumpall --globals-only -f /var/backups/globals.sql$ mysqldump --single-transaction --routines --events --databases shop > /var/backups/shop.sql$ rm -f /var/backups/app.db && sqlite3 /srv/app/app.db "VACUUM INTO '/var/backups/app.db'"

SQLite's documentation lists VACUUM INTO among the safe ways to copy a live database,⁸ though it fails if the target file already exists and is not empty, hence the rm before it.⁹ MySQL's manual names the mysql client as the way to load an SQL-format dump.⁵ For MySQL the size warning matters most. The manual says mysqldump is not meant as a fast or scalable way to back up large amounts of data, because restoring it replays every statement, with the inserts and index builds that implies, and it points large installations to physical backups instead.¹⁸ How big is too big is a number your restore drill will give you. How much memory and disk the database itself needs is covered in sizing a database server.

RPO and RTO: how much you can lose, and for how long

Two numbers decide what a backup setup has to do. NIST defines the recovery point objective as the point in time to which data must be recovered after an outage,¹⁹ and the recovery time objective as how long systems can be in recovery before the business is harmed.²⁰ Put simply, the RPO is how much recent work you are prepared to lose and the RTO is how long you can be down. CISA suggests using scheduled recovery tests to refine both.²

Take a small shop on PostgreSQL. A pg_dump runs at 02:00 and produces a 6 GB file that goes off site. At 16:40 the server's disk fails.

The recovery point is fixed by the schedule. The newest copy is from 02:00, so 14 hours 40 minutes of orders are gone, and the worst case for a nightly job is just under a day. Dumping every hour caps the loss at an hour; here it would be the 40 minutes since 16:00. Archiving PostgreSQL's log continuously goes further: the manual calls an archive_timeout of a minute or so usually reasonable, which puts the loss at about a minute.¹⁷

The recovery time is a sum, and it pays to write it out. Twenty-five minutes pass before anyone notices, at 17:05. Getting a fresh server with PostgreSQL installed takes, say, 20 minutes if it is scripted. Fetching the 6 GB dump is quick on a fast link: 1 Gbit/s is 125 MB/s, so 6,000 MB takes 48 seconds. At the 60 Mbps GitLab's disks were throttled to, about 7.5 MB/s, the same file takes 800 seconds, over 13 minutes, and every 100 GB takes close to four hours. Say fetching and restoring take 70 minutes on a fast link. Most of that is pg_restore loading every row and rebuilding every index. Fifteen minutes of checks and switching traffic, and the shop is back at 18:50, 2 hours 10 minutes after the failure: 25 + 20 + 70 + 15 = 130 minutes. (For converting link speeds into transfer times, see bandwidth versus data transfer.)

Orders lost by restoring the 02:00 dump

14 h 40min

Down, from the failure to back online

2 h 10min

Fetching 6 GB at 1 Gbit/s, best case

48s

Notice which step is the biggest and which one you cannot work out on paper. The restore itself dominates, and its length depends on your data, your indexes and your disks. The only way to know it is to time a real restore.

Retention: keep enough history to go back past the problem

Many problems are found late: a bad import, a bug that has been writing wrong totals for a week, an intruder who sat quietly before acting. If you keep only last night's backup, every copy you own already contains the problem by the time you notice it. CISA's advice is to make sure you can roll data back at least seven days.²

The usual answer keeps many recent restore points and fewer old ones. In restic, an open-source backup tool, that is a forget policy such as --keep-daily 7 --keep-weekly 5 --keep-monthly 12. The tiers overlap: restic's documentation points out that seven daily snapshots already include one weekly one.²¹ forget only removes the snapshot records; prune then deletes the data that nothing references any more, and --prune runs both together.²¹

Twenty-one full copies of the 6 GB dump would take 126 GB. A deduplicating tool stores much less: restic cuts files into variable-length pieces by their content, and for a modified file only the modified pieces are saved in the next backup.²² That only works if most of the bytes are the same night after night, and a compressed dump defeats it. pg_dump's custom format compresses its table data with gzip by default,¹⁵ and a compressed stream differs from the first changed byte onward, so a few updated rows early in a large table make most of that table look new. Turn pg_dump's compression off with -Z 0¹⁵ and let restic compress instead, which it does by default in its current repository format.²⁴²³ How much deduplication then saves depends on how much of your data changes between backups, which is another number to read off a real repository rather than guess.

Encrypt it, automate it, and listen for silence

A backup holds everything an attacker would want from your server, on storage you control less. CISA lists encryption next to offline copies among the protections a backup needs,² and the simplest place to do it is on the server, before anything leaves. restic encrypts everything it writes to a repository (AES-256) and attaches a message authentication code to each piece, so tampering with the stored data is detected.²² The catch is the key. restic's documentation is blunt that losing the repository password means the data is irrecoverably lost.²³ Keep that password in a password manager and on paper somewhere safe, never only on the server the backup exists to replace.

For the copy the server must not be able to delete, restic's rest-server has an append-only mode: it accepts new snapshots and refuses to delete or change old ones, so someone who breaks into the server cannot erase the backups it has already made.²⁶ Pruning old snapshots then runs from a different machine that holds full credentials. Object storage with object lock does the same job; CISA recommends either kind of delete protection.⁶ Keeping the server itself hard to get into is its own subject, covered in securing a Linux server.

Then take yourself out of the loop. A small script run by a systemd timer is enough. Note how the dump reaches restic: --stdin-from-command runs pg_dump itself and cancels the backup if it exits with an error. The tempting shortcut, piping a dump into restic backup --stdin, hides that error; restic's documentation shows a mysqldump that cannot connect producing an empty backup that restic reports as a success.²⁷

$ cat /usr/local/bin/backup-db#!/bin/shset -eurestic backup --stdin-filename globals.sql --stdin-from-command -- pg_dumpall --globals-onlyrestic backup --stdin-filename shop.dump --stdin-from-command -- pg_dump -Fc -Z 0 shopcurl -fsS -m 30 "$PING_URL" > /dev/null$ cat /etc/systemd/system/backup-db@.service[Unit]Description=Nightly database backup to %iOnFailure=backup-alert.service[Service]Type=oneshotUser=postgresEnvironmentFile=/etc/backup-db-%i.envExecStart=/usr/local/bin/backup-db$ cat /etc/systemd/system/backup-db@.timer[Timer]OnCalendar=*-*-* 02:00:00Persistent=true[Install]WantedBy=timers.target$ sudo systemctl daemon-reload$ sudo systemctl enable --now backup-db@nearby.timer backup-db@offsite.timer

The @ makes both units templates. systemd puts whatever follows the @ in an enabled unit's name wherever %i appears,²⁹ and each timer starts the service with its own name,²⁸ so backup-db@nearby.timer fills copy two from /etc/backup-db-nearby.env and backup-db@offsite.timer fills copy three, the append-only one, from /etc/backup-db-offsite.env. Each run takes its own dump, so one repository being unreachable does not stop the other. Each environment file sets RESTIC_REPOSITORY and RESTIC_PASSWORD_FILE, the variables restic reads for unattended runs,²³ plus the PING_URL that the last line calls.

Persistent=true makes the timer run a missed job as soon as the machine is back if it was off at 02:00.²⁸ OnFailure= starts another unit, here one you write to send a message to your phone or chat, whenever the backup service fails.²⁹ And the script covers the database and nothing else. Uploaded files, configuration, certificates, cron jobs and the backup script itself are part of getting back to a working server too, so list what a rebuild needs and add a restic backup of those paths.

That covers a job that fails loudly. GitLab's failed because the alert itself never arrived.¹ The stronger signal is the absence of success, a pattern usually called a dead man's switch. Because of set -e, the curl on the last line runs only after both backups have succeeded, and the monitoring service it calls, running anywhere but this server, raises the alarm when a ping is late. A machine that can read the repository can do the same check directly: restic snapshots --json gives the time each backup started,²⁵ and an alarm fires when the newest is more than a day and a bit old. A dead server, a broken timer and a lost alert all look the same to either check, which is the point.

The restore drill: proving the backup works

CISA's advice is to test the backup procedure so that you can restore data both fully and partially, and quickly.² A drill is a rehearsal of the day you lose the server, done on a quiet afternoon when nothing depends on it, and it is the only step in this guide that turns a copy into a backup.

Checking the repository is not the same thing. By default restic check does not read the stored data back; --read-data reads all of it and --read-data-subset reads a share on each run.³⁰ A clean check proves the repository is intact. It does not prove the dump inside loads, that the application starts, or how long any of it takes.

1. 1

   Pick a restore point, not always the newest

   Usually last night's. Now and then take one from the weekly or monthly tier, so you learn whether old restore points still load with today's tools.

2. 2

   Start from nothing

   Create a spare server with none of production's files, keys or logins. Give yourself only what you would have if the server were gone: the repository address and the password from wherever you keep it.

3. 3

   Fetch and restore, with a clock running

   Pull the dump from the off-site copy, restore the globals, create the database and load it. Note how long each part takes.

4. 4

   Check the data, not only the exit code

   Count the rows in the tables that matter, find the newest order and compare its time with your recovery point, then start the application against the restored database and log in.

5. 5

   Write down the two numbers

   The time from starting to a working application is your measured recovery time; the age of the newest data is your measured recovery point. Compare both with what the business needs.

6. 6

   Fix what broke, then clean up

   If the drill turned up a missing file, an outdated step or a password nobody could find, fix it and update the written procedure. Then delete the spare server, which now holds a copy of production data.

$ restic dump --path /globals.sql latest globals.sql > /tmp/globals.sql$ restic dump --path /shop.dump latest shop.dump > /tmp/shop.dump$ psql -X -f /tmp/globals.sql postgres$ createdb -T template0 shop$ time pg_restore --exit-on-error -d shop /tmp/shop.dump

restic dump writes one file from a snapshot to standard output. With two kinds of snapshot in one repository, name the path: restic's documentation warns that latest can otherwise pick a snapshot that does not contain the file at all.³¹ Stop the data restore at the first error. By default pg_restore carries on past errors and reports a count at the end,³² and a plain SQL dump loaded with psql keeps going unless ON_ERROR_STOP is set, leaving a partially restored database.¹³ The globals file is the one exception. It creates every role, including the superuser the new server already has, and the pg_dumpall manual says the "role already exists" error that follows is harmless and should be ignored.¹⁶ Read what psql prints there: that one line is expected, anything else is not. A restore that finishes with errors nobody read is the GitLab failure in a smaller form.

Then put the next drill in the calendar, monthly for anything that earns money, and after every change to the database version, the backup script or the storage. GitLab's dumps broke on exactly that kind of change, a version mismatch nobody noticed,¹ and one drill after the upgrade would have found an empty bucket.