PostgreSQL 数据库的 primary、standby 架设

Standby技术基于PostgreSQL的预写入日志(wal)同步机制，建立服务器级别的数据同步节点。
在以前的版本中，我们通常使用PostgreSQL的配置项 archive_cmmand将 wal 存档于一个指定的路径，
standby使用recovery 配置项 restore_command指定一个系统命令，自行获取需要恢复的wal；standby节点不提供读操作。仅限于做备份节点。

PostgreSQL9.0 版本的 Standby的改进。
快速同步：允许尽快的 将处理中的wal发布出去。
流式同步：允许standby节点连接到primary节点，以流式获取新的wal 数据，达到实时的 hot standby。
数据压缩：允许使用压缩方式传输wal，节省带宽。
standby节点可读：当standby节点打开hot standby选项，可以实时进行读查询。

配置环境：

primary：10.0.0.2
Standby：10.0.0.3
两台服务器已经安装好PostgreSQL9.0版本
操作系统基于 FreeBSD 8.1 （Linux下操作应该差不多）

以下操作均使用系统预置数据库操作用户 pgsql 操作

初始化数据库：  pg_ctl -D /data/primary initdb

1、Primary服务器配置（10.0.0.2）
设置同步账号
       直接使用 pgsql 系统用户。

修改访问控制
vim pg_hba.conf
最后添加以下内容
host replication     pgsql     10.0.0.3/32     trust   #直接使用IP地址验证，不需要密码

修改postgresql服务配置文件
vim postgresql.conf

主要配置修改如下
wal_level = hot_standby
archive_mode = on
archive_command = 'cp -i %p patchto_archive/%f </dev/null'
archive_timeout = 600
max_wal_senders = 1
wal_keep_segments = 16

我的postgresql.conf 配置文件配置项  

listen_addresses = '*' 
max_connections = 200  
#unix_socket_directory = '/var/postgres/primary'
shared_buffers = 1024MB
temp_buffers = 128MB
work_mem = 8MB 
wal_level = hot_standby
archive_mode = on  
archive_command = '/var/postgres/shell/pgsql_backup.sh archive %p %f'
archive_timeout = 600
max_wal_senders = 5
wal_sender_delay = 1s
wal_keep_segments = 32
hot_standby = on
log_destination = 'syslog'
syslog_facility = 'LOCAL1'
silent_mode = on
log_min_duration_statement = 1000
log_checkpoints = on
log_line_prefix = '<%h|%u>'
update_process_title = off
bytea_output = 'escape'
datestyle = 'iso, ymd'
lc_messages = 'zh_CN.UTF-8'
lc_monetary = 'zh_CN.UTF-8'
lc_numeric = 'zh_CN.UTF-8' 
lc_time = 'zh_CN.UTF-8'
default_text_search_config = 'pg_catalog.simple'

可在vim里使用下来替换方式获取定义的配置项
vim postgresql.conf
:%s/^\t\{1,\}//g
:%s/^ \t\{1,\}//g
:%s/^#.\{1,\}\n//g
:%s/#.*//g
:g/^\s*$/d
:%s/\t//g

配置说明参考网址
http://blog.163.com/czg_e/blog/static/46104561201141111445789/

创建socket目录
mkdir -p -m 700 /var/postgres/primary

启动Primary服务器数据库进程
pg_ctl -D /data/primary start


2、创建standby数据库恢复点备份（Primary库）
cd /data/primary
psql -h  /var/postgres/primary postgres -c "SELECT pg_start_backup('backup', true)"
#压缩数据库目录并同步到standby（10.0.0.3）上
tar -czf  /tmp/primary.tar.gz *
scp /tmp/primary.tar.gz pgsql@10.0.0.3:/data
psql -h  /var/postgres/primary postgres -c "SELECT pg_stop_backup()"

3、standby服务器配置

mkdir -m 700 /data/standby
cd /data/standby
tar -zxf  /data/primary.tar.gz -C .
rm -f  postmaster.*

修改postgresql.conf 
hot_standby = on 

创建 recovery.conf 文件
standby 服务端  recovery.conf 文件内容

standby_mode  = 'on'
primary_conninfo  = 'host=10.0.0.2 port=5432 user=pgsql'
trigger_file = '/var/postgres/standby/standby_trigger'
restore_command = 'tar -zxf /var/postgres/primary/archive/%f.tar.gz && mv %f %p'

trigger_file 配置项的作用为当配置项中指定的文件被创建出来，standby 节点将退出standby模式，数据库不再与primary节点同步，并提供写操作。
restore_command 配置项的作用仅在由于某些原因当主DB上已经完成归档，而standby例如网络故障尚未完成同步时使用归档文件来恢复数据之用。

当然还有一个前提条件就是已经将主DB上的归档文件同步到辅DB的对于目录中。

启动standby数据库系统
pg_ctl -D /data/standby start

