postgresql主从异步复制配置

1 流复制介绍
PostgreSQL 9.0以后版本支持：异步、同步两种复制策略，以及通过两种策略演化出来的同步+异步，异步复制从节点间级联复制。他们的优缺点简要介绍如下表：

复制方式 优点 缺点
同步 数据一致性高 1.写入性能低
2.只支持一个从节点、多余的从节点为备份
3.若只有一个从节点时、从节点正好宕机、则阻塞主节点事物
异步方式（从节点都连接主节点） 写入性能高 1.数据一致性相对弱点（具体看网络部署）
2.从节点多时，主节点存在压力较高
异步级联复制 主节点压力小 可靠性差、若前面从节点宕机，则后面从节点数据复制失效
异步+同步复制 数据一致性+高可用都会有保障 1. 需要对同步的从节点设置备份节点
2. 故障切换复杂

2 推荐部署方式
经研究keepalive/heartbeat 等成熟开源结构、由于租用服务器方式无法使用；目前高可用方式暂时只能借助域名实现，同时结合现有业务场景推荐部署方式如下图:

说明：

1. pgpool：提供健康检测、查询负载均衡
2. 机房一对外提供服务、并根据实际情况提供从节点的扩展；
3. 机房二属于冷备份、若机房一所有节点出现故障可以从机房二恢复数据。
   环境要求：

1. 操作系统：centos 6.5/6.7
2. 数据库版本：9.5
3. pgpool-II版本：3.5.4
4. 硬件：

1. 前期：
   a) 三台服务器：其中两台在机房一，另外一台在机房二；
   b) 一个域名
2. 后期：
   a) 若单个pgpool-II出现性能瓶颈，前面增加四层负载均衡
   b) 若数据库节点出现性能瓶颈、则机房一增加从节点
3. 服务器要求：（cpu/内存/硬盘）
   3 异步复制配置

环境介绍：
主：192.168.8.187
从：192.168.8.188
3.1 第一步：在两个环境中分别安装PostgreSQL数据库
1、 安装好数据库后，在两台服务器中打开5432端口，使用如下代码打开：

/sbin/iptables -I INPUT -p tcp --dport 5432 -j ACCEPT
/etc/init.d/iptables save   保存修改
service iptables restart    重启防火墙，修改生效

2、打开后，需要使用su – postgres切换到postgres用户，并使用如下代码来打开数据库：

pg_ctl start -l /usr/local/postgresql/log/pg_server.log

3、在postgres用户下，输入psql进入到交互界面，输入\q退出。

3.2 第二步：主服务器配置(以下操作在主服务上进行 192.168.8.187)
1、先创建一个新目录：（在root角色下）

mkdir -p /opt/pgsql/pg_archive

2、创建一个数据库用户进行主从同步。创建用户replica，并赋予登录和复制的权限。
1、su – postgres切换到postgers用户
2、psql 进入数据库交互界面
3、创建用户：
Psql# CREATE ROLE replica login replication encrypted password ‘replica’;
3、修改/usr/local/postgresql/data/pg_hba.conf，允许replica用户来同步。
在pg_hba.conf里增加：（切换到root用户）
host replication replica 192.168.8.188/32 md5 #允许188使用replica用户来复制
这样，就设置了replica这个用户可以从192.168.8.188进行流复制请求。
注：
第二个字段必须要填replication
4.修改postgresql.conf（/usr/local/postgresql/data/postgresql.conf）
listen_addresses = ‘*’ # 监听所有IP
archive_mode = on # 允许归档
archive_command = ‘cp %p /opt/pgsql/pg_archive/%f’ # 用该命令来归档logfile segment
wal_level = hot_standby
max_wal_senders = 32 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个wal_keep_segments = 256 ＃ 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s ＃ 设置流复制主机发送数据的超时时间
max_connections = 100 # 这个设置要注意下，从库的max_connections必须要大于主库的

5、配置完两个文件后，测试从服务器188是否可以连上主服务器187：
在从服务器先使用su – postgres切换到postgres用户，然后输入：
psql -h 192.168.8.187 -U postgres
如果提示Password for user postgres: 那么表示上面配置正常，输入postgres用户的密码后，可以进入主服务器的数据库。
6、重启服务器，并重新开启数据库。方法第一步已经介绍。

3.3第三步：从服务器配置
1.从主节点拷贝数据到从节点
1、su - postgres
2、 rm -rf /opt/pgsql/data/* #先将data目录下的数据都清空（没有该文件就创建mkdir –p /opt/pgsql/data，如果没有权限，则需要在root角色下创建并修改文件所属者和权限
chown postgre /opt/pgsql/data
chmod 700 /opt/pgsql/data
3、 pg_basebackup -F p --progress -D /opt/pgsql/data/ -h 192.168.8.187 -p 5432 -U replica # 使用replica用户从187拷贝数据到188（基础备份，切换到postgres用户，如果失败，提示pg_hba.conf中找不到replica这个用户，此时说明主服务器中的文件没有生效，需要重启主服务器，并重新开启数据库）
4、如果第三步成功，则提示输入密码:replica，开始基础备份
2.配置recovery.conf
复制/usr/local/postgresql/share/recovery.conf.sample到 /opt/pgsql/data/recovery.conf
cp /usr/local/postgresql/share/recovery.conf.sample /opt/pgsql/data/recovery.conf
修改recovery.conf（填入以下信息）
standby_mode = on # 说明该节点是从服务器
primary_conninfo = ‘host=192.168.8.187 port=5432 user=replica password=replica’ # 主服务器的信息以及连接的用户
recovery_target_timeline = ‘latest’
3.配置postgresql.conf(/opt/pgsql/data/postgresql.conf文件夹下的)

wal_level = hot_standby
max_connections = 1000 ＃ 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on ＃ 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 10s # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈
配置完后重启从服务器，
pg_ctl start -D /opt/pgsql/data （切换到postgres用户后，输入该语句启动）

3.4第四部：启动从库、检测流复制配置是否成功
在主节点上执行：
1、su – postgres #切换到postgres用户
select client_addr,sync_state from pg_stat_replication;
结果如下：
postgres=# select client_addr,sync_state from pg_stat_replication;
client_addr | sync_state
---------------±-----------
192.168.20.94 | async
(1 行记录)
说明94是从服务器，在接收流，而且是异步流复制。
至此，PostgreSQL主从流复制安装部署完成。
在主服务器上插入数据或删除数据，在从服务器上能看到相应的变化。从服务器上只能查询，不能插入或删除。