What is it? #
The database is usually the only part of a system that cannot be recreated. Code is in git, the server can be rebuilt, the data cannot.
A proper backup is consistent, automated, stored somewhere other than the server, encrypted, retained for a sensible period, and restore-tested.
Logical dumps are the everyday tool: a file containing the statements needed to recreate the database. They are portable, selectively restorable and simple.
For systems where losing a day of data is unacceptable, continuous archiving reduces the window to minutes, at the cost of more setup.
Think of it like this #
Photocopying the only ledger in the business and keeping the copy in a different building.
Keeping it in the same room protects against spilled coffee and nothing else. And a copy nobody has ever read might be blank.
Simple example #
A PostgreSQL database backed up nightly, encrypted, copied to object storage, retained for 30 days offsite and 14 locally, with a monthly restore test.
Code #
#!/usr/bin/env bash
# /srv/app/scripts/db-backup.sh
set -euo pipefail
source /srv/app/shared/.env
STAMP=$(date +%F-%H%M)
DIR=/var/backups/db
mkdir -p "$DIR"
# Consistent dump — custom format supports selective restore and parallelism
pg_dump --format=custom --compress=6 --no-owner --no-acl \
--file="<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>D</mi><mi>I</mi><mi>R</mi><mi mathvariant="normal">/</mi><mi>s</mi><mi>h</mi><mi>o</mi><mi>p</mi><mo>−</mo></mrow><annotation encoding="application/x-tex">DIR/shop-</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord">/</span><span class="mord mathnormal">s</span><span class="mord mathnormal">h</span><span class="mord mathnormal">o</span><span class="mord mathnormal">p</span><span class="mord">−</span></span></span></span>STAMP.dump" "$DATABASE_URL"
# Verify the dump is readable before trusting it
pg_restore --list "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>D</mi><mi>I</mi><mi>R</mi><mi mathvariant="normal">/</mi><mi>s</mi><mi>h</mi><mi>o</mi><mi>p</mi><mo>−</mo></mrow><annotation encoding="application/x-tex">DIR/shop-</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord">/</span><span class="mord mathnormal">s</span><span class="mord mathnormal">h</span><span class="mord mathnormal">o</span><span class="mord mathnormal">p</span><span class="mord">−</span></span></span></span>STAMP.dump" > /dev/null
# Encrypt before it leaves the machine
gpg --batch --yes --encrypt --recipient [email protected] \
--output "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>D</mi><mi>I</mi><mi>R</mi><mi mathvariant="normal">/</mi><mi>s</mi><mi>h</mi><mi>o</mi><mi>p</mi><mo>−</mo></mrow><annotation encoding="application/x-tex">DIR/shop-</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord">/</span><span class="mord mathnormal">s</span><span class="mord mathnormal">h</span><span class="mord mathnormal">o</span><span class="mord mathnormal">p</span><span class="mord">−</span></span></span></span>STAMP.dump.gpg" "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>D</mi><mi>I</mi><mi>R</mi><mi mathvariant="normal">/</mi><mi>s</mi><mi>h</mi><mi>o</mi><mi>p</mi><mo>−</mo></mrow><annotation encoding="application/x-tex">DIR/shop-</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord">/</span><span class="mord mathnormal">s</span><span class="mord mathnormal">h</span><span class="mord mathnormal">o</span><span class="mord mathnormal">p</span><span class="mord">−</span></span></span></span>STAMP.dump"
rm "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>D</mi><mi>I</mi><mi>R</mi><mi mathvariant="normal">/</mi><mi>s</mi><mi>h</mi><mi>o</mi><mi>p</mi><mo>−</mo></mrow><annotation encoding="application/x-tex">DIR/shop-</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord">/</span><span class="mord mathnormal">s</span><span class="mord mathnormal">h</span><span class="mord mathnormal">o</span><span class="mord mathnormal">p</span><span class="mord">−</span></span></span></span>STAMP.dump"
# Offsite
rclone copy "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>D</mi><mi>I</mi><mi>R</mi><mi mathvariant="normal">/</mi><mi>s</mi><mi>h</mi><mi>o</mi><mi>p</mi><mo>−</mo></mrow><annotation encoding="application/x-tex">DIR/shop-</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord">/</span><span class="mord mathnormal">s</span><span class="mord mathnormal">h</span><span class="mord mathnormal">o</span><span class="mord mathnormal">p</span><span class="mord">−</span></span></span></span>STAMP.dump.gpg" remote:db-backups/
# Retention
find "$DIR" -name "*.dump.gpg" -mtime +14 -delete
curl -fsS -m 10 "https://hc-ping.com/UUID" || true
echo "[<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mi>d</mi><mi>a</mi><mi>t</mi><mi>e</mi><mo>−</mo><mi>I</mi><mi>s</mi><mo stretchy="false">)</mo><mo stretchy="false">]</mo><mi>b</mi><mi>a</mi><mi>c</mi><mi>k</mi><mi>u</mi><mi>p</mi><mi>c</mi><mi>o</mi><mi>m</mi><mi>p</mi><mi>l</mi><mi>e</mi><mi>t</mi><mi>e</mi><mo>:</mo><mi>s</mi><mi>h</mi><mi>o</mi><mi>p</mi><mo>−</mo></mrow><annotation encoding="application/x-tex">(date -Is)] backup complete: shop-</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mopen">(</span><span class="mord mathnormal">d</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mord mathnormal">e</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal">s</span><span class="mclose">)]</span><span class="mord mathnormal">ba</span><span class="mord mathnormal">c</span><span class="mord mathnormal" style="margin-right:0.0315em;">k</span><span class="mord mathnormal">u</span><span class="mord mathnormal">p</span><span class="mord mathnormal">co</span><span class="mord mathnormal">m</span><span class="mord mathnormal" style="margin-right:0.0197em;">pl</span><span class="mord mathnormal">e</span><span class="mord mathnormal">t</span><span class="mord mathnormal">e</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">:</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">h</span><span class="mord mathnormal">o</span><span class="mord mathnormal">p</span><span class="mord">−</span></span></span></span>STAMP"# MySQL equivalent
mysqldump --single-transaction --quick --routines --triggers --events \
--databases shop | gzip > "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>D</mi><mi>I</mi><mi>R</mi><mi mathvariant="normal">/</mi><mi>s</mi><mi>h</mi><mi>o</mi><mi>p</mi><mo>−</mo></mrow><annotation encoding="application/x-tex">DIR/shop-</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord">/</span><span class="mord mathnormal">s</span><span class="mord mathnormal">h</span><span class="mord mathnormal">o</span><span class="mord mathnormal">p</span><span class="mord">−</span></span></span></span>STAMP.sql.gz"
# --single-transaction gives a consistent snapshot without locking InnoDB tables# The restore test — schedule this, do not do it only in an emergency
createdb restore_check
gpg --decrypt "$DIR/shop-2026-09-22-0200.dump.gpg" > /tmp/check.dump
pg_restore --dbname=restore_check --no-owner --jobs=4 /tmp/check.dump
psql restore_check -c "SELECT count(*) FROM orders;"
psql restore_check -c "SELECT max(created_at) FROM orders;" # is it recent?
dropdb restore_check && rm /tmp/check.dumpRecovery objectives, decided explicitly
RPO how much data can you afford to lose?
nightly dumps → up to 24 hours
hourly dumps → up to 1 hour
continuous archiving (WAL) → minutes
RTO how long can the restore take?
a 5 GB dump restores in minutes; 500 GB does not.
Measure it during a restore test and write the number down.Continuous archiving, in outline
PostgreSQL writes every change to a write-ahead log. Archiving those
segments continuously, alongside a periodic base backup, allows restoring
to any point in time — including the moment before a mistaken DELETE.
Tools: pgBackRest, WAL-G, or the provider's managed equivalent.
Worth the setup when losing a day of data is unacceptable.How it works #
pg_dump takes its snapshot inside a transaction, so the dump is consistent even while the database continues to serve writes. Copying the data directory of a running database produces a corrupt backup.
The custom format is compressed, supports parallel restore with --jobs, and allows restoring individual tables. Plain SQL format is more portable and slower for large databases.
pg_restore --list reads the dump's table of contents. If that fails, the file is corrupt, and you know immediately rather than during a recovery.
Encrypting before upload means the offsite copy is useless to anyone who obtains it. A database backup contains everything sensitive in the system.
For MySQL, --single-transaction provides the same consistency for InnoDB tables without locking them, which is essential on a live database.
The restore test is the part that makes the rest meaningful. Restoring into a scratch database and checking both the row count and the most recent timestamp confirms the backup is both valid and current.
The RPO and RTO numbers should be written down. Discovering during an incident that a full restore takes four hours, when the business assumed twenty minutes, is a bad time to find out.
Real-world use #
The common failure is not the absence of backups. It is backups that were never tested, were incomplete, or turned out to contain an empty database because a failing script kept producing files.
Ransomware has made offsite and immutable storage essential. Backups on a mounted share get encrypted along with everything else; object storage with versioning and delete protection does not.
Managed databases provide automated backups and point-in-time recovery, which is a strong argument for them if nobody on the team wants to own this.
Restoring is rarely only about the database. Uploaded files, configuration and secrets all need to be recoverable too, which is why the disaster recovery lesson treats it as one exercise.
A restore test schedule — quarterly at minimum, timed and documented — converts a hope into a measured recovery capability.
Common mistakes #
- Never testing a restore, so the backup is an assumption.
- Copying database files instead of taking a proper dump.
- Keeping the only copy on the same server or the same cloud account.
- Unencrypted offsite backups containing all your sensitive data.
- No alert when the backup job fails or silently stops running.
Practice #
Write and schedule an encrypted database backup with offsite copy, retention and a success ping. Then restore the most recent backup into a scratch database, verify the row counts and the latest timestamp, and record how long the restore took.