wjones
41942eb9c9
- Enhanced CSV parsing script to include advanced statistical analyses. - Implemented functions to detect consecutive numbers, odd/even distributions, high/low distributions, sum patterns, gap patterns, hot/cold numbers, overdue numbers, number pairs, and decade distributions. - Integrated pattern analysis results into combination generation for more strategic number selection. - Updated main function to run advanced analyses and generate pattern-based combinations.
148 lines
4.5 KiB
Bash
148 lines
4.5 KiB
Bash
#!/bin/bash
|
|
# Configuration
|
|
|
|
# If started by root (e.g., root crontab), re-run as postgres.
|
|
if [ "$(id -u)" -eq 0 ] && [ "$(id -un)" != "postgres" ]; then
|
|
if command -v runuser >/dev/null 2>&1; then
|
|
exec runuser -u postgres -- "$0" "$@"
|
|
fi
|
|
|
|
# Fallback for systems without runuser.
|
|
exec su - postgres -s /bin/bash -c "$(printf '%q ' "$0" "$@")"
|
|
fi
|
|
|
|
# Avoid permission warnings when root launches this script and postgres inherits /root as cwd.
|
|
cd / || exit 1
|
|
|
|
log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*"; }
|
|
|
|
: "${WAL_CLEANUP_ENABLED:=1}"
|
|
WAL_ARCHIVE_DIR="/var/lib/postgresql_archive/18/main/pg_wal"
|
|
WAL_MIN_AGE_DAYS=7
|
|
|
|
clean_walfiles() {
|
|
local newest_backup=""
|
|
local newest_backup_ts=""
|
|
local newest_backup_dt=""
|
|
local age_cutoff_ts=""
|
|
local age_cutoff_dt=""
|
|
local backup_ts=""
|
|
local file_ts=""
|
|
local deleted=0
|
|
local backup_file=""
|
|
local file=""
|
|
local -a backup_files=()
|
|
local -a files_to_delete=()
|
|
|
|
if [[ ! -d "$WAL_ARCHIVE_DIR" ]]; then
|
|
log "Archive directory does not exist: $WAL_ARCHIVE_DIR" >&2
|
|
return 1
|
|
fi
|
|
|
|
if ! [[ "$WAL_MIN_AGE_DAYS" =~ ^[0-9]+$ ]]; then
|
|
log "WAL_MIN_AGE_DAYS must be a non-negative integer" >&2
|
|
return 1
|
|
fi
|
|
|
|
mapfile -d '' backup_files < <(find "$WAL_ARCHIVE_DIR" -maxdepth 1 -type f -name '*.backup' -print0)
|
|
|
|
if [[ ${#backup_files[@]} -eq 0 ]]; then
|
|
log "No .backup files found in $WAL_ARCHIVE_DIR; skipping WAL cleanup."
|
|
return 0
|
|
fi
|
|
|
|
for backup_file in "${backup_files[@]}"; do
|
|
backup_ts=$(stat -c %Y "$backup_file")
|
|
if [[ -z "$newest_backup_ts" || "$backup_ts" -gt "$newest_backup_ts" ]]; then
|
|
newest_backup_ts="$backup_ts"
|
|
newest_backup="$backup_file"
|
|
fi
|
|
done
|
|
|
|
age_cutoff_ts=$(date -d "$WAL_MIN_AGE_DAYS days ago" +%s)
|
|
newest_backup_dt=$(date -d "@$newest_backup_ts" '+%Y-%m-%d %H:%M:%S')
|
|
age_cutoff_dt=$(date -d "@$age_cutoff_ts" '+%Y-%m-%d %H:%M:%S')
|
|
|
|
log "WAL archive dir : $WAL_ARCHIVE_DIR"
|
|
log "Newest .backup file : $newest_backup"
|
|
log "Newest .backup mtime : $newest_backup_dt"
|
|
log "WAL age cutoff : $age_cutoff_dt"
|
|
|
|
while IFS= read -r -d '' file; do
|
|
file_ts=$(stat -c %Y "$file")
|
|
|
|
# Delete only when both constraints are true.
|
|
if [[ "$file_ts" -lt "$newest_backup_ts" && "$file_ts" -lt "$age_cutoff_ts" ]]; then
|
|
files_to_delete+=("$file")
|
|
fi
|
|
done < <(find "$WAL_ARCHIVE_DIR" -maxdepth 1 -type f ! -name '*.backup' -print0)
|
|
|
|
# Also delete old .backup files (except the newest one) that exceed retention period
|
|
for backup_file in "${backup_files[@]}"; do
|
|
if [[ "$backup_file" != "$newest_backup" ]]; then
|
|
backup_ts=$(stat -c %Y "$backup_file")
|
|
if [[ "$backup_ts" -lt "$age_cutoff_ts" ]]; then
|
|
files_to_delete+=("$backup_file")
|
|
fi
|
|
fi
|
|
done
|
|
|
|
if [[ ${#files_to_delete[@]} -eq 0 ]]; then
|
|
log "No WAL files matched cleanup criteria."
|
|
return 0
|
|
fi
|
|
|
|
echo ""
|
|
log "WAL files matching cleanup criteria (${#files_to_delete[@]}):"
|
|
for file in "${files_to_delete[@]}"; do
|
|
log " $file"
|
|
done
|
|
|
|
for file in "${files_to_delete[@]}"; do
|
|
rm -f -- "$file"
|
|
((deleted += 1))
|
|
done
|
|
|
|
echo ""
|
|
log "Deleted $deleted WAL file(s)."
|
|
}
|
|
|
|
log "Beginning base backup."
|
|
BASEBACKUP_ROOT="/var/lib/postgresql_archive/pgbackups"
|
|
BACKUP_DIR="/var/lib/postgresql_archive/pgbackups/basebackup"
|
|
mkdir -p "$BACKUP_DIR"
|
|
# Perform base backup with WAL streaming
|
|
pg_basebackup -h localhost -U wjones -D "$BACKUP_DIR" -Fp -Xs -P
|
|
# Optional: Compress
|
|
BASEBACKUP_ARCHIVE="${BACKUP_DIR}_$(date +%Y%m%d_%H%M%S).tar.gz"
|
|
tar -czf "$BASEBACKUP_ARCHIVE" -C "$(dirname "$BACKUP_DIR")" "$(basename "$BACKUP_DIR")"
|
|
rm -rf "$BACKUP_DIR"
|
|
# Retention: Delete backups older than 7 days
|
|
find "$BASEBACKUP_ROOT" -type f -name "*.tar.gz" -mtime +7 -delete
|
|
|
|
log "Base backup completed.Now backing up individual databases."
|
|
|
|
BACKUP_DIR="/var/lib/postgresql_archive/pg_dumpfiles"
|
|
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
|
|
RETENTION_DAYS=7
|
|
|
|
# Ensure directory exists
|
|
mkdir -p "$BACKUP_DIR"
|
|
|
|
# Get all non-system databases and back each one up.
|
|
psql -U postgres -d postgres -Atc "SELECT datname FROM pg_database WHERE datistemplate = false AND datname <> 'postgres' ORDER BY datname" | while IFS= read -r DB_NAME; do
|
|
pg_dump -U postgres -d "$DB_NAME" | gzip > "$BACKUP_DIR/${DB_NAME}_$TIMESTAMP.sql.gz"
|
|
log "Backup completed: ${DB_NAME}_$TIMESTAMP.sql.gz"
|
|
done
|
|
|
|
# Delete backups older than retention period
|
|
find "$BACKUP_DIR" -type f -mtime +"$RETENTION_DAYS" -delete
|
|
|
|
log "Individual database backups completed and old backups cleaned up."
|
|
if [[ "$WAL_CLEANUP_ENABLED" -eq 1 ]]; then
|
|
log "Starting WAL archive cleanup."
|
|
clean_walfiles
|
|
else
|
|
log "WAL archive cleanup disabled; skipping final cleanup."
|
|
fi
|
|
log "Backup workflow completed." |