PostgreSQL 主从复制与高可用配置实战：从零搭建生产级数据库集群

引言

某电商平台在双十一大促期间，核心PostgreSQL数据库因单点故障导致服务中断4小时，直接损失超千万。事后复盘发现：团队虽然部署了主从复制，但缺乏自动故障转移机制，且流复制延迟监控缺失。这个真实案例揭示了一个残酷现实——90%的数据库高可用方案都存在致命缺陷。

PostgreSQL 主从复制与高可用配置 - 安全技术

作为经历过多次数据库灾难恢复的安全工程师，我将带你从零搭建一套符合生产标准的主从复制+高可用方案。本文所有操作基于PostgreSQL 15，包含流复制配置、Patroni自动故障转移、延迟监控等核心内容。

一、架构设计原则

生产环境高可用方案需满足三个黄金标准：
1. RPO（恢复点目标）≤ 5秒：通过同步复制实现
2. RTO（恢复时间目标）≤ 30秒：依赖自动故障检测
3. 脑裂防护：使用DCS（分布式配置存储）仲裁

我们将采用以下架构：
– 1主2从（同步+异步混合）
– Patroni + etcd集群（3节点）
– HAProxy负载均衡
– 流复制延迟实时监控

二、基础环境部署

2.1 服务器规划

角色        IP地址          PostgreSQL版本  系统
主节点      192.168.1.10    15.2            Ubuntu 22.04
同步从节点   192.168.1.11    15.2            Ubuntu 22.04
异步从节点   192.168.1.12    15.2            Ubuntu 22.04
etcd节点1   192.168.1.20    3.5.0           Ubuntu 22.04
etcd节点2   192.168.1.21    3.5.0           Ubuntu 22.04
etcd节点3   192.168.1.22    3.5.0           Ubuntu 22.04

2.2 系统优化（所有节点执行）

# 关闭透明大页
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

# 内核参数优化
cat >> /etc/sysctl.conf << EOF
vm.swappiness=1
vm.dirty_background_ratio=5
vm.dirty_ratio=10
kernel.sem=500 256000 250 1024
net.core.somaxconn=65535
EOF
sysctl -p

# 安装依赖
apt update && apt install -y wget curl vim postgresql-15

三、流复制配置（核心步骤）

3.1 主节点配置

# 编辑 postgresql.conf
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1024
hot_standby = on
synchronous_standby_names = 'FIRST 1 (sync_node)'

# 编辑 pg_hba.conf
host replication replicator 192.168.1.0/24 md5
host all all 192.168.1.0/24 md5

# 创建复制用户
CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'StrongP@ssw0rd';

# 重启服务
systemctl restart postgresql

3.2 从节点配置（同步节点示例）

# 停止服务
systemctl stop postgresql

