zabbix-postgres-backupconf.sh

#!/bin/bash

# Execute with a cron like:
# cd /srv/postgres && docker compose exec -u postgres  db /var/lib/postgresql/data/zabbix-postgres-backupconf.sh > /dev/null

# I'm not sure this backs up everything.
# stored procedures? routines? anything else?

# Also improved by passing a list of tables and table exclusions to pg_dump.
# This would probably make it so pg_dump only needs to be run twice instead of once for every table

DB_NAME="zabbix"
BACKUP_DIR="/db_dumps"

cd /tmp || exit 1


# Check if pg_dump is installed
#if ! command -v $PGDUMP &>/dev/null; then
#  echo "$PGDUMP not found. Please install PostgreSQL client tools."
#  exit 1
#fi

# Delete old backups
if [ -d "$BACKUP_DIR" ]; then
  echo "Deleting old backups"
  find $BACKUP_DIR -type f -mtime +200 -exec rm {} \;
fi

# List of tables to back up only schema
SCHEMA_ONLY=("alerts" "auditlog" "events" "event_recovery" "history" "history_log" "history_str" "history_text" "history_uint" "trends_uint" "trends")
#SCHEMA_ONLY="alerts auditlog events event_recovery history history_log history_str history_text history_uint trends_uint trends"

# Complete list of tables
#TABLES=$(psql -U $DBUSER -h $DBHOST -d $DB_NAME -t -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public';")
ALL_TABLES_STRING=$(psql -d $DB_NAME -t -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public';")
ALL_TABLES_STRING=$(echo "$ALL_TABLES_STRING" | tr -d '\n' | sed 's/^[[:space:]]*//;s/[[:space:]]*$//')

# Convert tables string to array
IFS=' ' read -ra ALL_TABLES <<< "$ALL_TABLES_STRING"
#IFS=$'\n' read -d '' -ra ALL_TABLES <<< "$ALL_TABLES_STRING"


# Create DATA_TABLES array by removing elements present in SCHEMA_ONLY from ALL_TABLES
DATA_TABLES=()
for table in "${ALL_TABLES[@]}"; do
    if [[ ! " ${SCHEMA_ONLY[@]} " =~ " $table " ]]; then
        DATA_TABLES+=("$table")
    fi
done



DUMPFILE="${BACKUP_DIR}/zbx-conf-bkup-$(date +%Y%m%d-%H%M).sql"
true >"${DUMPFILE}"

# echo "${my_array[@]}"

#echo "DATA_TABLES"
#echo "${DATA_TABLES[@]}"

#echo "ALL TABLES"
#echo "${ALL_TABLES[@]}"
#echo
#echo "ALL_TABLES_STRING"
#echo "$ALL_TABLES_STRING"

#echo "${SCHEMA_ONLY[@]}"


########## Backup data/configuration tables
#for table in "${DATA_TABLES[@]}"; do
#    echo "Backing up data+schema for table ${table}"
#    pg_dump -t "$table" -d $DB_NAME >>"${DUMPFILE}"
#done

# Construct pg_dump command
PGDUMP_CMD="pg_dump -d $DB_NAME"

for table in "${DATA_TABLES[@]}"; do
    PGDUMP_CMD+=" -t $table"
done

echo "Dumping data/configuration tables"
$PGDUMP_CMD >> "${DUMPFILE}"

########

########## Backup schema only for large data tables
#for table in "${SCHEMA_ONLY[@]}"; do
#    echo "Backing up schema for table ${table}"
#    pg_dump --schema-only -t "$table" -d $DB_NAME >>"${DUMPFILE}"
#done

# Construct pg_dump command
PGDUMP_CMD="pg_dump --schema-only -d $DB_NAME"

# Add tables to pg_dump command
for table in "${SCHEMA_ONLY[@]}"; do
    PGDUMP_CMD+=" -t $table"
done

echo "Dumping schema/history tables"
$PGDUMP_CMD >> "${DUMPFILE}"

########

#gzip -f "${DUMPFILE}"

echo
echo "Backup Completed - ${DUMPFILE}"