- 系統(tǒng)版本：Centos7.2
- postgres版本：9.6.3
主從ip規(guī)劃
| ip | 角色 |
|---|---|
| 192.168.10.1 | master（主） |
| 192.168.10.2 | slave（備） |
搭建流復(fù)制
Master端配置
- 配置host
vim /etc/hosts
#添加以下內(nèi)容
192.168.10.1 master
192.168.10.2 slave
- 在主庫(kù)添加復(fù)制用戶(hù)
[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=#
- 配置主機(jī)的pg_hba.conf
vim /data/pg_hba.conf
#在最后一行添加
host replication rep_user slave md5
- 配置主機(jī)的postgres.conf
vim /data/postgres.conf
#修改如下配置，歸檔日志的路徑可以根據(jù)自己需求定義
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
#將以下內(nèi)容復(fù)制進(jìn)去
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'
#配置好以后將主庫(kù)recovery修改為done
mv /data/recovery.conf /data/recovery.done
- 重啟數(shù)據(jù)庫(kù)服務(wù)
pg_ctl -D $PGDATA -m fast restart
slave端配置
- 配置host
vim /etc/hosts
#添加以下內(nèi)容
192.168.10.1 master
192.168.10.2 slave
- 使用pg_basebackup同步主庫(kù)data
#同步前記得刪掉從庫(kù)的data文件夾陨收，否則會(huì)報(bào)錯(cuò)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
- 同步好以后修改從庫(kù)的pg_hba.conf
vim /data/pg_hba.conf
#將剛才在主庫(kù)添加的最后一行規(guī)則修改如下
host replication rep_user master md5
- 配置從庫(kù)recovery.conf
[postgres@localhost data]$ mv recovery.done recovery.conf
[postgres@localhost data]$ vim recovery.conf
#將參數(shù)里邊的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.啟動(dòng)從庫(kù)postgres服務(wù)
pg_ctl -D $PGDATA start
6.流復(fù)制測(cè)試
- 主庫(kù)
[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=#
- 查詢(xún)從庫(kù)上數(shù)據(jù)是否同步過(guò)來(lái)
[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=#
數(shù)據(jù)已經(jīng)同步鞋拟，說(shuō)明流復(fù)制已經(jīng)搭建成功逆瑞。
ps：還有幾種方式查看流復(fù)制是否成功荡短。
1. ps -aux |grep postgres
#如果有以下進(jìn)程出現(xiàn)缸榛，可說(shuō)明復(fù)制搭建成功了俐银，主庫(kù)已經(jīng)開(kāi)始推送日志了懈涛。
postgres: wal sender process rep_user 192.168.10.2(50863) streaming 0/302F4E8
2. 在從庫(kù)執(zhí)行命令：pg_controldata |grep cluster
#該狀態(tài)表示搭建成功
Database cluster state: in archive recovery
3. 在數(shù)據(jù)庫(kù)里執(zhí)行sql查看復(fù)制狀態(tài)
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)
