PostgreSQL

• 1: Architecture
• 2: Configure
• 3: Parameter
• 4: Administration
  • 4.1: Parameter Tuning
  • 4.2: Maintenance
  • 4.3: Failure SOP
  • 4.4: Data Loss Recovery
• 5: Playbook
• 6: Monitor
• 7: FAQ
• 8: User Role
• 9: Database
• 10: Service
• 11: Auth / HBA
• 12: Privileges
• 13: Dashboard
• 14: Migration
• 15: Backup
  • 15.1: Mechanism
  • 15.2: Repository
  • 15.3: Policy
  • 15.4: Admin
  • 15.5: Restore
  • 15.6: Example
• 16: Kernel
  • 16.1: PostgreSQL
  • 16.2: Citus
  • 16.3: Babelfish
  • 16.4: IvorySQL
  • 16.5: Percona
  • 16.6: PolarDB
  • 16.7: OrioleDB
  • 16.8: OpenHalo
  • 16.9: Cloudberry
  • 16.10: Supabase
  • 16.11: FerretDB
• 17: Extension
  • 17.1: Quick Start
  • 17.2: Package
  • 17.3: Download
  • 17.4: Install
  • 17.5: Config
  • 17.6: Create
  • 17.7: Update
  • 17.8: Remove

    PostgreSQL cluster architecture and concept

    Reliable service access via lb, proxy, pool

    Define, create, and manage business databases

    Define, create, and manage users and roles

    Host-Based Authentication in Pigsty

    Access Control with default roles and privileges

    Replace vanilla PostgreSQL with exotic kernel forks

    Harness the synergistic power of PostgreSQL extensions

    Describe and configure PostgreSQL clusters

    Customize postgres cluster with 120 parameters

    Run administrative tasks on PostgreSQL clusters

    Control primitives with Ansible playbooks

    Backup and point-in-time recovery

    Zero-downtime blue-green deployment

    Monitor existing PostgreSQL or RDS

    Visualized information with Grafana dashboards

1 - Architecture

Entity-Relationships

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

• Cluster: An autonomous PostgreSQL business unit, the top-level namespace for other entities.
• Service: An abstraction of cluster ability, traffic routes, and expose services via different node ports.
• Instance: A postgres server which consists of a group of running processes & files on a single node.
• Node: An abstraction of hardware resources, which can be bare metal, virtual machine, or k8s pods.

Architecture

Here’s a PostgreSQL Cluster pg-test described in the config inventory:

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

It defines a HA PostgreSQL cluster looks like the above, and here are related entities in this cluster:

• 1 PostgreSQL cluster: pg-test
• 2 Instance Roles: primary & replica
• 3 PostgreSQL Instances: pg-test-1, pg-test-2, pg-test-3
• 3 Nodes: 10.10.10.11, 10.10.10.12, 10.10.10.13
• 4 PostgreSQL Services, auto generated by default:
  • pg-test-primary: Read-Write Service (route to primary pgbouncer)
  • pg-test-replica: Read-Only Service (route to replicas pgbouncer)
  • pg-test-default: Direct RW Service (route to primary postgres)
  • pg-test-offline: Offline Read Service (route to dedicated postgres)

HA Description

The PostgreSQL cluster is managed by Patroni, which is a battle-tested HA solution for PostgreSQL. It will set up PG Replication on multiple nodes, and perform automatic failover when the primary node is down.

The backup is handled by pgBackRest, which is a powerful backup tool for PostgreSQL, which supports incremental backup/restore, compression, encryption, backup to local disk or S3 / MinIO.

The pgbouncer is a lightweight connection pooler which can increase the performance with high-concurrency. It is 1:1 deployed with the Postgres server and used by primary / replica services by default.

The services are exposed by HAProxy, which is a high-performance TCP/HTTP load balancer, it’s part of NODE module. And 4 default services are auto exposed in an idempotent way on all cluster nodes.

The application can visit any of the haproxy to access the Postgres cluster, and the traffic will be routed to the correct instance based on patroni health check endpoints. So failover is transparent to the apps.

The patroni requires a functioning ETCD in your deployment, and pgbackrest can use the optional MinIO as centralized backup storage; and monitoring exporters will collect metrics & logs into the Infra module.

Components

The PGSQL Node consists of the following components (some can be disabled)

Interaction

Meanwhile, the Infra Node consists of the following components which interact with PGSQL.

• Cluster DNS is resolved by DNSMASQ on infra nodes
• Cluster VIP is managed by vip-manager, which binds to cluster primary.
  • vip-manager will acquire cluster leader info written by patroni from etcd cluster directly
• Cluster services are exposed by Haproxy on nodes, services are distinguished by node ports (543x).
  • Haproxy port 9101: monitoring metrics & stats & admin page
  • Haproxy port 5433: default service that routes to primary pgbouncer: primary
  • Haproxy port 5434: default service that routes to replica pgbouncer: replica
  • Haproxy port 5436: default service that routes to primary postgres: default
  • Haproxy port 5438: default service that routes to offline postgres: offline
  • HAProxy will route traffic based on health check information provided by patroni.
• Pgbouncer is a connection pool that listens to port 6432
  • 1:1 Deployed with the Postgres server through a local unix socket.
  • Production traffic (Primary/Replica) will go through pgbouncer by default
  • Bypass pgbouncer for primary/replica services by setting pg_default_service_dest ) to postgres
  • Default/Offline service will always bypass pgbouncer and connect to target Postgres directly.
• Postgres provides relational database services @ port 5432
  • Install PGSQL module on multiple nodes will automatically form a HA cluster based on replication.
  • PostgreSQL is supervised by patroni by default.
• Patroni will supervise PostgreSQL server @ port 8008 by default
  • Patroni spawn postgres servers as the child process
  • Patroni uses etcd as DCS: config storage, failure detection, and leader election.
  • Patroni will provide Postgres information through a health check, used by HAProxy
  • Patroni metrics will be scraped by prometheus on infra nodes
• PG Exporter will expose postgres metrics @ port 9630
• Pgbouncer Exporter will expose pgbouncer metrics @ port 9631
  • Pgbouncer’s metrics will be scraped by prometheus on infra nodes
• pgBackRest will work on the local repo by default (pgbackrest_method)
  • If local (default) is used as the backup repo, primary’s pg_fs_backup is used as local backup repo
  • If minio is used, pgBackRest will create the repo on the dedicated MinIO cluster
• Postgres-related logs (postgres,pgbouncer,patroni,pgbackrest) are exposed by promtail @ port 9080
  • Promtail will send logs to Loki on infra node

Full ER Diagram

There is one config inventory file and one infra corresponding to a Pigsty deployment. And there may have multiple database clusters in a Pigsty deployment.

A Cluster / Instance may have multiple Databases, and Databases contain Tables and other Objects (Query, Index, Function, Seq, …).

2 - Configure

You can define different types of instances & clusters.

• Identity Parameters: Parameters used for describing a PostgreSQL cluster
• Naming Convention: Parameters used for describing a PostgreSQL cluster
• Primary: Define a single instance cluster.
• Replica: Define a basic HA cluster with one primary & one replica.
• Offline: Define a dedicated instance for OLAP/ETL/Interactive queries
• Sync Standby: Enable synchronous commit to ensure no data loss.
• Quorum Commit: Use quorum sync commit for an even higher consistency level.
• Standby Cluster: Clone an existing cluster and follow it
• Delayed Cluster: Clone an existing cluster for emergency data recovery
• Citus Cluster: Define a Citus distributed database cluster

Identity Parameters

There are 4 REQUIRED parameters to describe a PostgreSQL Cluster:

• pg_cluster: Name of the cluster, configured at the cluster level.
• pg_role: Configured at the instance level, identifies the role of the instance.
  • the primary role will mark this instance as cluster leader (initially).
  • the replica is the default role, which marks this instance as common read-only replica.
  • the offline marks this instance as special read-only replica that serves the offline service.
• pg_seq: Used to identify the instance within the cluster, a non-negative integer
  • Start from 0 or 1, incremental allocation in sequence, never change once assigned,
  • {{ pg_cluster }}-{{ pg_seq }} is used to uniquely identify the instance, i.e. pg_instance.
  • {{ pg_cluster }}-{{ pg_role }} is used to identify the services within the cluster, i.e. pg_service.
• pg_shard and pg_group are used for horizontally sharding clusters, for citus & greenplum only.

These identities will be used in the entire system, for example, the metrics may look like:

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"}

Sharding Clusters

You can use the OPTIONAL pg_shard and pg_group param to identify horizontal sharded clusters:

For example, Horizontal sharding with citus, greenplum or sharding it manually

pg-citus:
  hosts:
    10.10.10.10: { pg_group: 0, pg_cluster: pg-citus0 ,pg_seq: 1, pg_role: primary }
    10.10.10.11: { pg_group: 0, pg_cluster: pg-citus0 ,pg_seq: 2, pg_role: replica }
    10.10.10.12: { pg_group: 1, pg_cluster: pg-citus1 ,pg_seq: 1, pg_role: primary }
    10.10.10.13: { pg_group: 2, pg_cluster: pg-citus2 ,pg_seq: 1, pg_role: primary }
  vars:
    pg_mode: citus          # pgsql cluster mode: citus
    pg_shard: pg-citus      # citus shard name: pg-citus

Naming Convention

• Cluster name should be a valid domain name matches [a-zA-Z0-9-]+, and ≤ 40 char
• Service names are prefixed with cluster name, and suffixed with a single word join by -
• Instance names are prefixed with cluster name and suffixed with an integer, join by -
• Nodes are identified by its primary IPv4 address, hostname is used as secondary identifier

Version Policy

Pigsty follows the PostgreSQL Version Policy and “Officially” support the following major versions.

Pigsty has PG 13 - 18 support. Lower major version (12-) “may” work, with no guarantee. For legacy PG version support, consider our professional services.

To use a different major version, configure the pg_version variable. Which can be globally configure with -v <ver> option. No further changed needed as long as they are available in local / upstream repo.

pg-v13:
  hosts: { 10.10.10.13: { pg_seq: 1 ,pg_role: primary } }
  vars:
    pg_cluster: pg-v13
    pg_version: 13

pg-v14:
  hosts: { 10.10.10.14: { pg_seq: 1 ,pg_role: primary } }
  vars:
    pg_cluster: pg-v14
    pg_version: 14

pg-v15:
  hosts: { 10.10.10.15: { pg_seq: 1 ,pg_role: primary } }
  vars:
    pg_cluster: pg-v15
    pg_version: 15

pg-v16:
  hosts: { 10.10.10.16: { pg_seq: 1 ,pg_role: primary } }
  vars:
    pg_cluster: pg-v16
    pg_version: 16

pg-v17:
  hosts: { 10.10.10.17: { pg_seq: 1 ,pg_role: primary } }
  vars:
    pg_cluster: pg-v17
    pg_version: 17

Primary

Let’s start with the simplest case, singleton meta:

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-test

Use the following command to create a primary database instance on the 10.10.10.11 node.

bin/pgsql-add pg-test

Replica

To add a physical replica, you can assign a new instance to pg-test with pg_role set to replica

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }  # <--- newly added
  vars:
    pg_cluster: pg-test

You can create an entire cluster or append a replica to the existing cluster:

bin/pgsql-add pg-test               # init entire cluster in one-pass
bin/pgsql-add pg-test 10.10.10.12   # add replica to existing cluster

Offline

The offline instance is a dedicated replica to serve slow queries, ETL, OLAP traffic and interactive queries, etc…

To add an offline instance, assign a new instance with pg_role set to offline.

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 } # <--- newly added
  vars:
    pg_cluster: pg-test

Offline instance works like common replica instances, but it is used as a backup server in pg-test-replica service. That is to say, offline and primary instances serve only when all replica instances are down.

You can have ad hoc access control offline with pg_default_hba_rules and pg_hba_rules. It will apply to the offline instance and any instances with pg_offline_query flag.

Sync Standby

Pigsty uses asynchronous stream replication by default, which may have a small replication lag (10KB / 10ms). A small window of data loss may occur when the primary fails (can be controlled with pg_rpo), but it is acceptable for most scenarios.

But in some critical scenarios (e.g., financial transactions), data loss is totally unacceptable or read-your-write consistency is required. In this case, you can enable synchronous commit to ensure that.

To enable sync standby mode, you can simply use crit.yml template in pg_conf

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
    pg_conf: crit.yml   # <--- use crit template

To enable sync standby on existing clusters, config the cluster and enable synchronous_mode:

$ pg edit-config pg-test    # run on admin node with admin user
+++
-synchronous_mode: false    # <--- old value
+synchronous_mode: true     # <--- new value
 synchronous_mode_strict: false

Apply these changes? [y/N]: y

If synchronous_mode: true, the synchronous_standby_names parameter will be managed by patroni. It will choose a sync standby from all available replicas and write its name to the primary’s configuration file.

Quorum Commit

When sync standby is enabled, PostgreSQL will pick one replica as the standby instance, and all other replicas as candidates. Primary will wait until the standby instance flushes to disk before a commit is confirmed, and the standby instance will always have the latest data without any lags.

However, you can achieve an even higher/lower consistency level with the quorum commit (trade-off with availability).

For example, to have all 2 replicas to confirm a commit:

synchronous_mode: true          # make sure synchronous mode is enabled
synchronous_node_count: 2       # at least 2 nodes to confirm a commit

If you have more replicas and wish to have more sync standby, increase synchronous_node_count accordingly. Beware of adjust synchronous_node_count accordingly when you append or remove replicas.

The postgres synchronous_standby_names parameter will be managed by patroni:

synchronous_standby_names = '2 ("pg-test-3","pg-test-2")'

The classic quorum commit is to use majority of replicas to confirm a commit.

synchronous_mode: quorum        # use quorum commit
postgresql:
  parameters:                   # change the PostgreSQL parameter `synchronous_standby_names`, use the `ANY n ()` notion
    synchronous_standby_names: 'ANY 1 (*)'  # you can specify a list of standby names, or use `*` to match them all

Standby Cluster

You can clone an existing cluster and create a standby cluster, which can be used for migration, horizontal split, multi-az deployment, or disaster recovery.

A standby cluster’s definition is just the same as any other normal cluster, except there’s a pg_upstream defined on the primary instance.

For example, you have a pg-test cluster, to create a standby cluster pg-test2, the inventory may look like this:

# pg-test is the original cluster
pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
  vars: { pg_cluster: pg-test }

# pg-test2 is a standby cluster of pg-test.
pg-test2:
  hosts:
    10.10.10.12: { pg_seq: 1, pg_role: primary , pg_upstream: 10.10.10.11 } # <--- pg_upstream is defined here
    10.10.10.13: { pg_seq: 2, pg_role: replica }
  vars: { pg_cluster: pg-test2 }

And pg-test2-1, the primary of pg-test2 will be a replica of pg-test and serve as a Standby Leader in pg-test2.

Just make sure that the pg_upstream parameter is configured on the primary of the backup cluster to pull backups from the original upstream automatically.

bin/pgsql-add pg-test     # Creating the original cluster
bin/pgsql-add pg-test2    # Creating a Backup Cluster

Delayed Cluster

A delayed cluster is a special type of standby cluster, which is used to recover “drop-by-accident” ASAP.

For example, if you wish to have a cluster pg-testdelay which has the same data as 1-day ago pg-test cluster:

# pg-test is the original cluster
pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
  vars: { pg_cluster: pg-test }

# pg-testdelay is a delayed cluster of pg-test.
pg-testdelay:
  hosts:
    10.10.10.12: { pg_seq: 1, pg_role: primary , pg_upstream: 10.10.10.11, pg_delay: 1d }
    10.10.10.13: { pg_seq: 2, pg_role: replica }
  vars: { pg_cluster: pg-test2 }

You can also configure a replication delay on the existing standby cluster.

$ pg edit-config pg-testdelay
 standby_cluster:
   create_replica_methods:
   - basebackup
   host: 10.10.10.11
   port: 5432
+  recovery_min_apply_delay: 1h    # <--- add delay here

Apply these changes? [y/N]: y

When some tuples & tables are dropped by accident, you can advance this delayed cluster to a proper time point and select data from it.

It takes more resources, but can be much faster and have less impact than PITR

Citus Cluster

Pigsty has native citus support. Check the conf/citus.yml example.

To define a citus cluster, you have to specify the following parameters:

• pg_mode has to be set to citus instead of default pgsql
• pg_shard & pg_group has to be defined on each sharding cluster
• patroni_primary_db has to be defined to specify the database to be managed
• pg_dbsu_password has to be set to a non-empty string plain password if you want to use the pg_dbsu postgres rather than default pg_admin_username to perform admin commands

Besides, extra hba rules that allow ssl access from local & other data nodes are required. Which may looks like this

all:
  children:
    pg-citus0: # citus data node 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'  }

And you can create distributed table & reference table on the coordinator node. Any data node can be used as the coordinator node since citus 11.2.

SELECT create_distributed_table('pgbench_accounts', 'aid'); SELECT truncate_local_data_after_distributing_table($$public.pgbench_accounts$$);
SELECT create_reference_table('pgbench_branches')         ; SELECT truncate_local_data_after_distributing_table($$public.pgbench_branches$$);
SELECT create_reference_table('pgbench_history')          ; SELECT truncate_local_data_after_distributing_table($$public.pgbench_history$$);
SELECT create_reference_table('pgbench_tellers')          ; SELECT truncate_local_data_after_distributing_table($$public.pgbench_tellers$$);

