Concepts

• 1: Architecture
  • 1.1: Nodes
  • 1.2: Infrastructure
  • 1.3: PGSQL Arch
• 2: ER Model
  • 2.1: E-R Model of Infra Cluster
  • 2.2: E-R Model of PostgreSQL Cluster
  • 2.3: E-R Model of Etcd Cluster
  • 2.4: MINIO Cluster Model
  • 2.5: E-R Model of Redis Cluster
• 3: Infra as Code
  • 3.1: Inventory
  • 3.2: Configure
  • 3.3: Parameters
  • 3.4: Conf Templates
  • 3.5: Use CMDB as Config Inventory
• 4: High Availability
  • 4.1: RPO Trade-offs
  • 4.2: Failure Model
    • 4.2.1: Model of Patroni Passive Failure
    • 4.2.2: Model of Patroni Active Failure
    • 4.2.3: Network Partition
  • 4.3: RTO Trade-offs
  • 4.4: Service Access
• 5: Point-in-Time Recovery — A Time Machine for PostgreSQL
  • 5.1: How PITR Works
  • 5.2: PITR Architecture
  • 5.3: PITR Tradeoffs
  • 5.4: Declarative Recovery
  • 5.5: PITR Scenarios
• 6: Monitoring System
• 7: Security and Compliance
  • 7.1: Security Model
  • 7.2: Authentication
  • 7.3: Access Control
  • 7.4: Encrypted Communication
  • 7.5: Data Security
  • 7.6: Compliance

1 - Architecture

Pigsty uses a modular architecture and declarative interface. Combine modules to meet your requirements.

• Use one or more modules from the modular design.
• Describe the environment with a configuration inventory and parameters. Apply it with Ansible playbooks.
• Run Pigsty on physical or virtual nodes with a compatible OS.

Modules

Pigsty uses a modular design with six main default modules: PGSQL, INFRA, NODE, ETCD, REDIS, and MINIO.

• PGSQL: Self-healing HA Postgres clusters powered by Patroni, Pgbouncer, HAproxy, PgBackrest, and more.
• INFRA: Local software repo, Nginx, Grafana, Victoria, AlertManager, Blackbox Exporter—the complete observability stack.
• NODE: Tune nodes to desired state—hostname, timezone, NTP, ssh, sudo, haproxy, docker, vector, keepalived.
• ETCD: Distributed key-value store as DCS for HA Postgres clusters: consensus leader election/config management/service discovery.
• REDIS: Redis servers supporting standalone primary-replica, sentinel, and cluster modes with full monitoring.
• MINIO: S3-compatible simple object storage that can serve as an optional backup destination for PG databases.

Combine modules through declarative configuration. For host monitoring, install INFRA on infrastructure nodes and NODE on managed nodes. Install ETCD and PGSQL across multiple nodes to form an HA PostgreSQL cluster automatically. You can reuse Pigsty infrastructure for custom modules; see REDIS and MINIO as examples. Compatibility modes, such as PostgreSQL Mongo mode, combine standard PGSQL and Docker APP workflows.

Every module requires NODE. Install it first on managed nodes. By default, NODE uses the local software repository and thus has a weak dependency on INFRA. The deploy.yml playbook bootstraps administration/infrastructure nodes with INFRA to resolve this circular dependency.

Standalone Installation

By default, Pigsty installs on one physical or virtual node. deploy.yml installs INFRA, ETCD, PGSQL, and optional MINIO on the current node. Monitoring includes VictoriaMetrics, VictoriaLogs, VictoriaTraces, Grafana, Alertmanager, and Blackbox Exporter. A standalone PostgreSQL instance serves as the CMDB: cluster pg-meta, database meta.

This node has monitoring, visualization, and PostgreSQL with automatic PITR configuration. One node cannot provide HA. Use it for development, tests, demonstrations, and data visualization or analysis. It can also serve as an administration node for additional nodes.

Monitoring

Use the standalone meta node as an administration node and monitoring center for additional nodes and database servers.

You can use Pigsty monitoring independently. It configures VictoriaMetrics/Grafana with dashboards for host nodes and PostgreSQL databases. A small configuration enables production monitoring and alerts for existing hosts and databases, including those managed outside Pigsty.

HA PostgreSQL Clusters

Pigsty lets you operate your own production HA PostgreSQL RDS service in your chosen environment.

To create an HA cluster, describe it in configuration and run the playbook:

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 3, pg_role: replica }
  vars: { pg_cluster: pg-test }

$ bin/pgsql-add pg-test  # Initialize cluster 'pg-test'

In less than 10 minutes, the cluster has service access, monitoring, backup PITR, and HA configured.

Patroni, etcd, and HAProxy provide automatic recovery from hardware failure. By default, primary failover completes within 45 seconds. HAProxy uses Patroni health checks to route read/write traffic to the new primary and avoid split brain. Clients need no configuration change or application restart. Replica failures and planned switchovers cause only a brief interruption to the current query.

pgBackRest and optional Silo provide local and cloud PITR for software failures, human errors, and datacenter disasters. They also support cross-region replication and disaster recovery after datacenter failure.

1.1 - Nodes

A node represents hardware resources and an operating system. It can be a physical server, virtual machine, container, or pod.

A node needs a Linux OS, systemd, and standard CPU, memory, disk, and network resources.

Install modules on nodes. The installed modules determine the node type:

In a single-node deployment, one node has all roles: regular, administration, infrastructure, ETCD, and database.

Regular Node

Use node.yml to configure managed nodes to the target state. A regular node can run these services:

node_exporter, vector, and haproxy are enabled by default. They export host metrics, collect logs, and balance traffic. Docker, keepalived, and keepalived_exporter are optional.

ADMIN Node

A deployment has exactly one administration node that runs Ansible playbooks and issues deployment or management commands.

This node has ssh/sudo access to every other node. Restrict access carefully. See Security Model: Trust Boundaries for its trusted access and critical assets.

During single-node installation and configuration, the current node becomes the administration node. A laptop with Ansible and SSH access to all managed nodes can also serve this role. This is not recommended for production.

For example, a laptop managing a cloud Pigsty VM is the administration node.

Production installations usually reserve 1–2 DBA machines for administration. With limited resources, INFRA nodes can also perform administration. All infrastructure nodes have Ansible by default.

INFRA Node

A Pigsty deployment may have 1 or more INFRA nodes; large production environments typically have 2-3.

The infra group in the inventory defines which nodes are INFRA nodes. These nodes run the INFRA module with these components:

Nginx provides the module Web UI and software repository. Services on multiple infrastructure nodes are independent. Each Grafana can access all monitoring data sources.

