PostgreSQL 高可用集群部署指南

一、概述

PostgreSQL 是一款功能强大的开源关系型数据库，广泛应用于企业级应用。为保障数据库服务的高可用性，本文介绍基于 Patroni + etcd + HAProxy 的高可用集群部署方案。

1.1 架构组件

组件 作用
PostgreSQL 数据库核心
Patroni 高可用管理，自动故障切换
etcd 分布式键值存储，保存集群状态
HAProxy 负载均衡，读写分离
Keepalived VIP 漂移，确保入口高可用

1.2 集群拓扑

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
                 ┌─────────────┐
                 │   HAProxy   │
                 │  (读写分离)  │
                 └──────┬──────┘
                        │ VIP
     ┌──────────────────┼──────────────────┐
     │                  │                  │
┌────▼────┐      ┌─────▼─────┐      ┌─────▼─────┐
│  Patroni │      │  Patroni  │      │  Patroni  │
│  PG(主)  │      │  PG(从 1)  │      │  PG(从 2)  │
└────┬────┘      └─────┬─────┘      └─────┬─────┘
     │                  │                  │
     └──────────────────┼──────────────────┘
                        │
                 ┌──────▼──────┐
                 │    etcd     │
                 │  (集群状态)  │
                 └─────────────┘

二、环境准备

2.1 服务器规划

主机名 IP 地址 角色
pg-node1 192.168.1.101 Patroni+PG+etcd
pg-node2 192.168.1.102 Patroni+PG+etcd
pg-node3 192.168.1.103 Patroni+PG+etcd
haproxy1 192.168.1.111 HAProxy+Keepalived
haproxy2 192.168.1.112 HAProxy+Keepalived
VIP 192.168.1.100 虚拟 IP

2.2 系统要求

  • CentOS 7+/Ubuntu 20.04+
  • 内存 ≥ 4GB
  • 磁盘 ≥ 50GB SSD
  • 网络互通，防火墙开放必要端口

2.3 端口规划

端口 服务 说明
5432 PostgreSQL 数据库服务
2379/2380 etcd 集群通信
8008 Patroni REST API
5000/5001 HAProxy 写/读端口
6432 HAProxy 统计页面

三、etcd 集群部署

3.1 安装 etcd

在所有节点执行：

1
2
3
4
5
6
7
# Ubuntu/Debian
wget https://github.com/etcd-io/etcd/releases/download/v3.5.9/etcd-v3.5.9-linux-amd64.tar.gz
tar xvf etcd-v3.5.9-linux-amd64.tar.gz
sudo mv etcd-v3.5.9-linux-amd64/etcd* /usr/local/bin/

# 或使用包管理器
sudo apt install etcd-server etcd-client

3.2 配置 etcd

创建 /etc/etcd/etcd.conf.yml:

1
2
3
4
5
6
7
8
9
name: etcd-node1
data-dir: /var/lib/etcd
listen-client-urls: http://0.0.0.0:2379
advertise-client-urls: http://192.168.1.101:2379
listen-peer-urls: http://0.0.0.0:2380
initial-advertise-peer-urls: http://192.168.1.101:2380
initial-cluster: etcd-node1=http://192.168.1.101:2380,etcd-node2=http://192.168.1.102:2380,etcd-node3=http://192.168.1.103:2380
initial-cluster-token: pg-cluster
initial-cluster-state: new

注意: 每个节点修改 name 和对应的 IP 地址。

3.3 启动 etcd

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 创建 systemd 服务
sudo tee /etc/systemd/system/etcd.service > /dev/null <<EOF
[Unit]
Description=etcd
After=network.target

[Service]
Type=notify
ExecStart=/usr/local/bin/etcd --config-file=/etc/etcd/etcd.conf.yml
Restart=always
User=etcd
Group=etcd

[Install]
WantedBy=multi-user.target
EOF

# 创建用户并设置权限
sudo useradd -r -s /bin/false etcd
sudo mkdir -p /var/lib/etcd
sudo chown -R etcd:etcd /var/lib/etcd

# 启动服务
sudo systemctl daemon-reload
sudo systemctl enable etcd
sudo systemctl start etcd

# 检查状态
sudo systemctl status etcd
etcdctl member list

四、PostgreSQL 安装

4.1 安装 PostgreSQL

在所有节点执行：

1
2
3
4
5
6
7
8
9
# Ubuntu/Debian
sudo apt install postgresql-15 postgresql-contrib-15 postgresql-server-dev-15