3 - Parameter

There are 121 parameters about the PGSQL module.

PG_ID

Here are some common parameters used to identify PGSQL entities: instance, service, etc…

pg_mode

name: pg_mode, type: enum, level: C

pgsql cluster mode, pgsql by default, i.e. standard PostgreSQL cluster.

• pgsql: Standard PostgreSQL cluster, default value.
• citus: Horizontal sharding cluster with citus extension.
• mssql: Babelfish MSSQL wire protocol compatible kernel.
• ivory: IvorySQL Oracle compatible kernel.
• polar: PolarDB for PostgreSQL kernel.
• oracle: PolarDB for Oracle kernel.
• gpsql: Greenplum / Cloudberry

If pg_mode is set to citus or gpsql, pg_shard and pg_group will be required for horizontal sharding clusters.

pg_cluster

name: pg_cluster, type: string, level: C

pgsql cluster name, REQUIRED identity parameter

The cluster name will be used as the namespace for PGSQL related resources within that cluster.

The naming needs to follow the specific naming pattern: [a-z][a-z0-9-]* to be compatible with the requirements of different constraints on the identity.

pg_seq

name: pg_seq, type: int, level: I

pgsql instance seq number, REQUIRED identity parameter

A serial number to identify these instances, unique within its cluster, starting from 0 or 1.

pg_role

name: pg_role, type: enum, level: I

pgsql role, REQUIRED, could be primary,replica,offline

Roles for PGSQL instance, can be: primary, replica, standby or offline.

• primary: Primary, there is one and only one primary in a cluster.
• replica: Replica for carrying online read-only traffic, there may be a slight replication delay through (10ms~100ms, 100KB).
• standby: Special replica that is always synced with primary, there’s no replication delay & data loss on this replica. (currently same as replica)
• offline: Offline replica for taking on offline read-only traffic, such as statistical analysis/ETL/personal queries, etc.

Identity params, required params, and instance-level params.

pg_instances

name: pg_instances, type: dict, level: I

define multiple pg instances on node in {port:ins_vars} format.

This parameter is reserved for multi-instance deployment on a single node which is not implemented in Pigsty yet.

pg_upstream

name: pg_upstream, type: ip, level: I

Upstream ip address for standby cluster or cascade replica

Setting pg_upstream is set on primary instance indicate that this cluster is a Standby Cluster, and will receiving changes from upstream instance, thus the primary is actually a standby leader.

Setting pg_upstream for a non-primary instance will explicitly set a replication upstream instance, if it is different from the primary IP Address, this instance will become a cascade replica. And it’s user’s responsibility to ensure that the upstream IP addr is another instance in the same cluster.

pg_shard

name: pg_shard, type: string, level: C

pgsql shard name, required identity parameter for sharding clusters (e.g. citus cluster), optional for common pgsql clusters.

When multiple pgsql clusters serve the same business together in a horizontally sharding style, Pigsty will mark this group of clusters as a Sharding Group.

pg_shard is the name of the shard group name. It’s usually the prefix of pg_cluster.

For example, if we have a sharding group pg-citus, and 4 clusters in it, there identity params will be:

cls pg_shard: pg-citus
cls pg_group = 0:   pg-citus0
cls pg_group = 1:   pg-citus1
cls pg_group = 2:   pg-citus2
cls pg_group = 3:   pg-citus3

pg_group

name: pg_group, type: int, level: C

pgsql shard index number, required identity for sharding clusters, optional for common pgsql clusters.

Sharding cluster index of a sharding group, used in pairs with pg_shard. You can use any non-negative integer as the index number.

gp_role

name: gp_role, type: enum, level: C

greenplum/matrixdb role of this cluster, could be master or segment

• master: mark the postgres cluster as greenplum master, which is the default value
• segment mark the postgres cluster as greenplum segment

This parameter is only used for greenplum & derived databases, and is ignored for common pgsql cluster.

pg_exporters

name: pg_exporters, type: dict, level: C

additional pg_exporters to monitor remote postgres instances, default values: {}

If you wish to monitor remote postgres instances, define them in pg_exporters and load them with pgsql-monitor.yml playbook.

pg_exporters: # list all remote instances here, alloc a unique unused local port as k
    20001: { pg_cluster: pg-foo, pg_seq: 1, pg_host: 10.10.10.10 }
    20004: { pg_cluster: pg-foo, pg_seq: 2, pg_host: 10.10.10.11 }
    20002: { pg_cluster: pg-bar, pg_seq: 1, pg_host: 10.10.10.12 }
    20003: { pg_cluster: pg-bar, pg_seq: 1, pg_host: 10.10.10.13 }

Check PGSQL Monitoring for details.

pg_offline_query

name: pg_offline_query, type: bool, level: I

set to true to enable offline queries on this instance

default value is false

When this parameter is enabled for a PostgreSQL instance, users belonging to the dbrole_offline group can directly connect to that PostgreSQL instance to perform offline queries (slow queries, interactive queries, ETL/analytical queries).

Instances with this flag are functionally similar to setting pg_role = offline, with the only difference being that offline instances by default do not handle replica service requests, as they exist specifically as dedicated offline/analytical replica instances.

If you don’t have spare instances that can be dedicated to this purpose, you can select a regular replica and enable this parameter at the instance level to accommodate offline queries when needed.

PG_BUSINESS

Database credentials, In-Database Objects that need to be taken care of by Users.

• Define Business Users: pg_users
• Define Business Databases: pg_databases
• Define Cluster Services: pg_services (Global Definition: pg_default_services)
• Ad-Hoc PostgreSQL HBA Rules: pg_default_services
• Ad-Hoc Pgbouncer HBA Rules: pgb_hba_rules

Default Database Users:

• Administrator: pg_admin_username / pg_admin_password
• Replication User: pg_replication_username / pg_replication_password
• Monitor User: pg_monitor_username / pg_monitor_password

WARNING: YOU HAVE TO CHANGE THESE DEFAULT PASSWORDs in production environment.

# postgres business object definition, overwrite in group vars
pg_users: []                      # postgres business users
pg_databases: []                  # postgres business databases
pg_services: []                   # postgres business services
pg_hba_rules: []                  # business hba rules for postgres
pgb_hba_rules: []                 # business hba rules for pgbouncer
# global credentials, overwrite in global vars
pg_dbsu_password: ''              # dbsu password, empty string means no dbsu password by default
pg_replication_username: replicator
pg_replication_password: DBUser.Replicator
pg_admin_username: dbuser_dba
pg_admin_password: DBUser.DBA
pg_monitor_username: dbuser_monitor
pg_monitor_password: DBUser.Monitor

pg_users

name: pg_users, type: user[], level: C

postgres business users, defined at cluster level.

default values: [], each object in the array defines a User/Role. Examples:

- name: dbuser_meta               # REQUIRED, `name` is the only mandatory field of a user definition
  password: DBUser.Meta           # optional, the 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 userlist? 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
  search_path: public             # key value config parameters according to postgresql documentation (e.g: use pigsty as default search_path)

The only mandatory field of a user definition is name, and the rest are optional.

pg_databases

name: pg_databases, type: database[], level: C

postgres business databases, defined at cluster level.

default values: [], each object in the array defines a Database. Examples:

- 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 }  # You can specify which schema to install the extension in, or leave it unspecified (if unspecified, it will be installed in the first schema of search_path)
    - { name: timescaledb }               # For example, some extensions will create and use fixed schemas, so you don't need to specify a schema.
    - vector                              # You can also directly use a string to specify the extension name
  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_size_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_max_db_conn: 100           # optional, max database connections at database level, default 100

In each database definition, the DB name is mandatory and the rest are optional.

pg_services

name: pg_services, type: service[], level: C

postgres business services exposed via haproxy, has to be defined at cluster level.

You can define ad hoc services with pg_services in additional to default pg_default_services

default values: [], each object in the array defines a Service. Examples:

- 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

name: pg_hba_rules, type: hba[], level: C

business hba rules for postgres

default values: [], each object in array is an HBA Rule definition:

Which are array of hba object, each hba object may look like

# RAW HBA RULES
- title: allow intranet password access
  role: common
  rules:
    - host   all  all  10.0.0.0/8      md5
    - host   all  all  172.16.0.0/12   md5
    - host   all  all  192.168.0.0/16  md5

• title: Rule Title, transform into comment in the hba file
• rules: Array of strings, each string is a raw hba rule record
• role : Applied roles, where to install these hba rules
  • common: apply for all instances
  • primary, replica,standby, offline: apply on corresponding instances with that pg_role.
  • special case: HBA rule with role == 'offline' will be installed on instance with pg_offline_query flag

or you can use another alias form

