搜索中...
🔍

未找到相关结果

Akemi

PG数据库从入门到高可用到K8s部署

PostgreSQL Patroni K8s
字数统计: 4.7k阅读时长: 22 min
2026/08/08

本文将学习单机与高可用PG集群的部署，以及通过harbor看Patroni + K8s ConfigMap

PG 部署形态谱（从简到复杂）

准备学习1和3层，4-5是DBA干的事

层级 方案 适用场景 复杂度
1 单机 + 定期备份 开发/测试，数据不重要 ⭐
2 主备流复制（异步/同步） 小规模生产，手动切换 ⭐⭐
3 自动故障转移集群 生产环境标准方案 ⭐⭐⭐
4 读写分离 读多写少，需要分担压力 ⭐⭐⭐⭐
5 分布式水平扩展 超大规模，单机扛不住 ⭐⭐⭐⭐⭐
6 云托管 RDS 不想运维，花钱省事 ⭐

PG单机部署

二进制部署

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 我是almalinux，卸载自带的pg13
dnf -qy module disable postgresql

# 官方yum源
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 安装
dnf install -y postgresql16-server postgresql16
# 初始化，创建/var/lib/pgsql/16/data/的内容，一个新实例
/usr/pgsql-16/bin/postgresql-16-setup initdb
# 启动
systemctl start postgresql-16

/var/lib/pgsql/16/data/postgresql.conf   ← 主配置
/var/lib/pgsql/16/data/pg_hba.conf       ← 认证配置（相当于白名单）

本机登录无密码

PG配置文件

其实这部分内容完全可以直接问AI，我这里就让AI整理完之后给我搬回来了

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
68
1.连接与认证
max_connections 100 # 需重启。最大并发连接数，每个连接约10MB内存。生产建议200~500
listen_addresses 'localhost' # 需重启。监听地址，远程连接改 '*'
port 5432 # 需重启。端口
superuser_reserved_connections 3 # 需重启。为超级用户保留的连接数，普通用户连满时超级用户仍可连入
password_encryption scram-sha-256 # 密码加密方式，推荐 scram-sha-256，兼容旧版用 md5
2.内存
shared_buffers 128MB # 需重启。最核心参数。PG的数据缓存池，类比MySQL innodb_buffer_pool_size。建议物理内存的25%
work_mem 4MB # 单个排序/哈希操作的内存。复杂查询会用多个，别设太大。生产建议16~64MB
maintenance_work_mem 64MB # VACUUM、CREATE INDEX用的内存。生产建议256~512MB
effective_cache_size 4GB # 告诉优化器"系统有多少内存可用于磁盘缓存"。建议物理内存50~75%
huge_pages try # 需重启。大页内存。on/try/off，shared_buffers很大时建议开启
3.WAL（预写日志）
wal_level replica # 需重启。WAL级别。replica（默认，支持流复制）/ logical（逻辑复制）/ minimal（最少日志）
max_wal_size 1GB # WAL文件最大总量，触发checkpoint的阈值。生产建议2~4GB
min_wal_size 80MB # WAL文件最小保留量
wal_buffers -1（自动） # 需重启。WAL写入缓冲区，-1 = shared_buffers 的 1/32
checkpoint_timeout 5min # 自动checkpoint间隔。范围30s~1d
checkpoint_completion_target 0.9 # checkpoint平滑写入目标比例。0.9 = 用90%间隔时间慢慢写
fsync on # 绝对不要关。关闭可导致不可恢复的数据损坏
synchronous_commit on # 事务提交是否等WAL写入磁盘。on=安全，off=快但可能丢最后几笔事务
4.WAL归档（备份恢复用）
archive_mode off # 需重启。是否开启WAL归档。做PITR（时间点恢复）必须设为on
archive_command '' # 归档命令模板。%p=WAL文件路径，%f=文件名。示例：cp %p /archive/%f
5.流复制（Level 2/3用）
max_wal_senders 10 # 需重启。允许多少个WAL发送进程（即最多连多少个备库）。1主2备至少设3
max_replication_slots 10 # 需重启。复制槽数量。用于保证备库断连后不会丢失WAL
wal_keep_size 0 # 保留多少MB的WAL供备库追赶。0=不保留（靠复制槽）
hot_standby on # 需重启。备库是否允许只读查询。on=允许（读写分离的基础）
synchronous_standby_names '' # 同步复制的备库名称。空=异步复制。设值后主库提交事务必须等该备库确认
6.日志
log_destination 'stderr' # 日志输出格式。stderr / csvlog / jsonlog / syslog
logging_collector on # 需重启。是否将stderr日志收集到文件。必须on才能写日志文件
log_directory 'log' # 日志目录（相对于数据目录）
log_filename 'postgresql-%a.log' # 日志文件名模式。%a=星期几，每天一个文件
log_rotation_age 1d # 日志轮转间隔
log_truncate_on_rotation on # 轮转时覆盖还是追加
log_line_prefix '%m [%p] ' # 日志行前缀。%m=毫秒时间戳，%p=进程ID。可加 %u=用户 %d=数据库 %r=客户端
log_timezone 'Asia/Shanghai' # 日志时区
log_min_duration_statement -1（关闭） # 慢查询日志阈值(ms)。-1=关闭，0=记录所有，1000=记录>1秒的SQL
log_statement 'none' # 记录哪些SQL。none / ddl / mod / all
log_checkpoints on # 是否记录checkpoint信息
log_connections / log_disconnections off # 是否记录连接/断开事件
7.Autovacuum（自动清理）
autovacuum on # 不要关。PG的UPDATE/DELETE不立即释放空间，靠VACUUM回收
autovacuum_max_workers 3 # 需重启。最大并行VACUUM进程数
autovacuum_naptime 1min # VACUUM检查间隔
autovacuum_vacuum_threshold 50 # 触发VACUUM的最小变更行数
autovacuum_vacuum_scale_factor 0.2 # 触发VACUUM的表大小比例。0.2 = 变更超过表的20%时触发
8.查询优化器
random_page_cost 4.0 # 随机IO代价估算。SSD可降到1.1~1.5，影响优化器是否走索引
effective_cache_size 4GB # 告诉优化器系统有多少缓存可用。建议物理内存50~75%
max_parallel_workers_per_gather 2 # 单个查询的最大并行工作进程数
9.锁管理
deadlock_timeout 1s # 等待锁超时后才检测死锁
max_locks_per_transaction 64 # 需重启。每个事务最多持有的锁数量。分区表多时需要调大
10.其他
dynamic_shared_memory_type posix # 需重启。动态共享内存类型。posix/sysv/mmap
shared_preload_libraries '' # 需重启。启动时预加载的扩展库。如pg_stat_statements需要在这里配

