如何使用 Pigsty 维护现有的 PostgreSQL 集群？
本章节提供 PostgreSQL 常见管理任务的标准操作流程（SOP）：
管理预案
数据库管理任务标准操作指南（SOP）
1 - 常用预案
Pigsty 中常用的 PostgreSQL 管理预案，用于维护生产环境中的数据库集群。
本文整理了 Pigsty 中常用的 PostgreSQL 管理预案，用于维护生产环境中的数据库集群。
以下是常见 PostgreSQL 管理任务的标准操作程序：
- 案例1： 创建集群
- 案例2： 创建用户
- 案例3： 创建数据库
- 案例4： 重载服务
- 案例5： 重载HBA
- 案例6： 配置集群
- 案例7： 添加实例
- 案例8： 移除实例
- 案例9： 下线集群
- 案例10： 主动切换
- 案例11： 备份集群
- 案例12： 恢复集群
- 案例13： 添加软件
- 案例14： 安装扩展
- 案例15： 小版本升级
- 案例16： 大版本升级
命令速查
PGSQL 剧本与快捷方式：
bin/pgsql-add <cls> # 创建 pgsql 集群 <cls>
bin/pgsql-user <cls> <username> # 在 <cls> 上创建 pg 用户 <username>
bin/pgsql-db <cls> <dbname> # 在 <cls> 上创建 pg 数据库 <dbname>
bin/pgsql-svc <cls> [...ip] # 重新加载集群 <cls> 的 pg 服务
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> # 删除 pgsql 集群 <cls>
Patroni 管理命令与快捷方式：
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> 上进行故障转移（主库故障）
pgBackRest 备份/恢复命令与快捷方式：
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 进行检查
使用 Systemd 管理系统组件的命令：
systemctl stop patroni # 启动 停止 重启 重载
systemctl stop pgbouncer # 启动 停止 重启 重载
systemctl stop pg_exporter # 启动 停止 重启 重载
systemctl stop pgbouncer_exporter # 启动 停止 重启 重载
systemctl stop node_exporter # 启动 停止 重启
systemctl stop haproxy # 启动 停止 重启 重载
systemctl stop vip-manager # 启动 停止 重启 重载
systemctl stop postgres # 仅当 patroni_mode == 'remove' 时使用这个服务
创建集群
要创建一个新的Postgres集群，请首先在配置清单中定义，然后进行初始化：
bin/node-add <cls> # 为集群 <cls> 初始化节点 # ./node.yml -l <cls>
bin/pgsql-add <cls> # 初始化集群 <cls> 的pgsql实例 # ./pgsql.yml -l <cls>
请注意，PGSQL 模块需要在 Pigsty 纳管的节点上安装，请先使用
bin/node-add纳管节点。
示例：创建集群
创建用户
要在现有的Postgres集群上创建一个新的业务用户，请将用户定义添加到 all.children.<cls>.pg_users，然后使用以下命令将其创建：
bin/pgsql-user <cls> <username> # ./pgsql-user.yml -l <cls> -e username=<username>
示例：创建业务用户
创建数据库
要在现有的Postgres集群上创建一个新的数据库用户，请将数据库定义添加到 all.children.<cls>.pg_databases，然后按照以下方式创建数据库：
bin/pgsql-db <cls> <dbname> # ./pgsql-db.yml -l <cls> -e dbname=<dbname>
注意：如果数据库指定了一个非默认的属主，该属主用户应当已存在，否则您必须先创建用户。
示例：创建业务数据库
重载服务
服务是 PostgreSQL 对外提供能力的访问点（PGURL可达），由主机节点上的 HAProxy 对外暴露。
当集群成员发生变化时使用此任务，例如：添加／移除副本，主从切换／故障转移 / 暴露新服务，或更新现有服务的配置（例如，LB权重）
要在整个代理集群，或特定实例上创建新服务或重新加载现有服务：
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
示例：重载PG服务以踢除一个实例
重载HBA
当您的 Postgres/Pgbouncer HBA 规则发生更改时，您 可能 需要重载 HBA 以应用更改。
如果您有任何特定于角色的 HBA 规则，或者在IP地址段中引用了集群成员的别名，那么当主从切换/集群扩缩容后也可能需要重载HBA。
要在整个集群或特定实例上重新加载 postgres 和 pgbouncer 的 HBA 规则：
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
示例：重载集群 HBA 规则
配置集群
要更改现有的 Postgres 集群配置，您需要在管理节点上使用管理员用户（安装Pigsty的用户，nopass ssh/sudo）发起控制命令：
另一种方式是在数据库集群中的任何节点上，使用 dbsu （默认为 postgres） ，也可以执行管理命令，但只能管理本集群。
pg edit-config <cls> # interactive config a cluster with patronictl
更改 patroni 参数和 postgresql.parameters，根据提示保存并应用更改即可。
示例：非交互式方式配置集群
您可以跳过交互模式，并使用 -p 选项覆盖 postgres 参数，例如：
pg edit-config -p log_min_duration_statement=1000 pg-test
pg edit-config --force -p shared_preload_libraries='timescaledb, pg_cron, pg_stat_statements, auto_explain'
示例：使用 Patroni REST API 更改集群配置
您还可以使用 Patroni REST API 以非交互式方式更改配置，例如：
$ curl -s 10.10.10.11:8008/config | jq . # get current config
$ curl -u 'postgres:Patroni.API' \
-d '{"postgresql":{"parameters": {"log_min_duration_statement":200}}}' \
-s -X PATCH http://10.10.10.11:8008/config | jq .
注意：Patroni 敏感API（例如重启等） 访问仅限于从基础设施/管理节点发起，并且有 HTTP 基本认证（用户名/密码）以及可选的 HTTPS 保护。
示例：使用 patronictl 配置集群
添加实例
若要将新从库添加到现有的 PostgreSQL 集群中，您需要将其定义添加到配置清单：all.children.<cls>.hosts 中，然后：
bin/node-add <ip> # 将节点 <ip> 纳入 Pigsty 管理
bin/pgsql-add <cls> <ip> # 初始化 <ip> ，作为集群 <cls> 的新从库
这将会把节点 <ip> 添加到 pigsty 并将其初始化为集群 <cls> 的一个副本。
集群服务将会重新加载以接纳新成员。
示例：为 pg-test 添加从库
例如，如果您想将 pg-test-3 / 10.10.10.13 添加到现有的集群 pg-test，您首先需要更新配置清单：
pg-test:
hosts:
10.10.10.11: { pg_seq: 1, pg_role: primary } # 已存在的成员
10.10.10.12: { pg_seq: 2, pg_role: replica } # 已存在的成员
10.10.10.13: { pg_seq: 3, pg_role: replica } # <--- 新成员
vars: { pg_cluster: pg-test }
然后按如下方式应用更改：
bin/node-add 10.10.10.13 # 将节点添加到 pigsty
bin/pgsql-add pg-test 10.10.10.13 # 在 10.10.10.13 上为集群 pg-test 初始化新的副本
这与集群初始化相似，但只在单个实例上工作：
[ OK ] 初始化实例 10.10.10.11 到 pgsql 集群 'pg-test' 中:
[WARN] 提醒：先将节点添加到 pigsty 中，然后再安装模块 'pgsql'
[HINT] $ bin/node-add 10.10.10.11 # 除 infra 节点外，先运行此命令
[WARN] 从集群初始化实例：
[ OK ] $ ./pgsql.yml -l '10.10.10.11,&pg-test'
[WARN] 重新加载现有实例上的 pg_service：
[ OK ] $ ./pgsql.yml -l 'pg-test,!10.10.10.11' -t pg_service
移除实例
若要从现有的 PostgreSQL 集群中移除副本：
bin/pgsql-rm <cls> <ip...> # ./pgsql-rm.yml -l <ip>
这将从集群 <cls> 中移除实例 <ip>。 集群服务将会重新加载以从负载均衡器中踢除已移除的实例。
示例：从 pg-test 移除从库
例如，如果您想从现有的集群 pg-test 中移除 pg-test-3 / 10.10.10.13：
bin/pgsql-rm pg-test 10.10.10.13 # 从 pg-test 中移除 pgsql 实例 10.10.10.13
bin/node-rm 10.10.10.13 # 从 pigsty 中移除该节点（可选）
vi pigsty.yml # 从目录中移除实例定义
bin/pgsql-svc pg-test # 刷新现有实例上的 pg_service，以从负载均衡器中踢除已移除的实例
[ OK ] 从 'pg-test' 移除 10.10.10.13 的 pgsql 实例：
[WARN] 从集群中移除实例：
[ OK ] $ ./pgsql-rm.yml -l '10.10.10.13,&pg-test'
并从配置清单中移除实例定义：
pg-test:
hosts:
10.10.10.11: { pg_seq: 1, pg_role: primary }
10.10.10.12: { pg_seq: 2, pg_role: replica }
10.10.10.13: { pg_seq: 3, pg_role: replica } # <--- 执行后移除此行
vars: { pg_cluster: pg-test }
最后，您可以重载PG服务并从负载均衡器中踢除已移除的实例：
bin/pgsql-svc pg-test # 重载 pg-test 上的服务
下线集群
要移除整个 Postgres 集群，只需运行：
bin/pgsql-rm <cls> # ./pgsql-rm.yml -l <cls>
示例：移除集群
示例：强制移除集群
注意：如果为这个集群配置了pg_safeguard（或全局设置为 true），pgsql-rm.yml 将中止，以避免意外移除集群。
您可以使用 playbook 命令行参数明确地覆盖它，以强制执行清除：
./pgsql-rm.yml -l pg-meta -e pg_safeguard=false # 强制移除 pg 集群 pg-meta
主动切换
您可以使用 patroni 命令行工具执行 PostgreSQL 集群的切换操作。
pg switchover <cls> # 交互模式，您可以使用下面的参数组合直接跳过此交互向导
pg switchover --leader pg-test-1 --candidate=pg-test-2 --scheduled=now --force pg-test
示例：pg-test 主从切换
$ pg switchover pg-test
Master [pg-test-1]:
Candidate ['pg-test-2', 'pg-test-3'] []: pg-test-2
When should the switchover take place (e.g. 2022-12-26T07:39 ) [now]: now
Current cluster topology
+ Cluster: pg-test (7181325041648035869) -----+----+-----------+-----------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-1 | 10.10.10.11 | Leader | running | 1 | | clonefrom: true |
| | | | | | | conf: tiny.yml |
| | | | | | | spec: 1C.2G.50G |
| | | | | | | version: '15' |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-2 | 10.10.10.12 | Replica | running | 1 | 0 | clonefrom: true |
| | | | | | | conf: tiny.yml |
| | | | | | | spec: 1C.2G.50G |
| | | | | | | version: '15' |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-3 | 10.10.10.13 | Replica | running | 1 | 0 | clonefrom: true |
| | | | | | | conf: tiny.yml |
| | | | | | | spec: 1C.2G.50G |
| | | | | | | version: '15' |
+-----------+-------------+---------+---------+----+-----------+-----------------+
Are you sure you want to switchover cluster pg-test, demoting current master pg-test-1? [y/N]: y
2022-12-26 06:39:58.02468 Successfully switched over to "pg-test-2"
+ Cluster: pg-test (7181325041648035869) -----+----+-----------+-----------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-1 | 10.10.10.11 | Replica | stopped | | unknown | clonefrom: true |
| | | | | | | conf: tiny.yml |
| | | | | | | spec: 1C.2G.50G |
| | | | | | | version: '15' |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-2 | 10.10.10.12 | Leader | running | 1 | | clonefrom: true |
| | | | | | | conf: tiny.yml |
| | | | | | | spec: 1C.2G.50G |
| | | | | | | version: '15' |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-3 | 10.10.10.13 | Replica | running | 1 | 0 | clonefrom: true |
| | | | | | | conf: tiny.yml |
| | | | | | | spec: 1C.2G.50G |
| | | | | | | version: '15' |
+-----------+-------------+---------+---------+----+-----------+-----------------+
要通过 Patroni API 来执行此操作（例如，在指定时间将主库从 2号实例 切换到 1号实例）
curl -u 'postgres:Patroni.API' \
-d '{"leader":"pg-test-2", "candidate": "pg-test-1","scheduled_at":"2022-12-26T14:47+08"}' \
-s -X POST http://10.10.10.11:8008/switchover
无论是主动切换还是故障切换，您都需要在集群成员身份发生变化后，重新刷新服务与HBA规则。您应当在变更发生后及时（例如几个小时，一天内），完成此操作：
bin/pgsql-svc <cls>
bin/pgsql-hba <cls>
备份集群
使用 pgBackRest 创建备份，需要以本地 dbsu （默认为 postgres）的身份运行以下命令：
pg-backup # 执行备份，如有必要，执行增量或全量备份
pg-backup full # 执行全量备份
pg-backup diff # 执行差异备份
pg-backup incr # 执行增量备份
pb info # 打印备份信息 （pgbackrest info）
参阅备份恢复获取更多信息。
示例：创建备份
示例：创建定时备份任务
您可以将 crontab 添加到 node_crontab 以指定您的备份策略。
# 每天凌晨1点做一次全备份
- '00 01 * * * postgres /pg/bin/pg-backup full'
# 周一凌晨1点进全量备份，其他工作日进行增量备份
- '00 01 * * 1 postgres /pg/bin/pg-backup full'
- '00 01 * * 2,3,4,5,6,7 postgres /pg/bin/pg-backup'
恢复集群
要将集群恢复到先前的时间点 (PITR)，请以本地 dbsu 用户（默认为postgres）运行 Pigsty 提供的辅助脚本 pg-pitr
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 列出
该命令会输出操作手册，请按照说明进行操作。查看备份恢复-PITR获取详细信息。
示例：使用原始pgBackRest命令进行 PITR
# 恢复到最新可用的点（例如硬件故障）
pgbackrest --stanza=pg-meta restore
# PITR 到特定的时间点（例如意外删除表）
pgbackrest --stanza=pg-meta --type=time --target="2022-11-08 10:58:48" \
--target-action=promote restore
# 恢复特定的备份点，然后提升（或暂停|关闭）
pgbackrest --stanza=pg-meta --type=immediate --target-action=promote \
--set=20221108-105325F_20221108-105938I restore
添加软件
要添加新版本的 RPM 包，你需要将它们加入到 repo_packages 和 repo_url_packages 中。
使用 ./infra.yml -t repo_build 子任务在 Infra 节点上重新构建本地软件仓库。然后，你可以使用 ansible 的 package 模块安装这些包：
ansible pg-test -b -m package -a "name=pg_cron_15,topn_15,pg_stat_monitor_15*" # 使用 ansible 安装一些包
示例：手动更本地新软件源中的包
# 在基础设施/管理节点上添加上游软件仓库，然后手工下载所需的软件包
cd ~/pigsty; ./infra.yml -t repo_upstream,repo_cache # 添加上游仓库（互联网）
cd /www/pigsty; repotrack "some_new_package_name" # 下载最新的 RPM 包
# 更新本地软件仓库元数据
cd ~/pigsty; ./infra.yml -t repo_create # 重新创建本地软件仓库
./node.yml -t node_repo # 刷新所有节点上的 YUM/APT 缓存
# 也可以使用 Ansible 手工刷新节点上的 YUM/APT 缓存
ansible all -b -a 'yum clean all' # 清理节点软件仓库缓存
ansible all -b -a 'yum makecache' # 从新的仓库重建yum/apt缓存
ansible all -b -a 'apt clean' # 清理 APT 缓存（Ubuntu/Debian）
ansible all -b -a 'apt update' # 重建 APT 缓存（Ubuntu/Debian）
例如，你可以使用以下方式安装或升级包：
ansible pg-test -b -m package -a "name=postgresql15* state=latest"
安装扩展
如果你想在 PostgreSQL 集群上安装扩展，请将它们加入到 pg_extensions 中，并执行：
./pgsql.yml -t pg_extension # 安装扩展
一部分扩展需要在 shared_preload_libraries 中加载后才能生效。你可以将它们加入到 pg_libs 中，或者配置一个已有的集群。
最后，在集群的主库上执行 CREATE EXTENSION <extname>; 来完成扩展的安装。
示例：在 pg-test 集群上安装 pg_cron 扩展
ansible pg-test -b -m package -a "name=pg_cron_15" # 在所有节点上安装 pg_cron 包
# 将 pg_cron 添加到 shared_preload_libraries 中
pg edit-config --force -p shared_preload_libraries='timescaledb, pg_cron, pg_stat_statements, auto_explain'
pg restart --force pg-test # 重新启动集群
psql -h pg-test -d postgres -c 'CREATE EXTENSION pg_cron;' # 在主库上安装 pg_cron
更多细节，请参考PGSQL扩展安装。
小版本升级
要执行小版本的服务器升级/降级，您首先需要在本地软件仓库中添加软件：最新的PG小版本 RPM/DEB。
首先对所有从库执行滚动升级/降级，然后执行集群主从切换以升级/降级主库。
ansible <cls> -b -a "yum upgrade/downgrade -y <pkg>" # 升级/降级软件包
pg restart --force <cls> # 重启集群
示例：将PostgreSQL 15.2降级到15.1
将15.1的包添加到软件仓库并刷新节点的 yum/apt 缓存：
cd ~/pigsty; ./infra.yml -t repo_upstream # 添加上游仓库
cd /www/pigsty; repotrack postgresql15-*-15.1 # 将15.1的包添加到yum仓库
cd ~/pigsty; ./infra.yml -t repo_create # 重建仓库元数据
ansible pg-test -b -a 'yum clean all' # 清理节点仓库缓存
ansible pg-test -b -a 'yum makecache' # 从新仓库重新生成yum缓存
# 对于 Ubutnu/Debian 用户，使用 apt 替换 yum
ansible pg-test -b -a 'apt clean' # 清理节点仓库缓存
ansible pg-test -b -a 'apt update' # 从新仓库重新生成apt缓存
执行降级并重启集群：
ansible pg-test -b -a "yum downgrade -y postgresql15*" # 降级软件包）
pg restart --force pg-test # 重启整个集群以完成升级
示例：将PostgreSQL 15.1升级回15.2
这次我们采用滚动方式升级：
ansible pg-test -b -a "yum upgrade -y postgresql15*" # 升级软件包（或 apt upgrade）
ansible pg-test -b -a '/usr/pgsql/bin/pg_ctl --version' # 检查二进制版本是否为15.2
pg restart --role replica --force pg-test # 重启从库
pg switchover --leader pg-test-1 --candidate=pg-test-2 --scheduled=now --force pg-test # 切换主从
pg restart --role primary --force pg-test # 重启主库
大版本升级
实现大版本升级的最简单办法是：创建一个使用新版本的新集群，然后通过逻辑复制，蓝绿部署，并进行在线迁移。
您也可以进行原地大版本升级，当您只使用数据库内核本身时，这并不复杂，使用 PostgreSQL 自带的 pg_upgrade 即可：
假设您想将 PostgreSQL 大版本从 14 升级到 15，您首先需要在仓库中添加软件，并确保两个大版本两侧安装的核心扩展插件也具有相同的版本号。
./pgsql.yml -t pg_pkg -e pg_version=15 # 安装pg 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 # 修复数据目录链接
2 - 集群管理
创建/销毁 PostgreSQL 集群，以及对现有集群进行扩容与缩容的标准操作指南。
创建集群
要创建一个新的Postgres集群，请首先在配置清单中定义，然后进行初始化：
bin/node-add <cls> # 为集群 <cls> 初始化节点 # ./node.yml -l <cls>
bin/pgsql-add <cls> # 初始化集群 <cls> 的pgsql实例 # ./pgsql.yml -l <cls>
请注意，PGSQL 模块需要在 Pigsty 纳管的节点上安装，请先使用
bin/node-add纳管节点。
示例：创建集群
创建用户
要在现有的Postgres集群上创建一个新的业务用户，请将用户定义添加到 all.children.<cls>.pg_users，然后使用以下命令将其创建：
bin/pgsql-user <cls> <username> # ./pgsql-user.yml -l <cls> -e username=<username>
示例：创建业务用户
创建数据库
要在现有的Postgres集群上创建一个新的数据库用户，请将数据库定义添加到 all.children.<cls>.pg_databases，然后按照以下方式创建数据库：
bin/pgsql-db <cls> <dbname> # ./pgsql-db.yml -l <cls> -e dbname=<dbname>
注意：如果数据库指定了一个非默认的属主，该属主用户应当已存在，否则您必须先创建用户。
示例：创建业务数据库
重载服务
服务是 PostgreSQL 对外提供能力的访问点（PGURL可达），由主机节点上的 HAProxy 对外暴露。
当集群成员发生变化时使用此任务，例如：添加／移除副本，主从切换／故障转移 / 暴露新服务，或更新现有服务的配置（例如，LB权重）
要在整个代理集群，或特定实例上创建新服务或重新加载现有服务：
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
示例：重载PG服务以踢除一个实例
重载HBA
当您的 Postgres/Pgbouncer HBA 规则发生更改时，您 可能 需要重载 HBA 以应用更改。
如果您有任何特定于角色的 HBA 规则，或者在IP地址段中引用了集群成员的别名，那么当主从切换/集群扩缩容后也可能需要重载HBA。
要在整个集群或特定实例上重新加载 postgres 和 pgbouncer 的 HBA 规则：
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
示例：重载集群 HBA 规则
配置集群
要更改现有的 Postgres 集群配置，您需要在管理节点上使用管理员用户（安装Pigsty的用户，nopass ssh/sudo）发起控制命令：
另一种方式是在数据库集群中的任何节点上，使用 dbsu （默认为 postgres） ，也可以执行管理命令，但只能管理本集群。
pg edit-config <cls> # interactive config a cluster with patronictl
更改 patroni 参数和 postgresql.parameters，根据提示保存并应用更改即可。
示例：非交互式方式配置集群
您可以跳过交互模式，并使用 -p 选项覆盖 postgres 参数，例如：
pg edit-config -p log_min_duration_statement=1000 pg-test
pg edit-config --force -p shared_preload_libraries='timescaledb, pg_cron, pg_stat_statements, auto_explain'
示例：使用 Patroni REST API 更改集群配置
您还可以使用 Patroni REST API 以非交互式方式更改配置，例如：
$ curl -s 10.10.10.11:8008/config | jq . # get current config
$ curl -u 'postgres:Patroni.API' \
-d '{"postgresql":{"parameters": {"log_min_duration_statement":200}}}' \
-s -X PATCH http://10.10.10.11:8008/config | jq .
注意：Patroni 敏感API（例如重启等） 访问仅限于从基础设施/管理节点发起，并且有 HTTP 基本认证（用户名/密码）以及可选的 HTTPS 保护。
示例：使用 patronictl 配置集群
添加实例
若要将新从库添加到现有的 PostgreSQL 集群中，您需要将其定义添加到配置清单：all.children.<cls>.hosts 中，然后：
bin/node-add <ip> # 将节点 <ip> 纳入 Pigsty 管理
bin/pgsql-add <cls> <ip> # 初始化 <ip> ，作为集群 <cls> 的新从库
这将会把节点 <ip> 添加到 pigsty 并将其初始化为集群 <cls> 的一个副本。
集群服务将会重新加载以接纳新成员。
示例：为 pg-test 添加从库
例如，如果您想将 pg-test-3 / 10.10.10.13 添加到现有的集群 pg-test，您首先需要更新配置清单：
pg-test:
hosts:
10.10.10.11: { pg_seq: 1, pg_role: primary } # 已存在的成员
10.10.10.12: { pg_seq: 2, pg_role: replica } # 已存在的成员
10.10.10.13: { pg_seq: 3, pg_role: replica } # <--- 新成员
vars: { pg_cluster: pg-test }
然后按如下方式应用更改：
bin/node-add 10.10.10.13 # 将节点添加到 pigsty
bin/pgsql-add pg-test 10.10.10.13 # 在 10.10.10.13 上为集群 pg-test 初始化新的副本
这与集群初始化相似，但只在单个实例上工作：
[ OK ] 初始化实例 10.10.10.11 到 pgsql 集群 'pg-test' 中:
[WARN] 提醒：先将节点添加到 pigsty 中，然后再安装模块 'pgsql'
[HINT] $ bin/node-add 10.10.10.11 # 除 infra 节点外，先运行此命令
[WARN] 从集群初始化实例：
[ OK ] $ ./pgsql.yml -l '10.10.10.11,&pg-test'
[WARN] 重新加载现有实例上的 pg_service：
[ OK ] $ ./pgsql.yml -l 'pg-test,!10.10.10.11' -t pg_service
移除实例
若要从现有的 PostgreSQL 集群中移除副本：
bin/pgsql-rm <cls> <ip...> # ./pgsql-rm.yml -l <ip>
这将从集群 <cls> 中移除实例 <ip>。 集群服务将会重新加载以从负载均衡器中踢除已移除的实例。
示例：从 pg-test 移除从库
例如，如果您想从现有的集群 pg-test 中移除 pg-test-3 / 10.10.10.13：
bin/pgsql-rm pg-test 10.10.10.13 # 从 pg-test 中移除 pgsql 实例 10.10.10.13
bin/node-rm 10.10.10.13 # 从 pigsty 中移除该节点（可选）
vi pigsty.yml # 从目录中移除实例定义
bin/pgsql-svc pg-test # 刷新现有实例上的 pg_service，以从负载均衡器中踢除已移除的实例
[ OK ] 从 'pg-test' 移除 10.10.10.13 的 pgsql 实例：
[WARN] 从集群中移除实例：
[ OK ] $ ./pgsql-rm.yml -l '10.10.10.13,&pg-test'
并从配置清单中移除实例定义：
pg-test:
hosts:
10.10.10.11: { pg_seq: 1, pg_role: primary }
10.10.10.12: { pg_seq: 2, pg_role: replica }
10.10.10.13: { pg_seq: 3, pg_role: replica } # <--- 执行后移除此行
vars: { pg_cluster: pg-test }
最后，您可以重载PG服务并从负载均衡器中踢除已移除的实例：
bin/pgsql-svc pg-test # 重载 pg-test 上的服务
下线集群
要移除整个 Postgres 集群，只需运行：
bin/pgsql-rm <cls> # ./pgsql-rm.yml -l <cls>
示例：移除集群
示例：强制移除集群
注意：如果为这个集群配置了pg_safeguard（或全局设置为 true），pgsql-rm.yml 将中止，以避免意外移除集群。
您可以使用 playbook 命令行参数明确地覆盖它，以强制执行清除：
./pgsql-rm.yml -l pg-meta -e pg_safeguard=false # 强制移除 pg 集群 pg-meta
3 - 用户管理
创建 PostgreSQL 用户/角色，管理连接池角色，刷新过期时间，用户密码轮换
创建用户
要在现有的Postgres集群上创建一个新的业务用户，请将用户定义添加到 all.children.<cls>.pg_users，然后使用以下命令将其创建：
bin/pgsql-user <cls> <username> # ./pgsql-user.yml -l <cls> -e username=<username>
示例：创建业务用户
定义用户
Pigsty通过两个配置参数定义数据库集群中的角色与用户：
pg_default_roles：定义全局统一使用的角色和用户pg_users：在数据库集群层面定义业务用户和角色
前者用于定义了整套环境中共用的角色与用户，后者定义单个集群中特有的业务角色与用户。二者形式相同，均为用户定义对象的数组。
你可以定义多个用户/角色，它们会按照先全局，后集群，最后按数组内排序的顺序依次创建，所以后面的用户可以属于前面定义的角色。
下面是 Pigsty 演示环境中默认集群 pg-meta 中的业务用户定义：
pg-meta:
hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
vars:
pg_cluster: pg-meta
pg_users:
- {name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [dbrole_admin] ,comment: pigsty admin user }
- {name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
- {name: dbuser_grafana ,password: DBUser.Grafana ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for grafana database }
- {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for bytebase database }
- {name: dbuser_kong ,password: DBUser.Kong ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for kong api gateway }
- {name: dbuser_gitea ,password: DBUser.Gitea ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for gitea service }
- {name: dbuser_wiki ,password: DBUser.Wiki ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for wiki.js service }
- {name: dbuser_noco ,password: DBUser.Noco ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for nocodb service }
每个用户/角色定义都是一个 object，可能包括以下字段，以 dbuser_meta 用户为例：
- name: dbuser_meta # 必需，`name` 是用户定义的唯一必选字段
password: DBUser.Meta # 可选，密码，可以是 scram-sha-256 哈希字符串或明文
login: true # 可选，默认情况下可以登录
superuser: false # 可选，默认为 false，是超级用户吗？
createdb: false # 可选，默认为 false，可以创建数据库吗？
createrole: false # 可选，默认为 false，可以创建角色吗？
inherit: true # 可选，默认情况下，此角色可以使用继承的权限吗？
replication: false # 可选，默认为 false，此角色可以进行复制吗？
bypassrls: false # 可选，默认为 false，此角色可以绕过行级安全吗？
pgbouncer: true # 可选，默认为 false，将此用户添加到 pgbouncer 用户列表吗？（使用连接池的生产用户应该显式定义为 true）
connlimit: -1 # 可选，用户连接限制，默认 -1 禁用限制
expire_in: 3650 # 可选，此角色过期时间：从创建时 + n天计算（优先级比 expire_at 更高）
expire_at: '2030-12-31' # 可选，此角色过期的时间点，使用 YYYY-MM-DD 格式的字符串指定一个特定日期（优先级没 expire_in 高）
comment: pigsty admin user # 可选，此用户/角色的说明与备注字符串
roles: [dbrole_admin] # 可选，默认角色为：dbrole_{admin,readonly,readwrite,offline}
parameters: {} # 可选，使用 `ALTER ROLE SET` 针对这个角色，配置角色级的数据库参数
pool_mode: transaction # 可选，默认为 transaction 的 pgbouncer 池模式，用户级别
pool_connlimit: -1 # 可选，用户级别的最大数据库连接数，默认 -1 禁用限制
search_path: public # 可选，根据 postgresql 文档的键值配置参数（例如：使用 pigsty 作为默认 search_path）
- 唯一必需的字段是
name，它应该是 PostgreSQL 集群中的一个有效且唯一的用户名。 - 角色不需要
password，但对于可登录的业务用户，通常是需要指定一个密码的。 password可以是明文或 scram-sha-256 / md5 哈希字符串，请最好不要使用明文密码。- 用户/角色按数组顺序逐一创建，因此，请确保角色/分组的定义在成员之前。
login、superuser、createdb、createrole、inherit、replication、bypassrls是布尔标志。pgbouncer默认是禁用的：要将业务用户添加到 pgbouncer 用户列表，您应当显式将其设置为true。
ACL系统
Pigsty 具有一套内置的，开箱即用的访问控制 / ACL 系统，您只需将以下四个默认角色分配给业务用户即可轻松使用：
dbrole_readwrite：全局读写访问的角色（主属业务使用的生产账号应当具有数据库读写权限）dbrole_readonly：全局只读访问的角色（如果别的业务想要只读访问，可以使用此角色）dbrole_admin：拥有DDL权限的角色 （业务管理员，需要在应用中建表的场景）dbrole_offline：受限的只读访问角色（只能访问 offline 实例，通常是个人用户）
如果您希望重新设计您自己的 ACL 系统，可以考虑定制以下参数和模板：
pg_default_roles：系统范围的角色和全局用户pg_default_privileges：新建对象的默认权限roles/pgsql/templates/pg-init-role.sql：角色创建 SQL 模板roles/pgsql/templates/pg-init-template.sql：权限 SQL 模板
创建用户
在 pg_default_roles 和 pg_users 中定义的用户和角色，将在集群初始化的 PROVISION 阶段中自动逐一创建。
如果您希望在现有的集群上创建用户，可以使用 bin/pgsql-user 工具。
将新用户/角色定义添加到 all.children.<cls>.pg_users，并使用以下方法创建该数据库：
bin/pgsql-user <cls> <username> # pgsql-user.yml -l <cls> -e username=<username>
不同于数据库，创建用户的剧本总是幂等的。当目标用户已经存在时，Pigsty会修改目标用户的属性使其符合配置。所以在现有集群上重复运行它通常不会有问题。
请使用剧本创建用户
我们不建议您手工创建新的业务用户，特别当您想要创建的用户使用默认的 pgbouncer 连接池时：除非您愿意手工负责维护 Pgbouncer 中的用户列表并与 PostgreSQL 保持一致。
使用 bin/pgsql-user 工具或 pgsql-user.yml 剧本创建新数据库时，会将此数据库一并添加到 Pgbouncer用户 列表中。
修改用户
修改 PostgreSQL 用户的属性的方式与 创建用户 相同。
首先，调整您的用户定义，修改需要调整的属性，然后执行以下命令应用：
bin/pgsql-user <cls> <username> # pgsql-user.yml -l <cls> -e username=<username>
请注意，修改用户不会删除用户，而是通过 ALTER USER 命令修改用户属性；也不会回收用户的权限与分组，并使用 GRANT 命令授予新的角色。
Pgbouncer用户
默认情况下启用 Pgbouncer，并作为连接池中间件，其用户默认被管理。
Pigsty 默认将 pg_users 中显式带有 pgbouncer: true 标志的所有用户添加到 pgbouncer 用户列表中。
Pgbouncer 连接池中的用户在 /etc/pgbouncer/userlist.txt 中列出：
"postgres" ""
"dbuser_wiki" "SCRAM-SHA-256$4096:+77dyhrPeFDT/TptHs7/7Q==$KeatuohpKIYzHPCt/tqBu85vI11o9mar/by0hHYM2W8=:X9gig4JtjoS8Y/o1vQsIX/gY1Fns8ynTXkbWOjUfbRQ="
"dbuser_view" "SCRAM-SHA-256$4096:DFoZHU/DXsHL8MJ8regdEw==$gx9sUGgpVpdSM4o6A2R9PKAUkAsRPLhLoBDLBUYtKS0=:MujSgKe6rxcIUMv4GnyXJmV0YNbf39uFRZv724+X1FE="
"dbuser_monitor" "SCRAM-SHA-256$4096:fwU97ZMO/KR0ScHO5+UuBg==$CrNsmGrx1DkIGrtrD1Wjexb/aygzqQdirTO1oBZROPY=:L8+dJ+fqlMQh7y4PmVR/gbAOvYWOr+KINjeMZ8LlFww="
"dbuser_meta" "SCRAM-SHA-256$4096:leB2RQPcw1OIiRnPnOMUEg==$eyC+NIMKeoTxshJu314+BmbMFpCcspzI3UFZ1RYfNyU=:fJgXcykVPvOfro2MWNkl5q38oz21nSl1dTtM65uYR1Q="
"dbuser_kong" "SCRAM-SHA-256$4096:bK8sLXIieMwFDz67/0dqXQ==$P/tCRgyKx9MC9LH3ErnKsnlOqgNd/nn2RyvThyiK6e4=:CDM8QZNHBdPf97ztusgnE7olaKDNHBN0WeAbP/nzu5A="
"dbuser_grafana" "SCRAM-SHA-256$4096:HjLdGaGmeIAGdWyn2gDt/Q==$jgoyOB8ugoce+Wqjr0EwFf8NaIEMtiTuQTg1iEJs9BM=:ed4HUFqLyB4YpRr+y25FBT7KnlFDnan6JPVT9imxzA4="
"dbuser_gitea" "SCRAM-SHA-256$4096:l1DBGCc4dtircZ8O8Fbzkw==$tpmGwgLuWPDog8IEKdsaDGtiPAxD16z09slvu+rHE74=:pYuFOSDuWSofpD9OZhG7oWvyAR0PQjJBffgHZLpLHds="
"dbuser_dba" "SCRAM-SHA-256$4096:zH8niABU7xmtblVUo2QFew==$Zj7/pq+ICZx7fDcXikiN7GLqkKFA+X5NsvAX6CMshF0=:pqevR2WpizjRecPIQjMZOm+Ap+x0kgPL2Iv5zHZs0+g="
"dbuser_bytebase" "SCRAM-SHA-256$4096:OMoTM9Zf8QcCCMD0svK5gg==$kMchqbf4iLK1U67pVOfGrERa/fY818AwqfBPhsTShNQ=:6HqWteN+AadrUnrgC0byr5A72noqnPugItQjOLFw0Wk="
而用户级别的连接池参数则是使用另一个单独的文件： /etc/pgbouncer/useropts.txt 进行维护，比如：
dbuser_dba = pool_mode=session max_user_connections=16
dbuser_monitor = pool_mode=session max_user_connections=8
当您创建数据库时，Pgbouncer 的数据库列表定义文件将会被刷新，并通过在线重载配置的方式生效，不会影响现有的连接。
Pgbouncer 使用和 PostgreSQL 同样的 dbsu 运行，默认为 postgres 操作系统用户，您可以使用 pgb 别名，使用 dbsu 访问 pgbouncer 管理功能。
Pigsty 还提供了一个实用函数 pgb-route ，可以将 pgbouncer 数据库流量快速切换至集群中的其他节点，用于零停机迁移：
连接池用户配置文件 userlist.txt 与 useropts.txt 会在您创建用户时自动刷新，并通过在线重载配置的方式生效，正常不会影响现有的连接。
请注意，pgbouncer_auth_query 参数允许你使用动态查询来完成连接池用户认证，当您懒得管理连接池中的用户时，这是一种折中的方案。
4 - 参数优化
调整 postgres 参数
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 的范围内。
{% raw %}
{% 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 %}
{% endraw %}
CPU参数调整
在 PostgreSQL 中，有 4 个与并行查询相关的重要参数，Pigsty 会自动根据当前系统的 CPU 核数进行参数优化。 在所有策略中，总并行进程数量（总预算）通常设置为 CPU 核数 + 8，且保底为 16 个，从而为逻辑复制与扩展预留足够的后台 worker 数量，OLAP 和 TINY 模板根据场景略有不同。
| OLTP | 设置逻辑 | 范围限制 |
|---|---|---|
max_worker_processes | max(100% CPU + 8, 16) | 核数 + 4，保底 12， |
max_parallel_workers | max(ceil(50% CPU), 2) | 1/2 CPU 上取整，最少两个 |
max_parallel_maintenance_workers | max(ceil(33% CPU), 2) | 1/3 CPU 上取整，最少两个 |
max_parallel_workers_per_gather | min(max(ceil(20% CPU), 2),8) | 1/5 CPU 下取整，最少两个，最多 8 个 |
| OLAP | 设置逻辑 | 范围限制 |
|---|---|---|
max_worker_processes | max(100% CPU + 12, 20) | 核数 + 12，保底 20， |
max_parallel_workers | max(ceil(80% CPU, 2)) | 4/5 CPU 上取整，最少两个 |
max_parallel_maintenance_workers | max(ceil(33% CPU), 2) | 1/3 CPU 上取整，最少两个 |
max_parallel_workers_per_gather | max(floor(50% CPU), 2) | 1/2 CPU 上取整，最少两个 |
| CRIT | 设置逻辑 | 范围限制 |
|---|---|---|
max_worker_processes | max(100% CPU + 8, 16) | 核数 + 8，保底 16， |
max_parallel_workers | max(ceil(50% CPU), 2) | 1/2 CPU 上取整，最少两个 |
max_parallel_maintenance_workers | max(ceil(33% CPU), 2) | 1/3 CPU 上取整，最少两个 |
max_parallel_workers_per_gather | 0, 按需启用 |
| TINY | 设置逻辑 | 范围限制 |
|---|---|---|
max_worker_processes | max(100% CPU + 4, 12) | 核数 + 4，保底 12， |
max_parallel_workers | max(ceil(50% CPU) 1) | 50% CPU 下取整，最少1个 |
max_parallel_maintenance_workers | max(ceil(33% CPU), 1) | 33% CPU 下取整，最少1个 |
max_parallel_workers_per_gather | 0, 按需启用 |
请注意，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 主数据目录所在磁盘的总空间，并以此作为依据指定下列参数：
{% raw %}
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
{% endraw %}
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
手工调整参数
除了使用 Pigsty 自动配置的参数外，您还可以手工调整 PostgreSQL 参数。
使用 pg edit-config <cluster> 命令可以交互式编辑集群配置：
pg edit-config pg-meta
或者使用 -p 参数直接设置参数：
pg edit-config -p log_min_duration_statement=1000 pg-meta
pg edit-config --force -p shared_preload_libraries='timescaledb, pg_cron, pg_stat_statements, auto_explain' pg-meta
您也可以使用 Patroni REST API 来修改配置：
curl -u 'postgres:Patroni.API' \
-d '{"postgresql":{"parameters": {"log_min_duration_statement":200}}}' \
-s -X PATCH http://10.10.10.10:8008/config | jq .
5 - 故障排查
常见故障与分析排查思路
本文档列举了 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 面板来确认表的年龄分布。 同时查阅数据库错误日志，通常可以找到定位根因的线索。
处理
- 立即冻结老事务：如果数据库尚未进入只读保护状态，立刻对受影响的库执行一次手动 VACUUM FREEZE。可以从老化最严重的表开始逐个冻结，而不是整库一起做，以加快效果。使用超级用户连接数据库，针对识别出的
relfrozenxid最大的表运行VACUUM FREEZE 表名;，优先冻结那些XID年龄最大的表元组。这样可以迅速回收大量事务ID空间。 - 单用户模式救援：如果数据库已经拒绝写入或宕机保护，此时需要启动数据库到单用户模式执行冻结操作。在单用户模式下运行
VACUUM FREEZE database_name;对整个数据库进行冻结清理。完成后再以多用户模式重启数据库。这样做可以解除回卷锁定，让数据库重新可写。需要注意在单用户模式下操作要非常谨慎，并确保有足够的事务ID余量完成冻结。 - 备用节点接管：在某些复杂场景（例如遭遇硬件问题导致 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新存储引擎故障案例》
6 - 误删处理
处理误删数据，误删表，误删数据库
误删数据
如果是小批量 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 类误操作，通常按照以下流程决定恢复方案。
- 确认此数据是否可以通过业务系统或其他数据系统找回，如果可以，直接从业务侧修复。
- 确认是否有延迟从库，如果有，推进延迟从库至误删时间点，查询出来恢复。
- 如果数据已经确认删除，确认备份信息，恢复范围是否覆盖误删时间点，如果覆盖，开始 PITR
- 确认是整集群原地 PITR 回滚，还是新开服务器重放，还是用从库来重放，并执行恢复策略
误删集群
如果出现整个数据库集群通过 Pigsty 管理命令被误删的情况，例如错误的执行 pgsql-rm.yml 剧本或 bin/pgsql-rm 命令。
除非您指定了 pg_rm_backup 参数为 false，否则备份会与数据库集群一起被删除。
警告：在这种情况，您的数据将无法找回！请务必三思而后行！
建议：对于生产环境，您可以在配置清单中全局配置此参数为 false，在移除集群时保留备份。
7 - 维护保养
常见系统维护任务
要确保 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 安全。
您可以使用以下命令手工对数据库执行 VACUUM FREEZE：
-- 对整个数据库执行 VACUUM FREEZE
VACUUM FREEZE;
-- 对特定表执行 VACUUM FREEZE
VACUUM FREEZE schema.table_name;
或者通过 crontab 设置定时任务，例如每周日凌晨执行：
# 每周日凌晨3点对所有数据库执行 VACUUM FREEZE
0 3 * * 0 postgres psql -c 'VACUUM FREEZE;' dbname
8 - 版本升级
如何升级（或降级） PostgreSQL 小版本内核，以及如何进行大版本升级。
小版本升级
要执行小版本的服务器升级/降级，您首先需要在本地软件仓库中 添加软件：最新的PG小版本 RPM/DEB。
首先对所有从库执行滚动升级/降级，然后执行集群 主从切换以升级/降级主库。
ansible <cls> -b -a "yum upgrade/downgrade -y <pkg>" # 升级/降级软件包
pg restart --force <cls> # 重启集群
这次我们采用滚动方式升级：
ansible pg-test -b -a "yum upgrade -y postgresql15*" # 升级软件包（或 apt upgrade）
ansible pg-test -b -a '/usr/pgsql/bin/pg_ctl --version' # 检查二进制版本是否为15.2
pg restart --role replica --force pg-test # 重启从库
pg switchover --leader pg-test-1 --candidate=pg-test-2 --scheduled=now --force pg-test # 切换主从
pg restart --role primary --force pg-test # 重启主库
小版本降级
将15.1的包添加到软件仓库并刷新节点的 yum/apt 缓存：
cd ~/pigsty; ./infra.yml -t repo_upstream # 添加上游仓库
cd /www/pigsty; repotrack postgresql15-*-15.1 # 将15.1的包添加到yum仓库
cd ~/pigsty; ./infra.yml -t repo_create # 重建仓库元数据
ansible pg-test -b -a 'yum clean all' # 清理节点仓库缓存
ansible pg-test -b -a 'yum makecache' # 从新仓库重新生成yum缓存
# 对于 Ubutnu/Debian 用户，使用 apt 替换 yum
ansible pg-test -b -a 'apt clean' # 清理节点仓库缓存
ansible pg-test -b -a 'apt update' # 从新仓库重新生成apt缓存
执行降级并重启集群：
ansible pg-test -b -a "yum downgrade -y postgresql15*" # 降级软件包）
pg restart --force pg-test # 重启整个集群以完成升级
大版本升级
实现大版本升级的最简单办法是：创建一个使用新版本的新集群，然后通过逻辑复制，蓝绿部署，并进行 在线迁移。
您也可以进行原地大版本升级，当您只使用数据库内核本身时，这并不复杂，使用 PostgreSQL 自带的 pg_upgrade 即可：
假设您想将 PostgreSQL 大版本从 14 升级到 15，您首先需要在仓库中 添加软件，并确保两个大版本两侧安装的核心扩展插件也具有相同的版本号。
./pgsql.yml -t pg_pkg -e pg_version=15 # 安装pg 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 # 修复数据目录链接