Skip to content

Maintenance

Common system maintenance tasks
Common system maintenance tasks

Ensuring healthy and stable operation of Pigsty and PostgreSQL clusters requires routine maintenance work.

Regular Monitoring Review

Pigsty provides an out-of-the-box monitoring platform. We recommend reviewing monitoring dashboards daily to track system status. At minimum, we suggest weekly monitoring reviews, focusing on alert events to proactively avoid most failures and issues.

Here’s a list of predefined alert rules in Pigsty.

Failover Follow-up

Pigsty’s high availability architecture allows PostgreSQL clusters to automatically perform primary-replica switching, meaning operations and DBAs don’t require immediate intervention. However, users still need to perform follow-up tasks at appropriate times (e.g., next business day), including:

  • Investigate and confirm failure root cause to prevent recurrence
  • Optionally restore original primary-replica topology or update configuration manifest to match new state
  • Refresh load balancer configuration via bin/pgsql-svc to update service routing state
  • Refresh cluster HBA rules via bin/pgsql-hba to prevent primary-replica specific rule drift
  • If necessary, remove failed servers with bin/pgsql-rm and expand with new replicas using bin/pgsql-add

Bloat Control

Long-running PostgreSQL instances develop “table bloat” / “index bloat”, degrading system performance.

Regular online rebuilding of tables and indexes using pg_repack helps maintain optimal PostgreSQL performance. Pigsty installs and enables this extension by default in all databases, ready for immediate use.

You can check table and index bloat through Pigsty’s PGCAT Database - Table Bloat panel. Select tables and indexes with high bloat rates (larger tables with >50% bloat) for online reorganization using pg_repack:

pg_repack dbname -t schema.table

Normal reads/writes continue during reorganization, but the switch moment at completion requires an AccessExclusive lock, blocking all access. For high-throughput operations, schedule during low-traffic periods or maintenance windows. For more details, see: Managing Relation Bloat

VACUUM FREEZE

Freezing expired transaction IDs (VACUUM FREEZE) is a critical PostgreSQL maintenance task preventing transaction ID (XID) exhaustion outages. While PostgreSQL provides AutoVacuum mechanisms, for high-standard production environments, we recommend combining automatic and manual approaches, regularly executing database-wide VACUUM FREEZE to ensure XID safety.