----配置同步流复制

****************************************************
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 postgres
You 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 ROLE

postgres=#

****************************************************
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 DATABASE
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
 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=# \d
No relations found.
wind=# create table t1(sid int not null primary key,sname varchar(10));
CREATE TABLE
wind=# insert into t1 values(101,'ocpyang');
INSERT 0 1
wind=# insert into t1 values(102,'yzw');
INSERT 0 1
wind=# 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_standby
224 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


***