# CentOS/RHEL
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install postgresql15 postgresql15-server postgresql15-contrib

# 初始化数据库（CentOS）
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

4.2 配置 PostgreSQL

编辑 /etc/postgresql/15/main/postgresql.conf (Ubuntu) 或 /var/lib/pgsql/15/data/postgresql.conf (CentOS):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 基本配置
listen_addresses = '*'
port = 5432
max_connections = 200

# WAL 配置
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
hot_standby_feedback = on

# 归档配置（可选）
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'

编辑 pg_hba.conf:

1
2
3
4
5
6
7
8
# 本地连接
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

# 集群内复制
host    replication     replicator      192.168.1.0/24          md5
host    all             all             192.168.1.0/24          md5

4.3 创建复制用户

1
2
3
sudo -u postgres psql <<EOF
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replica_password';
EOF

五、Patroni 配置

5.1 安装 Patroni

1
2
3
4
5
6
7
8
# 安装依赖
sudo apt install python3-pip python3-dev libpq-dev

# 安装 Patroni
sudo pip3 install patroni[etcd3]

# 或使用包管理器
sudo apt install patroni

5.2 配置 Patroni

创建 /etc/patroni/patroni.yml:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
scope: pg-cluster
namespace: /db/
name: pg-node1

restapi:
  listen: 192.168.1.101:8008
  connect_address: 192.168.1.101:8008

etcd3:
  hosts:
    - 192.168.1.101:2379
    - 192.168.1.102:2379
    - 192.168.1.103:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    synchronous_mode: true
    synchronous_mode_strict: true
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        hot_standby_feedback: "on"

  initdb:
    - encoding: UTF8
    - data-checksums

  pg_hba:
    - host replication replicator 192.168.1.0/24 md5
    - host all all 192.168.1.0/24 md5

  users:
    admin:
      password: admin_password
      options:
        - createrole
        - createdb

postgresql:
  listen: 192.168.1.101:5432
  connect_address: 192.168.1.101:5432
  data_dir: /var/lib/postgresql/15/main
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replica_password
    superuser:
      username: postgres
      password: postgres_password
  parameters:
    unix_socket_directories: /var/run/postgresql

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

注意: 每个节点修改 name、restapi、postgresql.listen 和 postgresql.connect_address 为本机 IP。

5.3 启动 Patroni

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 创建 systemd 服务
sudo tee /etc/systemd/system/patroni.service > /dev/null <<EOF
[Unit]
Description=Patroni PostgreSQL HA
After=network.target etcd.service

[Service]
Type=forking
User=postgres
Group=postgres
Environment=PATRONI_CONFIG_FILE=/etc/patroni/patroni.yml
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
ExecReload=/bin/kill -s HUP \$MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=30

[Install]
WantedBy=multi-user.target
EOF

# 启动服务
sudo systemctl daemon-reload
sudo systemctl enable patroni
sudo systemctl start patroni

# 检查状态
sudo systemctl status patroni
patronictl -c /etc/patroni/patroni.yml list

六、HAProxy 配置

6.1 安装 HAProxy

在 HAProxy 节点执行：

1
sudo apt install haproxy

6.2 配置 HAProxy

编辑 /etc/haproxy/haproxy.cfg:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
global
    log /dev/log local0
    log /dev/log local1 notice
    chroot /var/lib/haproxy
    stats socket /run/haproxy/admin.sock mode 660 level admin
    stats timeout 30s
    user haproxy
    group haproxy
    daemon

defaults
    log global
    mode tcp
    option tcplog
    option dontlognull
    timeout connect 5000
    timeout client 50000
    timeout server 50000

# 统计页面
listen stats
    bind *:6432
    stats enable
    stats uri /
    stats refresh 10s
    stats admin if LOCALHOST

# 写操作 - 指向主节点
listen postgresql-write
    bind *:5000
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-node1 192.168.1.101:5432 check port 8008
    server pg-node2 192.168.1.102:5432 check port 8008
    server pg-node3 192.168.1.103:5432 check port 8008

# 读操作 - 负载均衡到所有节点
listen postgresql-read
    bind *:5001
    option httpchk GET /health
    http-check expect status 200
    default-server inter 3s fall 3 rise 2
    server pg-node1 192.168.1.101:5432 check port 8008
    server pg-node2 192.168.1.102:5432 check port 8008
    server pg-node3 192.168.1.103:5432 check port 8008

6.3 启动 HAProxy