Pigsty is licensed under Apache-2.0, though embedded Grafana component uses AGPLv3.

ETCD Node

The ETCD module provides Distributed Consensus Service (DCS) for PostgreSQL high availability.

The etcd group in the inventory defines ETCD nodes. These nodes run etcd servers on two ports:

MINIO Node

The MINIO module provides optional backup storage for PostgreSQL.

The minio inventory group defines MINIO module nodes. In v4.5.0, these nodes run Silo servers on:

PGSQL Node

A PGSQL node has the PGSQL module. Each node runs one PostgreSQL instance, giving a 1:1 mapping.

node_id_from_pg lets a PGSQL node use its PostgreSQL instance name. The default is true.

PGSQL nodes run these additional components beyond regular node services:

vip-manager runs only when a PG VIP is configured. Define custom services through pg_services. haproxy exposes them on additional ports.

Node Relationships

Regular nodes normally select an INFRA node with admin_ip. For example, global admin_ip = 10.10.10.10 directs nodes to services at that address.

Parameters that reference ${admin_ip}:

The administration node usually also provides infrastructure. With multiple infrastructure nodes, the first usually administers the environment and others provide backups.

Large deployments can separate administration from infrastructure. For example, use 1–2 small DBA-managed administration hosts and 2–3 high-capacity physical monitoring servers.

Typical node counts by deployment scale:

1.2 - Infrastructure

Production HA PostgreSQL needs monitoring, alerts, logs, time synchronization, DNS, and software repositories. The INFRA module provides these services. It is optional but strongly recommended.

Overview

The following single-node deployment diagram shows INFRA components on the right:

Nginx

Nginx provides access to Pigsty WebUI services. HTTP and HTTPS use 80 and 443 by default. See the Live Demo.

Nginx exposes the Grafana, VictoriaMetrics (VMUI), AlertManager, and HAProxy web interfaces. It also serves the local software repository and other static files.

Nginx configures local web servers or reverse proxy servers based on definitions in infra_portal.

infra_portal:
  home : { domain: i.pigsty }

The default administration homepage is i.pigsty. Its endpoints proxy these components:

Configure Nginx as a file server or reverse proxy. HTTPS supports self-signed and publicly trusted certificates.

For more information, see: Tutorial: Nginx—Expose Web Services via Proxy and Tutorial: Certbot—Request and Renew HTTPS Certificates

Repo

During installation, Pigsty creates a local software repository on the infrastructure node to speed later installations. See the Live Demo.

The default directory is /www/pigsty. Nginx serves it at /pigsty:

For offline installation, copy a prepared local software repository into the target environment. If /www/pigsty/repo_complete exists, deployment skips upstream downloads and uses that repository. sow generates this file as a completion marker and SHA-256 manifest. To force a rebuild, run ./infra.yml -t repo_build -e repo_build=true.

For more information, see: Config: INFRA - REPO

Grafana

Grafana visualizes Pigsty metrics, logs, and other monitoring data. See the Live Demo.

Grafana listens on port 3000 by default and is proxied via Nginx at the /ui path:

Pigsty includes dashboards for VictoriaMetrics, Logs, and Traces. URL links let you move between summary and detail views for diagnosis.

Grafana also supports low-code visualization. Pigsty installs ECharts, victoriametrics-datasource, and victorialogs-datasource plugins by default. Vector/Victoria data sources use the names vmetrics-*, vlogs-*, and vtraces-* for custom dashboards.

For more information, see: Config: INFRA - GRAFANA.

VictoriaMetrics

VictoriaMetrics collects and stores Pigsty monitoring metrics as time-series data. See the Live Demo.

The default port is 8428. Access it through Nginx at /vmetrics or the p.pigsty domain:

VictoriaMetrics supports the Prometheus API, PromQL, remote read/write protocols, and the Alertmanager API. Its VMUI supports direct queries for metric exploration. It also supplies data to Grafana.

For more information, see: Config: INFRA - VMETRICS

VictoriaLogs

VictoriaLogs centrally stores structured logs from all Pigsty nodes. See the Live Demo.

It listens on port 9428 by default, mounted at Nginx /vlogs path:

Managed nodes run Vector by default. It collects system, PostgreSQL, Patroni, Pgbouncer, and other logs, structures them, and sends them to VictoriaLogs. The built-in Web UI supports search and filtering. Use the Grafana victorialogs-datasource plugin for visual analysis.

For more information, see: Config: INFRA - VLOGS

VictoriaTraces

VictoriaTraces is used for collecting trace data and slow SQL records. Live Demo

It listens on port 10428 by default, mounted at Nginx /vtraces path:

VictoriaTraces provides a Jaeger-compatible interface for service traces and slow SQL queries. Use it with Grafana to locate performance limits and trace their causes.

For more information, see: Config: INFRA - VTRACES

VMAlert

VMAlert evaluates alert rules and sends triggered events to Alertmanager. See the Live Demo.

It listens on port 8880 by default, mounted at Nginx /vmalert path:

VMAlert periodically evaluates rules against VictoriaMetrics data. Pigsty includes rules for common failures in PGSQL, NODE, REDIS, and other modules.

For more information, see: Config: INFRA - VMALERT

AlertManager

AlertManager handles alert event aggregation, deduplication, grouping, and dispatch. Live Demo

It listens on port 9059 by default, mounted at Nginx /alertmgr path, and also accessible via the a.pigsty domain:

AlertManager supports email, Webhook, Slack, PagerDuty, WeChat Work, and other notification channels. Routing rules select destinations by severity and module. Silencing and inhibition are also supported.

For more information, see: Config: INFRA - AlertManager

BlackboxExporter

Blackbox Exporter probes targets to check reachability.

It listens on port 9115 by default, mounted at Nginx /blackbox path:

Probe methods include ICMP Ping, TCP ports, and HTTP/HTTPS endpoints. Check VIP reachability, service ports, and external dependencies to determine the scope of a failure.

For more information, see: Config: INFRA - BLACKBOX

Ansible

Ansible runs Pigsty deployment, configuration, and management playbooks.

Installation adds Ansible to the administration/infrastructure node. Declarative configuration specifies the target state. Idempotent playbooks can run repeatedly to reach that state without additional side effects.

Ansible provides:

• Agentless execution: Connect through SSH without installing an agent on managed nodes.
• Declarative configuration: Describe the target state; configuration also documents it.
• Idempotent operations: Repeated runs produce consistent results and can retry partial failures.

For more information, see: Playbooks: Pigsty Playbook

DNSMASQ

