管理

• 1: 参数优化
• 2: 维护保养
• 3: 故障排查
• 4: 误删处理

bin/pgsql-add   <cls>                   # 创建 PostgreSQL 集群 <cls>
bin/pgsql-user  <cls> <username>        # 在集群 <cls> 上创建用户 <username>
bin/pgsql-db    <cls> <dbname>          # 在集群 <cls> 上创建数据库 <dbname>
bin/pgsql-svc   <cls> [...ip]           # 重载集群 <cls> 的 PostgreSQL 服务
bin/pgsql-hba   <cls> [...ip]           # 重载集群 <cls> 的 postgres/pgbouncer HBA 规则
bin/pgsql-add   <cls> [...ip]           # 为集群 <cls> 添加从库
bin/pgsql-rm    <cls> [...ip]           # 从集群 <cls> 移除从库
bin/pgsql-rm    <cls>                   # 移除 PostgreSQL 集群 <cls>

pg list        <cls>                    # 打印集群信息
pg edit-config <cls>                    # 编辑集群配置
pg reload      <cls> [ins]              # 重载集群配置
pg restart     <cls> [ins]              # 重启 PostgreSQL 集群
pg reinit      <cls> [ins]              # 重新初始化集群成员
pg pause       <cls>                    # 进入维护模式（无自动故障转移）
pg resume      <cls>                    # 退出维护模式
pg switchover  <cls>                    # 在集群 <cls> 上执行主从切换
pg failover    <cls>                    # 在集群 <cls> 上执行故障转移

pb info                                 # 打印 pgbackrest 仓库信息
pg-backup                               # 进行备份，增量备份，或在必要时进行完整备份
pg-backup full                          # 进行完整备份
pg-backup diff                          # 进行差异备份
pg-backup incr                          # 进行增量备份
pg-pitr -i                              # 恢复到最新备份完成的时间（不常用）
pg-pitr --time="2022-12-30 14:44:44+08" # 恢复到特定时间点（在删除数据库、删除表的情况下）
pg-pitr --name="my-restore-point"       # 恢复到由 pg_create_restore_point 创建的命名恢复点
pg-pitr --lsn="0/7C82CB8" -X            # 恢复到 LSN 之前
pg-pitr --xid="1234567" -X -P           # 恢复到特定事务 ID 之前，然后提升
pg-pitr --backup=latest                 # 恢复到最新备份集
pg-pitr --backup=20221108-105325        # 恢复到特定备份集，可通过 pgbackrest info 检查

systemctl stop patroni                  # start stop restart reload
systemctl stop pgbouncer                # start stop restart reload
systemctl stop pg_exporter              # start stop restart reload
systemctl stop pgbouncer_exporter       # start stop restart reload
systemctl stop node_exporter            # start stop restart
systemctl stop haproxy                  # start stop restart reload
systemctl stop vip-manager              # start stop restart reload
systemctl stop postgres                 # 仅当 patroni_mode == 'remove' 时

bin/node-add <cls>                # 为集群 <cls> 初始化节点           # ./node.yml  -l <cls>
bin/pgsql-add <cls>               # 初始化集群 <cls> 的 PostgreSQL 实例  # ./pgsql.yml -l <cls>

bin/pgsql-user <cls> <username>   # ./pgsql-user.yml -l <cls> -e username=<username>

bin/pgsql-db <cls> <dbname>       # ./pgsql-db.yml -l <cls> -e dbname=<dbname>

bin/pgsql-svc <cls>               # pgsql.yml -l <cls> -t pg_service -e pg_reload=true
bin/pgsql-svc <cls> [ip...]       # pgsql.yml -l ip... -t pg_service -e pg_reload=true

bin/pgsql-hba <cls>               # pgsql.yml -l <cls> -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true
bin/pgsql-hba <cls> [ip...]       # pgsql.yml -l ip... -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true

pg edit-config <cls>              # 使用 patronictl 交互式配置集群

