【PostgreSQL-16】搭建主从复制实现读写分离与故障转移

Rocky Linux 9.7通过PatronietcdHAProxypgBouncer搭建PostgreSQL 16主从复制架构实现一主一从、读写分离与自动故障切换。1、PGSQL主从复制原理PostgreSQL 主从采用基于 WAL 预写日志的物理流式复制主库客户端执行 DML数据变更先写入WAL 日志主库walsender进程通过 TCP 流式推送 WAL从库walreceiver接收日志写入本地 WAL从库startup回放进程重放 WAL同步本地数据从库开启hot_standby可对外提供只读查询。2、架构概览集群节点信息表为满足 etcd 集群最小奇数节点要求部署 3 个 etcd 节点pgsql-node、pgsql-node2、独立 etcd3使用 3 台 服务器。两个 PG 节点 一台独立 etcd 节点。虚拟机配置(CPU/内存/硬盘)主机名IP 地址角色描述Rocky9.72C4G 60Gpgsql-master10.0.0.146PostgreSQL, Patroni, pgBouncer, etcd, HAProxy, keepalivedRocky9.72C4G 60Gpgsql-node10.0.0.147PostgreSQL, Patroni, pgBouncer, etcd, HAProxy, keepalivedRocky9.72C4G 60Getcd310.0.0.145etcd 仲裁节点独立部署VIP 入口信息表VIP 名称IP 地址端口用途写 VIP10.0.0.2005000写入口HAProxy 路由至 PG 主库读 VIP10.0.0.2005001读入口HAProxy 路由至 PG 备库3、系统初始化3.1、关闭 SELinux、防火墙在pgsql-master、pgsql-node、etcd3三台主机上执行。# 永久关闭SELinux sed -i s/^SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config setenforce 0 # 永久关闭防火墙 systemctl disable --now firewalld3.2、更换yum源在pgsql-master、pgsql-node、etcd3三台主机上执行。# 1.备份原有源mkdir-p/etc/yum.repos.d/backcp-r/etc/yum.repos.d/*.repo /etc/yum.repos.d/back/# 2.配置阿里云yum源sed-es|^mirrorlist|#mirrorlist|g\-es|^#baseurlhttp://dl.rockylinux.org/$contentdir|baseurlhttps://mirrors.aliyun.com/rockylinux|g\-i.bak\/etc/yum.repos.d/*ocky-*.repo# 3.刷新缓存验证dnf clean alldnf makecache dnf repolist3.3、安装编译依赖包在pgsql-master、pgsql-node、etcd3三台主机上执行。dnfinstall-yreadline-devel zlib-devel openssl-devel libxml2-devel\libxslt-devel perl-devel python3-devel gcc gcc-cmakecmake bison flex\libicu-devel libuuid-devel libzstd-devel systemd-devel lz4-develwget\vimepel-releasetarunzip3.4、系统内核 / 资源限制调优在pgsql-master、pgsql-node两台主机上执行。文件句柄、进程数限制cat/etc/security/limits.d/99-postgres.confEOF postgres soft nproc 65535 postgres hard nproc 65535 postgres soft nofile 1048576 postgres hard nofile 1048576 postgres soft stack unlimited postgres hard stack unlimited EOF内核网络 / 内存参数在pgsql-master、pgsql-node两台主机上执行。cat/etc/sysctl.d/99-postgresql-16.confEOF # 共享内存、信号量、文件句柄、网络TCP优化 kernel.shmmax 68719476736 kernel.shmmni 4096 kernel.shmall 4294967296 kernel.sem 50100 6412800 50100 128 fs.file-max 767200 net.ipv4.tcp_syncookies 1 net.ipv4.tcp_tw_reuse 1 net.ipv4.tcp_keepalive_time 60 net.ipv4.tcp_fin_timeout 30 vm.max_map_count 262144 EOF生效内核参数sysctl-p在etcd3主机上执行。cat/etc/sysctl.d/99-etcd.confEOF # 全局文件句柄上限 fs.file-max 767200 # TCP 优化 net.ipv4.tcp_syncookies 1 net.ipv4.tcp_tw_reuse 1 net.ipv4.tcp_keepalive_time 60 net.ipv4.tcp_fin_timeout 30 # 增大虚拟内存映射区域数 vm.max_map_count 262144 EOF生效内核参数sysctl-p3.5、配置时间同步在pgsql-master、pgsql-node、etcd3三台主机上执行。cp/etc/chrony.conf /etc/chrony.conf.bak\cat/etc/chrony.confEOF server ntp.aliyun.com iburst server ntp1.aliyun.com iburst driftfile /var/lib/chrony/drift makestep 1.0 3 rtcsync keyfile /etc/chrony.keys ntsdumpdir /var/lib/chrony leapsectz right/UTC logdir /var/log/chrony EOF重启 chronydsystemctl restart chronyd验证同步状态chronyc sources-v3.6、修改主机名# PGSQL主节点hostnamectl set-hostname pgsql-masterexecbash# PGSQL从节点hostnamectl set-hostname pgsql-nodeexecbash# etcd3节点hostnamectl set-hostname etcd3execbash4、部署PostgreSQL 16在pgsql-master、pgsql-node两台主机上执行。安装 PostgreSQL 16 及相关软件dnfinstall-yhttps://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm dnf-qymodule disable postgresql dnfinstall-ypostgresql16-server postgresql16-contrib pgbouncer haproxy keepalived pip3install-ihttps://mirrors.aliyun.com/pypi/simple/ patroni[etcd,psycopg2-binary]# 如果无 pip先安装 python3-pip5、部署etcd(所有节点部署)etcd用于存储 PostgreSQL 集群状态、提供分布式锁避免双主脑裂、通过心跳检测节点故障一主一从双 PG 架构下若仅部署 2 个 etcd 实例任意一台服务器宕机都会导致 etcd 集群不满足过半投票机制失去自动切换能力因此需要额外独立部署一台 etcd 组成 3 节点奇数集群保证单节点故障后集群仍可正常选举新主库实现数据库自动高可用。在pgsql-master、pgsql-node、etcd3三台主机上执行。ETCD_VERv3.5.26curl-Lhttps://storage.googleapis.com/etcd/${ETCD_VER}/etcd-${ETCD_VER}-linux-amd64.tar.gz-o/tmp/etcd.tar.gztar-xzf/tmp/etcd.tar.gz-C/usr/local/bin --strip-components1etcd-${ETCD_VER}-linux-amd64/etcd etcd-${ETCD_VER}-linux-amd64/etcdctlmkdir-p/var/lib/etcd/default.etcdmkdir-p/etc/etcd5.1、部署 etcd 集群配置文件/etc/etcd/etcd.confpgsql-master上catEOF/etc/etcd/etcd.confETCD_NAMEpgsql-masterETCD_DATA_DIR/var/lib/etcd/default.etcdETCD_LISTEN_PEER_URLShttp://10.0.0.146:2380ETCD_LISTEN_CLIENT_URLShttp://10.0.0.146:2379,http://127.0.0.1:2379ETCD_INITIAL_ADVERTISE_PEER_URLShttp://10.0.0.146:2380ETCD_ADVERTISE_CLIENT_URLShttp://10.0.0.146:2379ETCD_INITIAL_CLUSTERpgsql-masterhttp://10.0.0.146:2380,pgsql-nodehttp://10.0.0.147:2380,etcd3http://10.0.0.145:2380ETCD_INITIAL_CLUSTER_TOKENpg-etcd-clusterETCD_INITIAL_CLUSTER_STATEnewEOF参数说明ETCD_NAME 当前 etcd 节点在集群内的唯一标识名称集群内不能重名。ETCD_DATA_DIR指定 etcd 持久化数据存放目录。ETCD_LISTEN_PEER_URLS节点间通信监听地址对等端口默认 2380。ETCD_LISTEN_CLIENT_URLS客户端访问监听地址客户端端口默认 2379。ETCD_INITIAL_ADVERTISE_PEER_URLS广播给集群其他节点的对等通信地址。ETCD_ADVERTISE_CLIENT_URLS 广播给集群客户端Patroni的访问地址。ETCD_INITIAL_CLUSTER定义整个 etcd 集群全部成员名称 各自对等通信地址。ETCD_INITIAL_CLUSTER_TOKEN集群唯一令牌自定义字符串即可。ETCD_INITIAL_CLUSTER_STATE仅集群第一次全新初始化启动时填new。重要注意点集群初始化完成后后续重启所有节点必须改成existing。如果重启依旧写newetcd 会尝试重新创建集群导致集群分裂、数据丢失、Patroni 主从切换异常。端口用途通信对象2380peer 对等端口etcd 节点↔etcd 节点(内部同步、选举)2379client 客户端端口Patroni/etcdctl↔etcd 节点(读写数据)pgsql-node 上将ETCD_NAME改为pgsql-nodeIP 改为10.0.0.147。etcd3 上ETCD_NAMEetcd3IP 改为10.0.0.145。pgsql-node上cat EOF /etc/etcd/etcd.conf ETCD_NAMEpgsql-node ETCD_DATA_DIR/var/lib/etcd/default.etcd ETCD_LISTEN_PEER_URLShttp://10.0.0.147:2380 ETCD_LISTEN_CLIENT_URLShttp://10.0.0.147:2379,http://127.0.0.1:2379 ETCD_INITIAL_ADVERTISE_PEER_URLShttp://10.0.0.147:2380 ETCD_ADVERTISE_CLIENT_URLShttp://10.0.0.147:2379 ETCD_INITIAL_CLUSTERpgsql-masterhttp://10.0.0.146:2380,pgsql-nodehttp://10.0.0.147:2380,etcd3http://10.0.0.145:2380 ETCD_INITIAL_CLUSTER_TOKENpg-etcd-cluster ETCD_INITIAL_CLUSTER_STATEnew EOFetcd3上cat EOF /etc/etcd/etcd.conf ETCD_NAMEetcd3 ETCD_DATA_DIR/var/lib/etcd/default.etcd ETCD_LISTEN_PEER_URLShttp://10.0.0.145:2380 ETCD_LISTEN_CLIENT_URLShttp://10.0.0.145:2379,http://127.0.0.1:2379 ETCD_INITIAL_ADVERTISE_PEER_URLShttp://10.0.0.145:2380 ETCD_ADVERTISE_CLIENT_URLShttp://10.0.0.145:2379 ETCD_INITIAL_CLUSTERpgsql-masterhttp://10.0.0.146:2380,pgsql-nodehttp://10.0.0.147:2380,etcd3http://10.0.0.145:2380 ETCD_INITIAL_CLUSTER_TOKENpg-etcd-cluster ETCD_INITIAL_CLUSTER_STATEnew EOF5.2、配置systemd服务文件在pgsql-master、pgsql-node、etcd3三台主机上执行。cat /etc/systemd/system/etcd.service EOF [Unit] Descriptionetcd Afternetwork-online.target [Service] Typenotify EnvironmentFile/etc/etcd/etcd.conf ExecStart/usr/local/bin/etcd Restartalways RestartSec10s LimitNOFILE65536 [Install] WantedBymulti-user.target EOF5.3、启动 etcd 集群在 所有三个节点 上依次执行systemctl daemon-reload systemctl enable --now etcd查看etcd状态systemctl status etcd验证集群健康etcdctl --endpointshttp://10.0.0.146:2379,\ http://10.0.0.147:2379,http://10.0.0.145:2379 endpoint health校验成功后三台全部修改配置文件将new替换为existing重载服务sed -i s/ETCD_INITIAL_CLUSTER_STATEnew/ETCD_INITIAL_CLUSTER_STATEexisting/ /etc/etcd/etcd.conf systemctl daemon-reload systemctl restart etcd不修改后果重启后 etcd 会尝试重建全新集群集群分裂、Patroni 获取不到 PG 主从状态、锁丢失。6、配置 PatroniPatroni是 PostgreSQL 高可用集群管理工具依托 etcd 分布式协调存储自动完成 PG 主从搭建、主库故障检测、自动选主、故障切换、流复制修复全程无需人工干预实现 PostgreSQL 无人工值守的高可用架构。在pgsql-master、pgsql-node两台主机上执行。6.1、创建必要目录/var/lib/pgsql/16/data/是单机独立 PG的默认目录。/data/postgresql是Patroni 高可用集群专用自定义数据目录二者场景不同不能混用。创建/data/pg_wal存放事务日志RPM 默认只能把 wal 塞在 data 目录内无法分离 IO。后续如果升级 PG17、18只需要新建/data/postgresql17不用改动系统默认路径。mkdir -p /data/postgresql /data/pg_wal /etc/patroni chown -R postgres:postgres /data/postgresql /data/pg_wal chmod 700 /data/postgresql /data/pg_wal6.2、Patroni 配置文件修改name和hostsIP 还有connect_addressIP在pgsql-master上cat /etc/patroni/postgres.yml EOF scope: pg_prod name: pgsql-master restapi: listen: 0.0.0.0:8008 connect_address: 10.0.0.146:8008 etcd3: hosts: 10.0.0.146:2379,10.0.0.147:2379,10.0.0.145:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 postgresql: use_pg_rewind: true use_slots: true parameters: listen_addresses: * port: 5432 max_connections: 200 shared_buffers: 2GB wal_level: replica hot_standby: on wal_keep_size: 1024 max_wal_senders: 10 max_replication_slots: 10 wal_log_hints: on archive_mode: on archive_command: /bin/true logging_collector: on log_directory: pg_log log_filename: postgresql-%a.log log_truncate_on_rotation: on log_statement: ddl initdb: - encoding: UTF8 ->cat /etc/patroni/postgres.yml EOF scope: pg_prod name: pgsql-node restapi: listen: 0.0.0.0:8008 connect_address: 10.0.0.147:8008 etcd3: hosts: 10.0.0.146:2379,10.0.0.147:2379,10.0.0.145:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 postgresql: use_pg_rewind: true use_slots: true parameters: listen_addresses: * port: 5432 max_connections: 200 shared_buffers: 2GB wal_level: replica hot_standby: on wal_keep_size: 1024 max_wal_senders: 10 max_replication_slots: 10 wal_log_hints: on archive_mode: on archive_command: /bin/true logging_collector: on log_directory: pg_log log_filename: postgresql-%a.log log_truncate_on_rotation: on log_statement: ddl initdb: - encoding: UTF8 ->6.3、启动 Patroni在PG主节点和从节点配置systemd服务文件cat /etc/systemd/system/patroni.service EOF [Unit] DescriptionPatroni for PostgreSQL Afternetwork-online.target Wantsnetwork-online.target [Service] Typesimple Userpostgres Grouppostgres ExecStart/usr/local/bin/patroni /etc/patroni/postgres.yml Restarton-failure LimitNOFILE65536 [Install] WantedBymulti-user.target EOF先启动pgsql-master它将初始化数据库并成为主库。启动服务并设置为开机自启systemctl daemon-reload systemctl enable --now patroni查看状态patronictl -c /etc/patroni/postgres.yml list在 pg2 上同样配置并启动它将自动成为备库。systemctl daemon-reload systemctl enable --now patroni查看状态patronictl -c /etc/patroni/postgres.yml list7、配置 pgBouncer 连接池pgBouncer作为 PostgreSQL 专用连接池复用数据库真实连接、控制后端并发数量减少数据库进程资源消耗避免高并发场景数据库连接溢出雪崩同时统一会话超时与访问管控适配 Patroni PostgreSQL 高可用集群。在pgsql-master、pgsql-node两台主机上执行。7.1、配置 pgBouncercat /etc/pgbouncer/pgbouncer.ini EOF [databases] * host127.0.0.1 port5432 [pgbouncer] listen_addr * listen_port 6432 auth_type md5 auth_file /etc/pgbouncer/userlist.txt admin_users postgres pool_mode transaction default_pool_size 30 max_client_conn 500 EOF7.2、生成 userlist.txtecho postgres PostgresPass123 /etc/pgbouncer/userlist.txt echo replicator ReplicatorPass123 /etc/pgbouncer/userlist.txt7.3、启动 pgBouncersystemctl enable --now pgbouncer8、配置 HAProxy 实现读写分离HAProxy为Patroni PostgreSQL集群提供数据库负载均衡与自动路由能力通过对接 Patroni 状态识别当前集群主节点对外统一读写访问端口节点故障或主从自动切换时自动转发流量搭配 Keepalived 虚拟 IP 实现业务无感知切换同时完成后端实例健康检测、并发限流隔离故障数据库节点保障业务数据库访问持续可用。8.1、创建健康检查用户在主库pgsql-master执行一次Patroni 会自动同步到备库pgsql-nodesudo -u postgres psql -c CREATE ROLE haproxy_check WITH LOGIN PASSWORD CheckPass123; sudo -u postgres psql -c GRANT pg_monitor TO haproxy_check;将用户加入 pgBouncer 的 userlist.txt两台都加echo haproxy_check CheckPass123 /etc/pgbouncer/userlist.txt8.2、配置HAProxy在pgsql-master、pgsql-node两台主机上执行。cp /etc/haproxy/haproxy.cfg{,_bak}cat /etc/haproxy/haproxy.cfgEOF global log /dev/log local0 maxconn 10000 defaults log global mode tcp retries 2 timeout client 30m timeout connect 5s timeout server 30m timeout check 5s # 写前端端口 5000 frontend psql_write bind *:5000 default_backend write_servers backend write_servers option httpchk GET /read-write http-check expect status 200 default-server inter 3s fall 3 rise 2 server pgsql-master 10.0.0.146:6432 check port 8008 server pgsql-node 10.0.0.147:6432 check port 8008 # 读前端端口 5001 frontend psql_read bind *:5001 default_backend read_servers backend read_servers option httpchk GET /read-only http-check expect status 200 default-server inter 3s fall 3 rise 2 server pgsql-master 10.0.0.146:6432 check port 8008 backup # 主库仅备库故障时才启用 server pgsql-node 10.0.0.147:6432 check port 8008 # 备库正常处理读请求 # 统计页面 listen stats bind *:7000 mode http stats enable stats uri / EOF关键/read-write只在主库返回 200/read-only只在备库返回 200HAProxy 根据响应决定路由。启动HAProxysystemctl enable --now haproxy9、配置 Keepalived 实现 VIP 高可用Keepalived通过 VRRP 协议提供集群浮动虚拟 IP消除 HAProxy 代理单点故障当节点服务器或代理服务故障时虚拟 IP 自动漂移至正常节点业务仅需连接固定 VIP 即可访问数据库无需修改连接地址配合 HAProxy、Patroni 实现数据库全链路无感知故障切换。注意由于 HAProxy 检查 Patroni API 决定路由即使 HAProxy 切换配置一致不会出错。查看网卡名ip add在pgsql-master上cp /etc/keepalived/keepalived.conf{,_bak}cat /etc/keepalived/keepalived.conf EOF vrrp_instance VI_1 { state MASTER interface ens33 # 更换为实际网卡名 virtual_router_id 51 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 123654 } virtual_ipaddress { 10.0.0.200/32 # 写读共用一个 VIP 或分开两个实例 } } # 如果需要读写分离的 VIP 各自独立可再配置一个实例此处略。 EOF启动 keepalivedsystemctl enable --now keepalived在pgsql-node上cp /etc/keepalived/keepalived.conf{,_bak}cat /etc/keepalived/keepalived.conf EOF vrrp_instance VI_1 { state BACKUP interface ens33 # 更换为实际网卡名 virtual_router_id 51 priority 90 advert_int 1 authentication { auth_type PASS auth_pass 123654 } virtual_ipaddress { 10.0.0.200/32 # 写读共用一个 VIP 或分开两个实例 } } # 如果需要读写分离的 VIP 各自独立可再配置一个实例此处略。 EOF启动 keepalivedsystemctl enable --now keepalivedHAProxy 监控页面HAProxy 监控页面http://VIP:7000可查看代理全局连接流量、前端客户端接入情况、PG 后端分组整体状态、单台数据库节点在线 / 故障状态、健康检测日志、连接延迟与流量分配数据能直观识别 Patroni 集群当前主库、节点故障切换记录用于数据库集群运维排查与负载观测。http://10.0.0.200:700010、验证读写分离与故障切换10.1、读写测试对主库进行读操作会返回f主库负责写操作无法进行读操作。PGPASSWORDPostgresPass123 \ psql -h 10.0.0.200 -p 5000 -U postgres -c SELECT pg_is_in_recovery();对从库进行读操作会返回t从库负责进行读操作PGPASSWORDPostgresPass123 \ psql -h 10.0.0.200 -p 5001 -U postgres -c SELECT pg_is_in_recovery();10.2、模拟主库故障在pgsql-master上停止Patronipgsql-node会自动提升为主systemctl stop patronipatronictl -c /etc/patroni/postgres.yml list此时pgsql-node升级为主库后就不在负责读操作将pgsql-master恢复后将会作为从节点需要在主库和从库服务器上修改/etc/haproxy/haproxy.cfg修改前backend read_servers option httpchk GET /read-only http-checkexpectstatus200default-server inter 3s fall3rise2server pgsql-master10.0.0.146:6432 check port8008backup server pgsql-node10.0.0.147:6432 check port8008修改后backend read_servers option httpchk GET /read-only http-checkexpectstatus200default-server inter 3s fall3rise2server pgsql-master10.0.0.146:6432 check port8008server pgsql-node10.0.0.147:6432 check port8008backuppatronictl-c/etc/patroni/postgres.yml listPGPASSWORDPostgresPass123\psql-h10.0.0.200-p5000-Upostgres-cSELECT pg_is_in_recovery();PGPASSWORDPostgresPass123\psql-h10.0.0.200-p5001-Upostgres-cSELECT pg_is_in_recovery();