PGSQL Arch

PGSQL organizes PostgreSQL as clusters. Each cluster is a logical group of database instances connected through primary-replica relationships.

Overview

The PGSQL module combines these components for production HA clusters:

Enable vip-manager when needed. PGSQL also uses components from other modules:

The PGSQL module combines the PostgreSQL engine with supporting services. Patroni and Etcd provide HA. pgBackRest and optional Silo provide backups. HAProxy, Pgbouncer, and vip-manager provide access. Exporters and Vector provide monitoring.

You can select other database kernels and extensions. The diagram represents the engine as a CPU and the complete service as a computer.

Component Interaction

• Cluster DNS is resolved by DNSMASQ on infra nodes
• Cluster VIP is managed by vip-manager, which binds pg_vip_address to the cluster primary node.
  • vip-manager gets cluster leader info written by patroni from the etcd cluster
• Cluster services are exposed by HAProxy on nodes, different services distinguished by node ports (543x).
  • HAProxy port 9101: Monitoring metrics & statistics & admin page
  • HAProxy port 5433: Routes to primary pgbouncer: read-write service
  • HAProxy port 5434: Routes to replica pgbouncer: read-only service
  • HAProxy port 5436: Routes to primary postgres: default service
  • HAProxy port 5438: Routes to offline postgres: offline service
  • HAProxy routes traffic based on health check info from patroni.
• Pgbouncer is connection pooling middleware, listening on port 6432 by default, buffering connections, exposing additional metrics, and providing extra flexibility.
  • Pgbouncer is stateless and deployed 1:1 with Postgres via local Unix socket.
  • Production traffic (primary/replica) goes through pgbouncer by default (can specify bypass via pg_default_service_dest)
  • Default/offline services always bypass pgbouncer and connect directly to target Postgres.
• PostgreSQL listens on port 5432, providing relational database services
  • Installing PGSQL module on multiple nodes with the same cluster name automatically forms an HA cluster via streaming replication
  • PostgreSQL process is managed by patroni by default.
• Patroni listens on port 8008 by default, supervising PostgreSQL server processes
  • Patroni starts Postgres server as child process
  • Patroni uses etcd as DCS: stores config, failure detection, and leader election.
  • Patroni provides Postgres info (e.g., primary/replica) via health checks, HAProxy uses this to distribute traffic
• pg_exporter exposes postgres monitoring metrics on port 9630
• pgbouncer_exporter exposes pgbouncer metrics on port 9631
• pgBackRest uses local backup repository by default (pgbackrest_method = local)
  • If using local (default), pgBackRest creates local repository under pg_fs_bkup on primary node
  • If using minio, pgBackRest creates the backup repository on dedicated Silo or an external S3 service
• Vector collects Postgres-related logs (postgres, pgbouncer, patroni, pgbackrest)
  • vector listens on port 9598, also exposes its own metrics to VictoriaMetrics on infra nodes
  • vector sends logs to VictoriaLogs on infra nodes

HA Subsystem

Patroni and etcd provide the HA subsystem. They detect failures, coordinate failover, and manage configuration.

Operation: Patroni manages each local PostgreSQL process. It writes the leader, members, and configuration to etcd. After primary failure, Patroni coordinates an election and promotes the healthiest replica. Recovery is automatic, with RTO typically below 45 seconds.

Key Interactions:

• PostgreSQL: Starts, stops, reloads PG as parent process, controls its lifecycle
• etcd: External dependency, writes/watches leader key for distributed consensus and failure detection
• HAProxy: Provides health checks via REST API (:8008), reporting instance role
• vip-manager: Watches leader key in etcd, auto-migrates VIP

For more information, see: High Availability and Config: PGSQL - PG_BOOTSTRAP

Access Subsystem

HAProxy, Pgbouncer, and vip-manager expose services, route traffic, and pool connections.

There are multiple access methods. A typical traffic path is: Client → DNS/VIP → HAProxy (543x) → Pgbouncer (6432) → PostgreSQL (5432)

Service Ports:

• 5433 primary: Read-write service, routes to primary Pgbouncer
• 5434 replica: Read-only service, routes to replica Pgbouncer
• 5436 default: Default service, direct to primary (bypasses pool)
• 5438 offline: Offline service, direct to offline replica (ETL/analytics)

Key Features:

• HAProxy uses Patroni REST API to determine instance role, auto-routes traffic
• Pgbouncer uses transaction-level pooling, absorbs connection spikes, reduces PG connection overhead
• vip-manager watches etcd leader key, auto-migrates VIP during failover

For more information, see: Service Access and Config: PGSQL - PG_ACCESS

Backup Subsystem

pgBackRest provides backups and PITR. Optional Silo or external S3 storage can serve as the remote repository.

Backup Types:

• Full backup: Complete database copy
• Incremental/differential backup: Only backs up changed data blocks
• WAL archiving: Continuous transaction log archiving, enables any point-in-time recovery

Storage Backends:

• local (default): Local disk, backups stored at pg_fs_bkup mount point
• minio: S3-compatible object storage, supports centralized backup management and off-site DR

Key Interactions:

• pgBackRest → PostgreSQL: Executes backup commands, manages WAL archiving
• pgBackRest → Patroni: Recovery can bootstrap replicas as new primary or standby
• pgbackrest_exporter → VictoriaMetrics: Exports backup status metrics through the Prometheus-compatible protocol to monitor backup health

For more information, see: PITR, Backup & Recovery, and Config: PGSQL - PG_BACKUP

Observability Subsystem

Three Exporters collect metrics, and Vector collects logs.

Data Flow:

• Metrics: Exporter → VictoriaMetrics (INFRA) → Grafana dashboards
• Logs: Vector → VictoriaLogs (INFRA) → Grafana log queries