bin/node-add <ip>                 # 为新从库初始化节点 <ip>
bin/pgsql-add <cls> <ip>          # 在 <ip> 上为集群 <cls> 初始化 PostgreSQL 实例

bin/pgsql-rm <cls> <ip...>        # ./pgsql-rm.yml -l <ip>

bin/pgsql-rm <cls>                # ./pgsql-rm.yml -l <cls>

pg switchover <cls>   # 交互模式，您可以使用以下选项跳过
pg switchover --leader pg-test-1 --candidate=pg-test-2 --scheduled=now --force pg-test

pg-backup                         # 进行 PostgreSQL 基础备份
pg-backup full                    # 进行完整备份
pg-backup diff                    # 进行差异备份
pg-backup incr                    # 进行增量备份
pb info                           # 检查备份信息

pg-pitr -i                              # 恢复到最新备份完成的时间（不常用）
pg-pitr --time="2022-12-30 14:44:44+08" # 恢复到特定时间点（在删除数据库、删除表的情况下）
pg-pitr --name="my-restore-point"       # 恢复到由 pg_create_restore_point 创建的命名恢复点
pg-pitr --lsn="0/7C82CB8" -X            # 恢复到 LSN 之前
pg-pitr --xid="1234567" -X -P           # 恢复到特定事务 ID 之前，然后提升
pg-pitr --backup=latest                 # 恢复到最新备份集
pg-pitr --backup=20221108-105325        # 恢复到特定备份集，可通过 pgbackrest info 检查

ansible pg-test -b -m package -a "name=pg_cron_15,topn_15,pg_stat_monitor_15*"  # 安装一些软件包

./pgsql.yml -t pg_ext     # 安装扩展

ansible <cls> -b -a "yum upgrade/downgrade -y <pkg>"    # 升级/降级软件包
pg restart --force <cls>                                # 重启集群

./pgsql.yml -t pg_pkg -e pg_version=15                         # 为 PostgreSQL 15 安装软件包
sudo su - postgres; mkdir -p /data/postgres/pg-meta-15/data/   # 为 15 准备目录
pg_upgrade -b /usr/pgsql-14/bin/ -B /usr/pgsql-15/bin/ -d /data/postgres/pg-meta-14/data/ -D /data/postgres/pg-meta-15/data/ -v -c # 预检
pg_upgrade -b /usr/pgsql-14/bin/ -B /usr/pgsql-15/bin/ -d /data/postgres/pg-meta-14/data/ -D /data/postgres/pg-meta-15/data/ --link -j8 -v -c
rm -rf /usr/pgsql; ln -s /usr/pgsql-15 /usr/pgsql;             # 修复二进制链接
mv /data/postgres/pg-meta-14 /data/postgres/pg-meta-15         # 重命名数据目录
rm -rf /pg; ln -s /data/postgres/pg-meta-15 /pg                # 修复数据目录链接

1 - 参数优化

Pigsty 默认提供了四套场景化参数模板，可以通过 pg_conf 参数指定并使用。

• tiny.yml：为小节点、虚拟机、小型演示优化（1-8核，1-16GB）
• oltp.yml：为OLTP工作负载和延迟敏感应用优化（4C8GB+）（默认模板）
• olap.yml：为OLAP工作负载和吞吐量优化（4C8G+）
• crit.yml：为数据一致性和关键应用优化（4C8G+）

Pigsty 会针对这四种默认场景，采取不同的参数优化策略，如下所示：

内存参数调整

Pigsty 默认会检测系统的内存大小，并以此为依据设定最大连接数量与内存相关参数。

• pg_max_conn：postgres 最大连接数，auto 将使用不同场景下的推荐值
• pg_shared_buffer_ratio：内存共享缓冲区比例，默认为 0.25

默认情况下，Pigsty 使用 25% 的内存作为 PostgreSQL 共享缓冲区，剩余的 75% 作为操作系统缓存。