# 清空数据目录
rm -rf /var/lib/postgresql/15/main/*

# 从主节点拉取基础备份
pg_basebackup -h 192.168.1.10 -U replicator -D /var/lib/postgresql/15/main -P -v -R -X stream

# 创建 standby.signal 文件（自动生成）
# 编辑 postgresql.conf 添加：
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=StrongP@ssw0rd application_name=sync_node'
synchronous_commit = remote_write

# 启动服务
systemctl start postgresql

3.3 异步从节点配置

# 与同步节点相同，但 postgresql.conf 中：
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=StrongP@ssw0rd application_name=async_node'
synchronous_commit = off

3.4 验证复制状态

-- 主节点查看复制状态
SELECT * FROM pg_stat_replication;

-- 从节点查看接收延迟
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), 
       pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS replay_lag;

四、Patroni高可用集群搭建

4.1 etcd集群部署（所有etcd节点）

# 下载etcd
wget https://github.com/etcd-io/etcd/releases/download/v3.5.0/etcd-v3.5.0-linux-amd64.tar.gz
tar xzf etcd-v3.5.0-linux-amd64.tar.gz
cp etcd-v3.5.0-linux-amd64/etcd* /usr/local/bin/

# 创建配置文件 /etc/etcd/etcd.conf（节点1示例）
name: etcd-node1
data-dir: /var/lib/etcd
listen-client-urls: http://192.168.1.20:2379
advertise-client-urls: http://192.168.1.20:2379
listen-peer-urls: http://192.168.1.20:2380
initial-advertise-peer-urls: http://192.168.1.20:2380
initial-cluster: etcd-node1=http://192.168.1.20:2380,etcd-node2=http://192.168.1.21:2380,etcd-node3=http://192.168.1.22:2380
initial-cluster-token: pgcluster
initial-cluster-state: new

# 启动etcd
systemctl enable etcd && systemctl start etcd

4.2 Patroni安装与配置（所有PG节点）

# 安装Patroni
pip install patroni[etcd] psycopg2-binary

# 创建配置文件 /etc/patroni.yml
scope: pgcluster
namespace: /db/
name: pg-node1

restapi:
  listen: 192.168.1.10:8008
  connect_address: 192.168.1.10:8008

etcd:
  host: 192.168.1.20:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_connections: 100
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
  initdb:
  - encoding: UTF8
  - data-checksums

postgresql:
  listen: 192.168.1.10:5432
  connect_address: 192.168.1.10:5432
  data_dir: /var/lib/postgresql/15/main
  bin_dir: /usr/lib/postgresql/15/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: StrongP@ssw0rd
    superuser:
      username: postgres
      password: SuperP@ssw0rd
  parameters:
    unix_socket_directories: '/var/run/postgresql'

# 启动Patroni
systemctl enable patroni && systemctl start patroni

4.3 验证集群状态

# 查看集群拓扑
patronictl -c /etc/patroni.yml list

# 输出示例：
+ Cluster: pgcluster ---------+---------+----+-----------+
| Member    | Host           | Role    | State | Lag       |
+-----------+----------------+---------+-------+-----------+
| pg-node1  | 192.168.1.10  | Leader  | running |          |
| pg-node2  | 192.168.1.11  | Replica | running | 0        |
| pg-node3  | 192.168.1.12  | Replica | running | 0        |
+-----------+----------------+---------+-------+-----------+

五、故障转移测试

5.1 模拟主节点故障

# 强制停止主节点Patroni
systemctl stop patroni

# 观察自动切换（约10-15秒）
patronictl -c /etc/patroni.yml list

# 切换后同步节点自动升为主
+ Cluster: pgcluster ---------+---------+----+-----------+
| Member    | Host           | Role    | State | Lag       |
+-----------+----------------+---------+-------+-----------+
| pg-node1  | 192.168.1.10  | Replica | running | 0        |
| pg-node2  | 192.168.1.11  | Leader  | running |          |
| pg-node3  | 192.168.1.12  | Replica | running | 0        |
+-----------+----------------+---------+-------+-----------+

5.2 数据一致性验证

-- 在原主节点恢复后，检查是否有数据丢失
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
-- 使用pg_rewind进行时间线对齐
patronictl rewind pgcluster pg-node1

六、延迟监控与告警

6.1 部署监控脚本

#!/bin/bash
# check_replication_lag.sh

LAG_THRESHOLD=100  # MB

for node in "192.168.1.11" "192.168.1.12"; do
    LAG=$(psql -h $node -U postgres -t -A -c \
        "SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) / 1024 / 1024;")

    if [ $LAG -gt $LAG_THRESHOLD ]; then
        echo "CRITICAL: Replication lag on $node is ${LAG}MB"
        # 发送告警（邮件/钉钉/短信）
    fi
done

6.2 设置crontab

*/1 * * * * /usr/local/bin/check_replication_lag.sh >> /var/log/replication_monitor.log 2>&1

七、安全加固措施

7.1 传输加密

# 生成证书
openssl req -new -text -nodes -subj '/CN=pg-master' -out server.req
openssl rsa -in privkey.pem -out server.key
openssl req -x509 -in server.req -text -key server.key -out server.crt

# postgresql.conf 配置
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'

7.2 访问控制

