Manage PostgreSQL Cron Jobs

Pigsty uses crontab to schedule routine backups, transaction freezing, and reorganization of bloated tables and indexes.

Quick Reference

For other management tasks, see: Backup Management, Monitoring System, HA Management.

Configure Cron Jobs

Use pg_crontab to configure cron jobs for the PostgreSQL database superuser. The pg_dbsu parameter selects this user; the default is postgres.

Example Configuration

The following pg-meta configuration schedules a full backup daily at 1:00 AM. The pg-test configuration schedules a full backup on Monday and incremental backups on other days.

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'

Recommended Maintenance Schedule

pg_crontab:
  - '00 01 * * * /pg/bin/pg-backup full'    # Daily full backup at 1:00 AM
  - '00 03 * * 0 /pg/bin/pg-vacuum'         # Weekly vacuum freeze on Sunday at 3:00 AM
  - '00 04 * * 1 /pg/bin/pg-repack'         # Weekly repack on Monday at 4:00 AM

Apply Cron Jobs

The pg_crontab task in pgsql.yml writes cron jobs to the default location for the operating system:

• EL (RHEL/Rocky/Alma): /var/spool/cron/postgres
• Debian/Ubuntu: /var/spool/cron/crontabs/postgres

./pgsql.yml -l pg-meta -t pg_crontab     # Apply pg_crontab config to specified cluster
./pgsql.yml -l 10.10.10.10 -t pg_crontab # Target specific host only

# Edit cron jobs as postgres user
sudo -u postgres crontab -e

# Or edit crontab file directly
sudo vi /var/spool/cron/postgres           # EL series
sudo vi /var/spool/cron/crontabs/postgres  # Debian/Ubuntu

Each playbook execution will fully overwrite the cron job configuration.

View Cron Jobs

Execute the following command as the pg_dbsu OS user to view cron jobs:

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

For explanations of crontab syntax, see Crontab Guru.

pg-backup

pg-backup is the Pigsty physical backup script. It uses pgBackRest and supports full, differential, and incremental backups.

Basic Usage

pg-backup                # Execute incremental backup (default), auto full if no existing full backup
pg-backup full           # Execute full backup
pg-backup diff           # Execute differential backup (based on most recent full backup)
pg-backup incr           # Execute incremental backup (based on most recent any backup)

Backup Types

Execution Requirements

• Script must run on primary as postgres user
• Script auto-detects current node role, exits (exit 1) when run on replica
• Auto-retrieves stanza name from /etc/pgbackrest/pgbackrest.conf

Common Cron Configurations

pg_crontab:
  - '00 01 * * * /pg/bin/pg-backup full'    # Daily full backup at 1:00 AM

pg_crontab:
  - '00 01 * * 1            /pg/bin/pg-backup full'  # Monday full backup
  - '00 01 * * 2,3,4,5,6,7  /pg/bin/pg-backup'       # Other days incremental

pg_crontab:
  - '00 01 * * 1            /pg/bin/pg-backup full'  # Monday full backup
  - '00 01 * * 2,3,4,5,6,7  /pg/bin/pg-backup diff'  # Other days differential

For more backup and recovery operations, see the Backup Management section.

pg-vacuum

pg-vacuum is the Pigsty transaction freeze script. It runs VACUUM FREEZE to prevent database shutdown caused by transaction ID (XID) wraparound.

Basic Usage

pg-vacuum                    # Freeze aging tables in all databases
pg-vacuum mydb               # Process specified database only
pg-vacuum mydb1 mydb2        # Process multiple databases

pg-vacuum -n mydb            # Dry run mode, display only without executing
pg-vacuum -a 80000000 mydb   # Use custom age threshold (default 100M)
pg-vacuum -r 50 mydb         # Use custom aging ratio threshold (default 40%)

-- Execute VACUUM FREEZE on entire database
VACUUM FREEZE;

-- Execute VACUUM FREEZE on specific table
VACUUM FREEZE schema.table_name;

Command Options

Logic

1. Check the database datfrozenxid age. Skip the database if its age is below the threshold.
2. Calculate the aging page ratio: table pages above the age threshold as a percentage of all pages.
3. If the aging ratio exceeds 40%, run VACUUM FREEZE ANALYZE on the full database.
4. Otherwise, run VACUUM FREEZE ANALYZE only on tables above the age threshold.

Script sets vacuum_cost_limit = 10000 and vacuum_cost_delay = 1ms to control I/O impact.

Execution Requirements

• Script must run on primary as postgres user
• Uses file lock /tmp/pg-vacuum.lock to prevent concurrent execution
• Auto-skips template0, template1, postgres system databases

Common Cron Configuration

pg_crontab:
  - '00 03 * * 0 /pg/bin/pg-vacuum'     # Weekly Sunday at 3:00 AM

pg-repack

pg-repack is the Pigsty bloat maintenance script. It uses the pg_repack extension to reorganize bloated tables and indexes online.

Basic Usage

pg-repack                    # Reorganize bloated tables and indexes in all databases
pg-repack mydb               # Reorganize specified database only
pg-repack mydb1 mydb2        # Reorganize multiple databases

pg-repack -n mydb            # Dry run mode, display only without executing
pg-repack -t mydb            # Reorganize tables only
pg-repack -i mydb            # Reorganize indexes only
pg-repack -T 30 -j 4 mydb    # Custom lock timeout (seconds) and parallelism

# Use pg_repack command directly to reorganize specific table
pg_repack dbname -t schema.table

# Use pg_repack command directly to reorganize specific index
pg_repack dbname -i schema.index

Command Options

Auto-Selection Thresholds

Script auto-selects objects to reorganize based on table/index size and bloat ratio:

Table Bloat Thresholds

Index Bloat Thresholds

The script skips tables and indexes larger than 64GB and shows a warning. Handle these objects manually.

Execution Requirements

• Script must run on primary as postgres user
• Requires pg_repack extension installed (installed by default in Pigsty)
• Requires pg_table_bloat and pg_index_bloat views in monitor schema
• Uses file lock /tmp/pg-repack.lock to prevent concurrent execution
• Auto-skips template0, template1, postgres system databases

Common Cron Configuration

pg_crontab:
  - '00 04 * * 1 /pg/bin/pg-repack'     # Weekly Monday at 4:00 AM

Use the PGCAT Database - Table Bloat panel to check database bloat. Then select tables and indexes with high bloat for reorganization.

For more details see: Managing Relation Bloat

Remove Cron Jobs

When pgsql-rm.yml removes a PostgreSQL cluster, it automatically deletes the postgres user crontab file.

./pgsql-rm.yml -l <cls> -t pg_crontab    # Remove cron jobs only
./pgsql-rm.yml -l <cls> --check          # Preflight full removal; execute only after backup and target confirmation

Related Documentation

• Backup Management: PostgreSQL backup and recovery
• Monitoring System: PostgreSQL monitoring and alerting
• Cluster Management: Cluster creation, scaling, and teardown
• Patroni Management: HA cluster management