DNSMASQ provides DNS resolution on INFRA nodes, resolving domain names to their corresponding IP addresses.

DNSMASQ listens on port 53 (UDP/TCP) by default, providing DNS resolution for all nodes. Records are stored in the /etc/dnsmasq.d/pigsty directory.

Other modules register domain names with DNSMASQ during deployment. Client nodes can use infrastructure nodes as DNS servers and access services by name. DNS is optional; Pigsty also works without it.

• dns_records: Default DNS records written to INFRA nodes
• node_dns_servers: Configure DNS servers for nodes, defaults to INFRA node via admin_ip (can also be disabled)

For more information, see: Config: INFRA - DNS and Tutorial: DNS—Configure Domain Resolution

Chronyd

Chronyd synchronizes node clocks through NTP. Its time-source service uses UDP port 123 by default.

Distributed systems need synchronized clocks. Logs need comparable timestamps, certificate checks need accurate time, and PostgreSQL streaming replication is sensitive to clock drift. In isolated networks, other nodes can synchronize to an infrastructure node acting as their NTP server.

All nodes run chronyd by default, using public pool.ntp.org servers. The Node module manages chronyd. In isolated networks, use admin_ip to select the INFRA node as the internal time source.

For more information, see: Config: NODE - TIME

INFRA Node vs Regular Node

In Pigsty, the relationship between nodes and infrastructure is a weak circular dependency: node_monitor → infra → node

NODE does not itself require INFRA. Its node_monitor functions require the infrastructure monitoring platform and services.

infra.yml and deploy resolve this dependency through these steps:

• Initialize NODE on regular nodes. Skip monitoring because the infrastructure is not ready.
• Initialize INFRA on the INFRA node to provide monitoring.
• Configure monitoring on regular nodes to connect to that platform.

Alternatively, use phased deployment: initialize infrastructure nodes before regular nodes.

How Are Nodes Coupled to Infrastructure?

Regular nodes use admin_ip to select an INFRA node for infrastructure services.

For example, global admin_ip = 10.10.10.10 normally directs all nodes to infrastructure at that address.

Change this address to switch infrastructure providers for many nodes. These parameters may reference ${admin_ip}:

For example, local uses the Nginx software repository at admin_ip:80/pigsty. DNS uses DNSMASQ at admin_ip:53. Both choices are optional. Nodes can install from Internet repositories, as most single-node templates do. They can also leave DNS unconfigured because Pigsty does not require it.

INFRA Node vs ADMIN Node

The ADMIN node usually also serves as the INFRA node, including in single-node deployments. With multiple infrastructure nodes, the first infra member usually administers the environment and others provide backups. You can also separate the roles.

For example, large production deployments can use 1–2 small DBA-managed VMs for administration. Use 2–3 high-capacity physical servers, or more, for monitoring infrastructure. Set admin_ip to an infrastructure node address. The parameter retains its historical name from when administration and infrastructure roles were combined.

For local management of cloud nodes, install Ansible on your laptop and select cloud servers as managed targets. The laptop is the administration node. Cloud servers provide infrastructure.

all:
  children:
    infra:   { hosts: { 10.10.10.10: { infra_seq: 1 , ansible_host: your_ssh_alias } } }  # <--- Use ansible_host to point to cloud node (fill in ssh alias)
    etcd:    { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }    # SSH connection will use: ssh your_ssh_alias
    pg-meta: { hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }, vars: { pg_cluster: pg-meta } }
  vars:
    version: v4.5.0
    admin_ip: 10.10.10.10
    region: default

Multiple INFRA Nodes

One infrastructure node usually meets the requirements. Failure of its INFRA module does not interrupt database services on other nodes.

For higher monitoring and alerting availability, use multiple infrastructure nodes. Two nodes can provide redundancy and monitor each other. A distributed Victoria cluster can use more nodes for horizontal scaling without a fixed limit.

Each infrastructure node operates independently. Nginx uses local services, and VictoriaMetrics collects metrics from every environment service. Nodes send logs to all VictoriaLogs endpoints by default. Grafana is the exception: each instance registers every VictoriaMetrics, Logs, Traces, and PostgreSQL instance as a data source. Thus, each Grafana can access all monitoring data.

Dashboard and data-source changes affect only the Grafana instance you edit. For shared configuration, use a PostgreSQL database as Grafana storage. See Configure Grafana High Availability.

1.3 - 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

2 - ER Model

A deployment is the largest entity in Pigsty. This entity-relationship diagram shows its main entities and their relationships:

A deployment is also an environment. Examples include Production (Prod), User Acceptance Testing (UAT), Staging, Testing, and Development (Devbox). Each environment has a Pigsty inventory that describes all its entities and attributes.

An environment usually has shared infrastructure (INFRA). This includes ETCD for the HA DCS and MINIO for centralized backup storage. Infrastructure serves multiple PostgreSQL clusters and other database modules. Deployments without infrastructure are also possible.

Pigsty organizes almost all database modules into clusters. Each cluster is an Ansible group with node resources. PostgreSQL HA, Redis, Etcd, and Silo use this model. An environment can contain multiple clusters.

• PostgreSQL Cluster
• ETCD Cluster
• MinIO Cluster
• Redis Cluster
• INFRA Nodes

2.1 - E-R Model of Infra Cluster

The INFRA module is a management hub made of infrastructure nodes, rather than a traditional cluster. It provides core services for the deployment. Each node is an autonomous service unit that runs components such as Nginx, Grafana, and VictoriaMetrics. Together, these nodes provide observability and management for database clusters.

There are two core entities in Pigsty’s INFRA module:

• Node: A server that runs infrastructure components. It can be a physical server, VM, container, or Pod.
• Component: An infrastructure service on a node, such as Nginx, Grafana, or VictoriaMetrics.

INFRA nodes usually also serve as administration nodes, which form the Pigsty control plane.

Component Composition

Each INFRA node runs the following core components:

These components together form Pigsty’s observability infrastructure.

Examples

This example defines a two-node INFRA deployment:

infra:
  hosts:
    10.10.10.10: { infra_seq: 1 }
    10.10.10.11: { infra_seq: 2 }

The configuration defines these entities:

For production, use at least two INFRA nodes for component redundancy.

Identity Parameters

The INFRA_ID parameter group assigns deterministic identities to INFRA entities. One parameter is required:

Set the node sequence number at node level. Pigsty generates unique identifiers with these rules:

INFRA assigns infra-N identifiers to distinguish infrastructure nodes in monitoring. This does not change their hostname or system identity. Nodes retain their existing hostname or IP address for identification.

Service Portal

