PostgreSQL12-主从异-同步流复制V9-6

12.主从异/同步流复制V9.6

物理复制也称流复制，是实例级的复制（复制出与主库一致的备库），分同步和异步

逻辑复制，可以做到表级别的复制，10版本前不支持内置逻辑复制，而是使用第三方工具（Slony-I, pglogical）

流复制与逻辑复制差异

逻辑复制核心原理是主库将WAL日志流发送给备库，备库接收到WAL日志流后进行重做

流复制只能对实例进行复制，逻辑复制能做到表级别复制
流复制能对DDL、DML操作进行复制，逻辑复制只有DML操作
流复制主库可读写，从库只能读不能写；逻辑复制的从库可读写
流复制要求PG大版本一致，逻辑复制支持跨PG大版本

需要重启数据库生效的参数wal_level

高亮部分为同步流复制需要设置的参数，其他与异步保持一致

主库配置

初始化
initdb -D /var/lib/pgsql/9.6/data -W

配置参数
vim postgresql.conf
listen_adresses = '*'
wal_level = replica                                        # 日志输出级别 minimal,replica,logical           
archive_mode = on                                      # 启用归档 on，off
archive_command = '/bin/date'                  # 归档命令，将文档放置何处
max_wal_senders= 32                                 # 主库上最大WAL发送进程数，默认10
wal_keep_segments = 64                             # 主库保留最少WAL日志文件数量
hot_standby = on                                        # 数据库恢复过程中支持读操作
max_connections = 1000                            # 表示到数据库的连接数，从库要比主库大
synchronous_standby_names = 'node2'    # 指定备节点名称
synchronous_commit = remote_apply        # 指定备节点处理WAL的方式

配置参数,便于主备互换
vim pg_hba.conf
host    replication    repuser    192.168.1.1/32    md5
host    replication    repuser    192.168.1.1/32    md5

启动
pg_ctl start

使用postgres用户创建同步用户
create role replica replication login connection limit 5 encrypted password '123456'

从库

初始化
initdb -D /var/lib/pgsql/9.6/data -W

清空数据目录
rm -rf /var/lib/pgsql/9.6/data/*

生成基准备份，将主数据库的/var/lib/pgsql/9.5/data的目录同步过来
pg_basebackup -D /var/lib/pgsql/9.6/data -Fp -Xs -v -P -h 172.21.16.6 -p 5432 -U repuser

-F 指定了输出的格式，支持p(原样输出),t(tar格式输出）
-X 设置在备份的过程中产生的 WAL 日志包含在备份中的方式,f(只有一个WAL发送进程),s(额外产生一个WAL发送进程)
-P 以百分比形式显示进度
-R 表示会在备份结束后自动生成recovery.conf文件
-D 指定备节点用来接收主库数据的目标目录
pg_basebackup --help

拷贝并编辑数据库恢复配置文件
cp /usr/pgsql-9.6/share/recovery.conf.sample /var/lib/pgsql/9.6/data/recovery.conf
vim recovery.confg  # 填写主库信息
recovery_target_timeline = 'latest'
standby mode = 'on'
primary_conninfo = 'host=l92.168.1.1 port=5432 user=repuser'
primary_conninfo = 'host=l92.168.1.1 port=5432 user=repuser application_name=node2'

或者创建隐藏文件，填写主库信息
touch .pgpass
chmod 0600 .pgpass
vim .pgpass
IP:port:database:username:password
192.21.16.6:5432:replicaion:repuser:123456
192.21.1610:5432:replicaion:repuser:123456

启动
chown -R postgres:postgres /var/lib/pgsql/9.6/data
pg_ctl start

synchronous_commit 是指当数据库提交事务时是否需要等待WAL日志写人硬盘后才向客户端返回成功
可选参数 on, off, local, remote_apply, remote_write

主库数据---主库WAL日志流---备库WAL BUFFER---备库WAL日志文件---备库数据

synchronous_commit参数详解

单实例环境

选项	安全性	性能	解释
on	安全，影响DB性能	较低	数据库提交事务时，WAL先写入WAL BUFFER再写入WAL日志，最后向客户端返回成功
off	追求性能
对数据精确度要求不高	较高	数据库提交事务时，WAL写入本地WAL BUFFER后，向客户端返回成功
local	同on选项

|同on选项                                                                |

流复制环境

选项	事务响应时间	安全性	解释
remote_write	短	低	备库将WAL写入本地WAL BUFFER日志中后，主库客户端返回成功
on	中	中	备库实例异常退出，已发送的WAL日志可以从WAL BUFFER中恢复