-- 限制复制用户来源IP
REVOKE ALL ON DATABASE postgres FROM replicator;
GRANT CONNECT ON DATABASE postgres TO replicator;
-- 创建专用复制槽
SELECT pg_create_physical_replication_slot('sync_slot');

八、生产环境最佳实践

网络隔离：复制流量走独立VLAN，避免与业务流量争抢带宽
资源预留：主从节点配置相同规格，避免切换后性能瓶颈
定期演练：每月执行一次故障切换演练，记录切换时间
备份策略：结合pgBackRest实现增量备份，RTO控制在1小时内
版本匹配：所有节点PostgreSQL小版本必须一致
监控维度：除延迟外，还需监控WAL生成速率、复制槽活跃度

总结

通过本文的实战部署，我们搭建了一个具备自动故障转移能力、RPO≤5秒、RTO≤30秒的生产级PostgreSQL集群。关键配置要点包括：

同步复制确保数据零丢失
Patroni + etcd实现脑裂防护
延迟监控脚本预防数据不一致
SSL加密保障传输安全

记住：高可用不是一次性的配置工程，而是需要持续维护的运维体系。建议在部署完成后，使用pgbench进行压力测试，验证集群在极端负载下的表现。

最后，引用PostgreSQL社区的一句名言：”Your database is only as available as your worst failover scenario.” 不要等到灾难发生才验证你的高可用方案。

📚 推荐资源

Kali Linux 渗透测试 — 渗透测试实战指南
Web安全深度剖析 — Web安全从入门到精通
白帽子讲Web安全 — 阿里安全专家经典之作

– 部分链接含推广返佣 –

🪐 加入「渗透实战安全圈」

每天分享渗透测试实战、挖洞技巧、漏洞分析、工具推荐

知识星球

https://t.zsxq.com/40MyD

💻 安全运维 / Linux运维 / 渗透测试技术支持
业务需求可联系博客作者

PostgreSQL 主从复制与高可用配置

PostgreSQL 主从复制与高可用配置实战：从零搭建生产级数据库集群

引言

一、架构设计原则

二、基础环境部署

2.1 服务器规划

2.2 系统优化（所有节点执行）

三、流复制配置（核心步骤）

3.1 主节点配置

3.2 从节点配置（同步节点示例）

3.3 异步从节点配置

3.4 验证复制状态

四、Patroni高可用集群搭建

4.1 etcd集群部署（所有etcd节点）

4.2 Patroni安装与配置（所有PG节点）

4.3 验证集群状态

五、故障转移测试

5.1 模拟主节点故障

5.2 数据一致性验证

六、延迟监控与告警

6.1 部署监控脚本

6.2 设置crontab

七、安全加固措施

7.1 传输加密

7.2 访问控制

八、生产环境最佳实践

总结

📚 推荐资源

🪐 加入「渗透实战安全圈」

By admin

发表回复取消回复

文章缩略

【速查】Linux 磁盘IO性能快速检测

Nmap 高级扫描技巧与绕过防火墙

【速查】网络故障排查从ping到traceroute

Linux 服务器性能调优排查方法

PostgreSQL 主从复制与高可用配置实战：从零搭建生产级数据库集群

引言

一、架构设计原则

二、基础环境部署

2.1 服务器规划

2.2 系统优化（所有节点执行）

三、流复制配置（核心步骤）

3.1 主节点配置

3.2 从节点配置（同步节点示例）

3.3 异步从节点配置

3.4 验证复制状态

四、Patroni高可用集群搭建

4.1 etcd集群部署（所有etcd节点）

4.2 Patroni安装与配置（所有PG节点）

4.3 验证集群状态

五、故障转移测试

5.1 模拟主节点故障

5.2 数据一致性验证

六、延迟监控与告警

6.1 部署监控脚本

6.2 设置crontab

七、安全加固措施

7.1 传输加密

7.2 访问控制

八、生产环境最佳实践

总结

📚 推荐资源

🪐 加入「渗透实战安全圈」

By admin

Related Post

发表回复 取消回复

文章缩略

发表回复取消回复