PostgreSQL9.6主从配置

参考文档：

备机日志传送：https://www.postgresql.org/docs/9.6/static/warm-standby.html
英文文档：https://www.postgresql.org/docs/current/static/index.html
中文文档：http://www.postgres.cn/docs/9.6/
pg_basebackup：https://www.postgresql.org/docs/current/static/app-pgbasebackup.html
参考1：http://blog.csdn.net/wlwlwlwl015/article/details/53287855
参考2：http://www.cnblogs.com/yjf512/p/4499547.html

本文涉及postgresql基于异步方式的主从复制的配置验证。

一．主从复制简介

1. 基于文件的日志传送

创建一个高可用性（HA）集群配置可采用连续归档，集群中主服务器工作在连续归档模式下，备服务器工作在连续恢复模式下（1台或多台可随时接管主服务器），备持续从主服务器读取WAL文件。

连续归档不需要对数据库表做任何改动，可有效降低管理开销，对主服务器的性能影响也相对较低。

直接从一个数据库服务器移动WAL记录到另一台服务器被称为日志传送，PostgreSQL通过一次一文件（WAL段）的WAL记录传输实现了基于文件的日志传送。

日志传送所需的带宽取根据主服务器的事务率而变化；
日志传送是异步的，即WAL记录是在事务提交后才被传送，那么在一个窗口期内如果主服务器发生灾难性的失效则会导致数据丢失，还没有被传送的事务将会被丢失；
数据丢失窗口可以通过使用参数archive_timeout进行限制，可以低至数秒，但同时会增加文件传送所需的带宽。

2. 流复制

PostgreSQL在9.x之后引入了主从的流复制机制，所谓流复制，就是备服务器通过tcp流从主服务器中同步相应的数据，主服务器在WAL记录产生时即将它们以流式传送给备服务器，而不必等到WAL文件被填充。

默认情况下流复制是异步的，这种情况下主服务器上提交一个事务与该变化在备服务器上变得可见之间客观上存在短暂的延迟，但这种延迟相比基于文件的日志传送方式依然要小得多，在备服务器的能力满足负载的前提下延迟通常低于一秒；
在流复制中，备服务器比使用基于文件的日志传送具有更小的数据丢失窗口，不需要采用archive_timeout来缩减数据丢失窗口；
将一个备服务器从基于文件日志传送转变成基于流复制的步骤是：把recovery.conf文件中的primary_conninfo设置指向主服务器；设置主服务器配置文件的listen_addresses参数与认证文件即可。

二．验证环境

1. 操作系统

CentOS-7-x86_64-Everything-1511

2. PostgresSQL版本

PostgreSQL 9.6.3：https://www.postgresql.org/download/linux/redhat/

3. 主机

采用VMware ESXi虚拟出的2台服务器：

host1：psql_master，10.11.4.186

host2：psql_standby，10.11.4.187

三．主库配置

1. 创建复制用户

#需要一个账号进行主从同步
postgres=#create role repl login replication encrypted password 'repl@123';

2. 认证文件pg_hba.conf

#配置从库可以采用repl账号进行同步
[root@psql_master ~]# vim /var/lib/pgsql/9.6/data/pg_hba.conf 
host replication repl 10.11.4.187/32 md5

3. 主库配置文件postgresql.conf

[root@psql_master ~]# vim /var/lib/pgsql/9.6/data/postgresql.conf
#监听端口
listen_addresses = '*'

#主从设置为热备模式，流复制必选参数
wal_level = hot_standby

#流复制允许的连接进程，一般同standby数量一致
max_wal_senders = 2

#流复制在没有基于文件的连续归档时，主服务器可能在备机收到WAL日志前回收这些旧的WAL，此时备机需要重新从一个新的基础备份初始化；可设置wal_keep_segments为一个足够高的值来确保旧的WAL段不会被太早重用；1个WAL日志为16MB，所以在设置wal_keep_segments时，在满足空间的前提下可以尽量设置大一些
wal_keep_segments = 64

#默认参数，非主从配置相关参数，表示到数据库的连接数，一般从库做主要的读服务时，设置值需要高于主库
max_connections = 100

4. 重启服务

#同时注意打开防火墙端口打开
[root@psql_master ~]# systemctl restart postgresql-9.6

四．从库配置

从库安装postgresql后，暂不初始化，如果从库已初始化，可以清空其data目录（默认安装是/ /var/lib/pgsql/9.6/data/目录）。

1. 基础备份

[root@psql_standby ~]# pg_basebackup -h 10.11.4.186 -p 5432 -U repl -F p -P -D /var/lib/pgsql/9.6/data/
#-h，主库主机，-p，主库服务端口；
#-U，复制用户；
#-F，p是默认输出格式，输出数据目录和表空间相同的布局，t表示tar格式输出；
#-P，同--progress，显示进度；
#-D，输出到指定目录；
#因为主库采用的是md5认证，这里需要密码认证。

2. 备份目录权限

#基于root账号做的基础备份，需要将相关目录文件的权限变更
[root@psql_standby ~]# chown -R postgres:postgres /var/lib/pgsql/9.6/data/

3. 从库配置文件postgresql.conf

#在基础备份时，初始化文件是从主库复制来的，所以配置文件一致，可将wal_level，max_wal_senders与wal_keep_segments等参数注释，以下是新增或修改的参数
[root@psql_standby ~]# vim /var/lib/pgsql/9.6/data/postgresql.conf
#在备份的同时允许查询
hot_standby = on

#可选，流复制最大延迟
max_standby_streaming_delay = 30s

#可选，从向主报告状态的最大间隔时间
wal_receiver_status_interval = 10s

#可选，查询冲突时向主反馈
hot_standby_feedback = on

