So, here's what I did:Ĥ of the 7 tables that contain backup data do not have any foreign dependencies, so I simply truncated them. In my case, I didn't care about retaining any backup history. Mv $FINAL_BACKUP_DIR " $DATABASE ".sql.gz.in_progress $FINAL_BACKUP_DIR " $DATABASE ".sql.I haven't dealt with this problem for a while, but I'm sure it's getting large yet again. If ! pg_dump -Fp -h " $HOSTNAME " -U " $USERNAME " " $DATABASE " | gzip > $FINAL_BACKUP_DIR " $DATABASE ".sql.gz.in_progress then echo " Failed to produce plain backup database $DATABASE " 1> & 2 else
c ) if then source " $2 " shift 2 else $ do EXCLUDE_SCHEMA_ONLY_CLAUSE = " $EXCLUDE_SCHEMA_ONLY_CLAUSE and datname !~ ' $SCHEMA_ONLY_DB '" done FULL_BACKUP_QUERY = "select datname from pg_database where not datistemplate and datallowconn $EXCLUDE_SCHEMA_ONLY_CLAUSE order by datname " echo -e "\n\nPerforming full backups" echo -e "-\n" for DATABASE in `psql -h " $HOSTNAME " -U " $USERNAME " -At -c " $FULL_BACKUP_QUERY " postgres ` do if then echo "Plain backup of $DATABASE " set -o pipefail #!/bin/bash # LOAD CONFIG # while do case $1 in # SETTINGS FOR ROTATED BACKUPS # Which day to take the weekly backup from (1-7 = Monday-Sunday) DAY_OF_WEEK_TO_KEEP = 5 # Number of days to keep daily backups DAYS_TO_KEEP = 7 # How many weeks to keep weekly backups WEEKS_TO_KEEP = 5 # pg_backup.sh # Will produce gzipped sql file containing the cluster globals, like users and passwords, if set to "yes" ENABLE_GLOBALS_BACKUPS =yes # Will produce a gzipped plain-format backup if set to "yes" ENABLE_PLAIN_BACKUPS =yes "system_log" will match "dev_system_log_2010-01") SCHEMA_ONLY_LIST = "" # Will produce a custom-format backup if set to "yes" ENABLE_CUSTOM_BACKUPS =yes Any database names which contain any of these # values will be considered candidates. # List of strings to match against in database name, separated by space or comma, for which we only # wish to keep a backup of the schema, not the data. BACKUP_DIR =/home/backups/database/postgresql/ This must be writable by the user the script is # running as. USERNAME = # This dir will be created if it doesn't exist.
Will default to "postgres" if none specified. HOSTNAME = # Optional username to connect to database as. Will default to "localhost" if none specified.
BACKUP_USER = # Optional hostname to adhere to pg_hba policies. If the user the script is running as doesn't match this # the script terminates. # POSTGRESQL BACKUP CONFIG # Optional system user to run backups as.
0 kommentar(er)
