管理 PostgreSQL 定时任务

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

速查手册

其他管理任务，请参考：备份管理，监控系统，高可用管理。

配置定时任务

使用 pg_crontab 参数配置 PostgreSQL 数据库超级用户（pg_dbsu，默认 postgres）的定时任务。

示例配置

下面 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
    pg_crontab:
      - '00 01 * * 1            /pg/bin/pg-backup full'
      - '00 01 * * 2,3,4,5,6,7  /pg/bin/pg-backup'

推荐的维护计划

pg_crontab:
  - '00 01 * * * /pg/bin/pg-backup full'    # 每天凌晨1点全量备份
  - '00 03 * * 0 /pg/bin/pg-vacuum'         # 每周日凌晨3点执行 vacuum freeze
  - '00 04 * * 1 /pg/bin/pg-repack'         # 每周一凌晨4点执行 repack

应用定时任务

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

• EL（RHEL/Rocky/Alma）：/var/spool/cron/postgres
• Debian/Ubuntu：/var/spool/cron/crontabs/postgres

• 剧本
• 手工

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

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

# 或直接编辑 crontab 文件
sudo vi /var/spool/cron/postgres           # EL 系列
sudo vi /var/spool/cron/crontabs/postgres  # Debian/Ubuntu

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

查看定时任务

使用 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

如果您不熟悉 Crontab 的语法，可以参考 Crontab Guru 的解释。

pg-backup

pg-backup 是 Pigsty 提供的物理备份脚本，基于 pgBackRest 实现，支持全量、差异、增量三种备份模式。

基本用法

pg-backup                # 执行增量备份（默认），如果没有全量备份则自动执行全量备份
pg-backup full           # 执行全量备份
pg-backup diff           # 执行差异备份（基于最近的全量备份）
pg-backup incr           # 执行增量备份（基于最近的任意备份）

备份类型说明

执行条件

• 脚本必须在 主库 上以 postgres 用户身份运行
• 脚本会自动检测当前节点角色，从库执行时会直接退出（exit 1）
• 从 /etc/pgbackrest/pgbackrest.conf 中自动获取 stanza 名称

常用定时任务配置

• 每日全量
• 周全量+日增量
• 周全量+日差异

pg_crontab:
  - '00 01 * * * /pg/bin/pg-backup full'    # 每天凌晨1点全量备份

pg_crontab:
  - '00 01 * * 1            /pg/bin/pg-backup full'  # 周一全量备份
  - '00 01 * * 2,3,4,5,6,7  /pg/bin/pg-backup'       # 其他日期增量备份

pg_crontab:
  - '00 01 * * 1            /pg/bin/pg-backup full'  # 周一全量备份
  - '00 01 * * 2,3,4,5,6,7  /pg/bin/pg-backup diff'  # 其他日期差异备份

更多备份恢复操作，请参考 备份管理 章节。

pg-vacuum

pg-vacuum 是 Pigsty 提供的事务冻结脚本，用于执行 VACUUM FREEZE 操作，防止事务ID（XID）回卷导致数据库停机。

基本用法

• 基本
• 选项
• 手工SQL

pg-vacuum                    # 冻结所有数据库中的老化表
pg-vacuum mydb               # 仅处理指定数据库
pg-vacuum mydb1 mydb2        # 处理多个数据库

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 影响。

执行条件

• 脚本必须在 主库 上以 postgres 用户身份运行
• 使用文件锁 /tmp/pg-vacuum.lock 防止并发执行
• 自动跳过 template0、template1、postgres 系统数据库

常用定时任务配置

pg_crontab:
  - '00 03 * * 0 /pg/bin/pg-vacuum'     # 每周日凌晨3点执行

pg-repack

pg-repack 是 Pigsty 提供的膨胀治理脚本，基于 pg_repack 扩展实现，用于在线重整膨胀的表与索引。

基本用法

• 基本
• 选项
• 手工

pg-repack                    # 重整所有数据库中的膨胀表与索引
pg-repack mydb               # 仅重整指定数据库
pg-repack mydb1 mydb2        # 重整多个数据库

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

# 直接使用 pg_repack 命令重整特定索引
pg_repack dbname -i schema.index

命令选项

自动选择阈值

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

表膨胀阈值

索引膨胀阈值

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

执行条件

• 脚本必须在 主库 上以 postgres 用户身份运行
• 需要安装 pg_repack 扩展（Pigsty 默认安装）
• 需要 monitor schema 中的 pg_table_bloat 和 pg_index_bloat 视图
• 使用文件锁 /tmp/pg-repack.lock 防止并发执行
• 自动跳过 template0、template1、postgres 系统数据库

常用定时任务配置

pg_crontab:
  - '00 04 * * 1 /pg/bin/pg-repack'     # 每周一凌晨4点执行

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

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

移除定时任务

当使用 pgsql-rm.yml 剧本移除 PostgreSQL 集群时，会自动删除 postgres 用户的 crontab 文件。

./pgsql-rm.yml -l <cls> -t pg_crontab    # 仅移除定时任务
./pgsql-rm.yml -l <cls>                  # 移除整个集群（包含定时任务）

相关文档

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