postgresql的备份

接触postgresql时间不长，目前还没找到一个快速学习的方法，对很多参数设置不是很了解（一同事也没接触过上来就是优化），为了一个数据库备份折腾了半个月，改了好几次备份策略终于搞定，闲话少说转入正题。

环境：数据库主要存位置信息，每月一个库，每天一张表，一主一从，从库备份

要求：备份近三个月的数据

方案一、全库备份

直接使用pg_dumpall进行全库备份，由于数据量大备份时间很长导致报错：

ERROR:  canceling statement due to conflict with recovery

DETAIL:  User query might have needed to see row versions that must be removed.

从网上找相关问题解决方法：

修改参数： max_standby_streaming_delay = 300 s;

                     max_standby_archive_delay

结果有时备份成功，有时备份失败而且报错和原来一样。

原因是由于备份过程中从库锁表时间过长，主从同步失败导致。

方案二：分库备份

使用pg_dump命令按月份备份数据库，当前月份的数据库备份没有问题，但是前两个月的数据库备份偶尔还是有错误，备份文件只有几百M，输出错误日志同上，原因同上

方案三：停止主从复制+分库备份

每周进行全库备份，每天备份当前月数据库，关键性的两个参数

暂停/恢复主从复制：pg_xlog_replay_pause()；

     pg_xlog_replay_resume()；

备份脚本已执行了近半个月，数据库备份一切正常。

总结：pg的主从复制是通过流复制来实现的，数据库备份过程中从库锁表时间过长将会导致主从复制失败，影响数据库备份。

附：当前月数据库备份脚本

#配置参数

USER=postgres

PASSWORD=''

PGLIB_DIR=/usr/local/postgresql

BACKUP_DIR=/usr/local/pgsql/databackup

DATE=`date +%Y%m%d`

LIST=(cetcnav_gps_`date +%Y%m` cetcnav_report_`date +%Y`)

#创建备份目录

mkdir $BACKUP_DIR/$DATE

chown postgres:root $BACKUP_DIR/$DATE

#暂停主从同步

su - postgres -c "${PGLIB_DIR}/bin/psql -c 'select * from pg_xlog_replay_pause();'"

#备份过程

for i in ${LIST[*]} 

        do

                #备份主体

                su - postgres -c "${PGLIB_DIR}/bin/pg_dump $i |gzip >  ${BACKUP_DIR}/$DATE/$i.gz" >> ${BACKUP_DIR}/$DATE/$i.log

            

                if [[ $? == 0 ]]; then 

                        #输入备份成功的消息到日记文件 

                        echo “Database $i-$DATE Backup Successful!” >> ${BACKUP_DIR}/$DATE/$i.log

                else

                        #如果出错，记录日志

                        echo “Database $i-$DATE Backup Fail!” >> ${BACKUP_DIR}/$DATE/$i.log

                fi

                #输出备份过程结束的提醒消息 

                echo “Backup Process Done”  >> ${BACKUP_DIR}/$DATE/$i.log

        done

#开启主从同步

su - postgres -c "${PGLIB_DIR}/bin/psql -c 'select * from pg_xlog_replay_resume();'"

#远程备份

#/usr/bin/rsync -auv --port=873  /$DATE  192.168.1.151::new  >>  ${BACKUP_DIR}/$DATE/$i.log

#####删除40天前的备份######

#find $BACKUP_DIR -name "*.tgz" -mtime +30 -type f -exec rm {} \; > /dev/null 2>&1