pg_exporter and pgbouncer_exporter use local Unix sockets, independently of HA topology. Slim installation can disable them.

For more information, see: Config: PGSQL - PG_MONITOR

PostgreSQL

PostgreSQL is the PGSQL database engine. It listens on 5432 by default, with one instance per node.

Pigsty supports PostgreSQL 14–18 through binary packages from the PGDG repository. You can replace the default engine with a PG-compatible kernel. Up to 600 extensions are available.

Patroni manages PostgreSQL processes for HA by default. A single-node cluster has one primary. Additional nodes automatically join as replicas and receive changes through real-time physical replication. Replicas serve read-only traffic and can automatically replace a failed primary.

You can access PostgreSQL directly, or through HAProxy and Pgbouncer connection pool.

For more information, see: Config: PGSQL - PG_BOOTSTRAP

Patroni

Patroni is the PostgreSQL HA control component, listening on port 8008 by default.

Patroni manages PostgreSQL startup, shutdown, configuration, and health. It writes leader and member records to etcd. It coordinates failover, replication factor, and parameter changes. Its REST API serves HAProxy, monitoring, and administrators.

HAProxy checks Patroni endpoints to identify instance roles and route primary or replica traffic. vip-manager watches the etcd leader key and moves the VIP when the primary changes.

For more information, see: Config: PGSQL - PG_BOOTSTRAP

Pgbouncer

Pgbouncer provides connection pooling on port 6432 by default. Each node has one Pgbouncer instance for its PostgreSQL instance.

Pgbouncer is stateless and connects to local PostgreSQL through a Unix socket. Its default transaction pooling handles connection bursts, stabilizes session counts, and reduces lock contention. This improves performance under high concurrency.

By default, read/write 5433 and read-only 5434 traffic use Pgbouncer. Default 5436 and offline 5438 services connect directly to PostgreSQL.

pgbouncer_poolmode defaults to transaction. Disable pooling with pgbouncer_enabled.

For more information, see: Config: PGSQL - PG_ACCESS

pgBackRest

pgBackRest provides full, incremental, and differential PostgreSQL backups and WAL archiving.

Pigsty uses pgBackRest for PITR to any time within the backup retention window.

pgBackRest coordinates with PostgreSQL for backups and archiving. By default, it creates a repository on the primary: pgbackrest_method = local. Configure Silo or external S3 storage for centralized backup management.

pgbackrest_init_backup can trigger the first full backup after initialization. Recovery integrates with Patroni to initialize replicas as new primaries or standbys.

For more information, see: Backup & Recovery and Config: PGSQL - PG_BACKUP

HAProxy

HAProxy is the service entry point and load balancer, exposing multiple database service ports.

HAProxy checks Patroni REST endpoints to identify roles and route primary or replica traffic. pg_default_services and pg_services define services.

By default, local HAProxy instances expose services. For higher traffic, use pg_service_provider to select a dedicated HAProxy node group.

For more information, see: Service Access and Config: PGSQL - PG_ACCESS

vip-manager

vip-manager binds L2 VIP to the current primary node. This is an optional component; enable it if your network supports L2 VIP.

vip-manager runs on each PG node. It watches the leader key that Patroni writes to etcd. It binds pg_vip_address to the primary network interface. During failover, it releases the old binding and immediately binds the VIP to the new primary.

Enable this optional component with pg_vip_enabled. All nodes must share one VLAN or VIP migration fails. Public clouds usually do not support L2 VIP. Use it for on-premises or private cloud deployments.

For more information, see: Tutorial: VIP Configuration and Config: PGSQL - PG_ACCESS

pg_exporter

pg_exporter exports PostgreSQL monitoring metrics, listening on port 9630 by default.

pg_exporter connects to local PostgreSQL through a Unix socket. It exports sessions, buffer hits, replication lag, transaction rates, and other metrics. VictoriaMetrics on infrastructure nodes collects them.

Set collection rules with pg_exporter_config. Use pg_exporter_auto_discovery for automatic database discovery. pg_exporter_cache_ttls controls different cache lifetimes.

Disable this component through parameters. Slim installation leaves it disabled.

For more information, see: Config: PGSQL - PG_MONITOR

pgbouncer_exporter

pgbouncer_exporter exports Pgbouncer connection pool metrics, listening on port 9631 by default.

pgbouncer_exporter uses the pg_exporter binary with separate metric configuration. It supports pgbouncer 1.8–1.25+. Pgbouncer statistics views provide pool utilization, wait queue, and hit rate metrics.

Disabling Pgbouncer also disables its exporter. Slim installation leaves the exporter disabled.

For more information, see: Config: PGSQL - PG_MONITOR

pgbackrest_exporter

pgbackrest_exporter exports backup status metrics, listening on port 9854 by default.

pgbackrest_exporter reads pgBackRest status to report the latest backup time, size, type, and other metrics. Alert rules can detect expired or failed backups. Large repositories or many backups can make collection expensive. The default collection interval is 2 minutes, so a completed backup can take 2 minutes to appear in monitoring.

For more information, see: Config: PGSQL - PG_MONITOR

etcd

etcd is a distributed consistent store (DCS), providing cluster metadata storage and leader election capability for Patroni.

The separate ETCD module manages etcd. PGSQL relies on it for HA. Patroni stores cluster state, leader information, and configuration there so nodes can reach consensus. vip-manager reads the leader key to move the VIP automatically.

For more information, see: ETCD Module

vector

The NODE module deploys Vector to collect PostgreSQL-related logs efficiently.

Vector reads node log directories for PostgreSQL, Pgbouncer, Patroni, and pgBackRest. It sends structured records to infrastructure-node VictoriaLogs for central storage and queries.

For more information, see: NODE Module