1
2
3
sudo systemctl enable haproxy
sudo systemctl start haproxy
sudo systemctl status haproxy

七、Keepalived 配置（可选）

7.1 安装 Keepalived

1
sudo apt install keepalived

7.2 配置 Keepalived

主节点 (haproxy1) /etc/keepalived/keepalived.conf:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
vrrp_script check_haproxy {
    script "killall -0 haproxy"
    interval 2
    weight 2
}

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass secret123
    }

    virtual_ipaddresses {
        192.168.1.100/24
    }

    track_script {
        check_haproxy
    }
}

备节点 (haproxy2): 修改 state 为 BACKUP，priority 为 90。

7.3 启动 Keepalived

1
2
sudo systemctl enable keepalived
sudo systemctl start keepalived

八、测试与验证

8.1 检查集群状态

1
2
3
4
5
6
7
8
9
10
11
# 查看集群拓扑
patronictl -c /etc/patroni/patroni.yml list

# 输出示例:
# + Cluster: pg-cluster ---------+---------+----+-----------+
# | Member    | Host            | Role    | State | TL | Lag in MB |
# +-----------+-----------------+---------+-------+----+-----------+
# | pg-node1  | 192.168.1.101   | Leader  | running | 1  |           |
# | pg-node2  | 192.168.1.102   | Replica | running | 1  | 0         |
# | pg-node3  | 192.168.1.103   | Replica | running | 1  | 0         |
# +-----------+-----------------+---------+-------+----+-----------+

8.2 测试读写分离

1
2
3
4
5
6
7
8
9
10
# 连接写端口
psql -h 192.168.1.100 -p 5000 -U admin -d postgres

# 连接读端口
psql -h 192.168.1.100 -p 5001 -U admin -d postgres

# 测试数据
CREATE TABLE test_ha (id serial PRIMARY KEY, data text);
INSERT INTO test_ha (data) VALUES ('test data');
SELECT * FROM test_ha;

8.3 故障切换测试

1
2
3
4
5
6
7
8
# 手动故障切换
patronictl -c /etc/patroni/patroni.yml switchover pg-cluster

# 或停止主节点 Patroni
sudo systemctl stop patroni  # 在主节点执行

# 观察集群状态变化
watch -n 1 'patronictl -c /etc/patroni/patroni.yml list'

九、监控与维护

9.1 监控指标

1
2
3
4
5
6
# 使用 Patroni REST API
curl http://192.168.1.101:8008/
curl http://192.168.1.101:8008/health

# 查看 PostgreSQL 状态
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

9.2 备份策略

1
2
3
4
5
# 使用 pg_basebackup
pg_basebackup -h 192.168.1.101 -p 5432 -U replicator -D /backup/base -Ft -z -P

# 使用 pg_dump
pg_dump -h 192.168.1.100 -p 5001 -U admin postgres > backup.sql

9.3 日志查看

1
2
3
4
5
6
7
8
# Patroni 日志
journalctl -u patroni -f

# PostgreSQL 日志
tail -f /var/log/postgresql/postgresql-15-main.log

# HAProxy 日志
tail -f /var/log/haproxy.log

十、故障排查

10.1 常见问题

Patroni 无法启动:

1
2
3
4
5
# 检查 etcd 连接
etcdctl --endpoints=http://192.168.1.101:2379 get /db/pg-cluster

# 检查配置文件
patroni --validate-config /etc/patroni/patroni.yml

复制延迟:

1
2
3
4
5
# 查看复制状态
sudo -u postgres psql -c "SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication;"

# 检查 WAL 发送进程
sudo -u postgres psql -c "SELECT * FROM pg_stat_activity WHERE backend_type = 'walsender';"

脑裂问题:

1
2
3
4
5
# 检查 etcd 集群健康
etcdctl endpoint health

# 强制重新初始化（谨慎操作）
patronictl -c /etc/patroni/patroni.yml reinit pg-cluster pg-node2

十一、总结

PostgreSQL 高可用集群是保障数据库服务连续性的关键方案。通过 Patroni + etcd + HAProxy 的组合，可以实现：

  • 自动故障切换: 主节点故障时自动选举新主
  • 读写分离: 写操作定向主节点，读操作负载均衡
  • 零数据丢失: 同步复制模式确保数据一致性
  • 透明切换: 应用层无需感知后端变化

关键要点:

  • etcd 集群至少 3 节点确保一致性
  • Patroni 配置需仔细校验
  • 定期演练故障切换流程
  • 监控告警必不可少

参考文档: