CentOS下PostgreSQL 主从实现之异步流复制（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之后提供了一个很方便的工具—— pg_basebackup，关于它的详细介绍和参数说明可以在官网中查看（pg_basebackup 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 'xxxxxxx';"

配置（从库）

（安装后不启动）
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;

参考链接 ：

PostgreSQL 主从实现之异步流复制（Hot Standby） ：https://mp.weixin.qq.com/s/-GagJ97B9V6dlU9WeNz0sg