INFRA nodes provide web access through Nginx. infra_portal defines the services that Nginx exposes.

The default configuration only defines the home server:

infra_portal:
  home : { domain: i.pigsty }

Pigsty configures reverse proxy endpoints for enabled components, including Grafana, VictoriaMetrics, and AlertManager. To access them through separate domains, add explicit configuration:

infra_portal:
  home         : { domain: i.pigsty }
  grafana      : { domain: g.pigsty, endpoint: "${admin_ip}:3000", websocket: true }
  prometheus   : { domain: p.pigsty, endpoint: "${admin_ip}:8428" }   # VMUI
  alertmanager : { domain: a.pigsty, endpoint: "${admin_ip}:9059" }

Use domain names to access Pigsty services where possible, instead of an IP address and port.

Deployment Scale

The number of INFRA nodes depends on deployment scale and HA requirements:

In a single-node deployment, INFRA shares a node with PGSQL, ETCD, and other modules. Small deployments usually use INFRA nodes as the administration node, backup administration node, and local software repository (/www/pigsty). Larger deployments can assign these responsibilities to dedicated nodes.

Monitoring Label System

Pigsty collects metrics from INFRA components themselves. Unlike database modules, INFRA treats each component as a separate monitoring object. The cls (class) label distinguishes component types.

A two-node INFRA deployment with infra_seq: 1 and infra_seq: 2 uses these component labels:

All INFRA component metrics have job="infra". The cls label distinguishes components:

nginx_up{cls="nginx", ins="nginx-1", ip="10.10.10.10", job="infra"}
grafana_info{cls="grafana", ins="grafana-1", ip="10.10.10.10", job="infra"}
vm_app_version{cls="vmetrics", ins="vmetrics-1", ip="10.10.10.10", job="infra"}
vlogs_rows_ingested_total{cls="vlogs", ins="vlogs-1", ip="10.10.10.10", job="infra"}
alertmanager_alerts{cls="alertmanager", ins="alertmanager-1", ip="10.10.10.10", job="infra"}

2.2 - E-R Model of PostgreSQL Cluster

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

Each cluster is an autonomous business unit with at least one primary instance. It exposes database capabilities through services.

There are four core entities in Pigsty’s PGSQL module:

• Cluster: An autonomous PostgreSQL business unit and the top-level namespace for other entities.
• Service: A named abstraction that exposes capabilities and routes traffic through node ports.
• Instance: One PostgreSQL server, including its running processes and database files, on one node.
• Node: A hardware resource that runs Linux and Systemd. It can be a physical server, VM, container, or Pod.

Two business entities, Database and Role, complete this logical model:

Examples

The following two examples show the model. The four-node Pigsty sandbox contains a three-node pg-test cluster:

    pg-test:
      hosts:
        10.10.10.11: { pg_seq: 1, pg_role: primary }
        10.10.10.12: { pg_seq: 2, pg_role: replica }
        10.10.10.13: { pg_seq: 3, pg_role: replica }
      vars: { pg_cluster: pg-test }

This configuration defines a high-availability PostgreSQL cluster with these entities:

Identity Parameters

The PG_ID parameter group assigns deterministic identities to PGSQL entities. Three parameters are required:

Set the cluster name at cluster level. Set each instance number and role at instance level. Pigsty generates unique identifiers with these rules:

Pigsty uses an exclusive 1:1 mapping between nodes and PostgreSQL instances. By default, the node inherits its PostgreSQL instance identifier through node_id_from_pg. Set nodename to override this name. Alternatively, disable nodename_overwrite to retain the current default.

Sharding Identity Parameters

To serve one business with multiple PostgreSQL clusters (sharding), use two additional identity parameters: pg_shard and pg_group.

These clusters share one pg_shard name. Each has its own pg_group number, as in this Citus cluster:

A pg_cluster name usually combines {{ pg_shard }}{{ pg_group }}, for example pg-citus0 or pg-citus1.

all:
  children:
    pg-citus0: # citus shard 0
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus0 , pg_group: 0 }
    pg-citus1: # citus shard 1
      hosts: { 10.10.10.11: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus1 , pg_group: 1 }
    pg-citus2: # citus shard 2
      hosts: { 10.10.10.12: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus2 , pg_group: 2 }
    pg-citus3: # citus shard 3
      hosts: { 10.10.10.13: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus3 , pg_group: 3 }

Pigsty has dedicated dashboards for horizontally sharded clusters. Use this naming convention to compare shard performance and load in those dashboards.

Other identity parameters support specific uses:

• pg_upstream: Set the upstream for a backup cluster or cascading replication.
• gp_role: Identify a Greenplum cluster role.
• pg_exporters: Define external monitoring instances.
• pg_offline_query: Define offline query instances.

See the PG_ID parameter documentation.

Monitoring Label System

The built-in monitoring system uses these identity parameters to identify PostgreSQL entities.

pg_up{cls="pg-test", ins="pg-test-1", ip="10.10.10.11", job="pgsql"}
pg_up{cls="pg-test", ins="pg-test-2", ip="10.10.10.12", job="pgsql"}
pg_up{cls="pg-test", ins="pg-test-3", ip="10.10.10.13", job="pgsql"}

The labels cls, ins, and ip identify the cluster name, instance name, and node IP. They accompany job in all native VictoriaMetrics metrics and VictoriaLogs log streams.

These job values are fixed:

• pgsql: PostgreSQL metrics.
• pgrds: Remote PostgreSQL instance metrics.
• postgres: PostgreSQL CSV logs.
• pgbackrest: pgBackRest logs.

Other PostgreSQL components collect logs through job: syslog.

Specific entity metrics also have these identity labels:

• datname: Database name, if a metric belongs to a specific database.
• relname: Table name, if a metric belongs to a specific table.
• idxname: Index name, if a metric belongs to a specific index.
• funcname: Function name, if a metric belongs to a specific function.
• seqname: Sequence name, if a metric belongs to a specific sequence.
• query: Query fingerprint, if a metric belongs to a specific query.

2.3 - E-R Model of Etcd Cluster

The ETCD module organizes production ETCD into clusters. A cluster is a logical entity whose ETCD instances coordinate through the Raft consensus protocol.

Each cluster is an autonomous distributed key-value store with at least one ETCD instance. It provides services through client ports.

There are three core entities in Pigsty’s ETCD module:

• Cluster: An autonomous ETCD service unit and the top-level namespace for other entities.
• Instance: One ETCD server process on a node, participating in Raft consensus.
• Node: An implicitly declared hardware resource that runs Linux and Systemd.