# 重启、重载
systemctl daemon-reload
systemctl restart postgresql-16

二进制的安装没有初始密码
ss -tunlp | grep post
tcp   LISTEN 0      200                            127.0.0.1:5432       0.0.0.0:*    users:(("postgres",pid=2465046,fd=8))

使用docker部署

可挂载可不挂，反正推荐正式环境用挂载，和mysql差不多，没啥可说的

1
2
3
4
5
6
7
8
9
10
docker run --name some-postgres \
  -v /my/own/datadir:/var/lib/postgresql/data \
  -e POSTGRES_PASSWORD=*** \
  -d postgres:16
  
POSTGRES_PASSWORD # 必填。超级用户密码
POSTGRES_USER # 可选。默认 postgres。会创建同名超级用户和数据库
POSTGRES_DB # 可选。默认和 POSTGRES_USER 同名。首次启动时创建的数据库
POSTGRES_INITDB_ARGS # 可选。传给 initdb 的参数，如 --data-checksums
POSTGRES_HOST_AUTH_METHOD # 可选。认证方式，默认 scram-sha-256

PG的使用与连接测试

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
设置一个密码
su - postgres -c "psql -c \"ALTER USER postgres PASSWORD 'postgres';\""

cat > test-pg.py <<EOF
import psycopg2

conn = psycopg2.connect(
    host="127.0.0.1",
    port=5432,
    dbname="postgres",
    user="postgres",
    password="postgres",
)

cur = conn.cursor()
cur.execute("SELECT version()")
print(cur.fetchone()[0])

cur.close()
conn.close()
EOF

chmod +x test-pg.py
pip3 install psycopg2-binary
python test-pg.py

PostgreSQL 16.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit

PG备份-物理备份/逻辑备份

没啥可说的，这个和mysql一样

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
# 生成数据
# 让AI生成了一些数据，让我可以直接写入进去
python /home/ws/pg_test_data.py
✅ 数据库 testdb 已创建
✅ 3张表已创建: departments, employees, orders
✅ 数据已插入: 5个部门, 10名员工, 50个订单
   departments: 5 行
   employees: 10 行
   orders: 50 行

📊 部门员工统计:
   技术部: 3人, 平均薪资16667
   市场部: 2人, 平均薪资12750
   财务部: 2人, 平均薪资17750
   人事部: 1人, 平均薪资16000
   运维部: 2人, 平均薪资12500

✅ 测试数据准备完成

# 备份
pg_dump -h 127.0.0.1 -U postgres -d testdb -f /tmp/testdb_backup.sql

# 启动docker部署的pg
docker run -d \
  --name pg-docker \
  -e POSTGRES_PASSWORD=postgres \
  -p 5433:5432 \
  --shm-size=256m \
  postgres:16
  
# 新建库
createdb -h 127.0.0.1 -p 5433 -U postgres testdb

# 恢复到docker
psql -h 127.0.0.1 -p 5433 -U postgres -f /tmp/testdb_backup.sql
# 验证
psql -h 127.0.0.1 -p 5433 -U postgres -d testdb -c "SELECT count(*) FROM employees"
Password for user postgres:
 count
-------
    10
(1 row)

# 清理环境
systemctl stop postgresql-16
docker rm e358641bd15b

PG自动故障转移集群patroni+etcd+haproxy

Patroni 是业界标准方案，适用裸机、虚拟机的绝大多数场景

对比项 Patroni+etcd repmgr Stolon pgpool-II
自动failover ✅ ✅ ✅ ⚠️ 有限
配置管理 ✅ API驱动 ❌ 手动 ✅ ❌
社区活跃度 🔥 最高 中 低 中
DCS灵活性 ✅ 可换 ❌ ❌ ❌
K8s适配 ✅ ConfigMap ❌ ✅ ❌
参考文档：https://github.com/patroni/patroni/tree/master/docker
官方文档学了个demo，自构建镜像（将Patroni 集成到了pg集群的容器中），但实际上Patroni 命令行是可以在宿主机单独解耦安装的。
但它更加省事，所以就用这个了。
里面提供了Three-node模式和Citus cluster两种模式，前者是高可用，后者是分布式扩展 
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
git clone https://github.com/patroni/patroni.git
cd patroni

# 构建镜像与启动docker-compose
docker pull postgres:17
docker build -t patroni .
docker compose up -d
WARN[0000] /root/patroni/docker-compose.yml: the attribute `version` is obsolete, it will be ignored, please remove it to avoid potential confusion
[+] Running 7/7
 ✔ Container demo-patroni1  Running                                                            0.0s
 ✔ Container demo-patroni3  Running                                                            0.0s
 ✔ Container demo-etcd2     Running                                                            0.0s
 ✔ Container demo-etcd3     Running                                                            0.0s
 ✔ Container demo-etcd1     Running                                                            0.0s
 ✔ Container demo-patroni2  Running                                                            0.0s
 ✔ Container demo-haproxy   Started                                                           
 
 应用
  ↓
HAProxy（代理层）     ← 知道谁是主，自动路由
  ↓
Patroni（管理层）     ← 大脑，决定谁当主
  ↓
PG实例（数据层）      ← 实际存数据
  ↓
etcd（共识层）        ← 分布式锁，防脑裂

命令行与状态

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
# 查看集群的所有key（Patroni在etcd里存的全部状态）
docker exec -ti demo-etcd1 etcdctl get --keys-only --prefix /service/demo
/service/demo/config
/service/demo/initialize
/service/demo/leader
/service/demo/members/patroni1
/service/demo/members/patroni2
/service/demo/members/patroni3
/service/demo/status

docker exec -ti demo-etcd1 etcdctl get /service/demo/leader        # 谁持有锁
docker exec -ti demo-etcd1 etcdctl get /service/demo/config        # 集群动态配置
docker exec -ti demo-etcd1 etcdctl get /service/demo/members/patroni1  # 成员详情(JSON)
docker exec -ti demo-etcd1 etcdctl get /service/demo/initialize    # 集群初始化标记
docker exec -ti demo-etcd1 etcdctl get /service/demo/status        # 集群状态

#etcd健康状态
docker exec -ti demo-etcd1 etcdctl endpoint health --cluster
docker exec -ti demo-etcd1 etcdctl endpoint status --cluster -w table
+------------------------+------------------+---------+---------+-----------+-----------+------------+
|        ENDPOINT        |        ID        | VERSION | DB SIZE | IS LEADER | RAFT TERM | RAFT INDEX |
+------------------------+------------------+---------+---------+-----------+-----------+------------+
| http://172.25.0.7:2379 | 1bab629f01fa9065 |  3.3.13 |  221 kB |      true |         2 |       3563 |
| http://172.25.0.2:2379 | 8ecb6af518d241cc |  3.3.13 |  221 kB |     false |         2 |       3563 |
| http://172.25.0.5:2379 | b2e169fcb8a34028 |  3.3.13 |  221 kB |     false |         2 |       3563 |
+------------------------+------------------+---------+---------+-----------+-----------+------------+

PG/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
30
31
32
33
34
35
36
37
38
39
40
41
42
# 查看从状态
docker exec -ti demo-patroni1 psql -U postgres -c \
  "SELECT client_addr, state, sent_lsn, write_lsn, replay_lsn, sync_state FROM pg_stat_replication;"
 client_addr |   state   | sent_lsn  | write_lsn | replay_lsn | sync_state
-------------+-----------+-----------+-----------+------------+------------
 172.25.0.4  | streaming | 0/404FB68 | 0/404FB68 | 0/404FB68  | async
 172.25.0.3  | streaming | 0/404FB68 | 0/404FB68 | 0/404FB68  | async
(2 rows)

# 查看WAL配置
docker exec -ti demo-patroni1 psql -U postgres -c \
  "SELECT name, setting FROM pg_settings WHERE name IN ('wal_level','max_wal_senders','hot_standby','wal_keep_size');"
      name       | setting
-----------------+---------
 hot_standby     | on
 max_wal_senders | 10
 wal_keep_size   | 128
 wal_level       | replica
(4 rows)

docker exec -ti demo-patroni1 patronictl list
+ Cluster: demo (7670850893685108768) --------+----+-------------+-----+------------+-----+
| Member   | Host       | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+----------+------------+---------+-----------+----+-------------+-----+------------+-----+
| patroni1 | 172.25.0.6 | Leader  | running   |  1 |             |     |            |     |
| patroni2 | 172.25.0.4 | Replica | streaming |  1 |   0/404FB68 |   0 |  0/404FB68 |   0 |
| patroni3 | 172.25.0.3 | Replica | streaming |  1 |   0/404FB68 |   0 |  0/404FB68 |   0 |
+----------+------------+---------+-----------+----+-------------+-----+------------+-----+

docker exec -ti demo-patroni1 patronictl show-config
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    max_connections: 100
  pg_hba:
  - local all all trust
  - host replication replicator all md5
  - host all all all md5
  use_pg_rewind: true
retry_timeout: 10
ttl: 30

haproxy层

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
# 看HAProxy配置（理解它怎么判断谁是主）
docker exec -ti demo-haproxy cat /etc/haproxy/haproxy.cfg
global
        maxconn 100

defaults
        log     global
        mode    tcp
        retries 2
        timeout client 30m
        timeout connect 4s
        timeout server 30m
        timeout check 5s

listen stats
        mode http
        bind *:7000
        stats enable
        stats uri /

listen primary
        bind *:5000
        option httpchk HEAD /primary
        http-check expect status 200
        default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
        server patroni1 172.25.0.6:5432 maxconn 100 check port 8008
        server patroni2 172.25.0.4:5432 maxconn 100 check port 8008
        server patroni3 172.25.0.3:5432 maxconn 100 check port 8008

listen replicas
        bind *:5001
        option httpchk HEAD /replica
        http-check expect status 200
        default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
        server patroni1 172.25.0.6:5432 maxconn 100 check port 8008
        server patroni2 172.25.0.4:5432 maxconn 100 check port 8008
        server patroni3 172.25.0.3:5432 maxconn 100 check port 8008

HAProxy对每个后端发 GET /master：

patroni1（Leader）→ 200 → 加入写路由池 ✅
patroni2（Replica）→ 503 → 排除 ❌
patroni3（Replica）→ 503 → 排除 ❌