默认情况下，如果用户没有设置一个 pg_max_conn 最大连接数，Pigsty 会根据以下规则使用默认值：

• oltp: 500 (pgbouncer) / 1000 (postgres)
• crit: 500 (pgbouncer) / 1000 (postgres)
• tiny: 300
• olap: 300

其中对于 OLTP 与 CRIT 模版来说，如果服务没有指向 pgbouncer 连接池，而是直接连接 postgres 数据库，最大连接会翻倍至 1000 条。

决定最大连接数后，work_mem 会根据共享内存数量 / 最大连接数计算得到，并限定在 64MB ~ 1GB 的范围内。

{% if pg_max_conn != 'auto' and pg_max_conn|int >= 20 %}{% set pg_max_connections = pg_max_conn|int %}{% else %}{% if pg_default_service_dest|default('postgres') == 'pgbouncer' %}{% set pg_max_connections = 500 %}{% else %}{% set pg_max_connections = 1000 %}{% endif %}{% endif %}
{% set pg_max_prepared_transactions = pg_max_connections if 'citus' in pg_libs else 0 %}
{% set pg_max_locks_per_transaction = (2 * pg_max_connections)|int if 'citus' in pg_libs or 'timescaledb' in pg_libs else pg_max_connections %}
{% set pg_shared_buffers = (node_mem_mb|int * pg_shared_buffer_ratio|float) | round(0, 'ceil') | int %}
{% set pg_maintenance_mem = (pg_shared_buffers|int * 0.25)|round(0, 'ceil')|int %}
{% set pg_effective_cache_size = node_mem_mb|int - pg_shared_buffers|int  %}
{% set pg_workmem =  ([ ([ (pg_shared_buffers / pg_max_connections)|round(0,'floor')|int , 64 ])|max|int , 1024])|min|int %}

CPU参数调整

在 PostgreSQL 中，有 4 个与并行查询相关的重要参数，Pigsty 会自动根据当前系统的 CPU 核数进行参数优化。 在所有策略中，总并行进程数量（总预算）通常设置为 CPU 核数 + 8，且保底为 16 个，从而为逻辑复制与扩展预留足够的后台 worker 数量，OLAP 和 TINY 模板根据场景略有不同。

请注意，CRIT 和 TINY 模板直接通过设置 max_parallel_workers_per_gather = 0 关闭了并行查询。 用户可以按需在需要时设置此参数以启用并行查询。

OLTP 和 CRIT 模板都额外设置了以下参数，将并行查询的 Cost x 2，以降低使用并行查询的倾向。

parallel_setup_cost: 2000           # double from 100 to increase parallel cost
parallel_tuple_cost: 0.2            # double from 0.1 to increase parallel cost
min_parallel_table_scan_size: 16MB  # double from 8MB to increase parallel cost
min_parallel_index_scan_size: 1024  # double from 512 to increase parallel cost

请注意 max_worker_processes 参数的调整必须在重启后才能生效。此外，当从库的本参数配置值高于主库时，从库将无法启动。 此参数必须通过 patroni 配置管理进行调整，该参数由 Patroni 管理，用于确保主从配置一致，避免在故障切换时新从库无法启动。

存储空间参数

Pigsty 默认检测 /data/postgres 主数据目录所在磁盘的总空间，并以此作为依据指定下列参数：

min_wal_size: {{ ([pg_size_twentieth, 200])|min }}GB                  # 1/20 disk size, max 200GB
max_wal_size: {{ ([pg_size_twentieth * 4, 2000])|min }}GB             # 2/10 disk size, max 2000GB
max_slot_wal_keep_size: {{ ([pg_size_twentieth * 6, 3000])|min }}GB   # 3/10 disk size, max 3000GB
temp_file_limit: {{ ([pg_size_twentieth, 200])|min }}GB               # 1/20 of disk size, max 200GB

