关于设置Postgresql Increment的备份方式
参考链接:http://hi.baidu.com/top2top/blog/item/42f0a98b3635867b9f2fb4be.html
http://www.javaworld.com.tw/roller/ingramchen/entry/2007_5_25_SimplePostgreSQLBackup
http://www.liqiuyun.com/logs/31044543.html
特此声明,对以上三位作者表示感谢.
1. vi /var/lib/pgsql/data/postgresql to add as following lines:archive_mode=onarchive_command='/var/lib/pgsql/backups/pgsql_backup.sh archive %p %f'archive_timeout = 6002. vi /var/lib/pgsql/backups/pgsql_backup.shto add lines like:#!/bin/bash# 备份PostgreSQL数据库# 支持以下命令:# base: 进行一个基础备份# archive: 归档数据库日志(用于设置postgresql.conf中的archive_command参数)# xlog: 复制当前部分填充的日志段# clean: 删除指定日期之前的备份数据##database=/raid/postgresql/8.1/main#backup=/data/backup/database#database=/var/lib/pgsql/databackup=/opt/pgsql_bak#. /etc/admin/pgsql_backup.conf#. /var/lib/pgsql/backups/pgsql_backup.confbase(){local v=$(date "+%Y%m%d")local d="$backup/base/$v"mkdir -p "$d"# vacuumdb -afzpsql template1 -c "select pg_start_backup('$v');"(cd "$database"; cp -a $(ls | sed 's/pg_xlog//') "$d")psql template1 -c "select pg_stop_backup();"}# archive_command = '/var/lib/pgsql/backups/pgsql_backup.sh archive %p %f'archive(){local p="$1"local f="$2"if [ "$p" = "" ] || [ "$f" = "" ] || [ -f "$backup/archive/$f" ]; thenreturn 1fimkdir -p "$backup/archive"cp -a "$p" "$backup/archive/$f"}xlog(){mkdir -p "$database/pg_xlog"rsync -a --delete "$database/pg_xlog" "$backup"}clean(){local t=$(echo "$1" | grep -E '^{4}-{1,2}-{1,2}$')if [ "$t" = "" ]; thenecho "Timestamp '$1' error"return 1fitouch -d "$t" "$backup/base/timestamp"echo -n "Clean archived logs before $(date -r "$backup/base/timestamp" +"%Y-%m-%d")? "read aif [ "$a" != "y" ]; thenreturn 1fiecho "cleaning..."find $backup/archive -mindepth 1 -maxdepth 1 -not -newer $backup/base/timestamp -print -exec rm -rf {} \;find $backup/base -mindepth 1 -maxdepth 1 -not -newer $backup/base/timestamp -print -exec rm -rf {} \;echo "done."}case $1 inbase)base;;archive)archive "$2" "$3";;clean)clean "$2";;xlog)xlog;;*)echo "usage: $0 {base|archive|xlog|clean}"exit 1;;esac3. chmod a+x /var/lib/pgsql/backups/pgsql_backup.sh4. chown postgres /var/lib/pgsql/backups/pgsql_backup.sh5. sudo mkdir /opt/pgsql_bak6. sudo chown -R postgres /opt/pgsql_bak7. sudo chmod -R 775 /opt/pgsql_bak8. sudo /etc/init.d/postgresql restart9.Base Backup vacuumdb -afzpgsql_backup.sh base--- Cron 每分钟执行,这样可以恢复到1分钟内的数据10. pgsql_backup.sh xlog --- 清除以前的备份11.pgsql_backup.sh clean--- 添加cron job12. --- Rebuild Database for recovering1. sudo /etc/init.d/postgresql stop2, sudo mv /var/lib/pgsql/data/* /tmp/postgresqlBak3. sudo cp -R /opt/pgsql_bak/base/* /var/lib/pgsql/data/4.sudo mkdir /var/lib/pgsql/data/pg_xlog5. sudo mkdir /var/lib/pgsql/data/pg_xlog/archive_status6. sudo vi/var/lib/pgsql/data/recovery.conf # set the archive log backup folderrestore_command = 'cp/opt/pgsql_bak/archive/%f %p'7. sudo /etc/init.d/postgresql start
页:
[1]