The ETCD model has no Service entities or complex Role distinctions, unlike PostgreSQL. All ETCD instances are functionally equivalent. Raft elects one Leader; the others become Followers. Non-voting Learner members are also permitted during intermediate scale-out states.

Examples

This example defines a three-node ETCD cluster:

etcd:
  hosts:
    10.10.10.10: { etcd_seq: 1 }
    10.10.10.11: { etcd_seq: 2 }
    10.10.10.12: { etcd_seq: 3 }
  vars:
    etcd_cluster: etcd

The configuration defines these entities:

Identity Parameters

The ETCD parameter group assigns deterministic identities to ETCD entities. Two parameters are required:

Set the cluster name at cluster level and the instance number at instance level. Pigsty generates unique identifiers with these rules:

ETCD does not assign a separate identity to host nodes. It uses their existing hostname or IP address.

Ports & Protocols

Each ETCD instance listens on the following two ports:

ETCD clusters enable TLS encryption and RBAC authentication by default. Clients need the correct certificates and passwords to access ETCD.

Cluster Size

ETCD cluster size determines availability. More than half the nodes, called a quorum, must be alive to maintain this distributed coordination service.

ETCD supports even member counts. However, they tolerate no more failures than the next smaller odd count. They also increase deployment and quorum costs. Production clusters therefore usually have one, three, or five members. Clusters with more than five members are uncommon.

Monitoring Label System

The built-in monitoring system uses these identity parameters to identify ETCD entities.

etcd_up{cls="etcd", ins="etcd-1", ip="10.10.10.10", job="etcd"}
etcd_up{cls="etcd", ins="etcd-2", ip="10.10.10.11", job="etcd"}
etcd_up{cls="etcd", ins="etcd-3", ip="10.10.10.12", job="etcd"}

The labels cls, ins, and ip identify the cluster name, instance name, and node IP. They accompany job in all ETCD metrics collected by VictoriaMetrics. The ETCD metrics job is always etcd.

2.4 - MINIO Cluster Model

MINIO is the compatibility module name for object storage. Pigsty v4.5.0 deploys Silo with minio_type: silo. It groups object storage instances into a cluster.

Each cluster is an autonomous S3-compatible object store with at least one instance. It exposes services through the S3 API port.

There are three core entities in Pigsty’s MINIO module:

• Cluster: An autonomous object storage service unit and the top-level namespace for other entities.
• Instance: One Silo server process on a node, managing local disks.
• Node: An implicitly declared hardware resource that runs Linux and Systemd.

Silo also retains the Storage Pool concept for expansion.

Deployment Modes

Silo supports Pigsty’s three inventory deployment modes:

For a quick SNSD experiment, you can use a regular directory. For multiple drives, use real disk mount points. Otherwise, Silo refuses to start.

Examples

This example explicitly selects Silo, the current default backend, and defines a four-node cluster with multiple drives:

minio:
  hosts:
    10.10.10.10: { minio_seq: 1 }
    10.10.10.11: { minio_seq: 2 }
    10.10.10.12: { minio_seq: 3 }
    10.10.10.13: { minio_seq: 4 }
  vars:
    minio_cluster: minio
    minio_type: silo
    minio_data: '/data{1...4}'
    minio_node: '${minio_cluster}-${minio_seq}.pigsty'

The configuration defines four Silo nodes with four disks each. Instance identifiers retain MINIO compatibility names:

Identity Parameters

The MINIO parameter group assigns deterministic identities to MINIO entities. Two parameters are required:

Set the cluster name at cluster level and the instance number at instance level. Pigsty generates unique identifiers with these rules:

MINIO uses the existing hostname or IP address as the host node identity. It does not assign another identity. minio_node generates internal Silo node names for cluster discovery in /etc/hosts. These names do not identify the host node.

Roles find members throughout the inventory by minio_cluster. The Ansible group name can differ from the cluster name. The retained backend selector minio_type currently must be silo.

Core Configuration Parameters

These parameters are also critical for Silo cluster configuration:

These parameters determine minio_volumes, which the role writes to Silo’s MINIO_VOLUMES:

• SNSD: Direct minio_data value, e.g., /data/minio
• SNMD: Expanded minio_data directories, e.g., /data{1...4}
• MNMD: Combined minio_node and minio_data, e.g., https://minio-{1...4}.pigsty:9000/data{1...4}

Ports & Services

Each object-storage instance listens on the following ports:

MINIO enables HTTPS by default through minio_https. For the default pgBackRest S3 repository configuration, keep HTTPS enabled and install the Pigsty CA correctly.

Clients can reach a multi-node Silo cluster through any member. For a stable entry point, use a load balancer such as HAProxy with a VIP.

Resource Provisioning

After deployment, Pigsty creates these Silo resources. minio_provision controls this step:

Default Buckets (defined by minio_buckets):

Default Users (defined by minio_users):

These public default credentials are only for demonstrations and local development. Replace them before production deployment.

pgbackrest serves PostgreSQL cluster backups. The reserved users s3user_meta and s3user_data are not actively used.

Monitoring Label System

Pigsty uses these identity parameters to identify object storage entities. This example shows a Silo availability series:

minio_up{cls="minio", ins="minio-1", ip="10.10.10.10", job="minio"}
minio_up{cls="minio", ins="minio-2", ip="10.10.10.11", job="minio"}
minio_up{cls="minio", ins="minio-3", ip="10.10.10.12", job="minio"}
minio_up{cls="minio", ins="minio-4", ip="10.10.10.13", job="minio"}

cls, ins, and ip identify the cluster name, instance name, and node IP. For monitoring compatibility, job="minio" remains unchanged. The current backend label is flavor=silo. See the metric list for details.

2.5 - E-R Model of Redis Cluster

The Redis module organizes production Redis into clusters. A cluster is a logical entity with Redis instances on one or more nodes.

Each cluster is an autonomous, high-performance cache or storage unit with at least one Redis instance. It exposes services through ports.

There are three core entities in Pigsty’s Redis module:

• Cluster: An autonomous Redis service unit and the top-level namespace for other entities.
• Instance: One Redis server process using a specific port on a node.
• Node: An implicitly declared hardware resource that runs Linux and Systemd. It can host multiple Redis instances.

Redis usually runs multiple instances per node, unlike PostgreSQL. This uses multiple CPU cores on a physical server or VM. Nodes and instances therefore have a 1:N relationship. For production, Redis instances with more than 12 GB of memory are generally discouraged.

Operating Modes

redis_mode selects one of three Redis modes:

• Standalone: The default mode. Set replication with replica_of. HA requires a separate Sentinel cluster.
• Sentinel: Monitor standalone clusters and provide automatic failover. Sentinel stores no business data. Multiple nodes provide HA for Sentinel itself.
• Native Cluster: Automatically shard data across multiple primaries. Each primary can have multiple replicas. Built-in HA removes the need for Sentinel.

Examples

The following examples show each mode.

Standalone Cluster

Classic master-replica on a single node:

redis-ms:
  hosts:
    10.10.10.10:
      redis_node: 1
      redis_instances:
        6379: { }
        6380: { replica_of: '10.10.10.10 6379' }
  vars:
    redis_cluster: redis-ms
    redis_password: 'redis.ms'
    redis_max_memory: 64MB

Sentinel Cluster

This example puts three Sentinel instances on one node. redis_sentinel_monitor specifies the standalone clusters they monitor:

redis-sentinel:
  hosts:
    10.10.10.11:
      redis_node: 1
      redis_instances: { 26379: {}, 26380: {}, 26381: {} }
  vars:
    redis_cluster: redis-sentinel
    redis_password: 'redis.sentinel'
    redis_mode: sentinel
    redis_max_memory: 16MB
    redis_sentinel_monitor:
      - { name: redis-ms, host: 10.10.10.10, port: 6379, password: redis.ms, quorum: 2 }

Native Cluster

This native distributed cluster has two nodes and six instances: the minimum specification of three primaries and three replicas.

redis-test:
  hosts:
    10.10.10.12: { redis_node: 1, redis_instances: { 6379: {}, 6380: {}, 6381: {} } }
    10.10.10.13: { redis_node: 2, redis_instances: { 6379: {}, 6380: {}, 6381: {} } }
  vars:
    redis_cluster: redis-test
    redis_password: 'redis.test'
    redis_mode: cluster
    redis_max_memory: 32MB

This creates a 3 primary 3 replica native Redis cluster.

Identity Parameters

The REDIS parameter group assigns deterministic identities to Redis entities. Three parameters are required:

Set the cluster name at cluster level. Set the node number and instance definitions at node level. Pigsty generates these unique identifiers:

Redis uses the existing hostname or IP address as the host node identity. It does not assign another identity. redis_node names instances, not host nodes.

Instance Definition

redis_instances is a JSON object. Each key is a port number; its value is the instance configuration:

redis_instances:
  6379: { }                                      # Primary instance, no extra config
  6380: { replica_of: '10.10.10.10 6379' }       # Replica, specify upstream primary
  6381: { replica_of: '10.10.10.10 6379' }       # Replica, specify upstream primary

Each Redis instance must use a unique port on its node. Avoid reserved ports below 1024 and conflicts with Pigsty ports. In standalone mode, replica_of defines replication. Use format '<ip> <port>' to specify the upstream primary address and port.

Each Redis node also runs Redis Exporter to collect metrics from all local instances:

The Redis deployment model has these limits:

• Node Exclusive: A node can belong to only one Redis cluster at a time.
• Port Unique: Instances on the same node must use different ports.
• Password Shared: Instances on the same node must share a password because of a redis_exporter limitation.
• Manual HA: Standalone clusters require additional Sentinel configuration for automatic failover.

Monitoring Label System

The built-in monitoring system uses these identity parameters to identify Redis entities.

redis_up{cls="redis-ms", ins="redis-ms-1-6379", ip="10.10.10.10", job="redis"}
redis_up{cls="redis-ms", ins="redis-ms-1-6380", ip="10.10.10.10", job="redis"}

The labels cls, ins, and ip identify the cluster name, instance name, and node IP. They accompany job in all Redis metrics collected by VictoriaMetrics. The Redis metrics job is always redis.

3 - Infra as Code

Pigsty uses IaC and GitOps. Describe the environment in a declarative inventory. Apply it with idempotent playbooks.

Declare the target state with parameters. Playbooks can repeatedly adjust nodes to that state. This resembles Kubernetes CRDs and Operators, implemented through Ansible on physical servers and VMs.

Pigsty was designed to manage large PostgreSQL deployments. Its target was to reproduce more than 100 database clusters, PG/Redis services, and monitoring on prepared servers within ten minutes. Manual GUI operations cannot complete that work in the available time. CLI-based IaC provides precise, efficient control.

pigsty.yml describes the deployment state. Production, staging, test, and development environments use the same deployment logic. Their inventories define the differences.

Store this configuration in Git for version history and auditing. You can also store it in PostgreSQL CMDB tables as infrastructure data. Integrate either method with existing workflows.

IaC supports enterprise operators, individual developers, and smaller organizations. Default values avoid the need to understand every parameter. Start a single-node database with zero configuration. Add two IP addresses for an enterprise HA PostgreSQL cluster.

Declare Modules

This default example installs INFRA, NODE, ETCD, and PGSQL on 10.10.10.10.

# monitoring, alerting, DNS, NTP and other infrastructure cluster...
infra: { hosts: { 10.10.10.10: { infra_seq: 1 } } }

# minio cluster, s3 compatible object storage
minio: { hosts: { 10.10.10.10: { minio_seq: 1 } }, vars: { minio_cluster: minio } }

# etcd cluster, used as DCS for PostgreSQL high availability
etcd: { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }

# PGSQL example cluster: pg-meta
pg-meta: { hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }, vars: { pg_cluster: pg-meta } }

Run these playbooks to install the modules:

./infra.yml -l 10.10.10.10  # Initialize infra module on node 10.10.10.10
./etcd.yml  -l 10.10.10.10  # Initialize etcd module on node 10.10.10.10
./minio.yml -l 10.10.10.10  # Initialize minio module on node 10.10.10.10
./pgsql.yml -l 10.10.10.10  # Initialize pgsql module on node 10.10.10.10

Declare Clusters

Install PGSQL on multiple nodes to declare a PostgreSQL cluster as one service unit:

For a three-node HA cluster using streaming replication, add this definition to all.children in pigsty.yml. The three nodes must already be managed by Pigsty.

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 3, pg_role: offline }
  vars:  { pg_cluster: pg-test }

After defining the cluster, create it with playbooks:

bin/pgsql-add pg-test   # Create the pg-test cluster

Instance roles include primary, replica, offline, delayed, and synchronous standby. Cluster types include standby, Citus, Redis, MINIO (Silo), and Etcd.

Customize Cluster Content

Declare databases, users, services, and HBA rules inside each cluster. This example customizes the default pg-meta single-node cluster:

It declares six business databases and seven business users. It adds a standby service for reads without replication delay, additional pg_hba rules, a primary L2 VIP, and a custom backup strategy.

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary , pg_offline_query: true } }
  vars:
    pg_cluster: pg-meta
    pg_databases:                       # define business databases on this cluster, array of database definition
      - name: meta                      # REQUIRED, `name` is the only mandatory field of a database definition
        baseline: cmdb.sql              # optional, database sql baseline path, (relative path among ansible search path, e.g files/)
        pgbouncer: true                 # optional, add this database to pgbouncer database list? true by default
        schemas: [pigsty]               # optional, additional schemas to be created, array of schema names
        extensions:                     # optional, additional extensions to be installed: array of `{name[,schema]}`
          - { name: postgis , schema: public }
          - { name: timescaledb }
        comment: pigsty meta database   # optional, comment string for this database
        owner: postgres                # optional, database owner, postgres by default
        template: template1            # optional, which template to use, template1 by default
        encoding: UTF8                 # optional, database encoding, UTF8 by default. (MUST same as template database)
        locale: C                      # optional, database locale, C by default.  (MUST same as template database)
        lc_collate: C                  # optional, database collate, C by default. (MUST same as template database)
        lc_ctype: C                    # optional, database ctype, C by default.   (MUST same as template database)
        tablespace: pg_default         # optional, default tablespace, 'pg_default' by default.
        allowconn: true                # optional, allow connection, true by default. false will disable connect at all
        revokeconn: false              # optional, revoke public connection privilege. false by default. (leave connect with grant option to owner)
        register_datasource: true      # optional, register this database to grafana datasources? true by default
        connlimit: -1                  # optional, database connection limit, default -1 disable limit
        pool_auth_user: dbuser_meta    # optional, all connection to this pgbouncer database will be authenticated by this user
        pool_mode: transaction         # optional, pgbouncer pool mode at database level, default transaction
        pool_size: 64                  # optional, pgbouncer pool size at database level, default 64
        pool_reserve: 32          # optional, pgbouncer pool size reserve at database level, default 32
        pool_size_min: 0               # optional, pgbouncer pool size min at database level, default 0
        pool_connlimit: 100          # optional, max database connections at database level, default 100
      - { name: grafana  ,owner: dbuser_grafana  ,revokeconn: true ,comment: grafana primary database }
      - { name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database }
      - { name: kong     ,owner: dbuser_kong     ,revokeconn: true ,comment: kong the api gateway database }
      - { name: gitea    ,owner: dbuser_gitea    ,revokeconn: true ,comment: gitea meta database }
      - { name: wiki     ,owner: dbuser_wiki     ,revokeconn: true ,comment: wiki meta database }
    pg_users:                           # define business users/roles on this cluster, array of user definition
      - name: dbuser_meta               # REQUIRED, `name` is the only mandatory field of a user definition
        password: DBUser.Meta           # optional, password, can be a scram-sha-256 hash string or plain text
        login: true                     # optional, can log in, true by default  (new biz ROLE should be false)
        superuser: false                # optional, is superuser? false by default
        createdb: false                 # optional, can create database? false by default
        createrole: false               # optional, can create role? false by default
        inherit: true                   # optional, can this role use inherited privileges? true by default
        replication: false              # optional, can this role do replication? false by default
        bypassrls: false                # optional, can this role bypass row level security? false by default
        pgbouncer: true                 # optional, add this user to pgbouncer user-list? false by default (production user should be true explicitly)
        connlimit: -1                   # optional, user connection limit, default -1 disable limit
        expire_in: 3650                 # optional, now + n days when this role is expired (OVERWRITE expire_at)
        expire_at: '2030-12-31'         # optional, YYYY-MM-DD 'timestamp' when this role is expired  (OVERWRITTEN by expire_in)
        comment: pigsty admin user      # optional, comment string for this user/role
        roles: [dbrole_admin]           # optional, belonged roles. default roles are: dbrole_{admin,readonly,readwrite,offline}
        parameters: {}                  # optional, role level parameters with `ALTER ROLE SET`
        pool_mode: transaction          # optional, pgbouncer pool mode at user level, transaction by default
        pool_connlimit: -1              # optional, max database connections at user level, default -1 disable limit
      - {name: dbuser_view     ,password: DBUser.Viewer   ,pgbouncer: true ,roles: [dbrole_readonly], comment: read-only viewer for meta database}
      - {name: dbuser_grafana  ,password: DBUser.Grafana  ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for grafana database   }
      - {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for bytebase database  }
      - {name: dbuser_kong     ,password: DBUser.Kong     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for kong api gateway   }
      - {name: dbuser_gitea    ,password: DBUser.Gitea    ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for gitea service      }
      - {name: dbuser_wiki     ,password: DBUser.Wiki     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for wiki.js service    }
    pg_services:                        # extra services in addition to pg_default_services, array of service definition
      # standby service will route {ip|name}:5435 to sync replica's pgbouncer (5435->6432 standby)
      - name: standby                   # required, service name, the actual svc name will be prefixed with `pg_cluster`, e.g: pg-meta-standby
        port: 5435                      # required, service exposed port (work as kubernetes service node port mode)
        ip: "*"                         # optional, service bind ip address, `*` for all ip by default
        selector: "[]"                  # required, service member selector, use JMESPath to filter inventory
        dest: default                   # optional, destination port, default|postgres|pgbouncer|<port_number>, 'default' by default
        check: /sync                    # optional, health check url path, / by default
        backup: "[? pg_role == `primary`]"  # backup server selector
        maxconn: 3000                   # optional, max allowed front-end connection
        balance: roundrobin             # optional, haproxy load balance algorithm (roundrobin by default, other: leastconn)
        options: 'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'
    pg_hba_rules:
      - {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'}
    pg_vip_enabled: true
    pg_vip_address: 10.10.10.2/24
    pg_vip_interface: eth1
    pg_crontab:  # full backup daily at 1am (installed in the postgres user crontab)
      - '00 01 * * * /pg/bin/pg-backup full'

Declare Access Control

Declarative configuration also controls access permissions. This example customizes security for pg-meta:

The three-node crit.yml template prioritizes consistency and zero-loss failover. It enables L2 VIP and restricts database and pool listeners to loopback, internal, and VIP addresses. It enables TLS for the Patroni API and PgBouncer. HBA rules require SSL for database access. $libdir/passwordcheck in pg_libs enforces the password policy.

A separate pg-meta-delay cluster follows pg-meta with a one-hour delay for emergency recovery after data deletion.

pg-meta:      # 3 instance postgres cluster `pg-meta`
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary }
    10.10.10.11: { pg_seq: 2, pg_role: replica }
    10.10.10.12: { pg_seq: 3, pg_role: replica , pg_offline_query: true }
  vars:
    pg_cluster: pg-meta
    pg_conf: crit.yml
    pg_users:
      - { name: dbuser_meta , password: DBUser.Meta   , pgbouncer: true , roles: [ dbrole_admin ] , comment: pigsty admin user }
      - { name: dbuser_view , password: DBUser.Viewer , pgbouncer: true , roles: [ dbrole_readonly ] , comment: read-only viewer for meta database }
    pg_databases:
      - {name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [{name: postgis, schema: public}, {name: timescaledb}]}
    pg_default_service_dest: postgres
    pg_services:
      - { name: standby ,src_ip: "*" ,port: 5435 , dest: default ,selector: "[]" , backup: "[? pg_role == `primary`]" }
    pg_vip_enabled: true
    pg_vip_address: 10.10.10.2/24
    pg_vip_interface: eth1
    pg_listen: '${ip},${vip},${lo}'
    patroni_ssl_enabled: true
    pgbouncer_sslmode: require
    pgbackrest_method: minio
    pg_libs: 'timescaledb, $libdir/passwordcheck, pg_stat_statements, auto_explain' # add passwordcheck extension to enforce strong password
    pg_default_roles:                 # default roles and users in postgres cluster
      - { name: dbrole_readonly  ,login: false ,comment: role for global read-only access     }
      - { name: dbrole_offline   ,login: false ,comment: role for restricted read-only access }
      - { name: dbrole_readwrite ,login: false ,roles: [dbrole_readonly]               ,comment: role for global read-write access }
      - { name: dbrole_admin     ,login: false ,roles: [pg_monitor, dbrole_readwrite]  ,comment: role for object creation }
      - { name: postgres     ,superuser: true  ,expire_in: 7300                        ,comment: system superuser }
      - { name: replicator ,replication: true  ,expire_in: 7300 ,roles: [pg_monitor, dbrole_readonly]   ,comment: system replicator }
      - { name: dbuser_dba   ,superuser: true  ,expire_in: 7300 ,roles: [dbrole_admin]  ,pgbouncer: true ,pool_mode: session, pool_connlimit: 16 , comment: pgsql admin user }
      - { name: dbuser_monitor ,roles: [pg_monitor] ,expire_in: 7300 ,pgbouncer: true ,parameters: {log_min_duration_statement: 1000 } ,pool_mode: session ,pool_connlimit: 8 ,comment: pgsql monitor user }
    pg_default_hba_rules:             # postgres host-based auth rules by default
      - {user: '${dbsu}'    ,db: all         ,addr: local     ,auth: ident ,title: 'dbsu access via local os user ident'  }
      - {user: '${dbsu}'    ,db: replication ,addr: local     ,auth: ident ,title: 'dbsu replication from local os ident' }
      - {user: '${repl}'    ,db: replication ,addr: localhost ,auth: ssl   ,title: 'replicator replication from localhost'}
      - {user: '${repl}'    ,db: replication ,addr: intra     ,auth: ssl   ,title: 'replicator replication from intranet' }
      - {user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: ssl   ,title: 'replicator postgres db from intranet' }
      - {user: '${monitor}' ,db: all         ,addr: localhost ,auth: pwd   ,title: 'monitor from localhost with password' }
      - {user: '${monitor}' ,db: all         ,addr: infra     ,auth: ssl   ,title: 'monitor from infra host with password'}
      - {user: '${admin}'   ,db: all         ,addr: infra     ,auth: ssl   ,title: 'admin @ infra nodes with pwd & ssl'   }
      - {user: '${admin}'   ,db: all         ,addr: world     ,auth: cert  ,title: 'admin @ everywhere with ssl & cert'   }
      - {user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: ssl   ,title: 'pgbouncer read/write via local socket'}
      - {user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: ssl   ,title: 'read/write biz user via password'     }
      - {user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: ssl   ,title: 'allow etl offline tasks from intranet'}
    pgb_default_hba_rules:            # pgbouncer host-based authentication rules
      - {user: '${dbsu}'    ,db: pgbouncer   ,addr: local     ,auth: peer  ,title: 'dbsu local admin access with os ident'}
      - {user: 'all'        ,db: all         ,addr: localhost ,auth: pwd   ,title: 'allow all user local access with pwd' }
      - {user: '${monitor}' ,db: pgbouncer   ,addr: intra     ,auth: ssl   ,title: 'monitor access via intranet with pwd' }
      - {user: '${monitor}' ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other monitor access addr' }
      - {user: '${admin}'   ,db: all         ,addr: intra     ,auth: ssl   ,title: 'admin access via intranet with pwd'   }
      - {user: '${admin}'   ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other admin access addr'   }
      - {user: 'all'        ,db: all         ,addr: intra     ,auth: ssl   ,title: 'allow all user intra access with pwd' }

# OPTIONAL delayed cluster for pg-meta
pg-meta-delay:                    # delayed instance for pg-meta (1 hour ago)
  hosts: { 10.10.10.13: { pg_seq: 1, pg_role: primary, pg_upstream: 10.10.10.10, pg_delay: 1h } }
  vars: { pg_cluster: pg-meta-delay }

Citus Distributed Cluster

This configuration declares a four-node Citus cluster:

all:
  children:
    pg-citus0: # citus coordinator, pg_group = 0
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus0 , pg_group: 0 }
    pg-citus1: # citus data node 1
      hosts: { 10.10.10.11: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus1 , pg_group: 1 }
    pg-citus2: # citus data node 2
      hosts: { 10.10.10.12: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus2 , pg_group: 2 }
    pg-citus3: # citus data node 3, with an extra replica
      hosts:
        10.10.10.13: { pg_seq: 1, pg_role: primary }
        10.10.10.14: { pg_seq: 2, pg_role: replica }
      vars: { pg_cluster: pg-citus3 , pg_group: 3 }
  vars:                               # global parameters for all citus clusters
    pg_mode: citus                    # pgsql cluster mode: citus
    pg_shard: pg-citus                # citus shard name: pg-citus
    patroni_citus_db: meta            # citus distributed database name
    pg_dbsu_password: DBUser.Postgres # all dbsu password access for citus cluster
    pg_users: [ { name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [ dbrole_admin ] } ]
    pg_databases: [ { name: meta ,extensions: [ { name: citus }, { name: postgis }, { name: timescaledb } ] } ]
    pg_hba_rules:
      - { user: 'all' ,db: all  ,addr: 127.0.0.1/32 ,auth: ssl ,title: 'all user ssl access from localhost' }
      - { user: 'all' ,db: all  ,addr: intra        ,auth: ssl ,title: 'all user ssl access from intranet'  }