PostgreSQL12-流复制

1、概述

流复制

复制类型：物理复制，备库是主库的字节级副本，包含所有数据库、表、索引、系统表，与主库数据完全一致；
同步方式：主库的 WAL 数据会实时流式传输到备库，备库接收后重放 WAL（应用到自身数据文件），保持与主库的同步；
角色划分：
- 主库（Primary）：可读写，处理业务请求，产生 WAL；
- 备库（Standby）：默认只读，接收并应用主库 WAL，分为热备库（可提供只读查询）和冷备库（仅用于恢复）。

利用WAL归档的流复制

在流复制架构中，建议开启基于文件的连续归档，不开也可以，就是复制的准确性和稳定性不能保障
开启归档的核心配置是 postgresql.conf 中的两项，如果这两项未配置（archive_mode = off），就属于没有基于文件的连续归档。

archive_mode = on  # 开启归档模式
archive_command = 'cp %p /data/pg_wal_archive/%f'  # 归档命令，%p=源WAL路径，%f=WAL文件名

避免同步中断

PostgreSQL的WAL文件是循环复用的,主库会不断生成新的WAL文件，记录数据变更；当WAL文件总量达到max_wal_size阈值时，主库会触发检查点，清理掉旧WAL文件
在无归档的流复制场景中，主库判断WAL文件是否可回收，不会考虑备库是否已经接收并应用该WAL文件,只要主库自身完成检查点，旧 WAL 就会被标记为可复用 / 删除。
如果备库因为网络延迟、性能不足等原因，还没来得及从主库接收这些旧 WAL 文件，主库就已经把它们回收了，备库就会丢失同步所需的 WAL 数据，导致主备同步中断。
为了避免备库因 WAL 被提前回收而同步中断,有 3 种方案：

设置 wal_keep_size

设置 wal_keep_size强制主库保留指定大小的旧 WAL 文件（比如 wal_keep_size = 1GB），即使触发检查点，也不会删除这些 WAL，直到备库接收完成。缺点：保留的 WAL 大小有限，若备库长时间离线，仍可能丢失 WAL。

配置复制槽

配置复制槽（Replication Slot）复制槽是主库为备库创建的 “专属 WAL 保留机制” —— 主库会跟踪备库的同步进度，只要备库没接收的 WAL，就不会被回收，彻底解决 WAL 提前删除的问题。优点：精准保留所需 WAL，无空间浪费；缺点：若备库永久离线，主库的 WAL 会持续堆积，需手动清理复制槽。

开启 WAL 归档

开启 WAL 归档（根本解决方案）一旦开启归档，所有写满的 WAL 文件都会被持久化到归档目录。即使主库回收了本地 WAL，备库也可以从归档目录中获取缺失的 WAL 文件，自主完成同步追赶，无需依赖主库的本地 WAL

逻辑复制

复制类型：逻辑复制，复制的是事务的逻辑变更（如 INSERT/UPDATE/DELETE），通过发布-订阅模型实现。
核心流程：
- 主库创建发布：指定要复制的表（或所有表）、复制的操作类型（增删改）；
- 备库创建订阅：订阅主库的发布，接收逻辑 WAL 变更；
- 备库应用变更：将接收到的逻辑操作应用到自身表中，实现数据同步。
核心特点
- 灵活的复制规模：可只复制指定数据库、指定表，甚至指定行（通过 WHERE 条件）；
- 备库可读写：备库不是主库的镜像，可独立写入数据（仅同步发布的表变更）；
- 跨版本 / 跨库兼容：主备库 PostgreSQL 版本可不同（如主库 12 → 备库 16），甚至可复制到其他数据库（如 MySQL，需插件）；
- 支持 DDL 复制：默认只复制 DML，需额外配置（如 pg_repack 插件）或手动同步 DDL。

流复制与逻辑复制差异

流复制只能对实例(整个集群)进行复制，逻辑复制能做到表级别、行级别复制
流复制能对DDL（create/alter/drop）、DML（INSERT/UPDATE/DELETE）操作进行复制。逻辑复制只有DML操作
流复制主库可读写，从库只能读不能写；逻辑复制的从库可读写
流复制要求PG大版本一致，逻辑复制支持跨PG大版本
流复制wal_level=replica，wal_level=logical

2、实现流复制（主从架构）

