管理 PostgreSQL 定时任务

Pigsty 使用 crontab 来管理定时任务，用于执行例行备份，冻结老化事务，重整膨胀表索引等维护工作。

快速上手

下面是两个使用 pg_crontab 参数配置定时任务的示例，如果您不熟悉 Crontab 的语法，可以参考 Crontab Guru 的解释。

pg-meta 集群配置了每天凌晨1点进行全量备份的定时任务，pg-test 配置了每周一全量备份，其余日期增量备份的定时任务。

    pg-meta:
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars:
        pg_cluster: pg-meta
        pg_crontab:
          - '00 01 * * * /pg/bin/pg-backup'
    pg-test:
      hosts:
        10.10.10.11: { pg_seq: 1, pg_role: primary }
        10.10.10.12: { pg_seq: 2, pg_role: replica }
      vars:
        pg_cluster: pg-test           # define pgsql cluster name
        pg_crontab:
          - '00 01 * * 1            /pg/bin/pg-backup full'
          - '00 01 * * 2,3,4,5,6,7  /pg/bin/pg-backup'

这些定时任务会在 pgsql.yml 剧本执行时（pg_crontab 任务）自动添加到对应操作系统发行版的默认位置：

• EL：/var/spool/cron/postgres
• Debian/Ubuntu：/var/spool/cron/crontabs/postgres

修改定时任务

要修改这些定时任务，可以直接编辑集群配置中的 pg_crontab 参数，然后重新执行剧本应用变更。

./pgsql.yml -t pg_crontab -l <cluster>

每次执行剧本都会 全量覆盖刷新 定时任务

查阅定时任务

要查看当前配置的定时任务，可以使用 pg_dbsu 操作系统用户执行以下命令：

crontab -l

# Pigsty Managed Crontab for postgres
SHELL=/bin/bash
PATH=/usr/pgsql/bin:/pg/bin:/usr/local/bin:/usr/bin:/usr/sbin:/bin:/sbin
MAILTO=""
00 01 * * * /pg/bin/pg-backup

常用任务

Pigsty 提供了一些常用的例行任务脚本，

• 物理备份：使用 pg-backup 脚本执行备份
• 事务冻结：使用 pg-vacuum 脚本冻结老化事务并进行 ANALYZE
• 膨胀治理：使用 pg-repack 脚本重整膨胀表与索引

上面这三个脚本，都可以在全体集群实例上配置，但只会在主库上进行，这确保了当集群发生主从切换时，新的主库也会继续执行维护任务。

唯一 默认 设置的定时任务是 “备份任务”，这确保了每天默认会有一个 “全量备份”。 您可以在 配置清单 中在全局层面或者集群层面进行覆盖。

crontab -l

pg-backup

pg-vacuum

pg-repack

要确保 PostgreSQL 集群健康稳定运行，需要进行例行维护保养工作。Pigsty 提供了开箱即用的维护脚本。

pg-repack                    # 重整所有数据库中的膨胀表与索引
pg-vacuum                    # 冻结所有数据库中的老化表
pg-backup full               # 执行全量备份

• 重整
• 冻结
• 定时

pg-repack [database...]      # 重整膨胀的表与索引
pg-repack -n mydb            # 空跑模式，只显示不执行
pg-repack -t mydb            # 仅重整表
pg-repack -i mydb            # 仅重整索引

pg-vacuum [database...]      # 冻结老化事务
pg-vacuum -n mydb            # 空跑模式，只显示不执行
pg-vacuum -a 80000000 mydb   # 使用自定义年龄阈值

# 在集群配置中添加定时任务
node_crontab:
  - '00 03 * * 0 postgres /pg/bin/pg-vacuum'   # 每周日凌晨3点
  - '00 04 * * 1 postgres /pg/bin/pg-repack'   # 每周一凌晨4点

关于备份恢复操作，请参考 备份管理 章节。关于监控告警，请参考 监控系统。

表膨胀治理

长时间运行的 PostgreSQL 会出现"表膨胀"/“索引膨胀"现象，导致系统性能劣化。定期使用 pg_repack 对表与索引进行在线重建，有助于维护良好性能。

您可以通过 Pigsty 的 PGCAT Database - Table Bloat 面板确认数据库中的膨胀情况，并选择膨胀率较高的表与索引进行重整。

使用 pg-repack 脚本