检查数据库日志，是否正常启动

实验中安装了 pgpool-II 组件，该组件可实现自动将写操作分配到primary库，读操作可实现primary、standby共同分担。

功能看上去很诱人，本想利用该组件实现实现postgreSQL数据库的自动主备切换，实现数据库的更高的可用性。

但后来的负载测试结果让人大跌眼界，性能实在是太烂。（使用sysbench测试）

附：

FreeBSD系统上需要修改几个系统运行参数

下面的数值为postgresql.conf 中配置200个socket连接的系统配置值（系统需重启生效）
/etc/sysctl.conf
kern.ipc.shmall=2105344
kern.ipc.shmmax=8931270656
kern.ipc.semmap=256

/boot/loader.conf 
kern.ipc.semmni=256
kern.ipc.semmns=1024
kern.ipc.semmnu=256

系统日志配置

/etc/syslog.conf
local1.*;local1.!warning /var/postgres/log/postgresql.log
local1.warning                             /var/postgres/log/postgresql-warning.log

自启动配置(/etc/rc.conf)

primary库
postgresql_enable="YES"
postgresql_data="/data/primary"

standby库
postgresql_enable="YES"
postgresql_data="/data/standby"

pgsql_backup.sh 数据库归档脚本

可实现数据库的完整备份、wal归档、pg_xlog的同步和旧备份或归档的清理功能

将使用不同参数的该脚本加入到计划任务中执行  (/etc/crontab)

#每5分钟同步一次 pg_xlog目录中的尚未完成归档的日志文件
*/5     *       *       *       *       pgsql  /var/postgres/shell/pgsql_backup.sh xlog > /dev/null 2>&1
#删除4天前的备份及归档
40      1       *       *       *       pgsql  /var/postgres/shell/pgsql_backup.sh clean 4 > /dev/null 2>&1 
#数据库完整备份
0       2       *       *       *       pgsql  /var/postgres/shell/pgsql_backup.sh base > /dev/null 2>&1

文件  pgsql_backup.sh 

#!/usr/local/bin/bash

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

dbname='mydb'
database=database_path/mydb
backup=backup_path/mydb

psql_cmd=/usr/local/bin/psql
sync_cmd=/usr/local/bin/rsync

base()
{
        local v=$(date "+%Y%m%d")
        local d="$backup/base"

        if [ ! -d $d ]; then
                mkdir -p "$d"
        fi

        if [ -f "$d/base_$v.tar.gz" ]; then
                return 1
        fi

        RUN=`ps ax | grep 'pgsql_backup.sh base' | grep -v grep | wc -l`
        if [ "$RUN" -gt 2 ]; then 
                echo "backup is already running"
                exit 1 
        fi 

        vacuumdb -afz
        $psql_cmd template1 -c "select pg_start_backup('$v');"       
        (cd "$database"; tar -czf $d/$dbname"_"$v".tar.gz"  $(ls | sed 's/pg_xlog//') )
        $psql_cmd template1 -c "select pg_stop_backup();"
}

# archive_command = '/pathto/pgsql_backup.sh archive %p %f'
archive()
{
        local p="$1"
        local f="$2"

        if [ "$p" = "" ] || [ "$f" = "" ] || [ -f "$backup/archive/$f.tar.gz" ]; then
                return 1
        fi

        if [ ! -d "$backup/archive" ]; then
                mkdir -p "$backup/archive"
        fi
        #cp -a "$p" "$backup/archive/$f"
        #使用打包压缩处理归档日志文件，数据量将大大减小，当数据库较繁忙时建议不采用压缩方式归档
        #使用压缩方式归档在做数据库恢复操作时 restore_command 使用
        # restore_command = 'tar -zxf archive_patch/%f.tar.gz && mv %f %p'
        #否则恢复失败
        cd pg_xlog && tar -czf "$backup/archive/$f.tar.gz" "$f"
}

xlog()
{
        if [ -d "$database/pg_xlog" ]; then
                mkdir -p "$database/pg_xlog"
        fi
        $sync_cmd -a --delete "$database/pg_xlog" "$backup"
}

clean()
{
    local t=$(echo "$1" | grep -E '^[0-9]{1,2}$')
    if [ "$t" = "" ]; then
        #echo "Day Number '$1' error"
        return 1
    fi

    find $backup/archive -mindepth 1 -maxdepth 1 -mtime +"$1" -exec rm -rf {} \;
    find $backup/base -mindepth 1 -maxdepth 1 -mtime +"$1" -exec rm -rf {} \;
    #echo "done."
}


case $1 in
        base)
                base
                ;;
        archive)
                archive "$2" "$3"
                ;;
        xlog)
                xlog
                ;;
        clean)
                clean "$2"
                ;;
        *)
                echo "usage: $0 {base|archive|xlog|clean}"
                exit 1
                ;;
esac