# REST API：HAProxy靠这些端点决定路由
docker exec -ti demo-patroni1 curl -s http://localhost:8008/master
docker exec -ti demo-patroni2 curl -s http://localhost:8008/replica
docker exec -ti demo-patroni2 curl -s http://localhost:8008/master    # 备库返回503

主备倒换测试

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
# 查看当前pg状态
docker exec -it demo-patroni1 patronictl list
+ Cluster: demo (7670850893685108768) --------+----+-------------+-----+------------+-----+
| Member   | Host       | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+----------+------------+---------+-----------+----+-------------+-----+------------+-----+
| patroni1 | 172.25.0.6 | Leader  | running   |  1 |             |     |            |     |
| patroni2 | 172.25.0.4 | Replica | streaming |  1 |   0/404FB68 |   0 |  0/404FB68 |   0 |
| patroni3 | 172.25.0.3 | Replica | streaming |  1 |   0/404FB68 |   0 |  0/404FB68 |   0 |
+----------+------------+---------+-----------+----+-------------+-----+------------+-----+

# 写入测试数据
docker exec demo-patroni1 psql -U postgres -c "CREATE TABLE test_failover(id int, msg text);"
CREATE TABLE
docker exec demo-patroni1 psql -U postgres -c "INSERT INTO test_failover VALUES (1, 'before_failover');"
INSERT 0 1

# kill当前的master
docker stop demo-patroni1

# 查看当前leader
docker exec demo-patroni2 patronictl list
+ Cluster: demo (7670850893685108768) --------+----+-------------+-----+------------+-----+
| Member   | Host       | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+----------+------------+---------+-----------+----+-------------+-----+------------+-----+
| patroni1 | 172.25.0.6 | Replica | stopped   |    |     unknown |     |    unknown |     |
| patroni2 | 172.25.0.4 | Leader  | running   |  2 |             |     |            |     |
| patroni3 | 172.25.0.3 | Replica | streaming |  2 |   0/4071430 |   0 |  0/4071430 |   0 |
+----------+------------+---------+-----------+----+-------------+-----+------------+-----+

# 进行数据验证
docker exec demo-patroni2 psql -U postgres -c "SELECT * FROM test_failover;"
 id |       msg
----+-----------------
  1 | before_failover
(1 row)

# 恢复3节点
docker start demo-patroni1
docker exec demo-patroni2 patronictl list
+ Cluster: demo (7670850893685108768) --------+----+-------------+-----+------------+-----+
| Member   | Host       | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+----------+------------+---------+-----------+----+-------------+-----+------------+-----+
| patroni1 | 172.25.0.6 | Replica | streaming |  2 |   0/4073680 |   0 |  0/4073680 |   0 |
| patroni2 | 172.25.0.4 | Leader  | running   |  2 |             |     |            |     |
| patroni3 | 172.25.0.3 | Replica | streaming |  2 |   0/4073680 |   0 |  0/4073680 |   0 |
+----------+------------+---------+-----------+----+-------------+-----+------------+-----+

可见patroni1重新变成了Replica

Switchover 手动切换测试

Switchover 用于计划性维护，比如：主库要打补丁、换磁盘、扩内存、升级 PG 版本

  • 可控：你指定谁当新主，而不是 Patroni 随机选
  • 零数据丢失：先确保新主数据同步完，再切换
  • 业务无感知：HAProxy 自动切路由，应用连接不用改
    简单说：Failover 是”被动救火”，Switchover 是”主动手术”。生产环境里 Switchover 用得比 Failover 多得多——因为大部分维护都是计划内的。
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
# 进行Switchover ，当前master是patroni2，手动指定patroni3为master
docker exec demo-patroni2 patronictl switchover --leader patroni2 --candidate patroni3 --force
Current cluster topology
+ Cluster: demo (7670850893685108768) --------+----+-------------+-----+------------+-----+
| Member   | Host       | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+----------+------------+---------+-----------+----+-------------+-----+------------+-----+
| patroni1 | 172.25.0.6 | Replica | streaming |  2 |   0/4073680 |   0 |  0/4073680 |   0 |
| patroni2 | 172.25.0.4 | Leader  | running   |  2 |             |     |            |     |
| patroni3 | 172.25.0.3 | Replica | streaming |  2 |   0/4073680 |   0 |  0/4073680 |   0 |
+----------+------------+---------+-----------+----+-------------+-----+------------+-----+
2026-08-07 08:25:46.14311 Successfully switched over to "patroni3"
+ Cluster: demo (7670850893685108768) ------+----+-------------+-----+------------+-----+
| Member   | Host       | Role    | State   | TL | Receive LSN | Lag | Replay LSN | Lag |
+----------+------------+---------+---------+----+-------------+-----+------------+-----+
| patroni1 | 172.25.0.6 | Replica | running |  2 |   0/40737C8 |   0 |  0/40737C8 |   0 |
| patroni2 | 172.25.0.4 | Replica | stopped |    |     unknown |     |    unknown |     |
| patroni3 | 172.25.0.3 | Leader  | running |  2 |             |     |            |     |
+----------+------------+---------+---------+----+-------------+-----+------------+-----+