• temp_file_limit 默认为磁盘空间的 5%，封顶不超过 200GB。
• min_wal_size 默认为磁盘空间的 5%，封顶不超过 200GB。
• max_wal_size 默认为磁盘空间的 20%，封顶不超过 2TB。
• max_slot_wal_keep_size 默认为磁盘空间的 30%，封顶不超过 3TB。

作为特例， OLAP 模板允许 20% 的 temp_file_limit ，封顶不超过 2TB

2 - 维护保养

要确保 Pigsty 与 PostgreSQL 集群健康稳定运行，需要进行一些例行维护保养工作。

定期查阅监控

Pigsty 提供了开箱即用的监控平台，我们建议您每天浏览一次监控大盘，关注系统状态。 极端情况下，我们建议您每周至少查阅一次监控，关注出现的告警事件，这样可以提前规避绝大多数故障与问题。

这里列举了 Pigsty 中预先定义的 告警规则 列表。

故障切换善后

Pigsty 的高可用架构允许 PostgreSQL 集群自动进行主从切换，这意味着运维与 DBA 无需即时介入与响应。 然而用户仍然需要在合适的时机（例如第二天工作日）进行以下善后工作，包括：

• 调查并确认故障出现的原因，避免再次出现
• 视情况恢复集群原本的主从拓扑，或者修改配置清单以匹配新的主从状态。
• 通过 bin/pgsql-svc 刷新负载均衡器配置，更新服务的路由状态
• 通过 bin/pgsql-hba 刷新集群的 HBA 规则，避免主从特定的规则漂移
• 如果有必要，使用 bin/pgsql-rm 移除故障服务器，并通过 bin/pgsql-add 扩容一台新从库

表膨胀治理

长时间运行的 PostgreSQL 会出现 “表膨胀” / “索引膨胀” 现象， 导致系统性能劣化。

定期使用 pg_repack 对表与索引进行在线重建，有助于维护 PostgreSQL 的良好性能表现。 Pigsty 已经默认在所有数据库中安装并启用了此扩展，因此您可以直接使用。

您可以通过 Pigsty 的 PGCAT Database - Table Bloat 面板， 确认数据库中的表膨胀情况与索引膨胀情况。并选择膨胀率较高（膨胀率高于 50% 的较大表）的表与索引，使用 pg_repack 进行在线重整：

pg_repack dbname -t schema.table

重整期间不会影响正常读写，但重整完毕之后的 切换瞬间 需要获取表上的 AccessExclusive 锁阻塞一切访问。 因此对于高吞吐量业务，建议在业务低峰期或者维护窗口进行。更多细节，请参考：关系膨胀的治理

VACUUM FREEZE

冻结过期事务ID（VACUUM FREEZE）是PostgreSQL重要的维护任务，用于防止事务ID (XID) 用尽导致停机。 尽管 PostgreSQL 已经提供了自动垃圾回收（AutoVacuum）机制，然而对于高标准的生产环境， 我们依然建议结合自动和手动两种方式，定期执行全库级别的 VACUUM FREEZE ，以确保 XID 安全。

3 - 故障排查

本文档列举了 PostgreSQL 和 Pigsty 中可能出现的故障，以及定位，处理，分析问题的 SOP。

磁盘空间写满

磁盘空间写满是最常见的故障类型。

现象

当数据库所在磁盘空间耗尽时，PostgreSQL 将无法正常工作，可能出现以下现象：数据库日志反复报错“no space left on device”（磁盘空间不足）， 新数据无法写入，甚至 PostgreSQL 可能触发 PANIC 强制关闭。

Pigsty 带有 NodeFsSpaceFull 告警规则，当文件系统可用空间不足 10% 时触发告警。 使用监控系统 NODE Instance 面板查阅 FS 指标面板定位问题。

诊断

您也可以登录数据库节点，使用 df -h 查看各挂载盘符使用率，确定哪个分区被写满。 对于数据库节点，重点检查以下目录及其大小，以判断是哪个类别的文件占满了空间：

