PostgrelSql 9.4 hot standby配置

----配置同步流复制****************************************************1.在主库增加同步的用户名与密码****************************************************$ psql -h 192.168.50.110  -d postgres -U postgres Password for user postgres: psql (9.4.4)Type "help" for help.postgres=# \l                                 List of databases   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   -----------+----------+----------+------------+------------+----------------------- postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |  template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +           |          |          |            |            | postgres=CTc/postgres template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +           |          |          |            |            | postgres=CTc/postgres(3 rows)postgres=# \c postgresYou are now connected to database "postgres" as user "postgres". postgres=# SELECT rolname from pg_roles ; rolname  ---------- postgres repluser(2 rows)postgres=# SELECT usename from pg_user;  usename  ---------- postgres repluser(2 rows)postgres=# CREATE ROLE repluser REPLICATION LOGIN PASSWORD '123';CREATE ROLEpostgres=#****************************************************2.新建测试数据库****************************************************postgres=# \l                                 List of databases   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   -----------+----------+----------+------------+------------+----------------------- postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |  template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +           |          |          |            |            | postgres=CTc/postgres template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +           |          |          |            |            | postgres=CTc/postgres(3 rows)postgres=# create database wind;CREATE DATABASEpostgres=# \l                                 List of databases   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   -----------+----------+----------+------------+------------+----------------------- postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |  template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +           |          |          |            |            | postgres=CTc/postgres template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +           |          |          |            |            | postgres=CTc/postgres wind      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | (4 rows)postgres=# \c wind;You are now connected to database "wind" as user "postgres".wind=# \dNo relations found.wind=# create table t1(sid int not null primary key,sname varchar(10));CREATE TABLEwind=# insert into t1 values(101,'ocpyang');INSERT 0 1wind=# insert into t1 values(102,'yzw');INSERT 0 1wind=# select * from t1; sid |  sname  -----+--------- 101 | ocpyang 102 | yzw(2 rows)****************************************************3.在主库pg01上进行配置****************************************************--3.1$ vi /data/pg/data/pg_hba.conf增加以下：host    replication     repluser        192.168.50.0/24           md5--3.2 在主库pg01的/data/pg/data/postgresql.conf中设置如下三个参数$ vi /data/pg/data/postgresql.conf 59  listen_addresses = '*'175 wal_level = hot_standby224 max_wal_senders = 5 226 wal_keep_segments = 64  --3.3 在主数据上指定同步复制的Standby名称，在主库pg01的/data/pg/data/postgresql.conf中设置:vi /data/pg/data/postgresql.conf 236 synchronous_standby_names = 'pg02'--3.4 修改上述参数后需要重启数据库.pg_ctl -m "immediate" restart****************************************************4.在备库pg02上进行配置****************************************************$pg_ctl -m "immediate" stop -----4.1 备份数据$rm -rf /data/pg/data/*$pg_basebackup -h 192.168.50.110 -U repluser -F p -P -x -R -D /data/pg/data/ -l backup20150921Password: 54491/54491 kB (100%), 1/1 tablespace--4.2 修改备库的pg02的recovery.conf $ cat /data/pg/data/recovery.conf standby_mode = 'on'primary_conninfo = 'user=repluser password=123 host=192.168.50.110 port=5432 sslmode=disable sslcompression=1'$vi /data/pg/data/recovery.conf #增加application_name,这个参数和主库中synchronous_standby_names指定的对应值一样.standby_mode = 'on'primary_conninfo = 'application_name=pg02 user=repluser password=123 host=192.168.50.110 port=5432 sslmode=disable sslcompression=1'recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据---4.3 修改备库的pg02的postgresql.conf vi /data/pg/data/postgresql.conf 64 max_connections = 1000 ＃ 一般查多于写的应用从库的最大连接数要比较大245 hot_standby = on 255 hot_standby_feedback = on  ---4.4 备库pg02启动pg_ctl  start$ cat  /data/pg/data/pg_log/pg01-2015-09-21_151458.log LOG:  database system was shut down in recovery at 2015-09-21 15:14:29 CSTLOG:  entering standby modeLOG:  redo starts at 0/30002E8LOG:  consistent recovery state reached at 0/30003C0LOG:  database system is ready to accept read only connectionsLOG:  record with zero length at 0/30003C0LOG:  started streaming WAL from primary at 0/3000000 on timeline 1****************************************************5.复制监控****************************************************select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication; pid  |   state   |  client_addr   | sync_priority | sync_state -------+-----------+----------------+---------------+------------ 65392 | streaming | 192.168.50.120 |             1 | sync(1 row)+-----+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------+| pid | usesysid | usename  | application_name | client_addr    | client_hostname | client_port | backend_start                 | backend_xmin | state     | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state |+-----+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------+| 632 | 16384    | repluser | pg02             | 192.168.50.120 | NULL            |       36746 | 2015-09-21 17:17:41.730857+08 | 1826         | streaming | 0/3002718     | 0/3002718      | 0/3002718      | 0/3002718       |             1 | sync       |+-----+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------+1 row in setpid              | 8467       # sender的进程usesysid         | 44673      # 复制的用户idusename          | replica    # 复制的用户用户名application_name | walreceiver  client_addr      | 10.12.12.12 # 复制的客户端地址client_hostname  |client_port      | 55804  # 复制的客户端端口backend_start    | 2015-05-12 07:31:16.972157+08  # 这个主从搭建的时间backend_xmin     |state            | streaming  # 同步状态 startup: 连接中、catchup: 同步中、streaming: 同步sent_location    | 3/CF123560 # Master传送WAL的位置write_location   | 3/CF123560 # Slave接收WAL的位置flush_location   | 3/CF123560 # Slave同步到磁盘的WAL位置replay_location  | 3/CF123560 # Slave同步到数据库的WAL位置sync_priority    | 0  #同步Replication的优先度                      0: 异步、1～?: 同步(数字越小优先度越高)sync_state       | async  # 有三个值，async: 异步、sync: 同步、potential: 虽然现在是异步模式，但是有可能升级到同步模式