- addr: 'intra'    # world|intra|infra|admin|local|localhost|cluster|<cidr>
  auth: 'pwd'      # trust|pwd|ssl|cert|deny|<official auth method>
  user: 'all'      # all|${dbsu}|${repl}|${admin}|${monitor}|<user>|<group>
  db: 'all'        # all|replication|....
  rules: []        # raw hba string precedence over above all
  title: allow intranet password access

pg_default_hba_rules is similar to this, but is used for global HBA rule settings

pgb_hba_rules

name: pgb_hba_rules, type: hba[], level: C

business hba rules for pgbouncer, default values: []

Similar to pg_hba_rules, array of hba rule object, except this is for pgbouncer.

pg_replication_username

name: pg_replication_username, type: username, level: G

postgres replication username, replicator by default

This parameter is globally used, it is not wise to change it.

pg_replication_password

name: pg_replication_password, type: password, level: G

postgres replication password, DBUser.Replicator by default

WARNING: CHANGE THIS IN PRODUCTION ENVIRONMENT!!!!

pg_admin_username

name: pg_admin_username, type: username, level: G

postgres admin username, dbuser_dba by default, which is a global postgres superuser.

default values: dbuser_dba

pg_admin_password

name: pg_admin_password, type: password, level: G

postgres admin password in plain text, DBUser.DBA by default

WARNING: CHANGE THIS IN PRODUCTION ENVIRONMENT!!!!

pg_monitor_username

name: pg_monitor_username, type: username, level: G

postgres monitor username, dbuser_monitor by default, which is a global monitoring user.

pg_monitor_password

name: pg_monitor_password, type: password, level: G

postgres monitor password, DBUser.Monitor by default.

Try not using the @:/ character in the password to avoid problems with PGURL string.

WARNING: CHANGE THIS IN PRODUCTION ENVIRONMENT!!!!

pg_dbsu_password

name: pg_dbsu_password, type: password, level: G/C

PostgreSQL dbsu password for pg_dbsu, empty string means no dbsu password, which is the default behavior.

PG_INSTALL

This section is responsible for installing PostgreSQL & Extensions.

If you wish to install a different major version, make sure repo packages exists and overwrite pg_version on cluster level.

To install extra extensions, overwrite pg_extensions on cluster level. Beware that not all extensions are available with other major versions.

pg_dbsu: postgres                 # os dbsu name, postgres by default, better not change it
pg_dbsu_uid: 26                   # os dbsu uid and gid, 26 for default postgres users and groups
pg_dbsu_sudo: limit               # dbsu sudo privilege, none,limit,all,nopass. limit by default
pg_dbsu_home: /var/lib/pgsql      # postgresql home directory, `/var/lib/pgsql` by default
pg_dbsu_ssh_exchange: true        # exchange postgres dbsu ssh key among same pgsql cluster
pg_version: 18                    # postgres major version to be installed, 18 by default
pg_bin_dir: /usr/pgsql/bin        # postgres binary dir, `/usr/pgsql/bin` by default
pg_log_dir: /pg/log/postgres      # postgres log dir, `/pg/log/postgres` by default
pg_packages:                      # pg packages to be installed, alias can be used
  - pgsql-main pgsql-common
pg_extensions: []                 # pg extensions to be installed, alias can be used

pg_dbsu

name: pg_dbsu, type: username, level: C

OS DBSU name, postgres by default, it’s not wise to change it.

When installing Greenplum / MatrixDB, set this parameter to the corresponding default value: gpadmin|mxadmin.

pg_dbsu_uid

name: pg_dbsu_uid, type: int, level: C

OS DBSU uid and gid, 26 for default postgres users and groups, which is consistent with the official pgdg RPM.

For Ubuntu/Debian, there’s no default postgres UID/GID, consider using another ad hoc value, such as 543 instead.

pg_dbsu_sudo

name: pg_dbsu_sudo, type: enum, level: C

OS DBSU sudo privilege, could be none, limit ,all ,nopass. limit by default

• none: No Sudo privilege
• limit: Limited sudo privilege to execute systemctl commands for database-related components, default.
• all: Full sudo privilege, password required.
• nopass: Full sudo privileges without a password (not recommended).

default values: limit, which only allow sudo systemctl <start|stop|reload> <postgres|patroni|pgbouncer|...>

Available sudo services:

• patroni
• pgbouncer
• postgres
• pg_exporter
• pgbackrest
• pgbouncer_exporter
• pgbackrest_exporter
• vip-manager
• haproxy (reload only)

pg_dbsu_home

name: pg_dbsu_home, type: path, level: C

postgresql home directory, /var/lib/pgsql by default, which is consistent with the official pgdg RPM.

pg_dbsu_ssh_exchange

name: pg_dbsu_ssh_exchange, type: bool, level: C

exchange postgres os dbsu ssh key among pgsql instances?

default value is true, means the dbsu can ssh to each other among the playbook execution hosts.

For scenarios where ssh access is strictly limited, you can set it to false.

Please note that SSH key exchange occurs between instances that are executing the same playbook. If you run the pgsql role for a single PostgreSQL cluster, the key exchange will occur between all instances in that cluster. If you run the pgsql role for all PostgreSQL clusters, the key exchange will occur between all instances, which can lead to severe combinatorial explosions for large clusters. If any instance involved in the key exchange does not have the pg_dbsu user, the key exchange will fail for that instance, but will not affect other instances.

pg_version

name: pg_version, type: enum, level: C

postgres major version to be installed, 18 by default

Note that PostgreSQL physical stream replication cannot cross major versions, so do not configure this on instance level.

You can use the parameters in pg_packages and pg_extensions to install rpm/deb for the specific pg major version.

pg_bin_dir

name: pg_bin_dir, type: path, level: C

postgres binary dir, /usr/pgsql/bin by default

The default value is a soft link created manually during the installation process, pointing to the specific Postgres version dir installed.

For example /usr/pgsql -> /usr/pgsql-17. For more details, check PGSQL File Structure for details.

pg_log_dir

name: pg_log_dir, type: path, level: C

postgres log dir, /pg/log/postgres by default.

caveat: if pg_log_dir is prefixed with pg_data it will not be created explicitly (it will be created by postgres itself then).

pg_packages

name: pg_packages, type: string[], level: C

PostgreSQL packages (rpm/deb) to be installed. This is an array of package names, where each element is a comma or space-separated list of PG package names or aliases.

Default value: [ pgsql-main pgsql-common ]

These default values are two aliases that are translated through alias mapping into the main RPM/DEB package names for the current PG major version, as well as version-independent common components (such as Patroni, PgBackrest, etc.)

Since Pigsty v3, you can use the alias lists specified in the system configuration in roles/node_id/vars for this parameter.

The advantage of using package aliases is that you don’t need to worry about package names, architectures, and major version numbers for PostgreSQL-related packages across different system platforms, thus abstracting away differences between operating systems:

Packages defined here will first be translated through the package_map, then undergo PG major version number substitution, and finally install the actual RPM/DEB packages.

You can also directly specify the final RPM/DEB package names to be installed, where version placeholders like ${pg_version} or $v in the package name will be replaced with the specific major version number pg_version.

pg_extensions

name: pg_extensions, type: string[], level: C

PG extensions to be installed (rpm/deb), this is an array of software package names, each element is a comma or space separated PG extension package name.

This parameter is similar to pg_packages, but is usually used to specify the extension to be installed @ global | cluster level, and the software packages specified here will be upgraded to the latest available version.

The default value of this parameter is the three most important extension plugins in the PG extension ecosystem: postgis, timescaledb, pgvector.

pg_extensions: []

The complete list of extensions can be found in auto generated config

The full extension list can be found in roles/node_id/vars and listed in Extension List.

PG_BOOTSTRAP

Bootstrap postgres cluster with patroni.

It also init cluster template databases with default roles, schemas & extensions & default privileges specified in PG_PROVISION

