What is it? #
Goal: build a backup system you would actually trust, and then prove it works by deliberately breaking it.
Most backup setups fail in one of four ways, all silent: the job stops running, the credentials expire, the disk fills, or the dump succeeds while containing almost nothing. A system that only handles the happy path gives false confidence, which is worse than none.
So this project builds all of it — the role, the script, retention, offsite copies, alerting, a dead man's switch and an automated restore test — and then spends the last section breaking each piece on purpose to confirm the failure is visible.
By the end you will have working scripts and, more importantly, evidence that they fail loudly.
Think of it like this #
Installing a smoke alarm and then holding a lit match under it.
Fitting the alarm is the easy part. Testing that it actually sounds is what distinguishes a safety device from a plastic disc on the ceiling.
Nobody wants to find out during the fire.
Simple example #
A single VPS with one PostgreSQL database.
The finished system: a nightly compressed dump with globals, verified, copied to object storage, with old copies removed only after success, failures alerting to a channel someone reads, a monitor that notices if backups stop entirely, and a monthly automated restore test.
Code #
SQL
-- ---------- PART 1: a least-privilege backup role ----------
CREATE ROLE backup_user WITH LOGIN PASSWORD 'generate-a-long-random-one';
GRANT pg_read_all_data TO backup_user; -- PostgreSQL 14+: read everything,
-- change nothing
BASH
# ---------- PART 2: credentials, stored safely ----------
# Never on the command line: it is visible in "ps" and shell history.
echo "localhost:5432:*:backup_user:generate-a-long-random-one" > ~/.pgpass
chmod 600 ~/.pgpass # PostgreSQL SILENTLY IGNORES the file otherwise
# Prove it works with no prompt:
psql -h localhost -U backup_user -d shop -c "SELECT 1;"
sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql
sudo chmod 700 /var/backups/postgresql # backups contain ALL your data
BASH
#!/usr/bin/env bash
# ---------- PART 3: /usr/local/bin/pg_backup.sh ----------
set -euo pipefail
# Without -e, a failed dump would be followed by retention happily
# deleting your older WORKING backups. This line is the whole safety net.
DB_NAME="${DB_NAME:-shop}"
DB_USER="${DB_USER:-backup_user}"
DB_HOST="${DB_HOST:-localhost}"
BACKUP_DIR="${BACKUP_DIR:-/var/backups/postgresql}"
RETENTION_DAYS="${RETENTION_DAYS:-14}"
MIN_SIZE="${MIN_SIZE:-10240}"
LOG_FILE="${LOG_FILE:-/var/log/pg_backup.log}"
S3_BUCKET="${S3_BUCKET:-}"
HEALTHCHECK_URL="${HEALTHCHECK_URL:-}"
STAMP="$(date +%F_%H-%M)"
DUMP="<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mrow><mi>B</mi><mi>A</mi><mi>C</mi><mi>K</mi><mi>U</mi><msub><mi>P</mi><mi>D</mi></msub><mi>I</mi><mi>R</mi></mrow><mi mathvariant="normal">/</mi></mrow><annotation encoding="application/x-tex">{BACKUP_DIR}/</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"><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.0715em;">C</span><span class="mord mathnormal" style="margin-right:0.0715em;">K</span><span class="mord mathnormal" style="margin-right:0.109em;">U</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.1389em;">P</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3283em;"><span style="top:-2.55em;margin-left:-0.1389em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0278em;">D</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span></span><span class="mord">/</span></span></span></span>{DB_NAME}_${STAMP}.dump"
GLOBALS="<span class="katex-error" title="ParseError: KaTeX parse error: Expected group after '_' at position 21: …UP_DIR}/globals_̲" style="color:#cc0000">{BACKUP_DIR}/globals_</span>{STAMP}.sql"
log() { 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><msup><mi>e</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo>+</mo></mrow><annotation encoding="application/x-tex">(date '+%F %T')]</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1.0019em;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"><span class="mord mathnormal">e</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mord">+</span></span></span></span>*" | tee -a "$LOG_FILE"; }
alert() {
log "ALERT: $1"
[[ -n "<span class="katex-error" title="ParseError: KaTeX parse error: Expected 'EOF', got '&' at position 23: …WEBHOOK:-}" ]] &̲& curl -fsS -X …" style="color:#cc0000">{SLACK_WEBHOOK:-}" ]] && curl -fsS -X POST "</span>SLACK_WEBHOOK" \
-H 'Content-Type: application/json' \
-d "{\"text\":\"Backup FAILED on <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mi>h</mi><mi>o</mi><mi>s</mi><mi>t</mi><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mo stretchy="false">)</mo><mo>:</mo></mrow><annotation encoding="application/x-tex">(hostname):</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">h</span><span class="mord mathnormal">os</span><span class="mord mathnormal">t</span><span class="mord mathnormal">nam</span><span class="mord mathnormal">e</span><span class="mclose">)</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">:</span></span></span></span>1\"}" || true
}
cleanup() {
local rc=$?
if [[ $rc -ne 0 ]]; then
alert "exited with code $rc"
[[ -f "<span class="katex-error" title="ParseError: KaTeX parse error: Expected 'EOF', got '&' at position 10: DUMP" ]] &̲& rm -f "" style="color:#cc0000">DUMP" ]] && rm -f "</span>DUMP" # never leave a partial dump
fi
exit $rc
}
trap cleanup EXIT # fires on EVERY exit path
log "=== backup start: $DB_NAME ==="
# Preflight: fail fast, for the right reason
AVAIL=<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>f</mi><mo>−</mo><mo>−</mo><mi>o</mi><mi>u</mi><mi>t</mi><mi>p</mi><mi>u</mi><mi>t</mi><mo>=</mo><mi>a</mi><mi>v</mi><mi>a</mi><mi>i</mi><mi>l</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">(df --output=avail "</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" style="margin-right:0.1076em;">df</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:0.8095em;vertical-align:-0.1944em;"></span><span class="mord">−</span><span class="mord mathnormal">o</span><span class="mord mathnormal">u</span><span class="mord mathnormal">tp</span><span class="mord mathnormal">u</span><span class="mord mathnormal">t</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.6944em;"></span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.0359em;">v</span><span class="mord mathnormal">ai</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mord">"</span></span></span></span>BACKUP_DIR" | tail -1)
[[ "$AVAIL" -lt 1048576 ]] && { alert "under 1GB free"; exit 1; }
psql -h "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>D</mi><msub><mi>B</mi><mi>H</mi></msub><mi>O</mi><mi>S</mi><mi>T</mi><mi mathvariant="normal">"</mi><mo>−</mo><mi>U</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">DB_HOST" -U "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8444em;vertical-align:-0.15em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3283em;"><span style="top:-2.55em;margin-left:-0.0502em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0813em;">H</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord">"</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:0.6944em;"></span><span class="mord mathnormal" style="margin-right:0.109em;">U</span><span class="mord">"</span></span></span></span>DB_USER" -d "$DB_NAME" -c 'SELECT 1' >/dev/null \
|| { alert "cannot connect"; exit 1; }
# Dump
log "dumping to $DUMP"
pg_dump -h "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>D</mi><msub><mi>B</mi><mi>H</mi></msub><mi>O</mi><mi>S</mi><mi>T</mi><mi mathvariant="normal">"</mi><mo>−</mo><mi>U</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">DB_HOST" -U "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8444em;vertical-align:-0.15em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3283em;"><span style="top:-2.55em;margin-left:-0.0502em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0813em;">H</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord">"</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:0.6944em;"></span><span class="mord mathnormal" style="margin-right:0.109em;">U</span><span class="mord">"</span></span></span></span>DB_USER" -d "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>D</mi><msub><mi>B</mi><mi>N</mi></msub><mi>A</mi><mi>M</mi><mi>E</mi><mi mathvariant="normal">"</mi><mo>−</mo><mi>F</mi><mi>c</mi><mo>−</mo><mi>Z</mi><mn>6</mn><mo>−</mo><mi>f</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">DB_NAME" -Fc -Z6 -f "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8444em;vertical-align:-0.15em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3283em;"><span style="top:-2.55em;margin-left:-0.0502em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.109em;">N</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.109em;">M</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord">"</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:0.7667em;vertical-align:-0.0833em;"></span><span class="mord mathnormal" style="margin-right:0.1389em;">F</span><span class="mord mathnormal">c</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:0.7667em;vertical-align:-0.0833em;"></span><span class="mord mathnormal" style="margin-right:0.0715em;">Z</span><span class="mord">6</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:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.1076em;">f</span><span class="mord">"</span></span></span></span>DUMP"
# Verify, in three layers
SIZE=<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mi>s</mi><mi>t</mi><mi>a</mi><mi>t</mi><mo>−</mo><mi>c</mi></mrow><annotation encoding="application/x-tex">(stat -c%s "</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">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</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:0.4306em;"></span><span class="mord mathnormal">c</span></span></span></span>DUMP")
[[ "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>S</mi><mi>I</mi><mi>Z</mi><mi>E</mi><mi mathvariant="normal">"</mi><mo>−</mo><mi>l</mi><mi>t</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">SIZE" -lt "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.7778em;vertical-align:-0.0833em;"></span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.0715em;">Z</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord">"</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:0.6944em;"></span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mord mathnormal">t</span><span class="mord">"</span></span></span></span>MIN_SIZE" ]] && { alert "dump only ${SIZE} bytes"; exit 1; }
pg_restore --list "$DUMP" >/dev/null || { alert "dump is unreadable"; exit 1; }
TABLES=<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mi>p</mi><msub><mi>g</mi><mi>r</mi></msub><mi>e</mi><mi>s</mi><mi>t</mi><mi>o</mi><mi>r</mi><mi>e</mi><mo>−</mo><mo>−</mo><mi>l</mi><mi>i</mi><mi>s</mi><mi>t</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">(pg_restore --list "</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">p</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0359em;">g</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:-0.0359em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0278em;">r</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">es</span><span class="mord mathnormal">t</span><span class="mord mathnormal" style="margin-right:0.0278em;">or</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:0.7778em;vertical-align:-0.0833em;"></span><span class="mord">−</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mord mathnormal">i</span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord">"</span></span></span></span>DUMP" | grep -c 'TABLE DATA' || true)
log "verified: <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mi>n</mi><mi>u</mi><mi>m</mi><mi>f</mi><mi>m</mi><mi>t</mi><mo>−</mo><mo>−</mo><mi>t</mi><mi>o</mi><mo>=</mo><mi>i</mi><mi>e</mi><mi>c</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">(numfmt --to=iec "</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">n</span><span class="mord mathnormal">u</span><span class="mord mathnormal">m</span><span class="mord mathnormal" style="margin-right:0.1076em;">f</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</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:0.6984em;vertical-align:-0.0833em;"></span><span class="mord">−</span><span class="mord mathnormal">t</span><span class="mord mathnormal">o</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.6944em;"></span><span class="mord mathnormal">i</span><span class="mord mathnormal">ec</span><span class="mord">"</span></span></span></span>SIZE"), $TABLES tables"
# Compare against the previous run — catches a dump that silently
# skipped tables after a permissions change
PREV=<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mi>f</mi><mi>i</mi><mi>n</mi><mi>d</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">(find "</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" style="margin-right:0.1076em;">f</span><span class="mord mathnormal">in</span><span class="mord mathnormal">d</span><span class="mord">"</span></span></span></span>BACKUP_DIR" -name "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><msub><mrow><mi>D</mi><msub><mi>B</mi><mi>N</mi></msub><mi>A</mi><mi>M</mi><mi>E</mi></mrow><mo>∗</mo></msub><mi mathvariant="normal">.</mi><mi>d</mi><mi>u</mi><mi>m</mi><mi>p</mi><mi mathvariant="normal">"</mi><mo stretchy="false">!</mo><mo>−</mo><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">{DB_NAME}_*.dump" ! -name "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8941em;vertical-align:-0.1997em;"></span><span class="mord"><span class="mord"><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3283em;"><span style="top:-2.55em;margin-left:-0.0502em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.109em;">N</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.109em;">M</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span></span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.126em;"><span style="top:-2.5003em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mbin mtight">∗</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.1997em;"><span></span></span></span></span></span></span><span class="mord">.</span><span class="mord mathnormal">d</span><span class="mord mathnormal">u</span><span class="mord mathnormal">m</span><span class="mord mathnormal">p</span><span class="mord">"</span><span class="mclose">!</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:0.6944em;"></span><span class="mord mathnormal">nam</span><span class="mord mathnormal">e</span><span class="mord">"</span></span></span></span>(basename "$DUMP")" \
-printf '%T@ %p\n' | sort -rn | head -1 | cut -d' ' -f2- || true)
if [[ -n "<span class="katex-error" title="ParseError: KaTeX parse error: Expected 'EOF', got '&' at position 10: PREV" ]] &̲& (( SIZE <" style="color:#cc0000">PREV" ]] && (( SIZE <</span>(stat -c%s "$PREV") / 2 )); then
alert "dump is less than half the previous size"
fi
# Globals: roles and passwords are NOT in pg_dump
pg_dumpall -h "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>D</mi><msub><mi>B</mi><mi>H</mi></msub><mi>O</mi><mi>S</mi><mi>T</mi><mi mathvariant="normal">"</mi><mo>−</mo><mi>U</mi><mi>p</mi><mi>o</mi><mi>s</mi><mi>t</mi><mi>g</mi><mi>r</mi><mi>e</mi><mi>s</mi><mo>−</mo><mo>−</mo><mi>g</mi><mi>l</mi><mi>o</mi><mi>b</mi><mi>a</mi><mi>l</mi><mi>s</mi><mo>−</mo><mi>o</mi><mi>n</mi><mi>l</mi><mi>y</mi><mo>−</mo><mi>f</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">DB_HOST" -U postgres --globals-only -f "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8444em;vertical-align:-0.15em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3283em;"><span style="top:-2.55em;margin-left:-0.0502em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0813em;">H</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord">"</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:0.8778em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.109em;">U</span><span class="mord mathnormal">p</span><span class="mord mathnormal">os</span><span class="mord mathnormal">t</span><span class="mord mathnormal" style="margin-right:0.0359em;">g</span><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="mord mathnormal">es</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:0.8889em;vertical-align:-0.1944em;"></span><span class="mord">−</span><span class="mord mathnormal" style="margin-right:0.0359em;">g</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mord mathnormal">o</span><span class="mord mathnormal">ba</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mord mathnormal">s</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:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">o</span><span class="mord mathnormal">n</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mord mathnormal" style="margin-right:0.0359em;">y</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:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.1076em;">f</span><span class="mord">"</span></span></span></span>GLOBALS"
# Offsite
if [[ -n "$S3_BUCKET" ]]; then
log "uploading"
aws s3 cp "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>D</mi><mi>U</mi><mi>M</mi><mi>P</mi><mi mathvariant="normal">"</mi><mi mathvariant="normal">"</mi><mi>s</mi><mn>3</mn><mo>:</mo><mi mathvariant="normal">/</mi><mi mathvariant="normal">/</mi></mrow><annotation encoding="application/x-tex">DUMP" "s3://</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.6944em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord mathnormal" style="margin-right:0.109em;">U</span><span class="mord mathnormal" style="margin-right:0.109em;">M</span><span class="mord mathnormal" style="margin-right:0.1389em;">P</span><span class="mord">""</span><span class="mord mathnormal">s</span><span class="mord">3</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:1em;vertical-align:-0.25em;"></span><span class="mord">//</span></span></span></span>{S3_BUCKET}/" --only-show-errors
aws s3 cp "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>G</mi><mi>L</mi><mi>O</mi><mi>B</mi><mi>A</mi><mi>L</mi><mi>S</mi><mi mathvariant="normal">"</mi><mi mathvariant="normal">"</mi><mi>s</mi><mn>3</mn><mo>:</mo><mi mathvariant="normal">/</mi><mi mathvariant="normal">/</mi></mrow><annotation encoding="application/x-tex">GLOBALS" "s3://</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.6944em;"></span><span class="mord mathnormal">G</span><span class="mord mathnormal">L</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="mord mathnormal">A</span><span class="mord mathnormal">L</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord">""</span><span class="mord mathnormal">s</span><span class="mord">3</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:1em;vertical-align:-0.25em;"></span><span class="mord">//</span></span></span></span>{S3_BUCKET}/" --only-show-errors
fi
# Retention LAST, and only because set -e got us here
log "pruning older than $RETENTION_DAYS days"
find "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>B</mi><mi>A</mi><mi>C</mi><mi>K</mi><mi>U</mi><msub><mi>P</mi><mi>D</mi></msub><mi>I</mi><mi>R</mi><mi mathvariant="normal">"</mi><mo>−</mo><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">BACKUP_DIR" -name "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8444em;vertical-align:-0.15em;"></span><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.0715em;">C</span><span class="mord mathnormal" style="margin-right:0.0715em;">K</span><span class="mord mathnormal" style="margin-right:0.109em;">U</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.1389em;">P</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3283em;"><span style="top:-2.55em;margin-left:-0.1389em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0278em;">D</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></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="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:0.6944em;"></span><span class="mord mathnormal">nam</span><span class="mord mathnormal">e</span><span class="mord">"</span></span></span></span>{DB_NAME}_*.dump" -mtime "+${RETENTION_DAYS}" -delete
find "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>B</mi><mi>A</mi><mi>C</mi><mi>K</mi><mi>U</mi><msub><mi>P</mi><mi>D</mi></msub><mi>I</mi><mi>R</mi><mi mathvariant="normal">"</mi><mo>−</mo><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mi mathvariant="normal">"</mi><mi>g</mi><mi>l</mi><mi>o</mi><mi>b</mi><mi>a</mi><mi>l</mi><msub><mi>s</mi><mo>∗</mo></msub><mi mathvariant="normal">.</mi><mi>s</mi><mi>q</mi><mi>l</mi><mi mathvariant="normal">"</mi><mo>−</mo><mi>m</mi><mi>t</mi><mi>i</mi><mi>m</mi><mi>e</mi><mi mathvariant="normal">"</mi><mo>+</mo></mrow><annotation encoding="application/x-tex">BACKUP_DIR" -name "globals_*.sql" -mtime "+</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8444em;vertical-align:-0.15em;"></span><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.0715em;">C</span><span class="mord mathnormal" style="margin-right:0.0715em;">K</span><span class="mord mathnormal" style="margin-right:0.109em;">U</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.1389em;">P</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3283em;"><span style="top:-2.55em;margin-left:-0.1389em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0278em;">D</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></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="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:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">nam</span><span class="mord mathnormal">e</span><span class="mord">"</span><span class="mord mathnormal" style="margin-right:0.0359em;">g</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mord mathnormal">o</span><span class="mord mathnormal">ba</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mord"><span class="mord mathnormal">s</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1757em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mbin mtight">∗</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord">.</span><span class="mord mathnormal">s</span><span class="mord mathnormal" style="margin-right:0.0359em;">q</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mord">"</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:0.7778em;vertical-align:-0.0833em;"></span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mord mathnormal">im</span><span class="mord mathnormal">e</span><span class="mord">"</span><span class="mord">+</span></span></span></span>{RETENTION_DAYS}" -delete
# Dead man's switch: tells a monitor we ran. The MONITOR alerts on silence.
[[ -n "<span class="katex-error" title="ParseError: KaTeX parse error: Expected 'EOF', got '&' at position 21: …HCHECK_URL" ]] &̲& curl -fsS --r…" style="color:#cc0000">HEALTHCHECK_URL" ]] && curl -fsS --retry 3 "</span>HEALTHCHECK_URL" >/dev/null
log "=== backup complete ==="
BASH
# ---------- PART 4: install and schedule ----------
sudo install -m 750 -o postgres -g postgres pg_backup.sh /usr/local/bin/pg_backup.sh
sudo touch /var/log/pg_backup.log
sudo chown postgres:postgres /var/log/pg_backup.log
# Run it by hand FIRST and read every line:
sudo -u postgres /usr/local/bin/pg_backup.sh
sudo crontab -u postgres -e
# 0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1
# ^^^^
# without 2>&1, errors are discarded and failures become invisible
BASH
# ---------- PART 5: log rotation ----------
sudo tee /etc/logrotate.d/pg_backup > /dev/null <<'EOF'
/var/log/pg_backup.log {
weekly
rotate 8
compress
missingok
notifempty
create 640 postgres postgres
}
EOF
BASH
#!/usr/bin/env bash
# ---------- PART 6: /usr/local/bin/pg_restore_test.sh ----------
# The step that turns files into proven backups. Run monthly.
set -euo pipefail
LATEST=$(ls -t /var/backups/postgresql/shop_*.dump | head -1)
TEST_DB="restore_test_$(date +%s)"
echo "testing restore of $LATEST"
createdb -U postgres "$TEST_DB"
trap 'dropdb -U postgres --if-exists "$TEST_DB"' EXIT
pg_restore -U postgres -d "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>T</mi><mi>E</mi><mi>S</mi><msub><mi>T</mi><mi>D</mi></msub><mi>B</mi><mi mathvariant="normal">"</mi><mo>−</mo><mi>j</mi><mn>4</mn><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">TEST_DB" -j 4 "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8444em;vertical-align:-0.15em;"></span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3283em;"><span style="top:-2.55em;margin-left:-0.1389em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0278em;">D</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="mord">"</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:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.0572em;">j</span><span class="mord">4"</span></span></span></span>LATEST"
psql -U postgres -d "$TEST_DB" -c "ANALYZE;"
ROWS=<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mi>p</mi><mi>s</mi><mi>q</mi><mi>l</mi><mo>−</mo><mi>U</mi><mi>p</mi><mi>o</mi><mi>s</mi><mi>t</mi><mi>g</mi><mi>r</mi><mi>e</mi><mi>s</mi><mo>−</mo><mi>d</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">(psql -U postgres -d "</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">p</span><span class="mord mathnormal">s</span><span class="mord mathnormal" style="margin-right:0.0359em;">q</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</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:0.8778em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.109em;">U</span><span class="mord mathnormal">p</span><span class="mord mathnormal">os</span><span class="mord mathnormal">t</span><span class="mord mathnormal" style="margin-right:0.0359em;">g</span><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="mord mathnormal">es</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:0.6944em;"></span><span class="mord mathnormal">d</span><span class="mord">"</span></span></span></span>TEST_DB" -tAc 'SELECT count(*) FROM orders')
[[ "$ROWS" -lt 1 ]] && { echo "FAILED: orders empty"; exit 1; }
# Is the data actually recent, or did we restore an ancient dump?
AGE=<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mi>p</mi><mi>s</mi><mi>q</mi><mi>l</mi><mo>−</mo><mi>U</mi><mi>p</mi><mi>o</mi><mi>s</mi><mi>t</mi><mi>g</mi><mi>r</mi><mi>e</mi><mi>s</mi><mo>−</mo><mi>d</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">(psql -U postgres -d "</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">p</span><span class="mord mathnormal">s</span><span class="mord mathnormal" style="margin-right:0.0359em;">q</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</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:0.8778em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.109em;">U</span><span class="mord mathnormal">p</span><span class="mord mathnormal">os</span><span class="mord mathnormal">t</span><span class="mord mathnormal" style="margin-right:0.0359em;">g</span><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="mord mathnormal">es</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:0.6944em;"></span><span class="mord mathnormal">d</span><span class="mord">"</span></span></span></span>TEST_DB" -tAc \
"SELECT EXTRACT(epoch FROM now() - max(placed_at))/3600 FROM orders")
(( <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mi>p</mi><mi>r</mi><mi>i</mi><mi>n</mi><mi>t</mi><msup><mi>f</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup></mrow><annotation encoding="application/x-tex">(printf '%.0f' "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1.0019em;vertical-align:-0.25em;"></span><span class="mopen">(</span><span class="mord mathnormal">p</span><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="mord mathnormal">in</span><span class="mord mathnormal">t</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.1076em;">f</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span></span></span></span>AGE") > 48 )) && { echo "FAILED: newest row is ${AGE}h old"; exit 1; }
echo "PASSED: <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>R</mi><mi>O</mi><mi>W</mi><mi>S</mi><mi>r</mi><mi>o</mi><mi>w</mi><mi>s</mi><mo separator="true">,</mo><mi>n</mi><mi>e</mi><mi>w</mi><mi>e</mi><mi>s</mi><mi>t</mi></mrow><annotation encoding="application/x-tex">ROWS rows, newest</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8778em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.1389em;">W</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="mord mathnormal">o</span><span class="mord mathnormal" style="margin-right:0.0269em;">w</span><span class="mord mathnormal">s</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal">n</span><span class="mord mathnormal">e</span><span class="mord mathnormal" style="margin-right:0.0269em;">w</span><span class="mord mathnormal">es</span><span class="mord mathnormal">t</span></span></span></span>{AGE}h old"
TEXT
=====================================================================
PART 7: BREAK IT ON PURPOSE — the part that proves it works
=====================================================================
Run each of these and confirm: it exits non-zero, it alerts, it removes
any partial dump, and IT DOES NOT DELETE YOUR EXISTING BACKUPS.
BASH
# TEST 1 — bad credentials
cp ~/.pgpass ~/.pgpass.bak
echo "localhost:5432:*:backup_user:wrong" > ~/.pgpass && chmod 600 ~/.pgpass
sudo -u postgres /usr/local/bin/pg_backup.sh ; echo "exit: $?"
# EXPECT: "cannot connect", exit 1, older backups untouched
cp ~/.pgpass.bak ~/.pgpass
# TEST 2 — no disk space
sudo -u postgres BACKUP_DIR=/nonexistent /usr/local/bin/pg_backup.sh ; echo "exit: $?"
# EXPECT: non-zero exit, alert fired
# TEST 3 — database does not exist
sudo -u postgres DB_NAME=no_such_db /usr/local/bin/pg_backup.sh ; echo "exit: $?"
# EXPECT: "cannot connect", exit 1
# TEST 4 — wrong permissions on .pgpass (the silent classic)
chmod 644 ~/.pgpass
sudo -u postgres /usr/local/bin/pg_backup.sh ; echo "exit: $?"
# EXPECT: failure, because PostgreSQL IGNORES a world-readable .pgpass
chmod 600 ~/.pgpass
# TEST 5 — a corrupt dump is detected
sudo -u postgres bash -c 'head -c 500 /dev/urandom > /var/backups/postgresql/shop_corrupt.dump'
pg_restore --list /var/backups/postgresql/shop_corrupt.dump || echo "correctly detected"
# TEST 6 — the restore test actually catches a bad backup
sudo -u postgres /usr/local/bin/pg_restore_test.sh ; echo "exit: $?"
# TEST 7 — cron stops running entirely (the dangerous silent case)
# Disable the cron entry for a day and confirm your healthcheck monitor
# alerts on the MISSING ping. Nothing in the script can detect this.
BASH
# ---------- PART 8: verify the whole thing ----------
ls -lh /var/backups/postgresql/ # dumps present and dated
tail -30 /var/log/pg_backup.log # readable, timestamped
aws s3 ls "s3://${S3_BUCKET}/" | tail -5 # offsite copies arriving
sudo crontab -u postgres -l # scheduled
find /var/backups/postgresql -name "shop_*.dump" -mtime -1 | grep -q . \
&& echo "recent backup exists" || echo "NO BACKUP IN 24 HOURS"How it works #
Expected result: nightly dumps landing locally and offsite, old copies pruned, a readable log, alerts on failure, a monitor that notices silence, and a restore test that has actually passed.
The design choices that matter:
set -euo pipefail combined with retention running last is the core safety property. If the dump fails, the script exits before reaching retention, so the older working backups survive. Reverse that order — or omit set -e — and a single failed night can delete your entire history while appearing to succeed.
trap cleanup EXIT guarantees there is no path out of the script that skips the alert, including unexpected failures partway through. It also deletes partial dumps, so a truncated file can never be mistaken later for a good backup.
Verification in three layers catches three different failures: a missing file, a dump that produced almost nothing, and structural corruption. The size comparison against the previous run catches the fourth and subtlest — a dump that succeeded technically but silently skipped tables after a permissions change. Nothing else in the chain would notice that.
The dead man's switch is the only thing that detects the script not running at all. In-script alerting requires the script to run. If cron is misconfigured, the file is deleted, or the server is off, nothing fires — and silence feels exactly like success. Inverting the logic so a missing ping raises the alarm is what closes that gap.
The restore test checks recency, not just success. A restore that works but produces data two weeks old means the backups stopped rotating. Checking the age of the newest row catches that.
Part 7 is the actual project. Untested failure handling is as unproven as an untested backup. Confirming that each failure mode exits non-zero, alerts, and leaves older backups intact is what converts this from a script into a system you can rely on.
Real-world use #
The backup systems that fail are rarely the ones nobody built. They are the ones built once, never exercised, and quietly broken for months.
Send alerts where someone will see them. An email to an unmonitored address is decoration. If the team lives in a chat channel, alert there.
Automate the restore test and treat its failure as seriously as a failed backup — more seriously, arguably, since it means every backup you hold may be unusable.
Keep credentials out of the script. Reading configuration from environment variables with sensible defaults, and relying on .pgpass for the password, means the script itself is safe to commit.
Enable versioning or object lock on the offsite bucket. Without it, anyone who compromises the server can delete the backups just as easily as the database, which is precisely the ransomware scenario offsite copies are meant to defend against.
For more than a single small database, move to pgBackRest or Barman. They provide incremental backups, parallelism, encryption, integrity verification and PITR integration, with the edge cases already handled. This project is genuinely useful for one VPS — and understanding exactly what it does is what lets you configure those tools with confidence.
Finally, document where the backups live and how to restore them, and have someone other than the author follow that document successfully. Backups are made by scripts; restores are made by people, under pressure.
Common mistakes #
- Building the happy path and never testing a single failure mode.
- Running retention before verifying the new dump, so one bad night deletes the history.
- Having no dead man’s switch, so a script that stops running raises no alarm at all.
- Leaving .pgpass world-readable, which PostgreSQL silently ignores, breaking cron runs only.
- Storing backups only on the same server, or in a bucket with no versioning.
Practice #
Complete all seven failure tests and record what happened for each — exit code, whether an alert fired, whether older backups survived. Then extend the system: add a second database to the same script without duplicating the logic; add a weekly full pg_dumpall alongside the per-database dumps; make the restore test verify three tables rather than one; and add a check that fails if the offsite copy is missing even when the local dump succeeded. Finally, disable cron for a day and confirm your monitor notices.