# 可以看到目前leader变成3了，进行数据验证
docker exec demo-patroni3 psql -U postgres -c "SELECT * FROM test_failover;"
 id |       msg
----+-----------------
  1 | before_failover
(1 row)

使用Operator——Patroni在K8s中部署

方案 维护方 定位 成熟度
Patroni 原生支持 Patroni 官方 Patroni 自己就能用 K8s API 做 DCS ✅ 官方文档有完整说明
Spilo Zalando PG + Patroni 打包成一个镜像，支持 PVC ✅ 生产级
postgres-operator (Zalando) Zalando Operator 模式，CRD 管理 PG 集群 🔥 最成熟，业界标准
Helm chart 社区 Helm 部署 Spilo + Patroni ✅ 可用
之前提到的harbor通过helm部署的版本，也属于Patroni 原生支持的方案，在基础上添加了harbor自己的rbac等内容
Patroni在K8s中部署的本质，是使用k8s集群的etcd所管理的configmap来存储pg数据库的状态，代替了单独部署的etcd集群
Operator部署方式参考文档：
https://github.com/zalando/postgres-operator/blob/master/docs/quickstart.md
https://github.com/zalando/postgres-operator

Operator部署

1
2
3
4
5
6
7
8
9
10
11
12
13
14
部署方式
Manual deployment 手动资源清单
Kustomization
Helm chart

那肯定选择使用helm部署，毕竟我们是helm高手 （其实也所谓用哪种了，crd都是随便装的
# add repo for postgres-operator
helm repo add postgres-operator-charts https://opensource.zalando.com/postgres-operator/charts/postgres-operator
# install the postgres-operator
helm install postgres-operator postgres-operator-charts/postgres-operator
# add repo for postgres-operator-ui
helm repo add postgres-operator-ui-charts https://opensource.zalando.com/postgres-operator/charts/postgres-operator-ui
# install the postgres-operator-ui
helm install postgres-operator-ui postgres-operator-ui-charts/postgres-operator-ui

CRD资源创建

最小模板试水

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
kubectl apply -f - <<'EOF'
apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: acid-minimal-cluster
spec:
  teamId: "acid"
  volume:
    size: 1Gi
  numberOfInstances: 3
  users:
    zalando:
    - superuser
    - createdb
  databases:
    foo: zalando
  postgresql:
    version: "18"
EOF

kubectl get postgresql
NAME                   TEAM   VERSION   PODS   VOLUME   CPU-REQUEST   MEMORY-REQUEST   AGE   STATUS
acid-minimal-cluster   acid   18        3      1Gi                                     42s   Creating

kubectl get pods -l application=spilo -o wide
NAME                     READY   STATUS    RESTARTS   AGE     IP            NODE             NOMINATED NODE   READINESS GATES
acid-minimal-cluster-0   1/1     Running   0          15h     10.244.1.20   ws-k8s-worker    <none>           <none>
acid-minimal-cluster-1   1/1     Running   0          15h     10.244.2.17   ws-k8s-worker2   <none>           <none>
acid-minimal-cluster-2   1/1     Running   0          3m54s   10.244.1.22   ws-k8s-worker    <none>           <none>

# 查看主节点
kubectl get pod -l spilo-role=master
NAME                     READY   STATUS    RESTARTS   AGE
acid-minimal-cluster-0   1/1     Running   0          15h
kubectl get pod -l spilo-role=replica
NAME                     READY   STATUS    RESTARTS   AGE
acid-minimal-cluster-1   1/1     Running   0          15h
acid-minimal-cluster-2   1/1     Running   0          4m49s

