What is it? #
The previous lesson ran the commands by hand. This one turns them into a script that can be trusted to run unattended.
The difference between a working command and a reliable script is almost entirely failure handling. A backup script that fails silently is worse than no backup at all, because it creates confidence that is not justified.
So this script checks that each step succeeded, uses exit codes properly, writes a log, refuses to delete old backups if the new one failed, and makes noise when something goes wrong.
The guiding principle: a backup system must fail loudly. Silence should mean success, and it should only mean success because failure was made impossible to miss.
Think of it like this #
A smoke alarm that has been quietly out of battery for two years.
The house feels safe. Nothing has beeped. The absence of an alarm is being read as evidence of no fire, when it is actually evidence of nothing at all.
A good backup script is an alarm that tests itself and complains when it cannot do its job. The most dangerous backup system is one that has been silently broken for months while everyone assumed the silence meant everything was fine.
Simple example #
A single script at /usr/local/bin/pg_backup.sh, run nightly by cron.
It dumps the database, verifies the dump, copies it offsite, removes old copies only if everything before that succeeded, logs every step with a timestamp, and sends an alert on any failure.
Code #
BASH
#!/usr/bin/env bash
#
# /usr/local/bin/pg_backup.sh
# Nightly PostgreSQL backup with verification, retention and alerting.
set -euo pipefail
# -e exit immediately if any command fails
# -u error on undefined variables (catches typos in names)
# -o pipefail a pipeline fails if ANY part fails, not just the last
#
# Without these, a failing pg_dump would be followed by the retention step
# happily deleting your older, working backups.
# ---------- Configuration ----------
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_BYTES="${MIN_SIZE_BYTES:-10240}" # anything smaller is suspicious
LOG_FILE="${LOG_FILE:-/var/log/pg_backup.log}"
S3_BUCKET="${S3_BUCKET:-}" # empty = skip offsite copy
TIMESTAMP="$(date +%F_%H-%M)"
BACKUP_FILE="<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}_${TIMESTAMP}.dump"
# ---------- Logging ----------
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() {
local message="$1"
log "ALERT: $message"
# Send wherever your team will actually see it. Examples:
# curl -fsS -X POST "$SLACK_WEBHOOK" \
# -H 'Content-Type: application/json' \
# -d "{\"text\":\"PostgreSQL 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>message\"}"
# echo "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>m</mi><mi>e</mi><mi>s</mi><mi>s</mi><mi>a</mi><mi>g</mi><mi>e</mi><mi mathvariant="normal">"</mi><mi mathvariant="normal">∣</mi><mi>m</mi><mi>a</mi><mi>i</mi><mi>l</mi><mo>−</mo><mi>s</mi><mi mathvariant="normal">"</mi><mi>B</mi><mi>a</mi><mi>c</mi><mi>k</mi><mi>u</mi><mi>p</mi><mi>f</mi><mi>a</mi><mi>i</mi><mi>l</mi><mi>e</mi><mi>d</mi><mi>o</mi><mi>n</mi></mrow><annotation encoding="application/x-tex">message" | mail -s "Backup failed on</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">m</span><span class="mord mathnormal">ess</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.0359em;">g</span><span class="mord mathnormal">e</span><span class="mord">"∣</span><span class="mord mathnormal">mai</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.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">s</span><span class="mord">"</span><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="mord mathnormal">a</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" style="margin-right:0.1076em;">f</span><span class="mord mathnormal">ai</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mord mathnormal">e</span><span class="mord mathnormal">d</span><span class="mord mathnormal">o</span><span class="mord mathnormal">n</span></span></span></span>(hostname)" [email protected]
}
# Runs on ANY exit. This is what guarantees a failure is reported.
cleanup() {
local exit_code=$?
if [[ $exit_code -ne 0 ]]; then
alert "Backup script failed with exit code $exit_code"
# Remove a partial dump so it is never mistaken for a good backup
[[ -f "<span class="katex-error" title="ParseError: KaTeX parse error: Expected 'EOF', got '&' at position 17: …ACKUP_FILE" ]] &̲& rm -f "" style="color:#cc0000">BACKUP_FILE" ]] && rm -f "</span>BACKUP_FILE"
fi
exit $exit_code
}
trap cleanup EXIT
# ---------- Preflight checks ----------
log "=== Backup starting: $DB_NAME ==="
mkdir -p "$BACKUP_DIR"
# Is there enough disk space? Refuse rather than fill the disk.
AVAILABLE_KB="<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)"
if [[ "$AVAILABLE_KB" -lt 1048576 ]]; then # less than 1 GB free
alert "Only <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mrow><mi>A</mi><mi>V</mi><mi>A</mi><mi>I</mi><mi>L</mi><mi>A</mi><mi>B</mi><mi>L</mi><msub><mi>E</mi><mi>K</mi></msub><mi>B</mi></mrow><mi>K</mi><mi>B</mi><mi>f</mi><mi>r</mi><mi>e</mi><mi>e</mi><mi>i</mi><mi>n</mi></mrow><annotation encoding="application/x-tex">{AVAILABLE_KB}KB free in</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord"><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.2222em;">V</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal">L</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="mord mathnormal">L</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0576em;">E</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.0576em;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.0715em;">K</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><span class="mord mathnormal" style="margin-right:0.0715em;">K</span><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="mord mathnormal" style="margin-right:0.1076em;">f</span><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="mord mathnormal">ee</span><span class="mord mathnormal">in</span></span></span></span>BACKUP_DIR — refusing to start"
exit 1
fi
# Can we actually reach the database?
if ! 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 2>&1; then
alert "Cannot connect to database <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>a</mi><mi>s</mi></mrow><annotation encoding="application/x-tex">DB_NAME as</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8333em;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 mathnormal">a</span><span class="mord mathnormal">s</span></span></span></span>DB_USER"
exit 1
fi
# ---------- The dump ----------
log "Dumping <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>t</mi><mi>o</mi></mrow><annotation encoding="application/x-tex">DB_NAME to</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8333em;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 mathnormal">t</span><span class="mord mathnormal">o</span></span></span></span>BACKUP_FILE"
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 "$DB_NAME" \
--format=custom \
--compress=6 \
--file="$BACKUP_FILE"
# ---------- Verification ----------
if [[ ! -f "$BACKUP_FILE" ]]; then
alert "Backup file was not created"
exit 1
fi
SIZE_BYTES="<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>BACKUP_FILE")"
if [[ "<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><msub><mi>E</mi><mi>B</mi></msub><mi>Y</mi><mi>T</mi><mi>E</mi><mi>S</mi><mi mathvariant="normal">"</mi><mo>−</mo><mi>l</mi><mi>t</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">SIZE_BYTES" -lt "</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.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"><span class="mord mathnormal" style="margin-right:0.0576em;">E</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.0576em;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.0502em;">B</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.2222em;">Y</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><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_BYTES" ]]; then
alert "Backup is only ${SIZE_BYTES} bytes — suspiciously small"
exit 1
fi
# Structural check: can pg_restore read it at all?
if ! pg_restore --list "$BACKUP_FILE" >/dev/null 2>&1; then
alert "Backup file is corrupt — pg_restore cannot read it"
exit 1
fi
TABLE_COUNT="<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>BACKUP_FILE" | grep -c 'TABLE DATA' || true)"
log "Backup OK: <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_BYTES"), $TABLE_COUNT tables"
# Compare against the previous backup — a sudden shrink means trouble
PREVIOUS="<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>−</mo><mi>n</mi><mi>o</mi><mi>t</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">{DB_NAME}_*.dump" -not -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="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 mathnormal">n</span><span class="mord mathnormal">o</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.6944em;"></span><span class="mord mathnormal">nam</span><span class="mord mathnormal">e</span><span class="mord">"</span></span></span></span>(basename "$BACKUP_FILE")" \
-printf '%T@ %p\n' 2>/dev/null | sort -rn | head -1 | cut -d' ' -f2-)"
if [[ -n "$PREVIOUS" ]]; then
PREV_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>PREVIOUS")"
if (( SIZE_BYTES < PREV_SIZE / 2 )); then
alert "Backup is less than half the size of the previous one (<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><msub><mi>E</mi><mi>B</mi></msub><mi>Y</mi><mi>T</mi><mi>E</mi><mi>S</mi><mi>v</mi><mi>s</mi></mrow><annotation encoding="application/x-tex">SIZE_BYTES vs</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8333em;vertical-align:-0.15em;"></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"><span class="mord mathnormal" style="margin-right:0.0576em;">E</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.0576em;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.0502em;">B</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.2222em;">Y</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 mathnormal" style="margin-right:0.0359em;">v</span><span class="mord mathnormal">s</span></span></span></span>PREV_SIZE)"
# Not fatal — the data may genuinely have shrunk — but somebody should look.
fi
fi
# ---------- Globals (roles and passwords) ----------
GLOBALS_FILE="<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>{TIMESTAMP}.sql"
log "Dumping globals"
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><mo>−</mo><mi>f</mi><mi>i</mi><mi>l</mi><mi>e</mi><mo>=</mo><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">DB_HOST" -U postgres --globals-only --file="</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">−</span><span class="mord mathnormal" style="margin-right:0.1076em;">f</span><span class="mord mathnormal">i</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</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.6944em;"></span><span class="mord">"</span></span></span></span>GLOBALS_FILE"
# ---------- Offsite copy ----------
if [[ -n "$S3_BUCKET" ]]; then
log "Uploading to $S3_BUCKET"
aws s3 cp "<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>F</mi></msub><mi>I</mi><mi>L</mi><mi>E</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">BACKUP_FILE" "s3://</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.1389em;">F</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">L</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</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><msub><mi>S</mi><mi>F</mi></msub><mi>I</mi><mi>L</mi><mi>E</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_FILE" "s3://</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">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"><span class="mord mathnormal" style="margin-right:0.0576em;">S</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.0576em;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.1389em;">F</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">L</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</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
log "Upload complete"
fi
# ---------- Retention ----------
# Reached ONLY if everything above succeeded, because of "set -e".
# This ordering is deliberate: never delete old backups after a failed new one.
log "Removing backups older than $RETENTION_DAYS days"
DELETED="<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>−</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">{DB_NAME}_*.dump" -mtime "+</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="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}" -print -delete | wc -l)"
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
log "Removed $DELETED old backup(s)"
log "=== Backup finished successfully ==="
BASH
# ---------- Installing it ----------
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 of output:
sudo -u postgres /usr/local/bin/pg_backup.sh
BASH
# ---------- Scheduling ----------
sudo crontab -u postgres -e
# 02:00 every day. No % characters here, so no escaping problems —
# another reason to put the logic in a script.
0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1
# 2>&1 is essential: without it, error output is discarded and failures
# become invisible.
BASH
# ---------- Log rotation: do not let the log fill the disk ----------
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
# ---------- Detecting a backup that STOPPED running ----------
# An alert only fires when the script RUNS and fails. If cron is broken,
# or the server is off, nothing fires at all — the dangerous silent case.
# Dead-man's-switch: the script pings a monitor on success, and the MONITOR
# alerts when the expected ping does not arrive.
# Add to the end of the script:
# curl -fsS --retry 3 "https://hc-ping.com/your-uuid" > /dev/null
# Or check locally that a recent backup exists:
find /var/backups/postgresql -name "shop_*.dump" -mtime -1 | grep -q . \
|| echo "NO BACKUP IN THE LAST 24 HOURS"
BASH
# ---------- Monthly restore test (the step that actually matters) ----------
#!/usr/bin/env bash
set -euo pipefail
LATEST="$(ls -t /var/backups/postgresql/shop_*.dump | head -1)"
TEST_DB="restore_test_$(date +%s)"
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><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">TEST_DB" "</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></span></span>LATEST"
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')"
if [[ "$ROWS" -lt 1 ]]; then
echo "RESTORE TEST FAILED: orders table is empty"
exit 1
fi
echo "Restore test 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><mi>i</mi><mi>n</mi><mi>o</mi><mi>r</mi><mi>d</mi><mi>e</mi><mi>r</mi><mi>s</mi><mo stretchy="false">(</mo><mi>f</mi><mi>r</mi><mi>o</mi><mi>m</mi></mrow><annotation encoding="application/x-tex">ROWS rows in orders (from</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.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="mord mathnormal">in</span><span class="mord mathnormal" style="margin-right:0.0278em;">or</span><span class="mord mathnormal">d</span><span class="mord mathnormal" style="margin-right:0.0278em;">er</span><span class="mord mathnormal">s</span><span class="mopen">(</span><span class="mord mathnormal" style="margin-right:0.1076em;">f</span><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="mord mathnormal">o</span><span class="mord mathnormal">m</span></span></span></span>LATEST)"How it works #
set -euo pipefail is the most important line in the script, and the reason is specific. Without -e, a failing pg_dump does not stop execution — the script carries on to the retention step and cheerfully deletes older, working backups because the new one "exists" as a zero-byte file. That single behaviour has destroyed real backup histories. -u catches variable name typos, and pipefail ensures a failure anywhere in a pipeline is not masked by a successful final command.
The trap cleanup EXIT guarantees reporting. It runs on every exit path — success, error, or an unexpected failure partway through — so there is no route out of the script that quietly skips the alert. It also removes a partial dump, which prevents a truncated file from later being mistaken for a valid backup.
Preflight checks fail fast and for the right reason. Checking free space before starting means the script refuses to run rather than filling the disk and taking the database down with it. Checking connectivity separately means a credentials problem produces "cannot connect" rather than an obscure pg_dump error.
Verification has three layers of increasing strength. The file exists. Its size is plausible. pg_restore --list can parse it. Each catches a different failure: a missing file, a dump that produced almost nothing, and structural corruption or truncation.
The size comparison against the previous backup catches the failure mode that all the other checks miss — a dump that succeeds technically but contains far less than it should, because a permissions change means entire tables were silently skipped. A sudden halving is worth a human look even though it is not automatically fatal.
Retention runs last, and only on success. This ordering is the entire safety property: if anything failed, set -e has already exited and no old backups were touched. Deleting old backups before confirming the new one is good is the single most dangerous ordering mistake in a backup script.
The dead man's switch addresses the gap no in-script alerting can cover. Alerts fire when the script runs and fails. If cron is misconfigured, the server is off, or the script was deleted, nothing runs and nothing alerts — the silence that feels like success. A monitor expecting a regular ping inverts the logic: absence itself becomes the alarm.
Real-world use #
Backup scripts fail in unglamorous ways. A password expires. A disk fills. Someone renames a database. A PostgreSQL major upgrade leaves pg_dump older than the server and it refuses to run. None of these are dramatic, and all of them are silent unless the script is built to complain.
Run the script manually first and read every line. Then deliberately break it — wrong password, full disk, non-existent database — and confirm the alert actually arrives where you expect. An alerting path that has never fired is as untested as a backup that has never been restored.
Send alerts somewhere people look. An email to an unmonitored address is not an alert. A message in a channel the team reads, or a page for the person on call, is.
Automate the restore test. The monthly restore script above is short and is the only thing that proves the whole chain works end to end. Failing that test should alert as loudly as a failing backup, because it means every backup you hold may be unusable.
Keep credentials out of the script. The version here reads configuration from environment variables with sensible defaults, and relies on .pgpass for the password, so the script itself can be committed to version control without exposing anything.
For anything beyond a single small database, consider a purpose-built tool. pgBackRest and Barman handle parallel backup, incremental backup, compression, encryption, retention policies, integrity verification and point-in-time recovery, and they have already solved the edge cases a hand-written script eventually meets. The script in this lesson is genuinely useful for a single VPS, and it is also the right thing to outgrow.
Finally: write down where backups live, how to restore them, and who to contact. Recovery is carried out by people under pressure, frequently not the person who wrote the script.
Common mistakes #
- Omitting
set -euo pipefail, so a failed dump is followed by retention deleting good backups. - Running retention before verifying the new backup succeeded.
- Sending alerts to an address or channel that nobody actually monitors.
- Having no dead-man’s switch, so a script that stops running entirely raises no alarm.
- Never testing the failure path, so the alerting is as unproven as an untested backup.
Practice #
Install the script on a test server and run it manually, reading every line of output. Then break it deliberately, three ways: change the password in .pgpass so authentication fails, point BACKUP_DIR at a full or non-existent filesystem, and set DB_NAME to a database that does not exist. Confirm each one exits non-zero, logs a clear message, removes any partial file, and — importantly — leaves your existing older backups untouched. Finally, set up the monthly restore test and run it once.