Pigsty 提供了开箱即用的 /pg/bin/pg-repack 脚本，必须在主库上以 postgres 用户身份运行：

• 基本用法
• 选项
• 手工

pg-repack                    # 重整所有数据库中的膨胀表与索引
pg-repack mydb               # 仅重整指定数据库
pg-repack -n mydb            # 空跑模式，只显示不执行

pg-repack -t mydb            # 仅重整表
pg-repack -i mydb            # 仅重整索引
pg-repack -T 30 -j 4 mydb    # 自定义锁超时(秒)和并行度

# 直接使用 pg_repack 命令重整特定表
pg_repack dbname -t schema.table

自动选择阈值

该脚本会根据表和索引的大小与膨胀率，自动选择需要重整的对象：

超过 64GB 的巨型表会被跳过并给出提示，需要手动处理。

事务冻结

冻结过期事务ID（VACUUM FREEZE）是 PostgreSQL 重要的维护任务，用于防止事务ID（XID）用尽导致停机。

使用 pg-vacuum 脚本

Pigsty 提供了开箱即用的 /pg/bin/pg-vacuum 脚本，必须在主库上以 postgres 用户身份运行：

• 基本用法
• 选项
• 手工

pg-vacuum                    # 冻结所有数据库中的老化表
pg-vacuum mydb               # 仅处理指定数据库
pg-vacuum -n mydb            # 空跑模式，只显示不执行

pg-vacuum -a 80000000 mydb   # 使用自定义年龄阈值（默认1亿）
pg-vacuum -r 50 mydb         # 使用自定义老化比例阈值（默认40%）

-- 对整个数据库执行 VACUUM FREEZE
VACUUM FREEZE;

-- 对特定表执行 VACUUM FREEZE
VACUUM FREEZE schema.table_name;

工作逻辑

1. 检查数据库的 datfrozenxid 年龄，如果低于阈值则跳过该库
2. 计算老化页面比例（超过年龄阈值的表页面占总页面的百分比）
3. 如果老化比例 > 40%，执行全库 VACUUM FREEZE ANALYZE
4. 否则，仅对超过年龄阈值的表执行 VACUUM FREEZE ANALYZE

脚本会设置 vacuum_cost_limit = 10000 和 vacuum_cost_delay = 1ms 以控制 I/O 影响。

自动化维护

对于生产环境，建议配置定时任务自动执行维护脚本。Pigsty 提供了 node_crontab 参数，可以在集群配置中声明定时任务。

推荐的维护计划

# 在集群配置的 vars 中添加
node_crontab:
  - '00 03 * * 0 postgres /pg/bin/pg-vacuum'     # 每周日凌晨3点执行 vacuum freeze
  - '00 04 * * 1 postgres /pg/bin/pg-repack'     # 每周一凌晨4点执行 repack
  - '00 01 * * * postgres /pg/bin/pg-backup full' # 每天凌晨1点全量备份

应用配置变更

• 剧本
• 手工

./node.yml -l pg-meta -t node_crontab    # 应用 node_crontab 配置到指定集群
./node.yml -l 10.10.10.10 -t node_crontab # 仅针对特定主机

# 以 postgres 用户编辑定时任务
sudo -u postgres crontab -e

# 或直接追加到系统 crontab
sudo tee -a /etc/crontab <<-'EOF'
00 03 * * 0 postgres /pg/bin/pg-vacuum
00 04 * * 1 postgres /pg/bin/pg-repack
EOF

故障善后

Pigsty 的高可用架构允许 PostgreSQL 集群自动进行主从切换，无需即时介入。然而在故障发生后，仍需进行以下善后工作：

善后检查清单

• 刷新服务
• 刷新HBA
• 恢复冗余

bin/pgsql-svc pg-test        # 刷新集群的负载均衡配置

bin/pgsql-hba pg-test        # 刷新集群的 HBA 规则

bin/pgsql-rm pg-test 10.10.10.13    # 移除故障节点
bin/pgsql-add pg-test 10.10.10.14   # 扩容新从库

定期查阅监控

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

预定义的 告警规则 列表可在 Grafana 中查看。

更多细节请参考：关系膨胀的治理

相关文档

• 备份管理：PostgreSQL 备份与恢复
• 监控系统：PostgreSQL 监控与告警
• 集群管理：集群的创建、扩缩容、销毁
• Patroni 管理：高可用集群管理