您最多选择25个主题 主题必须以字母或数字开头,可以包含连字符 (-),并且长度不得超过35个字符

pg_backup_rotated.sh 5.5KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202
  1. #!/bin/bash
  2. ###########################
  3. ####### LOAD CONFIG #######
  4. ###########################
  5. while [ $# -gt 0 ]; do
  6. case $1 in
  7. -c)
  8. CONFIG_FILE_PATH="$2"
  9. shift 2
  10. ;;
  11. *)
  12. ${ECHO} "Unknown Option \"$1\"" 1>&2
  13. exit 2
  14. ;;
  15. esac
  16. done
  17. if [ -z $CONFIG_FILE_PATH ] ; then
  18. SCRIPTPATH=$(cd ${0%/*} && pwd -P)
  19. CONFIG_FILE_PATH="${SCRIPTPATH}/pg_backup.config"
  20. fi
  21. if [ ! -r ${CONFIG_FILE_PATH} ] ; then
  22. echo "Could not load config file from ${CONFIG_FILE_PATH}" 1>&2
  23. exit 1
  24. fi
  25. source "${CONFIG_FILE_PATH}"
  26. ###########################
  27. #### PRE-BACKUP CHECKS ####
  28. ###########################
  29. # Make sure we're running as the required backup user
  30. if [ "$BACKUP_USER" != "" -a "$(id -un)" != "$BACKUP_USER" ] ; then
  31. echo "This script must be run as $BACKUP_USER. Exiting." 1>&2
  32. exit 1
  33. fi
  34. ###########################
  35. ### INITIALISE DEFAULTS ###
  36. ###########################
  37. if [ ! $HOSTNAME ]; then
  38. HOSTNAME="localhost"
  39. fi;
  40. if [ ! $USERNAME ]; then
  41. USERNAME="postgres"
  42. fi;
  43. ###########################
  44. #### START THE BACKUPS ####
  45. ###########################
  46. function perform_backups()
  47. {
  48. SUFFIX=$1
  49. FINAL_BACKUP_DIR=$BACKUP_DIR"`date +\%Y-\%m-\%d`$SUFFIX/"
  50. echo "Making backup directory in $FINAL_BACKUP_DIR"
  51. if ! mkdir -p $FINAL_BACKUP_DIR; then
  52. echo "Cannot create backup directory in $FINAL_BACKUP_DIR. Go and fix it!" 1>&2
  53. exit 1;
  54. fi;
  55. #######################
  56. ### GLOBALS BACKUPS ###
  57. #######################
  58. echo -e "\n\nPerforming globals backup"
  59. echo -e "--------------------------------------------\n"
  60. if [ $ENABLE_GLOBALS_BACKUPS = "yes" ]
  61. then
  62. echo "Globals backup"
  63. if ! pg_dumpall -g -h "$HOSTNAME" -U "$USERNAME" | gzip > $FINAL_BACKUP_DIR"globals".sql.gz.in_progress; then
  64. echo "[!!ERROR!!] Failed to produce globals backup" 1>&2
  65. else
  66. mv $FINAL_BACKUP_DIR"globals".sql.gz.in_progress $FINAL_BACKUP_DIR"globals".sql.gz
  67. fi
  68. else
  69. echo "None"
  70. fi
  71. ###########################
  72. ### SCHEMA-ONLY BACKUPS ###
  73. ###########################
  74. for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
  75. do
  76. SCHEMA_ONLY_CLAUSE="$SCHEMA_ONLY_CLAUSE or datname ~ '$SCHEMA_ONLY_DB'"
  77. done
  78. SCHEMA_ONLY_QUERY="select datname from pg_database where false $SCHEMA_ONLY_CLAUSE order by datname;"
  79. echo -e "\n\nPerforming schema-only backups"
  80. echo -e "--------------------------------------------\n"
  81. SCHEMA_ONLY_DB_LIST=`psql -h "$HOSTNAME" -U "$USERNAME" -At -c "$SCHEMA_ONLY_QUERY" postgres`
  82. echo -e "The following databases were matched for schema-only backup:\n${SCHEMA_ONLY_DB_LIST}\n"
  83. for DATABASE in $SCHEMA_ONLY_DB_LIST
  84. do
  85. echo "Schema-only backup of $DATABASE"
  86. if ! pg_dump -Fp -s -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress; then
  87. echo "[!!ERROR!!] Failed to backup database schema of $DATABASE" 1>&2
  88. else
  89. mv $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz
  90. fi
  91. done
  92. ###########################
  93. ###### FULL BACKUPS #######
  94. ###########################
  95. for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
  96. do
  97. EXCLUDE_SCHEMA_ONLY_CLAUSE="$EXCLUDE_SCHEMA_ONLY_CLAUSE and datname !~ '$SCHEMA_ONLY_DB'"
  98. done
  99. FULL_BACKUP_QUERY="select datname from pg_database where not datistemplate and datallowconn $EXCLUDE_SCHEMA_ONLY_CLAUSE order by datname;"
  100. echo -e "\n\nPerforming full backups"
  101. echo -e "--------------------------------------------\n"
  102. for DATABASE in `psql -h "$HOSTNAME" -U "$USERNAME" -At -c "$FULL_BACKUP_QUERY" postgres`
  103. do
  104. if [ $ENABLE_PLAIN_BACKUPS = "yes" ]
  105. then
  106. echo "Plain backup of $DATABASE"
  107. if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress; then
  108. echo "[!!ERROR!!] Failed to produce plain backup database $DATABASE" 1>&2
  109. else
  110. mv $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE".sql.gz
  111. fi
  112. fi
  113. if [ $ENABLE_CUSTOM_BACKUPS = "yes" ]
  114. then
  115. echo "Custom backup of $DATABASE"
  116. if ! pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" -f $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress; then
  117. echo "[!!ERROR!!] Failed to produce custom backup database $DATABASE"
  118. else
  119. mv $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress $FINAL_BACKUP_DIR"$DATABASE".custom
  120. fi
  121. fi
  122. done
  123. date > $FINAL_BACKUP_DIR/complete
  124. echo -e "\nAll database backups complete!"
  125. }
  126. # MONTHLY BACKUPS
  127. DAY_OF_MONTH=`date +%d`
  128. if [ $DAY_OF_MONTH -eq 1 ];
  129. then
  130. # Delete all expired monthly directories
  131. find $BACKUP_DIR -maxdepth 1 -name "*-monthly" -exec rm -rf '{}' ';'
  132. perform_backups "-monthly"
  133. exit 0;
  134. fi
  135. # WEEKLY BACKUPS
  136. DAY_OF_WEEK=`date +%u` #1-7 (Monday-Sunday)
  137. EXPIRED_DAYS=`expr $((($WEEKS_TO_KEEP * 7) + 1))`
  138. if [ $DAY_OF_WEEK = $DAY_OF_WEEK_TO_KEEP ];
  139. then
  140. # Delete all expired weekly directories
  141. find $BACKUP_DIR -maxdepth 1 -mtime +$EXPIRED_DAYS -name "*-weekly" -exec rm -rf '{}' ';'
  142. perform_backups "-weekly"
  143. exit 0;
  144. fi
  145. # DAILY BACKUPS
  146. # Delete daily backups 7 days old or more
  147. find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*-daily" -exec rm -rf '{}' ';'
  148. perform_backups "-daily"