backup-postgres.sh

#!/bin/bash
# Dump all Postgres databases from the Postgres VM.
# Run on the Proxmox host (or any machine with network access to PG).
#
# Prerequisites:
#   CREATE ROLE backup WITH LOGIN REPLICATION;
#   GRANT pg_read_all_data TO backup;  -- Postgres 14+
#
# Configure via environment variables or .env file:
#   POSTGRES_VM_IP  (default: 10.0.0.100)
#   PG_BACKUP_USER  (default: backup)
#
# Usage: ./backup-postgres.sh
set -euo pipefail

BACKUP_DIR="/var/backups/postgres"
TIMESTAMP=$(date +%Y-%m-%d_%H%M)
PG_HOST="${POSTGRES_VM_IP:-10.0.0.100}"
PG_USER="${PG_BACKUP_USER:-backup}"
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"

# Dump each database (excluding templates and postgres itself)
for db in $(psql -h "$PG_HOST" -U "$PG_USER" -d postgres -t -c \
    "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'"); do
    db=$(echo "$db" | xargs)  # trim whitespace
    echo "[$(date -Iseconds)] Dumping $db"
    pg_dump -h "$PG_HOST" -U "$PG_USER" -Fc "$db" \
        > "$BACKUP_DIR/${db}_${TIMESTAMP}.dump"
done

# Prune old daily backups
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete

echo "[$(date -Iseconds)] Local backup complete"