配置PostgreSQL Streaming Replication集群
运行环境：
Primary: 192.168.0.11
Standby: 192.168.0.21, 192.168.0.22
OS： CentOS 6.2
PostgreSQL: 9.1.2 版本以上才支持Streaming Replication。
1. 环境规划
Primary和Standby节点最好有相同的环境。
2. 安装PostgreSQL
1）在Primary和Standy节点上安装PostgreSQL软件，安装路径为/opt/pgsql
2）设置postgres用户的环境变量
PGHOME=/opt/pgsql
PGDATA=/home/postgres/main
PATH=$PG_HOME/bin:$PATH:$HOME/bin
3. Primary节点
1）切换到postgres用户
$su - postgres
2）初始化数据库
$initdb
3）配置pg_hba.conf
在# IPv4 local connections下面添加一行，设置PostgreSQL的访问及其权限
host all all 192.168.0.1/24 trust
在# replication privilege.下面添加一行，设置replication用户及权限
host replication postgres 192.168.0.1/24 trust
4）配置postgresql.conf
配置监听，修改listen_addresses = 'localhost'
listen_addresses = '*' # what IP address(es) to listen on;
配置Primary Replication参数
wal_level = hot_standby
max_wal_senders = 8
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /home/postgres/archive/%f < /dev/null'
"/home/postgres/archive"是Replication的archive的存储路径。PostgreSQL会将Replication的WAL保存在 "/home/postgres/archive"路径下。
5) 启动Primary上的PostgreSQL数据库
$pg_ctl start
6) 在primary上执行以下命令
$psql -c "SELECT pg_start_backup('label', true);"
将Primary的PGDATA目录下的文件，除了postmaster.pid复制到Standby节点的“/home/postgres/main”目录下，该目录是 Standby节点上的PostgreSQL数据库的PGDATA目录。
$rsync -a ${PGDATA}/ postgres@192.168.0.21:/home/postgres/main --exclude postmaster.pid
$psql -c "SELECT pg_stop_backup();"
192.168.0.21的/home/postgres/main目录下的内容为
4. Standby节点
PGDATA=/home/postgres/main
Standby节点的PGDATA路径就是Primary节点的PGDATA的副本
1）配置postgresql.conf
设置hot_standby为
hot_standby= on
2）编辑recovery.conf，文件路径为$(PGDATA)/recovery.conf，内容为
---------------------------------------------------------------------------------------------------------------------------
# Specifies whether to start the server as a standby. In streaming replication,
# this parameter must to be set to on.
standby_mode = 'on'
# Specifies a connection string which is used for the standby server to connect
# with the primary.
primary_conninfo = 'host=192.168.0.11 port=5432 user=postgres'
# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/home/postgres/trigger'
# Specifies a command to load archive segments from the WAL archive. If
# wal_keep_segments is a high enough number to retain the WAL segments
# required for the standby server, this may not be necessary. But
# a large workload can cause segments to be recycled before the standby
# is fully synchronized, requiring you to start again from a new base backup.
restore_command = 'cp /home/postgres/archive/%f %p'--------------------------------------------------------------------------------
3）复制pg_xlog下的所有文件到/home/postgres/archive目录下
4）启动Standby节点，完成Replication。
完成Streaming Replication配置。
5. 验证
使用psql连接primary，创建test数据库。刷新从节点，可以看到在其上也生成了test数据库。
6. 问题
1）Standby的data目录是空的，其所有配置均是在rsync之后；
2）recovery.conf文件其实只需要修改standby_mode 和 primary_info，其余并不是必须的；
3）注意Primary的防火墙，必要时关闭，免得干扰集群配置；
4）如果从节点PostgreSQL已有数据，需要全部删除。
参考:
PostgreSQL配置Streaming Replication集群(http://www.cnblogs.com/marsprj/archive/2013/03/04/2943373.html)
http://opensourcedbms.com/dbms/setup-replication-with-postgres-9-2-on-centos-6redhat-el6fedora/
