postgres流复制搭建

• 系统版本：Centos7.2
• postgres版本：9.6.3

主从ip规划

ip	角色
192.168.10.1	master（主）
192.168.10.2	slave（备）

搭建流复制

Master端配置

1. 配置host

vim /etc/hosts
#添加以下内容
192.168.10.1 master
192.168.10.2 slave

2. 在主库添加复制用户

[root@localhost ~]# su postgres
[postgres@localhost root]$ psql
psql (9.6.3)
Type "help" for help.

postgres=# create user rep_user login replication password 'rep';
CREATE ROLE
postgres=#

3. 配置主机的pg_hba.conf

vim /data/pg_hba.conf
#在最后一行添加
host    replication      rep_user         slave          md5

4. 配置主机的postgres.conf

vim /data/postgres.conf

#修改如下配置，归档日志的路径可以根据自己需求定义
wal_log_hints = on
wal_level = replica
max_wal_senders = 5
archive_mode =on
archive_command ='DATE=`date +%Y%m%d`;DIR="/opt/postgres/arch/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f'
hot_standby  =  on
port = 5432
listen_addresses = '*'

5. 配置recovery.conf

vim /data/recovery.conf

#将以下内容复制进去
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=slave port=5432 user=rep_user password=rep'
trigger_file = '/opt/postgres/data/trigger_file'

#配置好以后将主库recovery修改为done
mv /data/recovery.conf /data/recovery.done

6. 重启数据库服务

pg_ctl -D $PGDATA -m fast restart

slave端配置

1. 配置host

vim /etc/hosts
#添加以下内容
192.168.10.1 master
192.168.10.2 slave

2. 使用pg_basebackup同步主库data

#同步前记得删掉从库的data文件夹，否则会报错pg_basebackup: directory "/opt/postgres/data/" exists but is not empty

[root@localhost postgres]# su postgres
[postgres@localhost postgres]$ pg_basebackup -D $PGDATA -Fp -Xs -v -P -h master -p 5432 -U rep_user
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
transaction log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
22825/22825 kB (100%), 1/1 tablespace
transaction log end point: 0/2000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

3. 同步好以后修改从库的pg_hba.conf

vim /data/pg_hba.conf

#将刚才在主库添加的最后一行规则修改如下
host    replication      rep_user         master         md5

4. 配置从库recovery.conf

[postgres@localhost data]$ mv recovery.done recovery.conf
[postgres@localhost data]$ vim recovery.conf

#将参数里边的host修改成master
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=master port=5432 user=rep_user password=rep'
trigger_file = '/opt/postgres/data/trigger_file'

5.启动从库postgres服务

pg_ctl -D $PGDATA start

6.流复制测试

• 主库

[root@localhost ~]# su postgres
[postgres@localhost root]$ psql
psql (9.6.3)
Type "help" for help.

postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table t1(id int);
CREATE TABLE
test=# insert into t1 select 1;
INSERT 0 1
test=#

• 查询从库上数据是否同步过来

[root@localhost ~]# su postgres
[postgres@localhost root]$ psql
psql (9.6.3)
Type "help" for help.

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from t1;
 id
----
  1
(1 row)

test=#

数据已经同步，说明流复制已经搭建成功。

ps：还有几种方式查看流复制是否成功。

1. ps -aux |grep postgres 
  #如果有以下进程出现，可说明复制搭建成功了，主库已经开始推送日志了。
  postgres: wal sender process rep_user 192.168.10.2(50863) streaming 0/302F4E8 
  
2. 在从库执行命令：pg_controldata |grep cluster 
   #该状态表示搭建成功  
   Database cluster state:               in archive recovery
   
3. 在数据库里执行sql查看复制状态
postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
  pid  |   state   | client_addr  | sync_priority | sync_state
-------+-----------+--------------+---------------+------------
 17907 | streaming | 192.168.10.2 |             0 | async
(1 row)