PostgreSQL的备份脚本

使用方法：
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
1. 建立备份目录，设置所有者为postgres
2. 修改pgsql_backup.conf文件，设置需要备份的数据库的数据目录database，和备份目录backup
3. 修改数据库的配置文件postgresql.conf文件，修改archive_command = '/path/to/pgsql_backup.sh archive %p %f'
4. 以postgres用户身份建立定期任务，定期执行pgsql_backup.sh xlog
5. 执行pgsql_backup.sh base进行基础备份(之前，最好执行一次vacuumdb -afz)
6. 执行pgsql_backup.sh clean <timestamp>清除指定时间以前的备份数据


以下是备份脚本pgsql_backup.sh

#!/bin/bash

# 备份PostgreSQL数据库
# 支持以下命令: 
#     base:     进行一个基础备份
#     archive:  归档数据库日志(用于设置postgresql.conf中的archive_command参数)
#     xlog:     复制当前部分填充的日志段
#     clean:    删除指定日期之前的备份数据
#

#database=/raid/postgresql/8.1/main
#backup=/data/backup/database

. /etc/admin/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 = '/raid/bin/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

  <script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>