PostgreSQL配置Streaming Replication集群

熬死你的

运行环境：

Primary:      192.168.111.150

Standby:     192.168.111.151

                  192.168.111.152

OS：           CentOS5.8                  

PostgreSQL: 9.1.2 版本以上才支持Streaming Replication。





1. 环境规划

   Primary和Standby节点最好有相同的环境。





2. 安装PostgreSQL

1）在Primary和Standy节点上安装PostgreSQL软件，安装路径为/opt/pgsql-9.1.2

2）设置postgres用户的环境变量

     PGHOME=/opt/pgsql-9.1.2

     PGDATA=/storage0/database/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.111.1/24        trust

     在# replication privilege.下面添加一行，设置replication用户及权限

     host     replication     postgres        192.168.111.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 = 5

     wal_keep_segments = 32



     archive_mode = on

     archive_command = 'cp %p /storage0/database/postgres/archive/%f < /dev/null'

     "/storage0/database/postgres/archive"是Replication的archive的存储路径。PostgreSQL会将Replication的WAL保存在 "/storage0/database/postgres/archive"路径下。

5) 启动Primary上的PostgreSQL数据库

    $pg_ctl start

6) 在primary上执行以下命令

    $psql -c "SELECT pg_start_backup('label', true)"

    将Primary的PGDATA目录下的文件，除了postmaster.pid复制到Standby节点的“/storage0/database/postgres/main”目录下，该目录是 Standby节点上的PostgreSQL数据库的PGDATA目录。

    $rsync -a ${PGDATA}/ postgres@192.168.111.151:/storage0/database/postgres/main --exclude postmaster.pid

    $psql -c "SELECT pg_stop_backup()"

   

    192.168.111.151的/storage0/database/postgres/main目录下的内容为

   




4. Standby节点

    PGDATA=/storage0/database/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.111.150 port=5432 user=postgres'
# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/storage0/database/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 /storage0/database/postgres/archive/%f %p'
--------------------------------------------------------------------------------------------------------------------------


3）复制pg_xlog下的所有文件到/storage0/database/postgres/archive目录下

4）启动Standby节点，完成Replication。

完成Streaming Replication配置。
启动pgAdmin，连接192.168.111.150节点，创建test数据库，刷新 192.168.111.151节点。可以看到在192.168.111.151上也生成了test数据库。