关于设置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=on
archive_command='/var/lib/pgsql/backups/pgsql_backup.sh archive %p %f'
archive_timeout = 600


2. vi /var/lib/pgsql/backups/pgsql_backup.sh  to 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/data
backup=/opt/pgsql_bak
#. /etc/admin/pgsql_backup.conf
#. /var/lib/pgsql/backups/pgsql_backup.conf
base()
{
local v=$(date "+%Y%m%d")
local d="$backup/base/$v"
mkdir -p "$d"

# vacuumdb -afz
psql 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" ]; then
return 1
fi

mkdir -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 '^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}$')

if [ "$t" = "" ]; then
echo "Timestamp '$1' error"
return 1
fi

touch -d "$t" "$backup/base/timestamp"
echo -n "Clean archived logs before $(date -r "$backup/base/timestamp" +"%Y-%m-%d")? [y/N] "
read a
if [ "$a" != "y" ]; then
return 1
fi

echo "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 in
base)
base
;;
archive)
archive "$2" "$3"
;;
clean)
clean "$2"
;;
xlog)
xlog
;;
*)
echo "usage: $0 {base|archive|xlog|clean}"
exit 1
;;
esac


3. chmod a+x /var/lib/pgsql/backups/pgsql_backup.sh

4. chown postgres /var/lib/pgsql/backups/pgsql_backup.sh

5. sudo mkdir /opt/pgsql_bak

6. sudo chown -R postgres /opt/pgsql_bak

7. sudo chmod -R 775 /opt/pgsql_bak

8. sudo /etc/init.d/postgresql restart

9.  Base Backup 

vacuumdb -afz

pgsql_backup.sh base

--- Cron 每分钟执行,这样可以恢复到1分钟内的数据
10. pgsql_backup.sh xlog 

--- 清除以前的备份
11.pgsql_backup.sh clean

--- 添加cron job

12. 

--- Rebuild Database for recovering

1. sudo /etc/init.d/postgresql stop
2, sudo mv /var/lib/pgsql/data/* /tmp/postgresqlBak
3. sudo cp -R /opt/pgsql_bak/base/* /var/lib/pgsql/data/
4.sudo mkdir /var/lib/pgsql/data/pg_xlog
5. sudo mkdir /var/lib/pgsql/data/pg_xlog/archive_status
6. sudo vi  /var/lib/pgsql/data/recovery.conf 
# set the archive log backup folder
restore_command = 'cp  /opt/pgsql_bak/archive/%f %p'
7. sudo /etc/init.d/postgresql start