主库开启WAL归档，WAL在主库本地存储，也存储在NFS上，用于备库获取主库的WAL归档
同步复制需配置 synchronous_standby_names 和 synchronous_commit，异步复制无需。

2.1、主库配置

修改 postgresql.conf

listen_addresses = '*'        # 生产环境建议指定具体网段
wal_level = replica           # 默认值，无需修改（支持流复制）
max_wal_senders = 32          # 最大 WAL 发送进程数（决定能连接备库的数量，默认 10）
wal_keep_size = 16MB          # 保留的 WAL 文件大小（防止备库落后太多）

# 归档配置
archive_mode = on             # 启用归档（可选，用于时间点恢复）
archive_command = 'cp %p /archive/%f'  # 示例：将 WAL 复制到归档目录（需手动创建/archive）

# 备库只读配置（主库也需设置，便于切换）
hot_standby = on              # 允许备库在恢复时提供只读服务（默认 on，可省略）

# 连接数配置（备库 max_connections 需≥主库）
max_connections = 1000

# 同步复制特有参数（异步复制无需）
synchronous_standby_names = 'pg02'   # 指定同步备库的 application_name
synchronous_commit = remote_apply    # 同步提交级别（默认 on，同步复制建议设为 remote_apply）

参数说明

%f：是WAL文件的纯文件名，不含路径，
%p：是备库WAL文件存储的绝对路径，默认是$PGDATA/pg_wal
%r: 是备库完成WAL重放后，已经成功应用到本地数据文件的最新一个WAL文件名，无路径），这是备库的恢复边界，所有早于或等于该文件的WAL归档已无使用价值，可安全清理
synchronous_commit：是指当数据库提交事务时是否需要等待WAL日志写人硬盘后才向客户端返回成功。可选参数 on, off, local, remote_apply, remote_write
- 适合单实例环境的配置
  - on：数据库提交事务时，WAL先写入WAL BUFFER再写入WAL日志，最后向客户端返回成功，安全性高，无数据丢失风险，但性能较低
  - off：数据库提交事务时，WAL写入本地WAL BUFFER后，向客户端返回成功。安全性较差，有数据丢失风险，但性能高
  - local：同on选项
- 适合流复制环境的配置
  - remote_write：至少一个同步备库将 WAL 写入自身缓冲区后，主库即返回成功（备库崩溃可能丢失）
  - on：至少一个同步备库备库将 WAL 写入自身磁盘后，主库返回成功（备库 OS 宕机可能丢失）
  - remote_apply：至少一个同步备库备库将 WAL 日志应用到数据文件后，主库才返回成功（完全同步）
  - 注意：未被 synchronous_standby_names 指定的备库属于异步备库，主库不会等待其 WAL 写入 / 应用，无论 synchronous_commit 如何设置。

创建归档目录

# 创建归档目录
mkdir -p /archive
# 授权
chown -R postgres:postgres /archive
chmod 700 /archive

主库安装NFS

# 安装NFS
apt install -y nfs-kernel-server rpcbind

# 编辑NFS配置文件，添加共享规则
echo "/archive 192.168.40.42(rw,sync,no_root_squash,all_squash,anonuid=1001,anongid=1001)" >> /etc/exports

# 说明：
# rw：读写权限；sync：实时同步，保证数据不丢失；
# anonuid/anongid：指定匿名用户为postgres的UID/GID（主备库需一致，可通过id postgres查看）

# 生效NFS配置
exportfs -rv

# 启动并开机自启NFS服务
systemctl start nfs-server rpcbind
systemctl enable nfs-server rpcbind

添加复制用户访问主库的权限

配置vim pg_hba.conf

# 允许备库通过 repuser 进行复制连接。网段具体IP均可，具体IP是192.168.40.42/32
host    replication     repuser    192.168.40.0/24    password

启动主库并创建复制用户

# 启动主库
pg_ctl start

# 创建复制用户
CREATE ROLE repuser WITH REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '123456';

2.2、从库配置

备库安装NFS

# 更新软件源
apt update -y
# 安装NFS客户端
apt install -y nfs-common

创建挂载点目录并挂载

# 创建挂载点目录
mkdir -p /archive
# 授权
chown -R postgres:postgres /archive
chmod 700 /archive
# 挂载主库的归档目录到本地的/archive
echo "192.168.40.41:/archive /archive nfs defaults,_netdev 0 0" >> /etc/fstab
# 重新加载fstab，生效配置
mount -a
# 查看挂载状态，确认NFS挂载成功
df -h | grep archive

