Module: PGSQL

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

Reference

PGSQL

PGSQL is Pigsty’s core module. Ansible inventory declares PostgreSQL clusters; Patroni and etcd provide HA orchestration; 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. Exact defaults live only in the parameter reference so the module landing page does not become a second stale parameter snapshot.

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 current 575 packaged extensions and 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`

Real runs of pgsql.yml, pgsql-user.yml, pgsql-db.yml, and related playbooks change the target environment; pgsql-rm.yml can delete data and backups by default. Resolve the exact cluster/node and recent backup first. Removal also requires the operator to 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 tested backup and separate gates for shutdown, restore, data validation, timeline promotion, DCS rebuild, replica rebuild, and a fresh full backup.

Monitoring

The current source contains 29 PostgreSQL/PGCAT dashboards under files/grafana/pgsql, covering fleet, cluster, instance, database, table, query, session, transaction, replication, service, 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.