接触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
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。