#默认参数，非主从配置相关参数，表示到数据库的连接数，一般从库做主要的读服务时，设置值需要高于主库
max_connections = 1000

4. 恢复文件recovery.conf

#在做基础备份时，也可通过-R参数在备份结束后自动生产一个recovery.conf文件
[root@psql_standby ~]# cp /usr/pgsql-9.6/share/recovery.conf.sample /var/lib/pgsql/9.6/data/recovery.conf

[root@psql_standby ~]# chown postgres:postgres /var/lib/pgsql/9.6/data/recovery.conf

[root@psql_standby ~]# vim /var/lib/pgsql/9.6/data/recovery.conf
#指明从库身份
standby_mode = on

#连接到主库信息
primary_conninfo = 'host=10.11.4.186 port=5432 user=repl password=repl@123'

#同步到最新数据
recovery_target_timeline = 'latest'

#指定触发文件，文件存在时，将触发从库提升为主库，前提是必须设置”standby_mode = on”；如果不设置此参数，也可采用”pg_ctl promote“触发从库切换成主库
#trigger_file = ‘/var/lib/pgsql/9.6/data/trigger_activestandby’

5. 重启服务

[root@psql_standby ~]# systemctl restart postgresql-9.6

五．使用验证

1. 查看进程

1）主库sender进程

[root@psql_master ~]# ps -ef | grep postgres

2）从库receiver过程

[root@psql_standby ~]# ps -ef | grep postgres

2. 查看复制状态（主库）

postgres=# x
postgres=# select * from pg_stat_replication;
#pid，sender进程；
#usesysid，复制用户id；
#usename，复制用户名；
#application_name，复制进程名；
#client_addr，从库客户端地址；
#client_hostname，从库客户端名；
#client_port，从库客户端port；
#backend_start，主从复制开始时间；
#backend_xmin，当前后端的xmin范围，由备机提供；
#state，同步状态，startup：连接中；catchup：同步中；streaming：同步；
#sent_location，主传送wal的位置；
#write_location，从接收wal的位置；
#flush_location，从刷盘的wal位置；
#replay_location，从同步到数据库的wal位置；
#sync_priority，同步优先级，0表示异步；1～？表示同步，数字越小优先级越高；
#sync_state， async：异步；sync：同步；potential；当前是异步，但可能升级到同步模式；
#另外，”select pg_is_in_recovery();“命令也可以查看主从状态，false是主，true为从。

3. 表复制测试

1）建表（主库）

postgres=# create table postgrestb(id int primary key,name VARCHAR(20),salary real);
postgres=# insert into postgrestb values(10, 'Messi', 10000.00);
postgres=# insert into postgrestb values(6, 'Xavi', 10000.00);
postgres=# select * from postgrestb;

2）查询（从库）

[root@psql_standby ~]# su - postgres
-bash-4.2$ psql
postgres=# d
postgres=# select * from postgrestb;

3）从库写测试

#从库只读，不能写入数据
postgres=# insert into postgrestb values(8, 'Iniesta', 10000.00);

4. 主从切换

1）切换前状态

[root@psql_master ~]# pg_controldata /var/lib/pgsql/9.6/data/

[root@psql_standby ~]# pg_controldata /var/lib/pgsql/9.6/data/

2）主库故障

#以postgres账户停止主库
[root@psql_master ~]# su - postgres -c "pg_ctl stop -m fast"

[root@psql_master ~]# pg_controldata /var/lib/pgsql/9.6/data/

3）创建用户查看从库日志

#日志已经明确是不能连接到主库
[root@psql_standby ~]# tailf /var/lib/pgsql/9.6/data/pg_log/postgresql-Tue.log

4）激活从库

#采用”pg_ctl promote“切换从库为主库；
#切换后，从库的recovery.conf文件名字变成了recovery.done
[root@psql_standby ~]# su - postgres -c "pg_ctl promote"

5）查看从库日志，状态与进程

[root@psql_standby ~]# tailf /var/lib/pgsql/9.6/data/pg_log/postgresql-Tue.log

 [root@psql_standby ~]# tailf /var/lib/pgsql/9.6/data/pg_log/postgresql-Tue.log

[root@psql_standby ~]# ps aux | grep postgres

6）总结

配合keepalived可以做postgresql的高可用，需要写检测主从状态脚本，可参考：https://github.com/francs/PostgreSQL-Keepalived-HA；
检测trigger_file存在与否也可完成从库切换主库；
从库切换到主库，故障的原主库恢复后，可将其降为备库，主要是设置recovery.conf文件与postgres.conf文件，最差的情况下可清空此时的备库（原主库）的$PGDATA，重新同步数据。

六．同步流复制（补充）

1. 与异步流复制的区别

同步复制必须等待主库与从库都写完wal后才能commit事务，在一定程度上会增加事务的响应时间；
配置同步复制步骤：

1. 在主库postgresql.conf文件中设置参数synchronous_standby_names为1个字符串或"*"，存在多个从库时使用逗号分隔；
2. 在主库postgresql.conf文件中设置参数synchronous_commit参数设置为"on"，控制是否等待wal日志buffer刷入磁盘再返回用户事务状态信息，同步流复制需要打开；
3. 从库的recovery.conf中primary_conninfo参数需要指明"application_name"。

2. 注意事项

当只有1个从做同步流复制时，如果从库故障，则主库的写也会挂起（可以看到postgres下会有数据操作的waiting进程），此时的方案建议采用1+1+n的方式，即1 master+1 slave(同步)+n slave(异步)，做同步的slave故障后，可从n个异步slave中选举1个切换成同步模式；
设置synchronous_commit = off 后，即使同步复制模式的从库故障，主库的事务也不会出现等待挂起的现象。