PostgreSQL 9.6 主从实现之异步流复制（Hot Standby）

前言

简单记录一下postgresql主从的实现方式之一——基于Standby的异步流复制，这是PostgreSQL9.x版本（2010.9）之后提供的一个很nice的功能，类似的功能在Oracle中是11g之后才提供的active dataguard和SQL Server 2012版本之后才提供的日志传送，此处再次为pg鼓掌，确实是一个很棒的开源数据库。废话不多说，本篇blog就详细记录一下在pg9.5中实现Hot Standby异步流复制的完整配置过程和注意事项。

Standby数据库原理

• 简单介绍一些基础概念与原理，首先我们做主从同步的目的就是实现db服务的高可用性，通常是一台主数据库提供读写，然后把数据同步到另一台从库，然后从库不断apply从主库接收到的数据，从库不提供写服务，只提供读服务。在postgresql中提供读写全功能的服务器称为primary database或master database，在接收主库同步数据的同时又能提供读服务的从库服务器称为hot standby server。
• PostgreSQL在数据目录下的pg_xlog子目录中维护了一个WAL日志文件，该文件用于记录数据库文件的每次改变，这种日志文件机制提供了一种数据库热备份的方案，即：在把数据库使用文件系统的方式备份出来的同时也把相应的WAL日志进行备份，即使备份出来的数据块不一致，也可以重放WAL日志把备份的内容推到一致状态。这也就是基于时间点的备份（Point-in-Time Recovery），简称PITR。而把WAL日志传送到另一台服务器有两种方式，分别是：
• ==WAL日志归档（base-file）==
• ==流复制（streaming replication）==
• 第一种是写完一个WAL日志后，才把WAL日志文件拷贝到standby数据库中，简言之就是通过cp命令实现远程备份，这样通常备库会落后主库一个WAL日志文件。
• 而第二种流复制是postgresql9.x之后才提供的新的传递WAL日志的方法，它的好处是只要master库一产生日志，就会马上传递到standby库，同第一种相比有更低的同步延迟，所以我们肯定也会选择流复制的方式。

在实际操作之前还有一点需要说明就是standby的搭建中最关键的一步——在standby中生成master的基础备份。postgresql9.1之后提供了一个很方便的工具—— pgbasebackup，关于它的详细介绍和参数说明可以在官网中查看（pgbasebackup tool）

安装

https://yum.postgresql.org/repopackages.php
yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
下载源然后yum安装：yum install postgresql96-server
mv /var/lib/pgsql /data/;ln -s /data/pgsql /var/lib/pgsql
systemctl enable postgresql-9.6

配置（主库）

初始化：

su - postgres
/usr/pgsql-9.6/bin/initdb --encoding=utf8 -D /var/lib/pgsql/9.6/data

修改配置：

vi /var/lib/pgsql/9.6/data/pg_hba.conf
local   all             all                                     peer
host    all             all             127.0.0.1/32            ident
host    all             all             192.168.200.0/24        md5
host    all             all             192.168.10.0/24         md5
host    replication     repl     192.168.200.26/32             md5
vi /var/lib/pgsql/9.6/data/postgresql.conf
listen_addresses = '0.0.0.0'
max_connections = 5000
shared_buffers = 32GB
wal_level = 'hot_standby'
max_wal_senders = 1
wal_keep_segments=64
log_timezone = 'PRC'
timezone = 'PRC'

启动：

service postgresql-9.6 start

创建同步用户：

##psql -U postgres
su - postgres
psql -c "CREATE USER repl REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'blizzmi';"

配置（从库）

（安装后不启动）
su - postgres
同步主库：pg_basebackup -h 192.168.200.14 -U repl -F p -x -P -R -D /var/lib/pgsql/9.6/data/ -l replbackup20170418
vi /var/lib/pgsql/9.6/data/postgresql.conf
hot_standby=on

启动：

chown -R postgres:postgres /var/lib/pgsql/9.6/data
service postgresql-9.6 start

检查与测试：

在主数据库执行
psql -U postgres
CREATE USER dbuser WITH PASSWORD 'password'; 
CREATE DATABASE exampledb OWNER dbuser; 
\\c exampledb
CREATE TABLE rep_test (test varchar(40));
INSERT INTO rep_test VALUES ('data one');
INSERT INTO rep_test VALUES ('some more words');
INSERT INTO rep_test VALUES ('lalala');
INSERT INTO rep_test VALUES ('hello there');
INSERT INTO rep_test VALUES ('blahblah');
在从数据库执行
psql -U postgres
\\c exampledb
SELECT * FROM rep_test;
查看数据有无同步
验证从数据库的只读属性
INSERT INTO rep_test VALUES ('oops');
ERROR:  cannot execute INSERT in a read-only transaction
查看同步状态
在主数据库中执行
select * from pg_stat_replication;
查看状态：
/usr/pgsql-9.6/bin/pg_controldata -D /var/lib/pgsql/9.6/data/

监控Streaming Replication集群
pg_stat_replication视图(主库端执行)
pid
Wal sender process的进程ID
usesysid
执行流复制的用户的OID
usename
执行流复制的用户名
application_name
连接到master的Application name
client_addr
standby的ip地址
client_hostname
Standby主机名
client_port
standby上连接端口
backend_start
Wal sender process启动时间。
state
WAL sender process状态
sent_location
WAL发送位置
write_location
Standby端写WAL位置
flush_location
Standby端写WAL刷新到磁盘的位置
replay_location
Standby端应用WAL位置
sync_priority
standby的优先级
sync_state
standby的同步类型
主库上通过执行函数把WAL位置转换成WAL文件名与偏移量
select  *   from pg_xlogfile_name_offset('XXX');
查看备库落后主库多少个字节的wal日志
select pg_xlog_location_diff(pg_current_xlog_location(),replay_location)  from pg_stat_replication;