kubectl exec acid-minimal-cluster-0 -- patronictl list
+ Cluster: acid-minimal-cluster (7671242909911756865) -------+----+-------------+-----+------------+-----+
| Member                 | Host        | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+------------------------+-------------+---------+-----------+----+-------------+-----+------------+-----+
| acid-minimal-cluster-0 | 10.244.1.20 | Leader  | running   |  1 |             |     |            |     |
| acid-minimal-cluster-1 | 10.244.2.17 | Replica | streaming |  1 |  0/22003D30 |   0 | 0/22003D30 |   0 |
| acid-minimal-cluster-2 | 10.244.1.22 | Replica | streaming |  1 |  0/22003D30 |   0 | 0/22003D30 |   0 |
+------------------------+-------------+---------+-----------+----+-------------+-----+------------+-----+

故障演练

1
2
3
4
5
6
7
8
9
10
11
12
13
# 手动删除pod0，现在漂移到了pod2上
kubectl get pod -l spilo-role=master
NAME                     READY   STATUS    RESTARTS   AGE
acid-minimal-cluster-2   1/1     Running   0          6m41s

kubectl exec acid-minimal-cluster-0 -- patronictl list
+ Cluster: acid-minimal-cluster (7671242909911756865) -------+----+-------------+-----+------------+-----+
| Member                 | Host        | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+------------------------+-------------+---------+-----------+----+-------------+-----+------------+-----+
| acid-minimal-cluster-0 | 10.244.1.23 | Replica | streaming |  2 |  0/2319DAA0 |   0 | 0/2319DAA0 |   0 |
| acid-minimal-cluster-1 | 10.244.2.17 | Replica | streaming |  2 |  0/2319DAA0 |   0 | 0/2319DAA0 |   0 |
| acid-minimal-cluster-2 | 10.244.1.22 | Leader  | running   |  2 |             |     |            |     |
+------------------------+-------------+---------+-----------+----+-------------+-----+------------+-----+

Switchover 手动切换

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
kubectl exec acid-minimal-cluster-2 -- patronictl switchover --leader acid-minimal-cluster-2 --candidate acid-minimal-cluster-0 --force
Current cluster topology
+ Cluster: acid-minimal-cluster (7671242909911756865) -------+----+-------------+-----+------------+-----+
| Member                 | Host        | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+------------------------+-------------+---------+-----------+----+-------------+-----+------------+-----+
| acid-minimal-cluster-0 | 10.244.1.23 | Replica | streaming |  2 |  0/231A12D0 |   0 | 0/231A12D0 |   0 |
| acid-minimal-cluster-1 | 10.244.2.17 | Replica | streaming |  2 |  0/231A12D0 |   0 | 0/231A12D0 |   0 |
| acid-minimal-cluster-2 | 10.244.1.22 | Leader  | running   |  2 |             |     |            |     |
+------------------------+-------------+---------+-----------+----+-------------+-----+------------+-----+
2026-08-08 02:22:34.03814 Successfully switched over to "acid-minimal-cluster-0"
+ Cluster: acid-minimal-cluster (7671242909911756865) -------+----+-------------+-----+------------+-----+
| Member                 | Host        | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+------------------------+-------------+---------+-----------+----+-------------+-----+------------+-----+
| acid-minimal-cluster-0 | 10.244.1.23 | Leader  | running   |  2 |             |     |            |     |
| acid-minimal-cluster-1 | 10.244.2.17 | Replica | streaming |  2 |  0/231A12D0 |   0 | 0/231A12D0 |   0 |
| acid-minimal-cluster-2 | 10.244.1.22 | Replica | stopped   |    |     unknown |     |    unknown |     |
+------------------------+-------------+---------+-----------+----+-------------+-----+------------+-----+