清空数据目录

清空从库的数据目录。否则会报错“pg_basebackup: error: directory "/var/lib/pgsql/16/data" exists but is not empty”

rm -rf /var/lib/pgsql/16/data/*

从主库同步基础数据

pg_basebackup --help 可以查看帮助

pg_basebackup -D /var/lib/pgsql/16/data \
  -Fp \             # 原样复制（推荐）
  -Xs \             # 实时同步 WAL 日志
  -v -P \           # 详细输出+进度显示
  -h 192.168.1.1 \  # 主库 IP
  -p 5432 \         # 主库端口
  -U repuser \      # 复制用户

配置备库恢复参数

PG16 中无需创建 recovery.conf，直接在数据目录下创建 standby.signal 文件，并在 postgresql.auto.conf 中配置主库连接信息

# 创建 standby.signal 文件（标记为备库）
touch /var/lib/pgsql/16/data/standby.signal

# 配置主库连接信息（写入 postgresql.auto.conf）
cat << EOF > /var/lib/pgsql/16/data/postgresql.auto.conf
primary_conninfo = 'host=192.168.40.41 port=5432 user=repuser password=123456'
recovery_target_timeline = 'latest'
hot_standby = on
EOF

wal_sender_timeout是主库配置参数，作用是：主库的 wal_sender 进程（负责向备库发送 WAL 日志的进程），如果在指定时间内（此处 5 秒），与备库的 wal_receiver 进程之间没有任何网络活动（无 WAL 传输、无心跳包交互），则主动断开本次主备复制连接。
wal_receiver_timeout是备库配置参数，作用是：备库检测与主库的连接无活动，主动断开并重新连接
hot_standby = on 备库只读，适合生产环境主备集群（灾备 + 查询分流）
hot_standby = off备库禁止所有查询，适合纯灾备备库（无需提供查询，追求恢复效率）
当主库发生故障切换（如备库提升为主库）、或执行时间点恢复（PITR）后，新的主库会生成新的时间线（原时间线中断，新时间线延续数据变更）
每个 WAL 文件名的前 8 位就是时间线编号（如 00000002000000000000000A 对应时间线 2
restore_command。仅依赖主库实时流式传输 WAL，却保主库WAL不会提前回收的场景不用设置restore_command。例如测试环境流复制集群、主备网络低延迟无卡顿、备库不会长时间离线的小型生产集群。生产环境需要开启restore_command，避免因主库本地 WAL 被回收导致同步中断

启动备库

pg_ctl  start

在主库上创建复制槽

postgres=# SELECT * FROM pg_create_physical_replication_slot('node_a_slot');
  slot_name  | lsn
-------------+-----
 node_a_slot |

postgres=# SELECT slot_name, slot_type, active FROM pg_replication_slots;
  slot_name  | slot_type | active
-------------+-----------+--------
 node_a_slot | physical  | f
(1 row)

备库配置复制槽

要配置备库使用这个槽，在postgresql.auto.conf中配置primary_slot_name

primary_slot_name = 'node_a_slot'

流复制过程简述

主库执行事务，生成 WAL 日志并写入本地 WAL 缓冲区。
主库的 WAL 发送进程（wal_sender）将 WAL 日志流发送到备库。
备库的 WAL 接收进程（wal_receiver）接收日志，写入备库 WAL 缓冲区。
备库将 WAL 缓冲区日志刷入本地 WAL 文件，并根据日志重做数据（应用到数据文件）。
同步复制中，主库等待备库完成指定操作（如 remote_apply）后，向客户端返回事务成功；异步复制则无需等待，直接返回。

13.流复制主备切换延迟备库同步优选提交级联复制

主备切换-文件触发的方式

1.停止主库 
    pg_ctl stop -m smart
2.备库创建主备切换文件（与备库recovery.conf中的trigger_file设置的一致）
    touch /var/lib/pgsql/9.6/data/.trigger
    同步完成后recovery.conf变为recovery.done
    详细的配置文件位于 /usr/local/psql9.6/share/postgresql/recovery.conf.sample

3.原主库创建recovery.conf，修改primary_conninfo为新主库信息
4.重启原主库

主备切换-pgctl promote方式

1.停止主库 
    pg_ctl stop -m smart
2.在备库上执行 pg_ctl promote
    recovery.conf中trigger_file不用指定
    同步完成后recovery.conf变为recovery.done
3.原主库创建recovery.conf，修改primary_conninfo为新主库信息
4.重启原主库

备库切换为主库时忘记关闭旧主库

备库postgresql.conf设置 wal_log_hints = on
备库操作，填写原主库信息
pg_rewind --target-pgdata $PGDATA --source-server='host=192.168.195.132 port=5432 user=postgres dbname=auto' -P
备库 mv recovery.done recovery.conf
备库 pg_ctl start

延迟备库

vim recovery.conf
recovery_min_applt_delay (integer)
1.默认单位毫秒 ms ，其他单位s,min,h,d
2.开启此参数将阻塞同步流复制主库的写操作
3.过长的时间会使pg_wal日志过大

一主多备流复制，同步优选提交

vim postgresql.conf
synchronous_standby_names (string)
有三种形式
standby_name [, ...]
FIRST num_sync (standby_name [, ...])
ANY num_sync (standby_name [, ...])
分别对应
synchronous_standby_names = 's1 s2 s3'
--第一个为备库，第二个及以后为潜在备库
--s1宕机后，后续备库自动顶替
synchronous_standby_names = FIRST 2(s1 s2 s3)
--设置两个同步备库
--主库提交事务后，需要等待s1和s2将日志流写入WAL日志文件后再返回成功
--s1或s2宕机后s3升级为备库
synchronous_standby_names = ANY 2(s1 s2  s3)
--列表中的任意两个为同步备库
--需要等待任意两个备库写入WAL文件，再返回成功

级联复制

1.级联备库和备库都从主库同步初始数据
pg_basebackup -D /var/lib/pgsql/9.6/data -Fp -Xs -v -P -h 172.21.16.6 -p 5432 -U repuser
2.级联备库的primary_conninfo 指向主库，备库的则指向级联备库

17.pgpool-II+异步流复制实现高可用

1.pgpool部署

主备操作
tar xvf pgpool-II-3.6.6.tar.gz
./configure --prefix=/opt/pgpool --with-pgsql=/opt/pgsql

2.互信配置

主备操作
vim /etc/hosts
192.168.26.57 pghost4
192.168.26.57 pghost5
两者ssh免密登陆

3.配置pool_hba.conf

主备操作
cd /opt/pgpool/etc
cp pool_hba.conf.sample pool_hba.conf

加入一下内容，建议与PG的pg_hba.conf配置一致
host  replication  repuser  192.168.26.57/32  md5
host  replication  repuser  192.168.26.57/32  md5
host  replication  repuser  192.168.26.57/32  md5
host  all  all  0.0.0.0/0  md5

4.配置pool_passwd配置文件

主备操作
pg_md5 -u postgres -m postgres123
cat pool_passwd
postgres:md58sn28n8fn3k8snwk7vt2b7vb3s9

5.配置pgpool.conf配置文件

主备操作
cd /opt/pgpool/etc
cp pgpool.conf.sample-stream pgpool.conf

主库

vim pgpool.conf
端口
listen_addresses = '*'
port = 9999

后端节点
backend_hostname0 = 'pghost4'
backend_port0 = 1921
backend_data_directory0 = '/data1/pg10/pg_root'
backend_flag0 = 'ALLOW_TO_FAILOVER'

认证
enable_pool_hba = on
pool_passwd = 'pool_passwd'

日志
log_destination = 'syslog'
pid_filename = '/opt/pgpool/pgpool.pid'

LB
load_balance_mode = off

pgpool 的复制模式设置和流复制检测配置
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 10
sr_check_user = 'repuser'
sr_check_password = '123456'
sr_check_database = 'postgres'
delay_threshold = 10000000

健康检查
health_check_period = 5
health_check_timeout = 20
health_check_user = 'repuser'
health_check_password = '123456'
health_check_database = 'postgres'
health_check_max_retries = 3
health_check_retry_delay = 3

故障转移脚本
failover_command = '/opt/pgpool/failover_stream.sh %d %P %H %R'

watchdog配置
use_watchdog = on
wd_hostname 'pghost4'

备库

posted @ 2024-05-24 11:35 立勋阅读(139) 评论(0) 收藏举报

刷新页面返回顶部

题