数据库备份
工作需要定时备份postgresql slave数据库数据数据，服务器上运行了两个slave实例，隶属于两个不同的master。 备份 两个slave server实例分别监听在 5432和 4432端口 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 #!/bin/bash # # Daily PostgreSQL maintenance: vacuuming and backuping. # ## set -e for port in 5432 4432; do BACKDIR="/data/pg_back/$port" [ -d $BACKDIR ] || mkdir -p $BACKDIR echo "[`date`] begin Maintaining pg on port $port" # no need to use -U option for DB in $(psql -l -t -p $port |awk '{ print $1}' |grep -vE '^-|:|^List|^Name|template[0|1]|postgres|\|'); do ### swith form 'awk and grep' hacks to psql options and 'select sql' ### which is more dbaer professioner :) for DB in $(psql -AqXtc 'SELECT datname FROM pg_database WHERE datistemplate = false;'); do echo " [`date`] Maintaining $DB" PREFIX="$BACKDIR/$DB" # NO need to do `vacuum` on slaves # do `vacunm` on master instead # echo 'VACUUM' | psql -U postgres -hlocalhost -d $DB DUMP="$PREFIX.`date '+%Y%m%d'`.sql.gz" # no need for -U postgres option pg_dump -p $port $DB | gzip -c > $DUMP PREV="$PREFIX.`date -d'1 day ago' '+%Y%m%d'`.sql.gz" # md5sum -b $DUMP > $DUMP.md5 md5=($(md5sum -b $DUMP)) echo $md5 > $DUMP.md5 if [ -f $PREV.md5 ] && diff $PREV.md5 $DUMP.md5; then rm -f $PREV $PREV.md5 fi ## delete too old backup TOOOLD="$PREFIX.`date -d'15 day ago' '+%Y%m%d'`.sql.gz" [ -f $TOOOLD ] || rm -f $TOOOLD done echo "[`date`] Maintain pg on port $port finished" done 参考：Automatic Offsite PostgreSQL Backups Without a Password ...