pg_data: /pg/data                 # postgres data directory, `/pg/data` by default
pg_fs_main: /data/postgres        # postgres main data directory, `/data/postgres` by default
pg_fs_backup: /data/backups       # postgres backup data directory, `/data/backups` by default
pg_storage_type: SSD              # storage type for pg main data, SSD,HDD, SSD by default
pg_dummy_filesize: 64MiB          # size of `/pg/dummy`, hold 64MB disk space for emergency use
pg_listen: '0.0.0.0'              # postgres/pgbouncer listen addresses, comma separated list
pg_port: 5432                     # postgres listen port, 5432 by default
pg_localhost: /var/run/postgresql # postgres unix socket dir for localhost connection
patroni_enabled: true             # if disabled, no postgres cluster will be created during init
patroni_mode: default             # patroni working mode: default,pause,remove
pg_namespace: /pg                 # top level key namespace in etcd, used by patroni & vip
patroni_port: 8008                # patroni listen port, 8008 by default
patroni_log_dir: /pg/log/patroni  # patroni log dir, `/pg/log/patroni` by default
patroni_ssl_enabled: false        # secure patroni RestAPI communications with SSL?
patroni_watchdog_mode: off        # patroni watchdog mode: automatic, required, off. off by default
patroni_username: postgres        # patroni restapi username, `postgres` by default
patroni_password: Patroni.API     # patroni restapi password, `Patroni.API` by default
pg_primary_db: postgres           # primary database name, used by citus,etc... postgres by default
pg_parameters: {}                 # extra parameters in postgresql.auto.conf
pg_files: []                      # extra files to be copied to postgres data directory (e.g. license)
pg_conf: oltp.yml                 # config template: oltp,olap,crit,tiny. `oltp.yml` by default
pg_max_conn: auto                 # postgres max connections, `auto` will use recommended value
pg_shared_buffer_ratio: 0.25      # postgres shared buffers ratio, 0.25 by default, 0.1~0.4
pg_rto: 30                        # recovery time objective in seconds, `30s` by default
pg_rpo: 1048576                   # recovery point objective in bytes, `1MiB` at most by default
pg_libs: 'pg_stat_statements, auto_explain'  # preloaded libraries, `pg_stat_statements,auto_explain` by default
pg_delay: 0                       # replications apply delay for standby cluster leader
pg_checksum: true                 # enable data checksum for postgres cluster?
pg_pwd_enc: scram-sha-256         # passwords encryption algorithm: md5,scram-sha-256
pg_encoding: UTF8                 # database cluster encoding, `UTF8` by default
pg_locale: C                      # database cluster local, `C` by default
pg_lc_collate: C                  # database cluster collate, `C` by default
pg_lc_ctype: C                    # database character type, `C` by default
#pgsodium_key: ""                 # pgsodium key, 64 hex digits, default to sha256(pg_cluster)
#pgsodium_getkey_script: ""       # pgsodium getkey script path, pgsodium_getkey by default

pg_data

name: pg_data, type: path, level: C

postgres data directory, /pg/data by default

default values: /pg/data, DO NOT CHANGE IT.

It’s a soft link that points to the underlying data directory.

pg_fs_main

name: pg_fs_main, type: path, level: C

postgres main data directory, /data/postgres by default.

This directory will be created and owned by the pg_dbsu user, and it will be used as the main data directory for postgres.

If your main data directory node_data is changed, consider changing this parameter as well.

It’s recommended to use NVME SSD for postgres main data storage, Pigsty is optimized for SSD storage by default. If you are using HDD storage, consider changing the pg_storage_type to HDD to optimize for HDD storage.

pg_fs_backup

name: pg_fs_backup, type: path, level: C

postgres backup data directory, /data/backups by default

This directory will be created and owned by the pg_dbsu user, and it will be used as the local backup storage for postgres.

Local backup is enabled by default on primary pg cluster. If you are using the default pgbackrest_method = local, it is recommended to have a separate disk for backup storage. The backup disk should be large enough to hold all your backups, at least enough for 3 base backups + 2-day WAL archive. This is usually not a problem since you can use affordable and large HDD for that.

It’s optional if you are using remote / centralized backup storage (e.g. pgbackrest_method = minio).

pg_storage_type

name: pg_storage_type, type: enum, level: C

storage type for pg main data, SSD,HDD, SSD by default

default values: SSD, it will affect some tuning parameters, such as random_page_cost & effective_io_concurrency

pg_dummy_filesize

name: pg_dummy_filesize, type: size, level: C

size of /pg/dummy, default values: 64MiB, which hold 64MB disk space for emergency use

When the disk is full, removing the placeholder file can free up some space for emergency use, it is recommended to set at least 8GiB for production use.

pg_listen

name: pg_listen, type: ip, level: C

postgres/pgbouncer listen address, 0.0.0.0 (all ipv4 addr) by default

You can use placeholder in this variable:

• ${ip}: translate to inventory_hostname, which is primary private IP address in the inventory
• ${vip}: if pg_vip_enabled, this will translate to host part of pg_vip_address
• ${lo}: will translate to 127.0.0.1

For example: '${ip},${lo}' or '${ip},${vip},${lo}'.

pg_port

name: pg_port, type: port, level: C

postgres listen port, 5432 by default.

pg_localhost

name: pg_localhost, type: path, level: C

postgres unix socket dir for localhost connection, default values: /var/run/postgresql

The Unix socket dir for PostgreSQL and Pgbouncer local connection, which is used by pg_exporter and patroni.

pg_namespace

name: pg_namespace, type: path, level: C

top level key namespace in etcd, used by patroni & vip, default values is: /pg , and it’s not recommended to change it.

patroni_enabled

name: patroni_enabled, type: bool, level: C

if disabled, no postgres cluster will be created during init

default value is true, If disabled, Pigsty will skip pulling up patroni (thus postgres).

This option is useful when trying to add some components to an existing postgres instance.

patroni_mode

name: patroni_mode, type: enum, level: C

patroni working mode: default, pause, remove

default values: default

• default: Bootstrap PostgreSQL cluster with Patroni
• pause: Just like default, but entering maintenance mode after bootstrap
• remove: Init the cluster with Patroni, them remove Patroni and use raw PostgreSQL instead.

patroni_port

name: patroni_port, type: port, level: C

patroni listening port, 8008 by default, changing it is not recommended.

The Patroni API server listens to this port for health checking & API requests.

patroni_log_dir

name: patroni_log_dir, type: path, level: C

patroni log dir, /pg/log/patroni by default, which will be collected by promtail.

patroni_ssl_enabled

name: patroni_ssl_enabled, type: bool, level: G

Secure patroni RestAPI communications with SSL? default value is false

This parameter is a global flag that can only be set before deployment.

Since if SSL is enabled for patroni, you’ll have to perform healthcheck, metrics scrape, and API call with HTTPS instead of HTTP.

patroni_watchdog_mode

name: patroni_watchdog_mode, type: string, level: C

In case of primary failure, patroni can use watchdog to fencing the old primary node to avoid split-brain.

patroni watchdog mode: automatic, required, off:

• off: not using watchdog. avoid fencing at all. This is the default value.
• automatic: Enable watchdog if the kernel has softdog module enabled and watchdog is owned by dbsu
• required: Force watchdog, refuse to start if softdog is not available

default value is off, you should not enable watchdog on infra nodes to avoid fencing.

For those critical systems where data consistency prevails over availability, it is recommended to enable watchdog.

Beware that if all your traffic is accessed via haproxy, there is no risk of brain split at all.

patroni_username

name: patroni_username, type: username, level: C

patroni restapi username, postgres by default, used in pair with patroni_password

Patroni unsafe RESTAPI is protected by username/password by default, check Config Cluster and Patroni RESTAPI for details.

patroni_password

name: patroni_password, type: password, level: C

patroni restapi password, Patroni.API by default

WARNING: CHANGE THIS IN PRODUCTION ENVIRONMENT!!!!

pg_primary_db

name: pg_primary_db, type: string, level: C

primary database name, used by citus,etc… , postgres by default

Patroni 3.0’s native citus will specify a managed database for citus. which is created by patroni itself.

pg_parameters

Parameter Name: pg_parameters, Type: dict, Level: G/C/I

This parameter is used to specify and manage configuration parameters in postgresql.auto.conf.

After all instances in the cluster have completed initialization, the pg_param task will sequentially overwrite the key/value pairs in this dictionary to /pg/data/postgresql.auto.conf.

Note: Please do not manually modify this configuration file, or use ALTER SYSTEM to change cluster configuration parameters. Any changes will be overwritten during the next configuration sync.

This variable has a higher priority than the cluster configuration in Patroni/DCS (i.e., it has a higher priority than the cluster configuration edited by Patroni edit-config). Therefore, it can typically override the cluster default parameters at the instance level.

When your cluster members have different specifications (not recommended!), you can fine-tune the configuration of each instance using this parameter.

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary , pg_parameters: { shared_buffers: '5GB' } }
    10.10.10.12: { pg_seq: 2, pg_role: replica , pg_parameters: { shared_buffers: '4GB' } }
    10.10.10.13: { pg_seq: 3, pg_role: replica , pg_parameters: { shared_buffers: '3GB' } }

Please note that some important cluster parameters (which have requirements for primary and replica parameter values) are managed directly by Patroni through command-line parameters and have the highest priority. These cannot be overridden by this method. For these parameters, you must use Patroni edit-config for management and configuration.

PostgreSQL parameters that must remain consistent across primary and replicas (inconsistency will prevent the replica from starting!):

• wal_level
• max_connections
• max_locks_per_transaction
• max_worker_processes
• max_prepared_transactions
• track_commit_timestamp

