111 lines
3.7 KiB
Bash
Executable file
111 lines
3.7 KiB
Bash
Executable file
#!/usr/bin/env bash
|
|
#
|
|
# Daily binary backup of the `prop` Postgres database.
|
|
#
|
|
# Designed to run from cron on the database server itself. pg_dump writes
|
|
# to a local scratch path (fast NVMe) first so the DB connection isn't
|
|
# throttled by SMB/NFS throughput, then the completed file is copied
|
|
# over to /backups/prop (the network share) and atomically renamed into
|
|
# place. Dumps older than RETENTION_DAYS are pruned.
|
|
#
|
|
# Install a crontab entry like:
|
|
#
|
|
# 0 3 * * * BACKUP_DIR=/backups/prop /opt/backup_prop_db.sh >> /var/log/prop-backup.log 2>&1
|
|
#
|
|
# Restore:
|
|
#
|
|
# pg_restore -d prop_restore -Fc /backups/prop/prop_20260417_030000.dump
|
|
|
|
set -euo pipefail
|
|
|
|
DB_NAME="${DB_NAME:-prop}"
|
|
DB_USER="${DB_USER:-postgres}"
|
|
BACKUP_DIR="${BACKUP_DIR:-/backups}"
|
|
LOCAL_STAGE_DIR="${LOCAL_STAGE_DIR:-/var/tmp/prop-backup}"
|
|
RETENTION_DAYS="${RETENTION_DAYS:-7}"
|
|
|
|
timestamp=$(date -u +'%Y%m%d_%H%M%S')
|
|
filename="${DB_NAME}_${timestamp}.dump"
|
|
stage_file="${LOCAL_STAGE_DIR}/${filename}"
|
|
dest_partial="${BACKUP_DIR}/${filename}.partial"
|
|
dest_final="${BACKUP_DIR}/${filename}"
|
|
|
|
log() {
|
|
printf '%s backup_prop_db: %s\n' "$(date -u +'%Y-%m-%dT%H:%M:%SZ')" "$*"
|
|
}
|
|
|
|
die() {
|
|
log "ERROR: $*"
|
|
exit 1
|
|
}
|
|
|
|
cleanup_stage() {
|
|
# Always remove the local scratch file, whether we succeeded or
|
|
# crashed mid-run. Keeps /var/tmp from slowly filling up.
|
|
[[ -n "${stage_file:-}" && -f "$stage_file" ]] && rm -f "$stage_file"
|
|
}
|
|
trap cleanup_stage EXIT
|
|
|
|
# --- sanity checks ---
|
|
command -v pg_dump >/dev/null || die "pg_dump not on PATH"
|
|
|
|
# Verify BACKUP_DIR lives on a real network mount — not the root filesystem
|
|
# (which would mean /backups is missing and we'd silently fill the OS disk).
|
|
# `stat -c %m` returns the mount point of the filesystem holding the path.
|
|
mount_root=$(stat -c '%m' "$BACKUP_DIR" 2>/dev/null || true)
|
|
[[ -z "$mount_root" ]] && die "$BACKUP_DIR does not exist"
|
|
[[ "$mount_root" == "/" ]] && die "$BACKUP_DIR is on the root filesystem — network share not mounted"
|
|
[[ -w "$BACKUP_DIR" ]] || die "$BACKUP_DIR is not writable"
|
|
|
|
# Local staging area — must exist and be writable for pg_dump. Created
|
|
# lazily so fresh installs don't need a separate provisioning step.
|
|
mkdir -p "$LOCAL_STAGE_DIR"
|
|
[[ -w "$LOCAL_STAGE_DIR" ]] || die "$LOCAL_STAGE_DIR is not writable"
|
|
|
|
# --- dump to local scratch ---
|
|
log "dumping $DB_NAME -> $stage_file"
|
|
# -Fc: custom format (binary, compressed, selective-restore friendly)
|
|
# -Z 6: balanced compression level
|
|
# --no-owner / --no-acl: restores cleanly into a target DB with different
|
|
# role names, which is usually what you want for a DR restore.
|
|
pg_dump \
|
|
-U "$DB_USER" \
|
|
-d "$DB_NAME" \
|
|
-Fc \
|
|
-Z 6 \
|
|
--no-owner \
|
|
--no-acl \
|
|
-f "$stage_file"
|
|
|
|
size=$(stat -c '%s' "$stage_file" 2>/dev/null || stat -f '%z' "$stage_file")
|
|
log "dump complete ($(numfmt --to=iec "$size" 2>/dev/null || echo "$size bytes")) — copying to share"
|
|
|
|
# --- move to network share ---
|
|
# Cross-filesystem: cp then rename on the destination so readers never see
|
|
# a half-written *.dump. We can't use `mv` alone because its temp-file
|
|
# behavior across filesystems is GNU-specific, and some NFS/SMB clients
|
|
# don't preserve atomicity.
|
|
cp "$stage_file" "$dest_partial"
|
|
sync "$dest_partial" 2>/dev/null || true
|
|
mv "$dest_partial" "$dest_final"
|
|
log "wrote $dest_final"
|
|
|
|
# --- prune ---
|
|
log "pruning dumps older than ${RETENTION_DAYS} days"
|
|
find "$BACKUP_DIR" \
|
|
-maxdepth 1 \
|
|
-type f \
|
|
-name "${DB_NAME}_*.dump" \
|
|
-mtime +"${RETENTION_DAYS}" \
|
|
-print -delete
|
|
|
|
# Also clear any half-written destination files from a previous crashed
|
|
# run so they don't accumulate.
|
|
find "$BACKUP_DIR" \
|
|
-maxdepth 1 \
|
|
-type f \
|
|
-name "${DB_NAME}_*.dump.partial" \
|
|
-mtime +1 \
|
|
-print -delete
|
|
|
|
log "done"
|