بکاپ خودکار PostgreSQL با اسکریپت، cron و حذف بکاپ‌های قدیمی

sudo mkdir -p /var/backups/postgres
sudo chown postgres:postgres /var/backups/postgres
sudo chmod 700 /var/backups/postgres

sudo tee /usr/local/bin/pg-backup.sh > /dev/null <<'EOF'
#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="/var/backups/postgres"
KEEP_DAYS=14
STAMP=$(date +%F_%H-%M)

# نقش‌ها و کاربران
pg_dumpall --globals-only | gzip > "$BACKUP_DIR/globals_${STAMP}.sql.gz"

# هر دیتابیس جداگانه
for DB in $(psql -Atc "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres'"); do
  OUT="$BACKUP_DIR/${DB}_${STAMP}.dump"
  pg_dump -Fc "$DB" -f "$OUT.tmp"
  mv "$OUT.tmp" "$OUT"
  echo "$(date '+%F %T') OK  $OUT ($(du -h "$OUT" | cut -f1))"
done

find "$BACKUP_DIR" -type f \( -name '*.dump' -o -name 'globals_*.sql.gz' \) -mtime +"$KEEP_DAYS" -delete
EOF
sudo chmod 755 /usr/local/bin/pg-backup.sh

sudo -u postgres /usr/local/bin/pg-backup.sh
ls -lh /var/backups/postgres/

sudo -u postgres crontab -e

# هر شب ساعت 03:30
30 3 * * * /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1

rsync -az -e "ssh -i /var/lib/postgresql/.ssh/backup_key" \
  /var/backups/postgres/ backup@203.0.113.20:/srv/backups/postgres/

sudo -u postgres createdb restore_test
sudo -u postgres pg_restore -d restore_test /var/backups/postgres/appdb_2026-09-29_03-30.dump
sudo -u postgres psql -d restore_test -c "SELECT count(*) FROM users;"
sudo -u postgres dropdb restore_test