Parameters that should ideally remain consistent across primary and replicas (considering the possibility of primary-replica switch):

• listen_addresses
• port
• cluster_name
• hot_standby
• wal_log_hints
• max_wal_senders
• max_replication_slots
• wal_keep_segments
• wal_keep_size

You can set non-existent parameters (such as GUCs from extensions), but changing existing configurations to illegal values may prevent PostgreSQL from starting. Please configure with caution!

pg_files

Parameter Name: pg_files, Type: path[], Level: C

Designates a list of files to be copied to the {{ pg_data }} directory. The default value is an empty array: [].

Files specified in this parameter will be copied to the {{ pg_data }} directory. This is mainly used to distribute license files required by special commercial versions of the PostgreSQL kernel.

Currently, only the PolarDB (Oracle-compatible) kernel requires a license file. For example, you can place the license.lic file in the files/ directory and specify it in pg_files:

pg_files: [ license.lic ]

pg_conf

name: pg_conf, type: enum, level: C

config template: {oltp,olap,crit,tiny}.yml, oltp.yml by default

• tiny.yml: optimize for tiny nodes, virtual machines, small demo, (18Core, 116GB)
• oltp.yml: optimize for OLTP workloads and latency-sensitive applications, (4C8GB+), which is the default template
• olap.yml: optimize for OLAP workloads and throughput (4C8G+)
• crit.yml: optimize for data consistency and critical applications (4C8G+)

default values: oltp.yml, but configure procedure will set this value to tiny.yml if current node is a tiny node.

You can have your own template, just put it under templates/<mode>.yml and set this value to the template name.

pg_max_conn

name: pg_max_conn, type: int, level: C

postgres max connections, You can specify a value between 50 and 5000, or use auto to use recommended value.

default value is auto, which will set max connections according to the pg_conf and pg_default_service_dest.

• tiny: 250
• olap: 500
• crit: 500 (pgbouncer) / 1000 (postgres)
• oltp: 500 (pgbouncer) / 1000 (postgres)

It’s not recommended to set this value greater than 5000, otherwise you have to increase the haproxy service connection limit manually as well.

Pgbouncer’s transaction pooling can alleviate the problem of too many OLTP connections, but it’s not recommended to use it in OLAP scenarios.

pg_shared_buffer_ratio

name: pg_shared_buffer_ratio, type: float, level: C

postgres shared buffer memory ratio, 0.25 by default, 0.1~0.4

default values: 0.25, means 25% of node memory will be used as PostgreSQL shard buffers.

Setting this value greater than 0.4 (40%) is usually not a good idea.

Note that shared buffer is only part of shared memory in PostgreSQL, to calculate the total shared memory, use show shared_memory_size_in_huge_pages;.

pg_rto

name: pg_rto, type: int, level: C

recovery time objective in seconds, This will be used as Patroni TTL value, 30s by default.

If a primary instance is missing for such a long time, a new leader election will be triggered.

Decreasing the value can reduce the unavailable time (unable to write) of the cluster during failover, but it will make the cluster more sensitive to network jitter, thus increase the chance of false-positive failover.

Config this according to your network condition and expectation to trade-off between chance and impact, the default value is 30s, and it will be populated to the following patroni parameters:

# the TTL to acquire the leader lock (in seconds). Think of it as the length of time before initiation of the automatic failover process. Default value: 30
ttl: {{ pg_rto }}

# the number of seconds the loop will sleep. Default value: 10 , this is patroni check loop interval
loop_wait: {{ (pg_rto / 3)|round(0, 'ceil')|int }}

# timeout for DCS and PostgreSQL operation retries (in seconds). DCS or network issues shorter than this will not cause Patroni to demote the leader. Default value: 10
retry_timeout: {{ (pg_rto / 3)|round(0, 'ceil')|int }}

# the amount of time a primary is allowed to recover from failures before failover is triggered (in seconds), Max RTO: 2 loop wait + primary_start_timeout
primary_start_timeout: {{ (pg_rto / 3)|round(0, 'ceil')|int }}

pg_rpo

name: pg_rpo, type: int, level: C

recovery point objective in bytes, 1MiB at most by default

default values: 1048576, which will tolerate at most 1MiB data loss during failover.

when the primary is down and all replicas are lagged, you have to make a tough choice to trade off between Availability and Consistency:

• Promote a replica to be the new primary and bring the system back online ASAP, with the price of an acceptable data loss (e.g. less than 1MB).
• Wait for the primary to come back (which may never be) or human intervention to avoid any data loss.

You can use crit.yml conf template to ensure no data loss during failover, but it will sacrifice some performance.

pg_libs

name: pg_libs, type: string, level: C

shared preloaded libraries, pg_stat_statements,auto_explain by default.

They are two extensions that come with PostgreSQL, and it is strongly recommended to enable them.

For existing clusters, you can configure the shared_preload_libraries parameter of the cluster and apply it.

If you want to use TimescaleDB or Citus extensions, you need to add timescaledb or citus to this list. timescaledb and citus should be placed at the top of this list, for example:

citus,timescaledb,pg_stat_statements,auto_explain

Other extensions that need to be loaded can also be added to this list, such as pg_cron, pgml, etc.

Generally, citus and timescaledb have the highest priority and should be added to the top of the list.

pg_delay

name: pg_delay, type: interval, level: I

replications apply delay for standby cluster leader, default values: 0.

if this value is set to a positive value, the standby cluster leader will be delayed for this time before apply WAL changes.

Check delayed standby cluster for details.

pg_checksum

name: pg_checksum, type: bool, level: C

enable data checksum for postgres cluster? The v3.7.0 default value is true.

This parameter can only be set before PGSQL deployment. (but you can enable it manually later)

If pg_conf crit.yml template is used, data checksum is always enabled regardless of this parameter to ensure data integrity.

pg_pwd_enc

name: pg_pwd_enc, type: enum, level: C

password encryption algorithm: md5, scram-sha-256

default values: scram-sha-256, if you have compatibility issues with old clients, you can set it to md5 instead.

pg_encoding

name: pg_encoding, type: enum, level: C

database cluster encoding, UTF8 by default

pg_locale

name: pg_locale, type: enum, level: C

The locale set for PostgreSQL, default is C.

When configure detects that the current PG version is greater than or equal to 17, or the current system explicitly supports C.utf8, it will automatically configure this parameter to C.UTF-8.

When the PostgreSQL version is greater than or equal to 17, the C and C.UTF-8 configurations will use the PostgreSQL internal Locale Provider.

Unless you are very clear about what you are doing, it is strongly recommended to use the default C or C.UTF-8 configuration.

pg_lc_collate

name: pg_lc_collate, type: enum, level: C

The locale set for PostgreSQL, default is C.

When configure detects that the current PG version is greater than or equal to 17, or the current system explicitly supports C.utf8, it will automatically configure this parameter to C.UTF-8.

Unless you are very clear about what you are doing, it is strongly recommended to use the default C or C.UTF-8 configuration.

The parameter behaves like pg_locale, but for collate.

pg_lc_ctype

name: pg_lc_ctype, type: enum, level: C

The locale set for PostgreSQL, default is C.

When configure detects that the current PG version is greater than or equal to 17, or the current system explicitly supports C.utf8, it will automatically configure this parameter to C.UTF-8.

When the PostgreSQL version is greater than or equal to 17, the C and C.UTF-8 configurations will use the PostgreSQL internal Locale Provider.

This parameter behaves like pg_locale, but for ctype.

Unless you are very clear about what you are doing, it is strongly recommended to use the default C or C.UTF-8 configuration.

pgsodium_key

name: pgsodium_key, type: string, level: C

Default value is not defined, which will use the SHA256 hash of the pg_cluster as the key.

You can provide a custom pgsodium key, which should be a 64 hex digit string.

The key will be written to /pg/conf/pgsodium.key.

pgsodium_getkey_script

name: pgsodium_getkey_script, type: path, level: C

default value is pgsodium_getkey, which render the roles/pgsql/templates/pgsodium_getkey to /pg/bin/pgsodium_getkey.

The default getkey script will just read the pgsodium_key from /pg/conf/pgsodium.key, and return it. If your key is managed by external system like KMS, IAM, …, you can implement your own getkey script to fetch the key from there: examples.

PG_PROVISION

PG_BOOTSTRAP will bootstrap a new postgres cluster with patroni, while PG_PROVISION will create default objects in the cluster, including:

• Default Roles
• Default Users
• Default Privileges
• Default HBA Rules
• Default Schemas
• Default Extensions

