- 系统版本：Centos7.2
- postgres版本：9.6.3
主从ip规划
| ip | 角色 |
|---|---|
| 192.168.10.1 | master（主） |
| 192.168.10.2 | slave（备） |
搭建流复制
Master端配置
- 配置host
vim /etc/hosts
#添加以下内容
192.168.10.1 master
192.168.10.2 slave
- 在主库添加复制用户
[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=#
- 配置主机的pg_hba.conf
vim /data/pg_hba.conf
#在最后一行添加
host replication rep_user slave md5
- 配置主机的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 = '*'
- 配置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
- 重启数据库服务
pg_ctl -D $PGDATA -m fast restart
slave端配置
- 配置host
vim /etc/hosts
#添加以下内容
192.168.10.1 master
192.168.10.2 slave
- 使用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
- 同步好以后修改从库的pg_hba.conf
vim /data/pg_hba.conf
#将刚才在主库添加的最后一行规则修改如下
host replication rep_user master md5
- 配置从库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)