• 数据目录（/pg/data/base）：存放表和索引的数据文件，大量写入与临时文件需要关注
• WAL目录（如 pg/data/pg_wal）：存放 PG WAL，WAL 堆积/复制槽保留是常见的磁盘写满原因。
• 数据库日志目录（如 pg/log）：如果 PG 日志未及时轮转写大量报错写入，也可能占用大量空间。
• 本地备份目录（如 data/backups）：使用 pgBackRest 等在本机保存备份时，也有可能撑满磁盘。

如果问题出在 Pigsty 管理节点或监控节点，还需考虑：

• 监控数据：Prometheus 的时序指标和 Loki 日志存储都会占用磁盘，可检查保留策略。
• 对象存储数据：Pigsty 集成的 MinIO 对象存储可能会被用于 PG 备份保存。

明确占用空间最大的目录后，可进一步使用 du -sh <目录> 深入查找特定大型文件或子目录。

处理

磁盘写满属于紧急问题，需立即采取措施释放空间并保证数据库继续运行。 当数据盘并未与系统盘区分时，写满磁盘可能导致 Shell 命令无法执行。这种情况下，可以删除 /pg/dummy 占位文件，释放少量应急空间以便 shell 命令恢复正常。 如果数据库由于 pg_wal 写满已经宕机，清理空间后需要重启数据库服务并仔细检查数据完整性。

事务号回卷

PostgreSQL 循环使用 32 位事务ID (XID)，耗尽时会出现“事务号回卷”故障（XID Wraparound）。

现象

第一阶段的典型征兆是 PGSQL Persist - Age Usage 面板年龄饱和度进入警告区域。 数据库日志开始出现：WARNING: database "postgres" must be vacuumed within xxxxxxxx transactions 字样的信息。

若问题持续恶化，PostgreSQL 会进入保护模式：当剩余事务ID不到约100万时数据库切换为只读模式；达到上限约21亿（2^31）时则拒绝任何新事务并迫使服务器停机以避免数据错误。

诊断

PostgreSQL 与 Pigsty 默认启用自动垃圾回收（AutoVacuum），因此此类故障出现通常有更深层次的根因。 常见的原因包括：超长事务（SAGE），Autovacuum 配置失当，复制槽阻塞，资源不足，存储引擎/扩展BUG，磁盘坏快。

首先定位年龄最大的数据库，然后可通过 Pigsty PGCAT Database - Tables 面板来确认表的年龄分布。 同时查阅数据库错误日志，通常可以找到定位根因的线索。

处理

1. 立即冻结老事务：如果数据库尚未进入只读保护状态，立刻对受影响的库执行一次手动 VACUUM FREEZE。可以从老化最严重的表开始逐个冻结，而不是整库一起做，以加快效果。使用超级用户连接数据库，针对识别出的 relfrozenxid 最大的表运行 VACUUM FREEZE 表名;，优先冻结那些XID年龄最大的表元组。这样可以迅速回收大量事务ID空间。
2. 单用户模式救援：如果数据库已经拒绝写入或宕机保护，此时需要启动数据库到单用户模式执行冻结操作。在单用户模式下运行 VACUUM FREEZE database_name; 对整个数据库进行冻结清理。完成后再以多用户模式重启数据库。这样做可以解除回卷锁定，让数据库重新可写。需要注意在单用户模式下操作要非常谨慎，并确保有足够的事务ID余量完成冻结。
3. 备用节点接管：在某些复杂场景（例如遭遇硬件问题导致 vacuum 无法完成），可考虑提升集群中的只读备节点为主，以获取一个相对干净的环境来处理冻结。例如主库因坏块导致无法 vacuum，此时可以手动Failover提升备库为新的主库，再对其进行紧急 vacuum freeze。确保新主库已冻结老事务后，再将负载切回来。