pg_provision: true                # provision postgres cluster after bootstrap
pg_init: pg-init                  # provision init script for cluster template, `pg-init` by default
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                                          ,comment: system superuser }
  - { name: replicator ,replication: true  ,roles: [pg_monitor, dbrole_readonly]   ,comment: system replicator }
  - { name: dbuser_dba   ,superuser: true  ,roles: [dbrole_admin]  ,pgbouncer: true ,pool_mode: session, pool_connlimit: 16 , comment: pgsql admin user }
  - { name: dbuser_monitor   ,roles: [pg_monitor, dbrole_readonly] ,pgbouncer: true ,parameters: {log_min_duration_statement: 1000 } ,pool_mode: session ,pool_connlimit: 8 ,comment: pgsql monitor user }
pg_default_privileges:            # default privileges when created by admin user
  - GRANT USAGE      ON SCHEMAS   TO dbrole_readonly
  - GRANT SELECT     ON TABLES    TO dbrole_readonly
  - GRANT SELECT     ON SEQUENCES TO dbrole_readonly
  - GRANT EXECUTE    ON FUNCTIONS TO dbrole_readonly
  - GRANT USAGE      ON SCHEMAS   TO dbrole_offline
  - GRANT SELECT     ON TABLES    TO dbrole_offline
  - GRANT SELECT     ON SEQUENCES TO dbrole_offline
  - GRANT EXECUTE    ON FUNCTIONS TO dbrole_offline
  - GRANT INSERT     ON TABLES    TO dbrole_readwrite
  - GRANT UPDATE     ON TABLES    TO dbrole_readwrite
  - GRANT DELETE     ON TABLES    TO dbrole_readwrite
  - GRANT USAGE      ON SEQUENCES TO dbrole_readwrite
  - GRANT UPDATE     ON SEQUENCES TO dbrole_readwrite
  - GRANT TRUNCATE   ON TABLES    TO dbrole_admin
  - GRANT REFERENCES ON TABLES    TO dbrole_admin
  - GRANT TRIGGER    ON TABLES    TO dbrole_admin
  - GRANT CREATE     ON SCHEMAS   TO dbrole_admin
pg_default_schemas: [ monitor ]   # default schemas to be created
pg_default_extensions:            # default extensions to be created
  - { name: pg_stat_statements ,schema: monitor }
  - { name: pgstattuple        ,schema: monitor }
  - { name: pg_buffercache     ,schema: monitor }
  - { name: pageinspect        ,schema: monitor }
  - { name: pg_prewarm         ,schema: monitor }
  - { name: pg_visibility      ,schema: monitor }
  - { name: pg_freespacemap    ,schema: monitor }
  - { name: postgres_fdw       ,schema: public  }
  - { name: file_fdw           ,schema: public  }
  - { name: btree_gist         ,schema: public  }
  - { name: btree_gin          ,schema: public  }
  - { name: pg_trgm            ,schema: public  }
  - { name: intagg             ,schema: public  }
  - { name: intarray           ,schema: public  }
  - { name: pg_repack }
pg_reload: true                   # reload postgres/pgbouncer/vip after conf changes
pg_default_hba_rules:             # postgres default host-based authentication rules
  - {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: pwd   ,title: 'replicator replication from localhost'}
  - {user: '${repl}'    ,db: replication ,addr: intra     ,auth: pwd   ,title: 'replicator replication from intranet' }
  - {user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: pwd   ,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: pwd   ,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: ssl   ,title: 'admin @ everywhere with ssl & pwd'    }
  - {user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: pwd   ,title: 'pgbouncer read/write via local socket'}
  - {user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: pwd   ,title: 'read/write biz user via password'     }
  - {user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: pwd   ,title: 'allow etl offline tasks from intranet'}
pgb_default_hba_rules:            # pgbouncer default 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: pwd   ,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: pwd   ,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: pwd   ,title: 'allow all user intra access with pwd' }

pg_provision

name: pg_provision, type: bool, level: C

provision postgres cluster after bootstrap, default value is true.

If disabled, postgres cluster will not be provisioned after bootstrap.

pg_init

name: pg_init, type: string, level: G/C

Provision init script for cluster template, pg-init by default, which is located in roles/pgsql/templates/pg-init

You can add your own logic in the init script, or provide a new one in templates/ and set pg_init to the new script name.

pg_default_roles

name: pg_default_roles, type: role[], level: G/C

default roles and users in postgres cluster.

Pigsty has a built-in role system, check PGSQL Access Control for details.

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                                          ,comment: system superuser }
  - { name: replicator ,replication: true  ,roles: [pg_monitor, dbrole_readonly]   ,comment: system replicator }
  - { name: dbuser_dba   ,superuser: true  ,roles: [dbrole_admin]  ,pgbouncer: true ,pool_mode: session, pool_connlimit: 16 , comment: pgsql admin user }
  - { name: dbuser_monitor   ,roles: [pg_monitor, dbrole_readonly] ,pgbouncer: true ,parameters: {log_min_duration_statement: 1000 } ,pool_mode: session ,pool_connlimit: 8 ,comment: pgsql monitor user }

pg_default_privileges

name: pg_default_privileges, type: string[], level: G/C

default privileges for each databases:

pg_default_privileges:            # default privileges when created by admin user
  - GRANT USAGE      ON SCHEMAS   TO dbrole_readonly
  - GRANT SELECT     ON TABLES    TO dbrole_readonly
  - GRANT SELECT     ON SEQUENCES TO dbrole_readonly
  - GRANT EXECUTE    ON FUNCTIONS TO dbrole_readonly
  - GRANT USAGE      ON SCHEMAS   TO dbrole_offline
  - GRANT SELECT     ON TABLES    TO dbrole_offline
  - GRANT SELECT     ON SEQUENCES TO dbrole_offline
  - GRANT EXECUTE    ON FUNCTIONS TO dbrole_offline
  - GRANT INSERT     ON TABLES    TO dbrole_readwrite
  - GRANT UPDATE     ON TABLES    TO dbrole_readwrite
  - GRANT DELETE     ON TABLES    TO dbrole_readwrite
  - GRANT USAGE      ON SEQUENCES TO dbrole_readwrite
  - GRANT UPDATE     ON SEQUENCES TO dbrole_readwrite
  - GRANT TRUNCATE   ON TABLES    TO dbrole_admin
  - GRANT REFERENCES ON TABLES    TO dbrole_admin
  - GRANT TRIGGER    ON TABLES    TO dbrole_admin
  - GRANT CREATE     ON SCHEMAS   TO dbrole_admin

Pigsty has a built-in privileges based on the default role system, check PGSQL Privileges for details.

pg_default_schemas

name: pg_default_schemas, type: string[], level: G/C

default schemas to be created, default values is: [ monitor ], which will create a monitor schema on all databases.

pg_default_extensions

name: pg_default_extensions, type: extension[], level: G/C

default extensions to be created, default value:

pg_default_extensions: # default extensions to be created
  - { name: pg_stat_statements ,schema: monitor }
  - { name: pgstattuple        ,schema: monitor }
  - { name: pg_buffercache     ,schema: monitor }
  - { name: pageinspect        ,schema: monitor }
  - { name: pg_prewarm         ,schema: monitor }
  - { name: pg_visibility      ,schema: monitor }
  - { name: pg_freespacemap    ,schema: monitor }
  - { name: postgres_fdw       ,schema: public  }
  - { name: file_fdw           ,schema: public  }
  - { name: btree_gist         ,schema: public  }
  - { name: btree_gin          ,schema: public  }
  - { name: pg_trgm            ,schema: public  }
  - { name: intagg             ,schema: public  }
  - { name: intarray           ,schema: public  }
  - { name: pg_repack }

The only 3rd party extension is pg_repack, which is important for database maintenance, all other extensions are built-in postgres contrib extensions.

Monitor related extensions are installed in monitor schema, which is created by pg_default_schemas.

pg_reload

name: pg_reload, type: bool, level: A

reload postgres after hba changes, default value is true

This is useful when you want to check before applying HBA changes, set it to false to disable reload.

pg_default_hba_rules

name: pg_default_hba_rules, type: hba[], level: G/C

postgres default host-based authentication rules, array of hba rule object.

default value provides a fair enough security level for common scenarios, check PGSQL Authentication for details.

pg_default_hba_rules:             # postgres default host-based authentication rules
  - {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: pwd   ,title: 'replicator replication from localhost'}
  - {user: '${repl}'    ,db: replication ,addr: intra     ,auth: pwd   ,title: 'replicator replication from intranet' }
  - {user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: pwd   ,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: pwd   ,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: ssl   ,title: 'admin @ everywhere with ssl & pwd'    }
  - {user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: pwd   ,title: 'pgbouncer read/write via local socket'}
  - {user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: pwd   ,title: 'read/write biz user via password'     }
  - {user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: pwd   ,title: 'allow etl offline tasks from intranet'}

pgb_default_hba_rules

name: pgb_default_hba_rules, type: hba[], level: G/C

