一、前提

分别在两台主机上安装好pg数据库，安装过程参考之前文章Centos安装PostgreSQL

二、节点信息

节点信息

三、部署流复制

#设置hosts（master&&slave）

[root@localhost ~]# cat >> /etc/hosts << EOF

10.1.83.136    master

10.1.83.135    slave

EOF

#初始化master数据库

[root@localhost ~]# su - postgres

[postgres@localhost ~]$ pg_ctl stop -D $PGDATA

[postgres@localhost ~]$ rm -rf /app/postgres/data/

[postgres@localhost ~]$ initdb -D $PGDATA

[postgres@localhost ~]$ pg_ctl start -D $PGDATA

[postgres@localhost ~]$ psql

psql (9.6.0)

Type "help" for help.

postgres=# ALTER ROLE postgres password 'postgres';

ALTER ROLE

postgres=# \q

[postgres@localhost ~]$ vim $PGDATA/postgresql.conf

# - Connection Settings -

listen_addresses = '*'

port = 5432

log_destination = 'stderr'

logging_collector = on

log_directory = '/app/postgres/log/'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_file_mode = 0600

log_rotation_age = 1d

log_rotation_size = 100MB

log_min_messages = error

max_wal_senders = 2

wal_level = replica

archive_mode = on

archive_command = 'cd ./'

hot_standby = on

wal_keep_segments = 64

full_page_writes = on

wal_log_hints = on

[postgres@localhost ~]$ vim $PGDATA/pg_hba.conf

# "local" is for Unix domain socket connections only

local  all            all                                    md5

# IPv4 local connections:

host    all            all            127.0.0.1/32            md5

# IPv6 local connections:

host    all            all            ::1/128                md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

#local  replication    postgres                                trust

#host    replication    postgres        127.0.0.1/32            trust

#host    replication    postgres        ::1/128                trust

host    all            all            0.0.0.0/0              md5

host    replication    repuser        slave                  md5

[postgres@localhost ~]$ pg_ctl restart -D $PGDATA

[postgres@localhost ~]$ psql

Password:

psql (9.6.0)

Type "help" for help.

postgres=# CREATE USER repuser replication LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'repuser';

CREATE ROLE

postgres=# \q

#部署slave数据库

[root@localhost ~]# su - postgres

[postgres@localhost ~]$ rm -rf /app/postgres/data/

[postgres@localhost ~]$ pg_basebackup -D $PGDATA -Fp -Xs -v -P -h master -p 5432 -U repuser

[postgres@localhost ~]$ vim $PGDATA/pg_hba.conf

host replication      repuser      master              md5

#配置master的recovery.done

[postgres@localhost ~]$ cd $PGHOME

[postgres@localhost postgres]$ cp share/recovery.conf.sample data/recovery.done

[postgres@localhost postgres]$ vim data/recovery.done

recovery_target_timeline='latest'

standby_mode=on

primary_conninfo='host=slave port=5432 user=repuser password=repuser'

trigger_file='/app/postgres/data/trigger_file'

#配置slave的recovery.conf

[postgres@localhost ~]$ cd $PGHOME

[postgres@localhost postgres]$ cp share/recovery.conf.sample data/recovery.conf

[postgres@localhost postgres]$ vim data/recovery.conf

recovery_target_timeline='latest'

standby_mode=on

primary_conninfo='host=master port=5432 user=repuser password=repuser'

trigger_file='/app/postgres/data/trigger_file'

#配置master的.pgpass

[postgres@localhost postgres]$ echo 'slave:5432:postgres:repuser:repuser' > /home/postgres/.pgpass;chmod 0600 /home/postgres/.pgpass

#配置slave的.pgpass

[postgres@localhost postgres]$ echo 'master:5432:postgres:repuser:repuser' > /home/postgres/.pgpass;chmod 0600 /home/postgres/.pgpass

四、流复制数据同步测试

分别重启master，slave数据库

#在master插入数据

[postgres@localhost ~]$ psql

Password:

psql (9.6.0)

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 tt(id serial not null,name text);

CREATE TABLE

test=# insert into tt(name) values ('china');

INSERT 0 1

test=# \q

#在slave查看master插入的数据是否同步过来

[postgres@localhost ~]$ psql

Password:

psql (9.6.0)

Type "help" for help.

postgres=# \c test

You are now connected to database "test" as user "postgres".

test=# select * from tt;

id | name 

----+-------

  1 | china

(1 row)

test=# \q

五、流复制管理

一般可以通过若干命令查询数据库的主备属性，主数据库是读写的，备数据库是只读的。当主数据库宕机了，可以通过建立触发文件，备数据库将被提升为主数据库，实现一些基本的HA应用。

#查看主备属性

[postgres@localhost ~]$ pg_controldata | grep 'Database cluster state'

主机的cluster state是in production，备机的cluster state是in archive recovery。

#字典表pg_stat_replication

[postgres@localhost ~]$ psql

Password:

psql (9.6.0)

Type "help" for help.

postgres=# select pid,application_name,client_addr,client_port,state,sync_state from pg_stat_replication;

pid  | application_name | client_addr | client_port |  state  | sync_state

------+------------------+-------------+-------------+-----------+------------

1401 | walreceiver      | 10.1.83.135 |      48860 | streaming | async

在主机字典表中是能查到记录，备机中是查询不到的。

#进程信息识别

[postgres@localhost ~]$ ps -ef | grep postgres | grep 'wal sender\|wal receiver' | grep -v grep

进程中显示wal sender的是主机，显示wal receiver的是备机

#pg函数

[postgres@localhost ~]$ psql

Password:

psql (9.6.0)

Type "help" for help.

postgres=# select pg_is_in_recovery();

备机是t，主机是f。

#切换主从

1、master关闭

[postgres@localhost ~]$ pg_ctl stop -D $PGDATA

2、slave触发切换流程

[postgres@localhost ~]$ touch /app/postgres/data/trigger_file

[postgres@localhost ~]$ pg_controldata | grep 'Database cluster state'

Database cluster state:              in production

[postgres@localhost ~]$ psql

Password:

psql (9.6.0)

Type "help" for help.

postgres=# \c test

You are now connected to database "test" as user "postgres".

test=# insert into tt(name) values('sdf');

INSERT 0 1

3、master恢复后，重新作为new slave

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

#启动pg服务

[postgres@master data]$ pg_ctl start -D $PGDATA

#查看日志，看切换后，是否需要拉取增量数据，如果需要则执行以下步骤，如果不需要则跳过以下步骤

[postgres@master data]$ less /app/postgres/log/postgresql-2020-01-08_154428.log

如果出现如下ERROR日志，则需要拉取增量数据

ERROR: requested starting point 0/6000000 on timeline 1 is not in this server's history

DETAIL: This server's history forked from timeline 1 at 0/4000098.

ERROR: requested starting point 0/6000000 on timeline 1 is not in this server's history

DETAIL: This server's history forked from timeline 1 at 0/4000098.

ERROR: requested starting point 0/6000000 on timeline 1 is not in this server's history  

[postgres@master data]$ pg_ctl stop -D $PGDATA

[postgres@master ~]$ pg_rewind --target-pgdata=/app/postgres/data --source-server='host=slave port=5432 user=postgres dbname=postgres password=postgres'

[postgres@master ~]$ pg_ctl start -D $PGDATA

#查看增量数据，是否已拉取过来

[postgres@master ~]$ psql

Password:

psql (9.6.0)

Type "help" for help.

postgres=# \c test

You are now connected to database "test" as user "postgres".

test=# select * from tt;

id | name 

----+-------

  1 | china

34 | sdf

(2 rows)