Module: PGSQL

Declare, deploy, expose, observe, back up, and manage PostgreSQL clusters with Pigsty v4.5.

PGSQL is the core Pigsty module. An Ansible inventory declares PostgreSQL clusters. Patroni and etcd manage high availability. pgBackRest provides backup and PITR. HAProxy, VIP, DNS, PgBouncer, and the observability stack expose and monitor database services.

This page follows the Pigsty v4.5.0 source layout. For exact defaults, use the parameter reference. Keeping defaults in one reference prevents conflicting or outdated copies.

Modeling and Configuration

Cluster model: clusters, instances, identity, and roles.
Architecture: relationships among Patroni, etcd, service access, and observability.
Cluster configuration: primary, replica, offline instance, synchronous commit, standby cluster, delayed cluster, and Citus.
Kernel: PostgreSQL major version, distribution, and package selection.
Users, databases, HBA, and ACL: business objects and access control.
Service access: read-write/read-only services, HAProxy, VIP, DNS, and pooling.
Extension catalog: the 600 currently packaged extensions and their platform coverage.

Deployment and Administration

Task	Entry point
Initialize a cluster or add an instance	Cluster administration · `pgsql.yml`
Create or change users	User administration · `pgsql-user.yml`
Create or change databases	Database administration · `pgsql-db.yml`
Change HBA or parameters	HBA administration · Component administration
Patroni switchovers, maintenance, and incidents	Patroni administration
Install, create, update, or remove extensions	Extension administration
Monitor an external instance	`pgsql-monitor.yml`
Prepare a migration	Migration · `pgsql-migration.yml`
Remove an instance or cluster	Safe removal workflow · `pgsql-rm.yml`

pgsql.yml, pgsql-user.yml, pgsql-db.yml, and related playbooks change the target environment when run. By default, pgsql-rm.yml can delete data and backups. First identify the exact cluster or node and verify a recent backup. For removal, the operator must also type and confirm the exact target.

Backup and Recovery

Backup and recovery overview: capabilities, boundaries, and entry points.
Mechanism and policy: base backups, WAL, recovery window, and retention.
Repository: local, S3/Silo, and other pgBackRest repositories.
Administration: status, checks, scheduling, and expiration.
Restore operations: cluster-level pgsql-pitr.yml, node-local pig pitr, and low-level pig pb restore.
Manual drill: validate PITR in explicit stages inside a disposable sandbox.

Restore is destructive. Production recovery requires an independent, recent backup that has passed a restore test. Confirm each stage separately: shutdown, restore, data validation, timeline promotion, DCS rebuild, replica rebuild, and a new full backup.

Monitoring

The current source contains 29 PostgreSQL and PGCAT dashboards in files/grafana/pgsql. They cover fleets, clusters, instances, databases, tables, queries, sessions, transactions, replication, services, PgBouncer, PITR, and alerts.

Parameter Groups

The PGSQL parameter reference is the single documentation source for v4.5.0 defaults and semantics:

PG_ID: cluster and instance identity.
PG_BUSINESS: users, databases, services, and other business objects.
PG_INSTALL: kernel, packages, and extensions.
PG_BOOTSTRAP: Patroni bootstrap, replication, and database initialization.
PG_PROVISION: in-database objects and privileges.
PG_BACKUP: pgBackRest and backup repositories.
PG_ACCESS: PgBouncer, services, VIP, and DNS.
PG_MONITOR: exporters, monitoring registration, and metrics.
PG_REMOVE: removal safeguards and cleanup scope.