pgbouncer default host-based authentication rules, array or hba rule object.

default value provides a fair enough security level for common scenarios, check PGSQL Authentication for details.

pgb_default_hba_rules:            # pgbouncer default 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: pwd   ,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: pwd   ,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: pwd   ,title: 'allow all user intra access with pwd' }

PG_BACKUP

This section defines variables for pgBackRest, which is used for PGSQL PITR (Point-In-Time-Recovery).

Check PGSQL Backup & PITR for details.

pgbackrest_enabled: true          # enable pgbackrest on pgsql host?
pgbackrest_clean: true            # remove pg backup data during init?
pgbackrest_log_dir: /pg/log/pgbackrest # pgbackrest log dir, `/pg/log/pgbackrest` by default
pgbackrest_method: local          # pgbackrest repo method: local,minio,[user-defined...]
pgbackrest_init_backup: true      # take a full backup after pgbackrest is initialized?
pgbackrest_repo:                  # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
  local:                          # default pgbackrest repo with local posix fs
    path: /pg/backup              # local backup directory, `/pg/backup` by default
    retention_full_type: count    # retention full backups by count
    retention_full: 2             # keep 2, at most 3 full backups when using local fs repo
  minio:                          # optional minio repo for pgbackrest
    type: s3                      # minio is s3-compatible, so s3 is used
    s3_endpoint: sss.pigsty       # minio endpoint domain name, `sss.pigsty` by default
    s3_region: us-east-1          # minio region, us-east-1 by default, useless for minio
    s3_bucket: pgsql              # minio bucket name, `pgsql` by default
    s3_key: pgbackrest            # minio user access key for pgbackrest
    s3_key_secret: S3User.Backup  # minio user secret key for pgbackrest
    s3_uri_style: path            # use path style uri for minio rather than host style
    path: /pgbackrest             # minio backup path, default is `/pgbackrest`
    storage_port: 9000            # minio port, 9000 by default
    storage_ca_file: /etc/pki/ca.crt  # minio ca file path, `/etc/pki/ca.crt` by default
    block: y                      # Enable block incremental backup
    bundle: y                     # bundle small files into a single file
    bundle_limit: 20MiB           # Limit for file bundles, 20MiB for object storage
    bundle_size: 128MiB           # Target size for file bundles, 128MiB for object storage
    cipher_type: aes-256-cbc      # enable AES encryption for remote backup repo
    cipher_pass: pgBackRest       # AES encryption password, default is 'pgBackRest'
    retention_full_type: time     # retention full backup by time on minio repo
    retention_full: 14            # keep full backup for the last 14 days

pgbackrest_enabled

name: pgbackrest_enabled, type: bool, level: C

enable pgBackRest on pgsql host? default value is true

When using the local file system backup repository (local), only the primary instance of the cluster will actually enable pgbackrest. Other instances will only initialize an empty repository.

pgbackrest_clean

name: pgbackrest_clean, type: bool, level: C

remove pg backup data during init? default value is true

pgbackrest_log_dir

name: pgbackrest_log_dir, type: path, level: C

pgBackRest log dir, /pg/log/pgbackrest by default, which is referenced by promtail the logging agent.

pgbackrest_method

name: pgbackrest_method, type: enum, level: C

pgBackRest repo method: local, minio, or other user-defined methods, local by default

This parameter is used to determine which repo to use for pgBackRest, all available repo methods are defined in pgbackrest_repo.

Pigsty will use local backup repo by default, which will create a backup repo on primary instance’s /pg/backup directory. The underlying storage is specified by pg_fs_backup.

pgbackrest_init_backup

name: pgbackrest_init_backup, type: bool, level: C

Take a full backup after pgBackRest is initialized? default value is true.

An initial pgbackrest backup is created after repo init if:

• pgbackrest_init_backup is true (and pgbackrest_enabled is true of course)
• The /etc/pgbackrest/initial.done marker file doesn’t exist (will be created after the initial backup is done).

If you don’t want to take an initial full backup at all, just set this parameter tofalse.

pgbackrest_repo

name: pgbackrest_repo, type: dict, level: G/C

pgBackRest repo document: https://pgbackrest.org/configuration.html#section-repository

default value includes two repo methods: local and minio, which are defined as follows:

pgbackrest_repo:                  # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
  local:                          # default pgbackrest repo with local posix fs
    path: /pg/backup              # local backup directory, `/pg/backup` by default
    retention_full_type: count    # retention full backups by count
    retention_full: 2             # keep 2, at most 3 full backups when using local fs repo
  minio:                          # optional minio repo for pgbackrest
    type: s3                      # minio is s3-compatible, so s3 is used
    s3_endpoint: sss.pigsty       # minio endpoint domain name, `sss.pigsty` by default
    s3_region: us-east-1          # minio region, us-east-1 by default, useless for minio
    s3_bucket: pgsql              # minio bucket name, `pgsql` by default
    s3_key: pgbackrest            # minio user access key for pgbackrest
    s3_key_secret: S3User.Backup  # minio user secret key for pgbackrest
    s3_uri_style: path            # use path style uri for minio rather than host style
    path: /pgbackrest             # minio backup path, default is `/pgbackrest`
    storage_port: 9000            # minio port, 9000 by default
    storage_ca_file: /etc/pki/ca.crt  # minio ca file path, `/etc/pki/ca.crt` by default
    block: y                      # Enable block incremental backup
    bundle: y                     # bundle small files into a single file
    bundle_limit: 20MiB           # Limit for file bundles, 20MiB for object storage
    bundle_size: 128MiB           # Target size for file bundles, 128MiB for object storage
    cipher_type: aes-256-cbc      # enable AES encryption for remote backup repo
    cipher_pass: pgBackRest       # AES encryption password, default is 'pgBackRest'
    retention_full_type: time     # retention full backup by time on minio repo
    retention_full: 14            # keep full backup for the last 14 days

You can define a new backup repository, for example, using AWS S3, GCP or another cloud provider’s S3-compatible storage service.

In the backup repository definition parameters, you can use ${pg_cluster} variable to reference the cluster name, for example, as part of the backup path or encryption key. But if you have cross-cluster PITR requirements, you should keep the backup repository path and encryption key the same.

PG_ACCESS

This section is about exposing PostgreSQL service to the outside world: including:

• Connection Pooling with pgbouncer
• Exposing different PostgreSQL services on different ports with haproxy
• Bind an optional L2 VIP to the primary instance with vip-manager
• Register cluster/instance DNS records with to dnsmasq on infra nodes

pgbouncer_enabled: true           # if disabled, pgbouncer will not be launched on pgsql host
pgbouncer_port: 6432              # pgbouncer listen port, 6432 by default
pgbouncer_log_dir: /pg/log/pgbouncer  # pgbouncer log dir, `/pg/log/pgbouncer` by default
pgbouncer_auth_query: false       # query postgres to retrieve unlisted business users?
pgbouncer_poolmode: transaction   # pooling mode: transaction,session,statement, transaction by default
pgbouncer_sslmode: disable        # pgbouncer client ssl mode, disable by default

pg_weight: 100          #INSTANCE # relative load balance weight in service, 100 by default, 0-255
pg_default_service_dest: pgbouncer # default service destination if svc.dest='default'
pg_default_services:              # postgres default service definitions
  - { name: primary ,port: 5433 ,dest: default  ,check: /primary   ,selector: "[]" }
  - { name: replica ,port: 5434 ,dest: default  ,check: /read-only ,selector: "[]" , backup: "[? pg_role == `primary` || pg_role == `offline` ]" }
  - { name: default ,port: 5436 ,dest: postgres ,check: /primary   ,selector: "[]" }
  - { name: offline ,port: 5438 ,dest: postgres ,check: /replica   ,selector: "[? pg_role == `offline` || pg_offline_query ]" , backup: "[? pg_role == `replica` && !pg_offline_query]"}
pg_vip_enabled: false             # enable a l2 vip for pgsql primary? false by default
pg_vip_address: 127.0.0.1/24      # vip address in `<ipv4>/<mask>` format, require if vip is enabled
pg_vip_interface: eth0            # vip network interface to listen, eth0 by default
pg_dns_suffix: ''                 # pgsql dns suffix, '' by default
pg_dns_target: auto               # auto, primary, vip, none, or ad hoc ip

pgbouncer_enabled

name: pgbouncer_enabled, type: bool, level: C

default value is true, if disabled, pgbouncer will not be launched on pgsql host

pgbouncer_port

name: pgbouncer_port, type: port, level: C

pgbouncer listen port, 6432 by default

pgbouncer_log_dir

name: pgbouncer_log_dir, type: path, level: C

pgbouncer log dir, /pg/log/pgbouncer by default, referenced by promtail the logging agent.

pgbouncer_auth_query

name: pgbouncer_auth_query, type: bool, level: C