连接耗尽

PostgreSQL 有一个最大连接数配置 (max_connections)，当客户端连接数超过此上限时，新的连接请求将被拒绝。典型现象是在应用端看到数据库无法连接，并报出类似 FATAL: remaining connection slots are reserved for non-replication superuser connections 或 too many clients already 的错误。 这表示普通连接数已用完，仅剩下保留给超管或复制的槽位

诊断

连接耗尽通常由客户端大量并发请求引起。您可以通过 PGCAT Instance / PGCAT Database / PGCAT Locks 直接查阅数据库当前的活跃会话。 并判断是什么样的查询填满了系统，并进行进一步的处理。特别需要关注是否存在大量 Idle in Transaction 状态的连接以及长时间运行的事务（以及慢查询）。

处理

杀查询：对于已经耗尽导致业务受阻的情况，通常立即使用 pg_terminate_backend(pid) 进行紧急降压。 对于使用连接池的情况，则可以调整连接池大小参数，并执行 reload 重载的方式减少数据库层面的连接数量。

您也可以修改 max_connections 参数为更大的值，但本参数需要重启数据库后才能生效。

etcd 配额写满

etcd 配额写满将导致 PG 高可用控制面失效，无法进行配置变更。

诊断

Pigsty 在实现高可用时使用 etcd 作为分布式配置存储(DCS)，etcd 自身有一个存储配额（默认约为2GB）。 当 etcd 存储用量达到配额上限时，etcd 将拒绝写入操作，报错 “etcdserver: mvcc: database space exceeded”。在这种情况下，Patroni 无法向 etcd 写入心跳或更新配置，从而导致集群管理功能失效。

解决

在 Pigsty v2.0.0 - v2.5.1 之间的版本默认受此问题影响。Pigsty v2.6.0 为部署的 etcd 新增了自动压实的配置项，如果您仅将其用于 PG 高可用租约，则常规用例下不会再有此问题。

有缺陷的存储引擎

目前，TimescaleDB 的试验性存储引擎 Hypercore 被证实存在缺陷，已经出现 VACUUM 无法回收出现 XID 回卷故障的案例。 请使用该功能的用户及时迁移至 PostgreSQL 原生表或者 TimescaleDB 默认引擎

详细介绍：《PG新存储引擎故障案例》

4 - 误删处理

误删数据

如果是小批量 DELETE 误操作，可以考虑使用 pg_surgery 或者 pg_dirtyread 扩展进行原地手术恢复。

-- 立即关闭此表上的 Auto Vacuum 并中止 Auto Vacuum 本表的 worker 进程
ALTER TABLE public.some_table SET (autovacuum_enabled = off, toast.autovacuum_enabled = off);

CREATE EXTENSION pg_dirtyread;
SELECT * FROM pg_dirtyread('tablename') AS t(col1 type1, col2 type2, ...);

如果被删除的数据已经被 VACUUM 回收，那么使用通用的误删处理流程。

误删对象

当出现 DROP/DELETE 类误操作，通常按照以下流程决定恢复方案。

1. 确认此数据是否可以通过业务系统或其他数据系统找回，如果可以，直接从业务侧修复。
2. 确认是否有延迟从库，如果有，推进延迟从库至误删时间点，查询出来恢复。
3. 如果数据已经确认删除，确认备份信息，恢复范围是否覆盖误删时间点，如果覆盖，开始 PITR
4. 确认是整集群原地 PITR 回滚，还是新开服务器重放，还是用从库来重放，并执行恢复策略

误删集群

如果出现整个数据库集群通过 Pigsty 管理命令被误删的情况，例如错误的执行 pgsql-rm.yml 剧本或 bin/pgsql-rm 命令。 除非您指定了 pg_rm_backup 参数为 false，否则备份会与数据库集群一起被删除。

建议：对于生产环境，您可以在配置清单中全局配置此参数为 false，在移除集群时保留备份。