kubectl exec acid-minimal-cluster-0 -- patronictl list                      + Cluster: acid-minimal-cluster (7671242909911756865) -------+----+-------------+-----+------------+-----+
| Member                 | Host        | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+------------------------+-------------+---------+-----------+----+-------------+-----+------------+-----+
| acid-minimal-cluster-0 | 10.244.1.23 | Leader  | running   |  3 |             |     |            |     |
| acid-minimal-cluster-1 | 10.244.2.17 | Replica | streaming |  3 |  0/2418FF60 |   0 | 0/2418FF60 |   0 |
| acid-minimal-cluster-2 | 10.244.1.22 | Replica | streaming |  3 |  0/2418FF60 |   0 | 0/2418FF60 |   0 |
+------------------------+-------------+---------+-----------+----+-------------+-----+------------+-----+

可以看到master回到了pod 0

相关命令行参数

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
# patronictl命令行
list	集群成员状态（最常用）	patronictl list
show	单个成员详细状态	patronictl show patroni1
show-config	集群动态配置	patronictl show-config
history	failover/switchover历史记录	patronictl history
dsn	生成连接字符串	patronictl dsn patroni1
query	直接在节点上执行SQL	patronictl query patroni1 -c "SELECT 1"
version	Patroni版本	patronictl version
switchover	手动切换主库（计划内）	patronictl switchover
failover	强制切换（计划外）	patronictl failover

pg数据库常用命令行
# 连接登录（非交互式
PGPASSWORD=*** psql -h 127.0.0.1 -p 5432 -U postgres -d dbname
psql "postgresql://user:***@host:5432/dbname"

# .pgpass 文件
echo "127.0.0.1:5432:dbname:postgres:yourpass" > ~/.pgpass
chmod 600 ~/.pgpass
# 之后 psql 不需要输密码
psql -h 127.0.0.1 -U postgres -d dbname

# 逻辑备份/恢复
pg_dump -h HOST -U USER -d DB -f backup.sql          # 备份单库(SQL格式)
pg_dump -h HOST -U USER -d DB -Fc -f backup.dump      # 自定义格式（推荐，支持并行恢复）
pg_dumpall -h HOST -U USER                            # 备份所有库
psql -h HOST -U USER -d DB -f backup.sql              # 恢复SQL格式
pg_restore -h HOST -U USER -d DB -j 4 backup.dump     # 恢复dump格式（-j 并行）
# 常用查询
\l                             # 列出所有数据库
\dt                            # 列出当前库所有表
\d+ tablename                  # 查看表结构
\du                            # 列出所有用户
SELECT * FROM pg_stat_replication;  # 查看复制状态
SELECT pg_size_pretty(pg_database_size(dbname));  # 查库大小

原文作者：王盛

原文链接：https://akemi.zj.cn/2026/08/08/PG-Patroni-K8s-Deployment/

发表日期：August 8th 2026, 10:37:00 am

更新日期：August 8th 2026, 10:38:27 am

版权声明：本文采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可

📚 相关文章推荐

暂无相关文章

CATALOG
  1. 1. PG 部署形态谱（从简到复杂）
  2. 2. PG单机部署
    1. 2.1. 二进制部署
    2. 2.2. PG配置文件
    3. 2.3. 使用docker部署
  3. 3. PG的使用与连接测试
  4. 4. PG备份-物理备份/逻辑备份
  5. 5. PG自动故障转移集群patroni+etcd+haproxy
    1. 5.1. 命令行与状态
    2. 5.2. 主备倒换测试
    3. 5.3. Switchover 手动切换测试
  6. 6. 使用Operator——Patroni在K8s中部署
    1. 6.1. Operator部署
    2. 6.2. CRD资源创建
    3. 6.3. 故障演练
    4. 6.4. Switchover 手动切换
  7. 7. 相关命令行参数
Total : 353
2026
2025
2024
2023
自动化 web服务 k8s Docker Kubernetes DaemonSet Linux服务 前端 存储 Consul VictoriaMetrics Promxy 虚拟化 网络 Python 容器 Dify Prometheus 数据库 PVE EVE-NG EFK Elasticsearch ELK logstash filebeat elasticsearch kibana Kibana GeoIP 数据清洗 Fluentd Fluent Bit etcd 运维 安全 纳甲六爻 易经 六爻 六神 AI 占卜 GPU虚拟化 HAMi Milvus Helm LangChain DNS CoreDNS 数据备份 MoneyPrinter 容器技术 实战 PostgreSQL Patroni K8s 监控 Rsyslog 审计日志 Blackbox Exporter Meilisearch 博客优化 iflow OpenClaw iFlow 故障处理 容器运行时 测试