"Postgresql" postgresql9.3.9 version based on stream replication mode dual-Machine hot standby scheme

System Environment: centos6.5
Database version: postgres9.3.9
Virtual Machine 2 units:
master:10.0.2.160
slave:10.0.2.69
Data storage location:/usr/local/pgsql/data/

2. Installing the Pgsql Database

   安装过程可参考我上一篇博客：http://blog.51cto.com/13632960/2117902两台机器都需要安装完成，我在做热备的时候，Master数据库开启，Slave关闭。

4. Create a Stream replication user

   Master端进入数据库并执行：CREATE USER repuser?replication PASSWORD ‘repuser‘;?

6. Configure Access file pg_hba.conf on the master side?

   vim /usr/local/pgsql/data/pg_hba.conf 在最后添加：host replication repuser?10.0.2.69/16 md5 #md5为连接方式 你们也可以选择其他的

8. Configuring the master-side profile postgresql.conf
   Create a folder for archive files before you modify: mkdir/usr/local/pgsql/data/archive
   Vim/usr/local/pgsql/data/postgresql.conf
   Modify the following:

   max_wal_senders = 1?wal_level = hot_standbyarchive_mode = on?archive_command = ‘cp %p /usr/local/pgsql/data/archive/%f‘‘hot_standby = onwal_keep_segments = 64

   注释： max_wal_senders是Slave库的节点数，有多少个slave库就设多少,? ? ?         wal_level是write ahead log参数值,设置流复制务必将此值更新成hot_standby?          ? ?wal_keep_segments默认值是16,是PG_XLOG下的日志文件数相关参数??          ? archive也可以选择关闭，归档是定时恢复用的，流复制不是必须的

10. Main Library backup--master side
    To turn on file backup:

    /usr/local/pgsql/bin/psql psql (9.3.9)Type "help" for help.postgres=# select pg_start_backup(‘backup_1‘);

12. Package The master-side/usr/local/pgsql/data/directory and send it to the slave server to exclude Pg_xlog directory content!!!!!

    cd /usr/local/pgsql/tar -czvf? data.tar.gz data--exclude=data/pg_xlog?Slave端如果已经安装了postgres数据库，数据目录名称一样的话，可以先停掉备机数据库，更改数据目录名称mv data data.old远程拷贝至slave端并在备机端解压scp data.tar.gz [email protected]:/usr/local/pgsql/拷贝完成后，进入数据库关闭备份：select pg_stop_backup(),current_timestamp;

14. Slave-side decompression and modification of the relevant configuration file

    cd /usr/local/pgsqltar -zxvf data.tar.gzcd  /usr/local/pgsql/datamkdir pg_xlog vim /usr/local/pgsql/data/postgresql.conf修改：hot_standby = on

16. The standby needs to configure the recovery.conf file to copy and rename the recovery.conf.sample in the share directory.

    cp  /usr/local/pgsql/share/recovery.conf.sample     /usr/local/pgsql/data/recovery.conf复制完成后修改如下内容：?standby_mode = ‘on‘primary_conninfo =‘host=10.0.2.69??port=5432 user=repuser password=repuser   keepalives_idle=60‘

18. Start the Slave library

    正常启动备库，有异常可以看log/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data >>logfile 2>&1

20. View the main library and boot status from the library
    Master:
    
    Slave:
    
22. At this point, the PostgreSQL streaming replication hot-standby scheme is deployed and can be used for data insertion or table test functionality availability through Navicat or the command line.

"Postgresql" postgresql9.3.9 version based on stream replication mode dual-Machine hot standby scheme