Parameters

The PGSQL module needs to be installed on nodes managed by Pigsty (i.e., nodes that have the NODE module configured), and also requires an available ETCD cluster in your deployment to store cluster metadata.

Installing the PGSQL module on a single node will create a standalone PGSQL server/instance, i.e., a primary instance. Installing on additional nodes will create read replicas, which can serve as standby instances and handle read-only requests. You can also create offline instances for ETL/OLAP/interactive queries, use sync standby and quorum commit to improve data consistency, or even set up standby clusters and delayed clusters to quickly respond to data loss caused by human errors and software defects.

You can define multiple PGSQL clusters and further organize them into a horizontal sharding cluster: Pigsty natively supports Citus cluster groups, allowing you to upgrade your standard PGSQL cluster in-place to a distributed database cluster.

Parameter Overview

PG_ID parameters are used to define PostgreSQL cluster and instance identity, including cluster name, instance sequence number, role, shard, and other core identity parameters.

PG_BUSINESS parameters are used to define business users, databases, services and access control rules, as well as default system user credentials.

PG_INSTALL parameters are used to configure PostgreSQL installation options, including version, paths, packages, and extensions.

PG_BOOTSTRAP parameters are used to configure PostgreSQL cluster initialization, including Patroni high availability, storage paths, networking, encoding, and other core settings.

PG_PROVISION parameters are used to configure PostgreSQL cluster template provisioning, including default roles, privileges, schemas, extensions, and HBA rules.

PG_BACKUP parameters are used to configure pgBackRest backup and recovery, including repository type, paths, and retention policies.

PG_ACCESS parameters are used to configure service exposure, connection pooling, VIP, DNS, and other client access options.

PG_MONITOR parameters are used to configure PostgreSQL monitoring exporters, including pg_exporter, pgbouncer_exporter, and pgbackrest_exporter.

PG_REMOVE parameters are used to configure PostgreSQL instance cleanup and uninstall behavior, including data directory, backup, and package removal control.

PG_ID

Here are commonly used parameters for identifying entities in the PGSQL module: clusters, instances, services, etc…

# pg_cluster:           #CLUSTER  # pgsql cluster name, required identity parameter
# pg_seq: 0             #INSTANCE # pgsql instance seq number, required identity parameter
# pg_role: replica      #INSTANCE # pgsql role, required, could be primary,replica,offline
# pg_instances: {}      #INSTANCE # define multiple pg instances on node in `{port:ins_vars}` format
# pg_upstream:          #INSTANCE # repl upstream ip addr for standby cluster or cascade replica
# pg_shard:             #CLUSTER  # pgsql shard name, optional identity for sharding clusters
# pg_group: 0           #CLUSTER  # pgsql shard index number, optional identity for sharding clusters
# gp_role: master       #CLUSTER  # greenplum role of this cluster, could be master or segment
pg_offline_query: false #INSTANCE # set to true to enable offline query on this instance

You must explicitly specify these identity parameters, they have no default values:

• pg_cluster: Identifies the cluster name, configured at cluster level.
• pg_role: Configured at instance level, identifies the role of the instance. Only primary role is treated specially. If not specified, defaults to replica role, with special delayed and offline roles.
• pg_seq: Used to identify instances within a cluster, typically an integer starting from 0 or 1, once assigned it doesn’t change.
• {{ pg_cluster }}-{{ pg_seq }} uniquely identifies an instance, i.e., pg_instance.
• {{ pg_cluster }}-{{ pg_role }} identifies services within the cluster, i.e., pg_service.
• pg_shard and pg_group are used for horizontal sharding clusters, only for citus, greenplum, and matrixdb.

pg_cluster, pg_role, pg_seq are core identity parameters, required for any Postgres cluster and must be explicitly specified. Here is an example:

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

All other parameters can be inherited from global or default configuration, but identity parameters must be explicitly specified and manually assigned.

pg_mode

Parameter Name: pg_mode, Type: enum, Level: C

PostgreSQL cluster mode, default value is pgsql, i.e., standard PostgreSQL cluster.

Available mode options include:

• pgsql: Standard PostgreSQL cluster
• citus: Citus distributed database cluster
• mssql: Babelfish MSSQL wire protocol compatible kernel
• mysql: OpenHalo/HaloDB MySQL wire protocol compatible kernel
• ivory: IvorySQL Oracle compatible kernel
• pgtde: Percona PostgreSQL with pg_tde
• polar: PolarDB for PostgreSQL kernel
• gpsql: Greenplum parallel database cluster (monitoring)
• agens: AgensGraph graph database kernel
• oriole: OrioleDB storage-engine kernel
• pgedge: pgEdge distributed-replication kernel

pg_shard and pg_group default to pg_cluster and 0, respectively. When pg_mode is citus or gpsql and the sharded system contains multiple physical clusters, set both explicitly to define the horizontal-sharding identity.

In both cases, each PostgreSQL cluster is part of a larger business unit.

pg_cluster

Parameter Name: pg_cluster, Type: string, Level: C

PostgreSQL cluster name, required identity parameter, no default value.

The cluster name is used as the namespace for resources.

The current role validation accepts names matching ^[A-Za-z0-9-]+$ and rejects root. To keep DNS names, service names, and operation scripts consistent, lowercase names beginning with a letter and containing only lowercase letters, digits, and hyphens are still recommended.

pg_seq

Parameter Name: pg_seq, Type: int, Level: I

PostgreSQL instance sequence number, required identity parameter, no default value.

The sequence number of this instance, uniquely assigned within its cluster, typically using natural numbers starting from 0 or 1, usually not recycled or reused.

pg_role

Parameter Name: pg_role, Type: enum, Level: I

PostgreSQL instance role, required identity parameter with no default. Current validation accepts primary, replica, standby, offline, and delayed.

The commonly used service-membership labels are:

• primary: Primary instance, there is one and only one in a cluster.
• replica: Replica for serving online read-only traffic, may have slight replication delay under high load (10ms~100ms, 100KB).
• offline: Offline replica for handling offline read-only traffic, such as analytics/ETL/personal queries.

standby and delayed are also valid inventory role values, but these strings alone do not create a standby cluster or delayed replication. Configure the topology with pg_upstream and pg_delay. Role-filtered HBA rules use these inventory labels directly.

pg_instances

Parameter Name: pg_instances, Type: dict, Level: I

Define multiple PostgreSQL instances on a single host using {port:ins_vars} format.

This parameter is reserved for multi-instance deployment on a single node. Pigsty has not yet implemented this feature and strongly recommends dedicated node deployment.

pg_upstream

Parameter Name: pg_upstream, Type: ip, Level: I

Upstream instance IP address for standby cluster or cascade replica.

Setting pg_upstream on the primary instance of a cluster indicates this cluster is a standby cluster, and this instance will act as a standby leader, receiving and applying changes from the upstream cluster.

Setting pg_upstream on a non-primary instance specifies a specific instance as the upstream for physical replication. If different from the primary instance IP address, this instance becomes a cascade replica. It is the user’s responsibility to ensure the upstream IP address is another instance in the same cluster.

pg_shard

Parameter Name: pg_shard, Type: string, Level: C

PostgreSQL horizontal shard name, defaulting to pg_cluster. Specify it explicitly for horizontal-sharding systems made up of multiple physical clusters, such as Citus.

When multiple standard PostgreSQL clusters serve the same business together in a horizontal sharding manner, Pigsty marks this group of clusters as a horizontal sharding cluster.

pg_shard is the shard group name. It is typically a prefix of pg_cluster.

For example, if we have a shard group pg-citus with 4 clusters, their identity parameters would 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

Parameter Name: pg_group, Type: int, Level: C

PostgreSQL horizontal-sharding cluster index, defaulting to 0. Specify it explicitly for horizontal-sharding systems made up of multiple physical clusters, such as Citus.

This parameter is used in conjunction with pg_shard, typically using non-negative integers as index numbers.

gp_role

Parameter Name: gp_role, Type: enum, Level: C

Greenplum/Matrixdb role of the PostgreSQL cluster, can be master or segment.

• master: Marks the postgres cluster as a greenplum master instance (coordinator node), this is the default value.
• segment: Marks the postgres cluster as a greenplum segment cluster (data node).

This parameter is only used for Greenplum/MatrixDB databases (pg_mode is gpsql) and has no meaning for regular PostgreSQL clusters.

pg_exporters

Parameter Name: pg_exporters, Type: dict, Level: C

Additional exporter definitions for monitoring remote PostgreSQL instances, default value: {}

If you want to monitor remote PostgreSQL instances, define them in the pg_exporters parameter on the cluster where the monitoring system resides (Infra node), and use the pgsql-monitor.yml playbook to complete the deployment.

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 }

pg_offline_query

Parameter Name: pg_offline_query, Type: bool, Level: I

Set to true to mark this instance as eligible for offline queries. The default is false.

The flag adds the instance to the default offline service candidate set and activates HBA rules with role: offline on that instance. It does not grant connection access by itself; effective access still depends on generated HBA rules, database CONNECT privileges, and role attributes.

Instances with this flag have an effect similar to setting pg_role = offline for the instance, with the only difference being that offline instances by default do not serve replica service requests and exist as dedicated offline/analytics replica instances.

If no dedicated offline instance is available, enable the parameter on a regular replica. To restrict dbrole_offline to these instances, also set role: offline explicitly on the corresponding HBA rule.

PG_BUSINESS

Customize cluster templates: users, databases, services, and permission rules.

Users should pay close attention to this section of parameters, as this is where business declares its required database objects.

• Business user definition: pg_users
• Business database definition: pg_databases
• Cluster-specific service definition: pg_services (global definition: pg_default_services)
• PostgreSQL cluster/instance-specific HBA rules: pg_hba_rules
• Pgbouncer connection pool-specific HBA rules: pgb_hba_rules
• Cron job (crontab) definition: pg_crontab

Default database users and their credentials. Their passwords must be changed in production.

• PG admin user: pg_admin_username / pg_admin_password
• PG replication user: pg_replication_username / pg_replication_password
• PG monitor user: pg_monitor_username / pg_monitor_password

# 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
pg_crontab: []                    # crontab entries for postgres dbsu
# 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

Parameter Name: pg_users, Type: user[], Level: C

PostgreSQL business user list, needs to be defined at the PG cluster level. Default value: [] empty list.

Each array element is a user/role definition, for example:

- name: dbuser_meta               # required, `name` is the only required field for user definition
  password: DBUser.Meta           # optional, password, can be scram-sha-256 hash string or plaintext
  login: true                     # optional, can login by default
  superuser: false                # optional, default false, is superuser?
  createdb: false                 # optional, default false, can create database?
  createrole: false               # optional, default false, can create role?
  inherit: true                   # optional, by default, can this role use inherited privileges?
  replication: false              # optional, default false, can this role do replication?
  bypassrls: false                # optional, default false, can this role bypass row-level security?
  pgbouncer: true                 # optional, default false, add this user to pgbouncer user list? (production users using connection pool should explicitly set to true)
  connlimit: -1                   # optional, user connection limit, default -1 disables limit
  expire_in: 3650                 # optional, this role expires: calculated from creation + n days (higher priority than expire_at)
  expire_at: '2030-12-31'         # optional, when this role expires, use YYYY-MM-DD format string to specify a specific date (lower priority than expire_in)
  comment: pigsty admin user      # optional, description and comment string for this user/role
  roles: [dbrole_admin]           # optional, default roles are: dbrole_{admin,readonly,readwrite,offline}
  parameters: {}                  # optional, use `ALTER ROLE SET` for this role, configure role-level database parameters
  pool_mode: transaction          # optional, pgbouncer pool mode at user level, default transaction
  pool_connlimit: 100             # optional, user-level pool connection limit; omitted values inherit Pigsty's global default of 100
  search_path: public             # optional, key-value config parameter per postgresql docs (e.g., use pigsty as default search_path)

User-level pool quota is consistently defined by pool_connlimit (mapped to Pgbouncer max_user_connections).

pg_databases

Parameter Name: pg_databases, Type: database[], Level: C

PostgreSQL business database list, needs to be defined at the PG cluster level. Default value: [] empty list.

Each array element is a business database definition, for example:

- name: meta                      # required, `name` is the only required field for database definition
  baseline: cmdb.sql              # optional, database sql baseline file path (relative path in ansible search path, e.g., files/)
  pgbouncer: true                 # optional, add this database to pgbouncer database list? default true
  schemas: [pigsty]               # optional, additional schemas to create, array of schema name strings
  extensions:                     # optional, additional extensions to install: array of extension objects
    - { name: postgis , schema: public }  # can specify which schema to install extension into, or not (if not specified, installs to first schema in search_path)
    - { name: timescaledb }               # some extensions create and use fixed schemas, so no need to specify schema
  comment: pigsty meta database   # optional, description and comment for the database
  owner: postgres                 # optional, database owner, default is postgres
  template: template1             # optional, template to use, default is template1, target must be a template database
  encoding: UTF8                  # optional, database encoding, default UTF8 (must match template database)
  locale: C                       # optional, database locale setting, default C (must match template database)
  lc_collate: C                   # optional, database collate rule, default C (must match template database), no reason to change
  lc_ctype: C                     # optional, database ctype character set, default C (must match template database)
  tablespace: pg_default          # optional, default tablespace, default is 'pg_default'
  allowconn: true                 # optional, allow connections, default true. Explicitly set false to completely forbid connections
  revokeconn: false               # optional, revoke public connect privileges. default false, when true, CONNECT privilege revoked from users other than owner and admin
  register_datasource: true       # optional, register this database to grafana datasource? default true, explicitly false skips registration
  connlimit: -1                   # optional, database connection limit, default -1 means no limit, positive integer limits connections
  pool_auth_user: dbuser_meta     # optional, all connections to this pgbouncer database will authenticate using this user (useful when pgbouncer_auth_query enabled)
  pool_mode: transaction          # optional, database-level pgbouncer pooling mode, default transaction
  pool_size: 50                   # optional, database-level pgbouncer default pool size, default 50
  pool_reserve: 30                # optional, database-level pgbouncer pool reserve, default 30, max additional burst connections when default pool insufficient
  pool_size_min: 0                # optional, database-level pgbouncer pool minimum size, default 0
  pool_connlimit: 100             # optional, database-level max database connections, default 100

Since Pigsty v4.1.0, database pool fields are unified as pool_reserve and pool_connlimit; legacy aliases pool_size_reserve / pool_max_db_conn are converged.

In each database definition object, only name is a required field, all other fields are optional.

pg_services

Parameter Name: pg_services, Type: service[], Level: C

PostgreSQL service list, needs to be defined at the PG cluster level. Default value: [], empty list.

Used to define additional services at the database cluster level. Each object in the array defines a service. A complete service definition example:

- name: standby                   # required, service name, final svc name will use `pg_cluster` as prefix, e.g., pg-meta-standby
  port: 5435                      # required, exposed service port (as kubernetes service node port mode)
  ip: "*"                         # optional, IP address to bind service, default is all IP addresses
  selector: "[]"                  # required, service member selector, use JMESPath to filter inventory
  backup: "[? pg_role == `primary`]"  # optional, service member selector (backup), service is handled by these instances when default selector instances are all down
  dest: default                   # optional, target port, default|postgres|pgbouncer|<port_number>, default is 'default', Default means use pg_default_service_dest value to decide
  check: /sync                    # optional, health check URL path, default is /, here uses Patroni API: /sync, only sync standby and primary return 200 health status
  maxconn: 5000                   # optional, max frontend connections allowed, default 5000
  balance: roundrobin             # optional, haproxy load balancing algorithm (default roundrobin, other option: 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'

Note that this parameter is used to add additional services at the cluster level. If you want to globally define services that all PostgreSQL databases should provide, use the pg_default_services parameter.

pg_hba_rules

Parameter Name: pg_hba_rules, Type: hba[], Level: C

Client IP whitelist/blacklist rules for database cluster/instance. Default: [] empty list.

Array of objects, each object represents a rule. HBA rule object definition:

- 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 name, rendered as comment in HBA file.
• rules: Rule array, each element is a standard HBA rule string.
• role: Rule application scope, which instance roles will enable this rule?
  • common: Applies to all instances
  • primary, replica, offline: Only applies to instances with specific pg_role.
  • Special case: role: 'offline' rules apply to instances with pg_role : offline, and also to instances with pg_offline_query flag.

In addition to the native HBA rule definition above, Pigsty also provides a more convenient 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 parameter, but it’s used to define global HBA rules, while this parameter is typically used to customize HBA rules for specific clusters/instances.

pgb_hba_rules

Parameter Name: pgb_hba_rules, Type: hba[], Level: C

Pgbouncer business HBA rules, default value: [], empty array.

This parameter is similar to pg_hba_rules, both are arrays of hba rule objects, the difference is that this parameter is for Pgbouncer.

pgb_default_hba_rules is similar to this parameter, but it’s used to define global connection pool HBA rules, while this parameter is typically used to customize HBA rules for specific connection pool clusters/instances.

pg_crontab

Parameter Name: pg_crontab, Type: string[], Level: C

Cron job list for the PostgreSQL database superuser (dbsu, default postgres), default value: [] empty array.

Each array element is a crontab entry line, using standard user crontab format: minute hour day month weekday command (no need to specify username).

pg_crontab:
  - '00 01 * * * /pg/bin/pg-backup full'      # Full backup at 1 AM daily
  - '00 13 * * * /pg/bin/pg-backup'           # Incremental backup at 1 PM daily

This parameter writes cron jobs to the postgres user’s personal crontab file:

• EL systems: /var/spool/cron/postgres
• Debian systems: /var/spool/cron/crontabs/postgres

Note: This parameter replaces the old practice of configuring postgres user tasks in node_crontab. Because node_crontab is written to /etc/crontab during NODE initialization, the postgres user may not exist yet, causing cron errors.

pg_replication_username

Parameter Name: pg_replication_username, Type: username, Level: G

PostgreSQL physical replication username, default is replicator, not recommended to change this parameter.

pg_replication_password

Parameter Name: pg_replication_password, Type: password, Level: G

PostgreSQL physical replication user password, default value: DBUser.Replicator.

Warning: Please change this password in production environments!

pg_admin_username

Parameter Name: pg_admin_username, Type: username, Level: G

PostgreSQL / Pgbouncer admin name, default: dbuser_dba.

This is the globally used database administrator with database Superuser privileges and connection pool traffic management permissions. Please control its usage scope.

pg_admin_password

Parameter Name: pg_admin_password, Type: password, Level: G

PostgreSQL / Pgbouncer admin password, default: DBUser.DBA.

Warning: Please change this password in production environments!

pg_monitor_username

Parameter Name: pg_monitor_username, Type: username, Level: G

PostgreSQL/Pgbouncer monitor username, default: dbuser_monitor.

This is a database/connection pool user for monitoring, not recommended to change this username.

However, if your existing database uses a different monitor user, you can use this parameter to specify the monitor username when defining monitoring targets.

pg_monitor_password

Parameter Name: pg_monitor_password, Type: password, Level: G

Password used by PostgreSQL/Pgbouncer monitor user, default: DBUser.Monitor.

Try to avoid using characters like @:/ that can be confused with URL delimiters in passwords to reduce unnecessary trouble.

Warning: Please change this password in production environments!

pg_dbsu_password

Parameter Name: pg_dbsu_password, Type: password, Level: G/C

PostgreSQL pg_dbsu superuser password, default is empty string, meaning no password is set.

We don’t recommend configuring password login for dbsu as it increases the attack surface. The exception is: pg_mode = citus, in which case you need to configure a password for each shard cluster’s dbsu to allow connections within the shard cluster.

PG_INSTALL

This section is responsible for installing PostgreSQL and its extensions. If you want to install different major versions and extension plugins, just modify pg_version and pg_extensions. Note that not all extensions are available for all major versions.

pg_dbsu: postgres                 # os dbsu name, default is postgres, better not change it
pg_dbsu_uid: 26                   # os dbsu uid and gid, default is 26, for default postgres user and group
pg_dbsu_sudo: limit               # dbsu sudo privilege, none,limit,all,nopass. default is limit
pg_dbsu_home: /var/lib/pgsql      # postgresql home directory, default is `/var/lib/pgsql`
pg_dbsu_ssh_exchange: true        # exchange postgres dbsu ssh key among same pgsql cluster
pg_version: 18                    # postgres major version to be installed, default is 18
pg_bin_dir: /usr/pgsql/bin        # postgres binary dir, default is `/usr/pgsql/bin`
pg_log_dir: /pg/log/postgres      # postgres log dir, default is `/pg/log/postgres`
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

Parameter Name: pg_dbsu, Type: username, Level: C

OS dbsu username used by PostgreSQL, default is postgres, changing this username is not recommended.

However, in certain situations, you may need a username different from postgres, for example, when installing and configuring Greenplum / MatrixDB, you need to use gpadmin / mxadmin as the corresponding OS superuser.

pg_dbsu_uid

Parameter Name: pg_dbsu_uid, Type: int, Level: C

OS database superuser uid and gid, 26 is the default postgres user UID/GID from PGDG RPM.

For Debian/Ubuntu systems, there is no default value, and user 26 is often taken. Therefore, when Pigsty detects the installation environment is Debian-based and uid is 26, it will automatically use the replacement pg_dbsu_uid = 543.

pg_dbsu_sudo

Parameter Name: pg_dbsu_sudo, Type: enum, Level: C

Database superuser sudo privilege, can be none, limit, all, or nopass. Default is limit

• none: No sudo privilege

• limit: Limited sudo privilege for executing systemctl commands for database-related components (default option).

• all: Full sudo privilege, requires password.

• nopass: Full sudo privilege without password (not recommended).

• Default value is limit, only allows executing sudo systemctl <start|stop|reload> <postgres|patroni|pgbouncer|...>.

pg_dbsu_home

Parameter Name: pg_dbsu_home, Type: path, Level: C

PostgreSQL home directory, default is /var/lib/pgsql, consistent with official pgdg RPM.

pg_dbsu_ssh_exchange

Parameter Name: pg_dbsu_ssh_exchange, Type: bool, Level: C

Whether to exchange OS dbsu ssh keys within the same PostgreSQL cluster?

Default is true, meaning database superusers in the same cluster can ssh to each other.

The exchange set comes from pg_cluster_members, which matches actual hosts with the same pg_cluster across the current inventory; it does not require an Ansible group named after the cluster. The execution-time -l limit still restricts playbook targets, so ensure it covers every member that needs configuration.

pg_version

Parameter Name: pg_version, Type: enum, Level: C

PostgreSQL major version to install, default is 18.

Note that PostgreSQL physical streaming replication cannot cross major versions, so it’s best not to configure this at the instance level.

You can use parameters in pg_packages and pg_extensions to install different packages and extensions for specific PG major versions.

pg_bin_dir

Parameter Name: pg_bin_dir, Type: path, Level: C

PostgreSQL binary directory, default is /usr/pgsql/bin.

The default value is a symlink manually created during installation, pointing to the specific installed Postgres version directory.

For example /usr/pgsql -> /usr/pgsql-15. On Ubuntu/Debian it points to /usr/lib/postgresql/15/bin.

For more details, see PGSQL File Structure.

pg_log_dir

Parameter Name: pg_log_dir, Type: path, Level: C

PostgreSQL log directory, default: /pg/log/postgres. The Vector log agent uses this variable to collect PostgreSQL logs.

Note that if the log directory pg_log_dir is prefixed with the data directory pg_data, it won’t be explicitly created (created automatically during data directory initialization).

pg_packages

Parameter Name: pg_packages, Type: string[], Level: C

PostgreSQL packages to install (RPM/DEB), this is an array of package names where elements can be space or comma-separated package aliases.

Pigsty v4 converges the default value to two aliases:

pg_packages:
  - pgsql-main pgsql-common

• pgsql-main: Maps to PostgreSQL kernel, client, PL languages, and core extensions like pg_repack, wal2json, pgvector on the current platform.
• pgsql-common: Maps to companion components required for running the database, such as Patroni, Pgbouncer, pgBackRest, pg_exporter, vip-manager, and other daemons.

Alias definitions can be found in pg_package_map under roles/node_id/vars/. Pigsty first resolves aliases based on OS and architecture, then replaces $v/${pg_version} with the actual major version pg_version, and finally installs the real packages. This shields package name differences between distributions.

If additional packages are needed (e.g., specific FDW or extensions), you can append aliases or real package names directly to pg_packages. But remember to keep pgsql-main pgsql-common, otherwise core components will be missing.

pg_extensions

Parameter Name: pg_extensions, Type: string[], Level: G/C

PostgreSQL extension packages to install (RPM/DEB), this is an array of extension package names or aliases.

Starting from v4, the default value is an empty list []. Pigsty no longer forces installation of large extensions, users can choose as needed to avoid extra disk and dependency usage.

To install extensions, fill in like this:

pg_extensions:
  - postgis timescaledb pgvector
  - pgsql-fdw     # use alias to install common FDWs at once

pg_package_map provides aliases that hide package-name differences between distributions. The following examples use single-extension aliases and category groups from the current v4.5 EL9 map; availability can differ by platform and PostgreSQL major version:

pg_extensions: # extensions to be installed on this cluster
  - timescaledb postgis pgvector pg_search pg_duckdb pg_repack wal2json
  # You can also select current category groups; install only the groups you need
  # - pgsql-time pgsql-gis pgsql-rag pgsql-fts pgsql-olap
  # - pgsql-feat pgsql-lang pgsql-type pgsql-util pgsql-func
  # - pgsql-admin pgsql-stat pgsql-sec pgsql-fdw pgsql-sim pgsql-etl

For the exact mapping, consult roles/node_id/vars/<os>.<arch>.yml for the target platform and the current extension catalog. pg_analytics and spat were removed from the v4.5 catalog and current mainstream-platform maps; do not copy them from older examples.

PG_BOOTSTRAP

Bootstrap PostgreSQL cluster with Patroni and set up 1:1 corresponding Pgbouncer connection pool.

It also initializes the database cluster with default roles, users, privileges, schemas, and extensions defined in PG_PROVISION.

The following parameters configure the PGSQL bootstrap stage. The internal variable pg_data always represents the /pg/data symlink and must not be overridden in inventory. Configure pg_fs_main to change the physical location of the primary data directory.

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_etcd_password: ''              # etcd password for this pg cluster, '' to use pg_cluster
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_io_method: worker              # io method for postgres, auto,sync,worker,io_uring, worker by default
pg_rto: norm                      # shared RTO mode: fast,norm,safe,wide
pg_rpo: 1048576                   # sampled failover-candidate lag threshold in bytes, `1MiB` by default
pg_libs: 'pg_stat_statements, auto_explain'  # preloaded libraries, `pg_stat_statements,auto_explain` by default
pg_delay: 0                       # replication apply delay for standby cluster leader
pg_checksum: true                 # enable data checksum for postgres cluster?
pg_pwd_enc: scram-sha-256         # passwords encryption algorithm: fixed to 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 digit, default to sha256(pg_cluster)
#pgsodium_getkey_script: ""       # pgsodium getkey script path, pgsodium_getkey by default

pg_data

Internal Variable: pg_data, Type: path

pg_data is an internal Pigsty variable, not a user configuration parameter. It always represents the PostgreSQL data-directory symlink at /pg/data.

Patroni templates, maintenance scripts, and cleanup workflows all rely on this symlink. Do not override or modify it in pigsty.yml. Configure pg_fs_main to change the underlying physical data location. See PGSQL File Structure for details.

pg_fs_main

Parameter Name: pg_fs_main, Type: path, Level: C

Mount point/file system path for PostgreSQL main data disk, default is /data/postgres.

Default value: /data/postgres, which will be used directly as the parent directory of PostgreSQL main data directory.

NVME SSD is recommended for PostgreSQL main data storage. Pigsty is optimized for SSD storage by default, but also supports HDD.

You can change pg_storage_type to HDD for HDD storage optimization.

pg_fs_backup

Parameter Name: pg_fs_backup, Type: path, Level: C

Mount point/file system path for PostgreSQL backup data disk, default is /data/backups.

If you’re using the default pgbackrest_method = local, it’s recommended to use a separate disk for backup storage.

The backup disk should be large enough to hold all backups, at least sufficient for 3 base backups + 2 days of WAL archives. Usually capacity isn’t a big issue since you can use cheap large HDDs as backup disks.

It’s recommended to use a separate disk for backup storage, otherwise Pigsty will fall back to the main data disk and consume main data disk capacity and IO.

pg_storage_type

Parameter Name: pg_storage_type, Type: enum, Level: C

Type of PostgreSQL data storage media: SSD or HDD, default is SSD.

Default value: SSD, which affects some tuning parameters like random_page_cost and effective_io_concurrency.

pg_dummy_filesize

Parameter Name: pg_dummy_filesize, Type: size, Level: C

Size of /pg/dummy, default is 64MiB, 64MB disk space for emergency use.

When disk is full, deleting the placeholder file can free some space for emergency use. Recommend at least 8GiB for production.

pg_listen

Parameter Name: pg_listen, Type: ip, Level: C

PostgreSQL / Pgbouncer listen address, default is 0.0.0.0 (all ipv4 addresses).

You can use placeholders in this variable, for example: '${ip},${lo}' or '${ip},${vip},${lo}':

• ${ip}: Translates to inventory_hostname, which is the primary internal IP address defined in the inventory.
• ${vip}: If pg_vip_enabled is enabled, will use the host part of pg_vip_address.
• ${lo}: Will be replaced with 127.0.0.1

For production environments with high security requirements, it’s recommended to restrict listen IP addresses.

pg_port

Parameter Name: pg_port, Type: port, Level: C

Port that PostgreSQL server listens on, default is 5432.

pg_localhost

Parameter Name: pg_localhost, Type: path, Level: C

Unix socket directory for localhost PostgreSQL connection, default is /var/run/postgresql.

Unix socket directory for PostgreSQL and Pgbouncer local connections. pg_exporter and patroni will preferentially use Unix sockets to access PostgreSQL.

pg_namespace

Parameter Name: pg_namespace, Type: path, Level: C

Top-level namespace used in etcd, used by patroni and vip-manager, default is: /pg, not recommended to change.

patroni_enabled

Parameter Name: patroni_enabled, Type: bool, Level: C

Enable Patroni? Default is: true.

If disabled, no Postgres cluster will be created during initialization. Pigsty will skip the task of starting patroni, which can be used when trying to add some components to existing postgres instances.

patroni_mode

Parameter Name: patroni_mode, Type: enum, Level: C

Patroni working mode: default, pause, remove. Default: default.

• default: Normal use of Patroni to bootstrap PostgreSQL cluster
• pause: Similar to default, but enters maintenance mode after bootstrap
• remove: Use Patroni to initialize cluster, then remove Patroni and use raw PostgreSQL.

patroni_port

Parameter Name: patroni_port, Type: port, Level: C

Patroni listen port, default is 8008, not recommended to change.

Patroni API server listens on this port for health checks and API requests.

patroni_log_dir

Parameter Name: patroni_log_dir, Type: path, Level: C

Patroni log directory, default is /pg/log/patroni, collected by Vector log agent.

patroni_ssl_enabled

Parameter Name: patroni_ssl_enabled, Type: bool, Level: G

Secure patroni RestAPI communications with SSL? Default is false.

This parameter is a global flag that can only be set before deployment. Because if SSL is enabled for patroni, you will have to use HTTPS instead of HTTP for health checks, fetching metrics, and calling APIs.

patroni_watchdog_mode

Parameter Name: patroni_watchdog_mode, Type: string, Level: C

Patroni watchdog mode: automatic, required, off, default is off.

In case of primary failure, Patroni can use watchdog to force shutdown old primary node to avoid split-brain.

• off: Don’t use watchdog. No fencing at all (default behavior)
• automatic: Enable watchdog if kernel has softdog module enabled and watchdog belongs to dbsu.
• required: Force enable watchdog, refuse to start Patroni/PostgreSQL if softdog unavailable.

Default is off. You should not enable watchdog on Infra nodes. Critical systems where data consistency takes priority over availability, especially business clusters involving money, can consider enabling this option.

Note that if all your access traffic uses HAproxy health check service access, there is normally no split-brain risk.

patroni_username

Parameter Name: patroni_username, Type: username, Level: C

Patroni REST API username, default is postgres, used with patroni_password.

Patroni’s dangerous REST APIs (like restarting cluster) are protected by additional username/password. See Configure Cluster and Patroni RESTAPI for details.

patroni_password

Parameter Name: patroni_password, Type: password, Level: C

Patroni REST API password, default is Patroni.API.

Warning: Must change this parameter in production environments!

pg_primary_db

Parameter Name: pg_primary_db, Type: string, Level: C

Specify the primary database name in the cluster, used for citus and other business databases, default is postgres.

For example, when using Patroni to manage HA Citus clusters, you must choose a “primary database”.

Additionally, the database name specified here will be displayed in the printed connection string after PGSQL module installation is complete.

pg_parameters

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

Used to specify and manage configuration parameters in postgresql.auto.conf.

After all cluster instances are initialized, the pg_param task will write the key/value pairs from this dictionary sequentially to /pg/data/postgresql.auto.conf.

Note: Do not manually modify this configuration file, or modify cluster configuration parameters via ALTER SYSTEM, changes will be overwritten on the next configuration sync.

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

When your cluster members have different specifications (not recommended!), you can use this parameter for fine-grained configuration management of each instance.

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

Note that some important cluster parameters (with requirements on primary/replica parameter values) are managed directly by Patroni via command line arguments, have highest priority, and cannot be overridden this way. For these parameters, you must use Patroni edit-config for management and configuration.

PostgreSQL parameters that must be consistent on primary and replicas (inconsistency will cause replica to fail to start!):

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

Parameters that should preferably be consistent on primary and replicas (considering possibility of failover):

• 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 (e.g., GUCs from extensions, thus configuring “not yet existing” parameters that ALTER SYSTEM cannot modify), but modifying existing configuration to illegal values may cause PostgreSQL to fail to start, configure with caution!

pg_files

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

Used to specify a list of files to be copied to the PGDATA directory, default is empty array: []

Files specified in this parameter will be copied to the {{ pg_data }} directory, mainly used to distribute license files required by special commercial PostgreSQL kernels.

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

pg_files: [ license.lic ]

pg_conf

Parameter Name: pg_conf, Type: enum, Level: C

Configuration template: {oltp,olap,crit,tiny}.yml, default is oltp.yml.

• tiny.yml: Optimized for small nodes, VMs, small demos (1-8 cores, 1-16GB)
• oltp.yml: Optimized for OLTP workloads and latency-sensitive applications (4C8GB+) (default template)
• olap.yml: Optimized for OLAP workloads and throughput (4C8G+)
• crit.yml: Optimized for data consistency and critical applications (4C8G+)

Default is oltp.yml, but the configure script will set this to tiny.yml when current node is a small node.

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

pg_max_conn

Parameter Name: pg_max_conn, Type: int, Level: C

PostgreSQL server max connections. You can choose a value between 50 and 5000, or use auto for recommended value.

Default is auto, which sets max connections based on pg_conf and pg_default_service_dest.

• tiny: 100
• olap: 200
• oltp: 200 (pgbouncer) / 1000 (postgres)
  • pg_default_service_dest = pgbouncer : 200
  • pg_default_service_dest = postgres : 1000
• crit: 200 (pgbouncer) / 1000 (postgres)
  • pg_default_service_dest = pgbouncer : 200
  • pg_default_service_dest = postgres : 1000

Not recommended to set this value above 5000, otherwise you’ll need to manually increase haproxy service connection limits.

Pgbouncer’s transaction pool can mitigate excessive OLTP connection issues, so setting a large connection count is not recommended by default.

For OLAP scenarios, change pg_default_service_dest to postgres to bypass connection pooling.

pg_shared_buffer_ratio

Parameter Name: pg_shared_buffer_ratio, Type: float, Level: C

Postgres shared buffer memory ratio, default is 0.25, normal range is 0.1~0.4.

Default: 0.25, meaning 25% of node memory will be used as PostgreSQL’s shared buffer. If you want to enable huge pages for PostgreSQL, this value should be appropriately smaller than node_hugepage_ratio.

Setting this value above 0.4 (40%) is usually not a good idea, but may be useful in extreme cases.

Note that shared buffers are only part of PostgreSQL’s shared memory. To calculate total shared memory, use show shared_memory_size_in_huge_pages;.

pg_rto

Parameter Name: pg_rto, Type: enum, Level: C

Recovery Time Objective (RTO) mode controlling Patroni and HAProxy timeout parameters. The default is norm.

Pigsty provides four presets tuned for different network conditions and deployment scenarios:

A shorter RTO speeds recovery but increases the risk that network jitter is mistaken for failure. Choose a mode that matches your network conditions. See RTO Trade-offs for details.

The current template recognizes only these four string keys. Other values, including numbers, fall back to norm; they are not interpreted as seconds. To define another timeout combination, extend pg_rto_plan with a new key and use that key as pg_rto.

pg_rto: norm   # default, suitable for a single datacenter
pg_rto: safe   # recommended for cross-datacenter deployments
# pg_rto: 30   # invalid key; current templates fall back to norm

pg_rto_plan

Parameter Name: pg_rto_plan, Type: dict, Level: G

Dictionary of RTO presets defining Patroni HA and HAProxy health-check timeouts. The defaults contain four modes:

pg_rto_plan:  # [ttl, loop, retry, start, margin, inter, fastinter, downinter, rise, fall]
  fast: [ 20  ,5  ,5  ,15 ,5  ,'1s' ,'0.5s' ,'1s' ,3 ,3 ]  # rto < 30s
  norm: [ 30  ,5  ,10 ,25 ,5  ,'2s' ,'1s'   ,'2s' ,3 ,3 ]  # rto < 45s
  safe: [ 60  ,10 ,20 ,45 ,10 ,'3s' ,'1.5s' ,'3s' ,3 ,3 ]  # rto < 90s
  wide: [ 120 ,20 ,30 ,95 ,15 ,'4s' ,'2s'   ,'4s' ,3 ,3 ]  # rto < 150s

Each mode is an array of ten values controlling Patroni and HAProxy together:

Override this dictionary to customize an existing mode or add a new one:

pg_rto_plan:
  ultra: [ 10, 2, 3, 8, 2, '0.5s', '0.25s', '0.5s', 2, 2 ]  # aggressive; low-latency networks only

Caution: inappropriate timeout combinations can make the cluster unstable or cause frequent false failovers.

pg_rpo

Parameter Name: pg_rpo, Type: int, Level: C

Failover-candidate lag threshold in bytes, default: 1048576 (1 MiB). It is written to Patroni’s maximum_lag_on_failover and determines whether a replica is eligible for failover; it is not a hard upper bound on actual data loss. With asynchronous replication, worst-case loss also depends on write rate and when Patroni sampled the primary’s WAL position.

When the primary goes down and all replicas are lagging, you must make a difficult choice, trade-off between availability and consistency:

• Promote a lagging replica and restore service as soon as possible, accepting possible data loss.
• Wait for primary to come back online (may never happen), or manual intervention to avoid any data loss.

You can use the crit.yml conf template to ensure no data loss during failover, but this sacrifices some performance.

pg_libs

Parameter Name: pg_libs, Type: string, Level: C

Preloaded dynamic shared libraries, default is pg_stat_statements,auto_explain, two PostgreSQL built-in extensions that are strongly recommended to enable.

For existing clusters, you can directly configure cluster shared_preload_libraries parameter and apply.

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 front of this list, for example:

citus,timescaledb,pg_stat_statements,auto_explain

Other extensions requiring dynamic loading can also be added to this list, such as pg_cron, pgml, etc. Typically citus and timescaledb have highest priority and should be added to the front of the list.

pg_delay

Parameter Name: pg_delay, Type: interval, Level: I

Delayed standby replication delay, default: 0.

If this value is set to a positive value, the standby cluster leader will be delayed by this time before applying WAL changes. Setting to 1h means data in this cluster will always lag the original cluster by one hour.

See Delayed Standby Cluster for details.

pg_checksum

Parameter Name: pg_checksum, Type: bool, Level: C

Enable data checksum for PostgreSQL cluster? Default is true, enabled.

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

Data checksums help detect disk corruption and hardware failures. This feature is enabled by default since Pigsty v3.5 to ensure data integrity.

pg_pwd_enc

Parameter Name: pg_pwd_enc, Type: enum, Level: C

Password encryption algorithm, fixed to scram-sha-256 since Pigsty v4.

All new users will use SCRAM credentials. md5 has been deprecated. For compatibility with old clients, upgrade to SCRAM in business connection pools or client drivers.

pg_encoding

Parameter Name: pg_encoding, Type: enum, Level: C

Database cluster encoding, default is UTF8.

Using other non-UTF8 encodings is not recommended.

pg_locale

Parameter Name: pg_locale, Type: enum, Level: C

Database cluster locale, default is C.

This parameter controls the database’s default Locale setting, affecting collation, character classification, and other behaviors. Using C or POSIX provides best performance and predictable sorting behavior.

If you need specific language localization support, you can set it to the corresponding Locale, such as en_US.UTF-8 or zh_CN.UTF-8. Note that Locale settings affect index sort order, so they cannot be changed after cluster initialization.

pg_lc_collate

Parameter Name: pg_lc_collate, Type: enum, Level: C

Database cluster collation, default is C.

Unless you know what you’re doing, modifying cluster-level collation settings is not recommended.

pg_lc_ctype

Parameter Name: pg_lc_ctype, Type: enum, Level: C

Database character set CTYPE, default is C.

Starting from Pigsty v3.5, to be consistent with pg_lc_collate, the default value changed to C.

pg_io_method

Parameter Name: pg_io_method, Type: enum, Level: C

PostgreSQL IO method, default is worker. Available options include:

• auto: Automatically select based on operating system, uses io_uring on Debian-based systems or EL 10+, otherwise uses worker
• sync: Use traditional synchronous IO method
• worker: Use background worker processes to handle IO (default option)
• io_uring: Use Linux’s io_uring asynchronous IO interface

Current tuning templates write this parameter only for PostgreSQL 18 and later, where it controls the asynchronous I/O execution method.

• PostgreSQL 18’s actual GUC values are worker, io_uring, and sync; auto is Pigsty template-selection logic, not a PostgreSQL enum value.
• PostgreSQL 18 defaults to worker, which uses background processes for asynchronous I/O.
• If you’re using Debian 12/Ubuntu 22+ or EL 10+ systems and want optimal IO performance, consider setting this to io_uring.

Note that setting this value on systems that don’t support io_uring may cause PostgreSQL startup to fail, so auto or worker are safer choices.

pg_etcd_password

Parameter Name: pg_etcd_password, Type: password, Level: C

The password used by this PostgreSQL cluster in etcd, default is empty string ''.

If set to empty string, the pg_cluster parameter value will be used as the password (for Citus clusters, the pg_shard parameter value is used).

This password is used for authentication when Patroni connects to etcd and when vip-manager accesses etcd.

pgsodium_key

Parameter Name: pgsodium_key, Type: string, Level: C

The encryption master key for the pgsodium extension, consisting of 64 hexadecimal digits.

This parameter is not set by default. If not specified, Pigsty will automatically generate a deterministic key using the value of sha256(pg_cluster).

pgsodium is a PostgreSQL extension based on libsodium that provides encryption functions and transparent column encryption capabilities. If you need to use pgsodium’s encryption features, it’s recommended to explicitly specify a secure random key and keep it safe.

Example command to generate a random key:

openssl rand -hex 32   # Generate 64-digit hexadecimal key

pgsodium_getkey_script

Parameter Name: pgsodium_getkey_script, Type: path, Level: C

Path to the pgsodium key retrieval script, default uses the pgsodium_getkey script from Pigsty templates.

This script is used to retrieve pgsodium’s master key when PostgreSQL starts. The default script reads the key from environment variables or configuration files.

If you have custom key management requirements (such as using HashiCorp Vault, AWS KMS, etc.), you can provide a custom script path.

PG_PROVISION

If PG_BOOTSTRAP is about creating a new cluster, then PG_PROVISION is about creating 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                  # init script for cluster template, default is `pg-init`
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 admin user creates objects
  - 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
pg_default_extensions:            # default extensions
  - { 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 config after HBA changes?
pg_default_hba_rules:             # postgres default HBA rules, ordered by `order`
  - {user: '${dbsu}'    ,db: all         ,addr: local     ,auth: ident ,title: 'dbsu access via local os user ident'  ,order: 100}
  - {user: '${dbsu}'    ,db: replication ,addr: local     ,auth: ident ,title: 'dbsu replication from local os ident' ,order: 150}
  - {user: '${repl}'    ,db: replication ,addr: localhost ,auth: pwd   ,title: 'replicator replication from localhost',order: 200}
  - {user: '${repl}'    ,db: replication ,addr: intra     ,auth: pwd   ,title: 'replicator replication from intranet' ,order: 250}
  - {user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: pwd   ,title: 'replicator postgres db from intranet' ,order: 300}
  - {user: '${monitor}' ,db: all         ,addr: localhost ,auth: pwd   ,title: 'monitor from localhost with password' ,order: 350}
  - {user: '${monitor}' ,db: all         ,addr: infra     ,auth: pwd   ,title: 'monitor from infra host with password',order: 400}
  - {user: '${admin}'   ,db: all         ,addr: infra     ,auth: ssl   ,title: 'admin @ infra nodes with pwd & ssl'   ,order: 450}
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: ssl   ,title: 'admin @ everywhere with ssl & pwd'    ,order: 500}
  - {user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: pwd   ,title: 'pgbouncer read/write via local socket',order: 550}
  - {user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: pwd   ,title: 'read/write biz user via password'     ,order: 600}
  - {user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: pwd   ,title: 'allow etl offline tasks from intranet',order: 650}
pgb_default_hba_rules:            # pgbouncer default HBA rules, ordered by `order`
  - {user: '${dbsu}'    ,db: pgbouncer   ,addr: local     ,auth: peer  ,title: 'dbsu local admin access with os ident',order: 100}
  - {user: 'all'        ,db: all         ,addr: localhost ,auth: pwd   ,title: 'allow all user local access with pwd' ,order: 150}
  - {user: '${monitor}' ,db: pgbouncer   ,addr: intra     ,auth: pwd   ,title: 'monitor access via intranet with pwd' ,order: 200}
  - {user: '${monitor}' ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other monitor access addr' ,order: 250}
  - {user: '${admin}'   ,db: all         ,addr: intra     ,auth: pwd   ,title: 'admin access via intranet with pwd'   ,order: 300}
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other admin access addr'   ,order: 350}
  - {user: 'all'        ,db: all         ,addr: intra     ,auth: pwd   ,title: 'allow all user intra access with pwd' ,order: 400}

pg_provision

Parameter Name: pg_provision, Type: bool, Level: C

Complete the PostgreSQL cluster provisioning work defined in this section after the cluster is bootstrapped. Default value is true.

If disabled, the PostgreSQL cluster will not be provisioned. For some special “PostgreSQL” clusters, such as Greenplum, you can disable this option to skip the provisioning phase.

pg_init

Parameter Name: pg_init, Type: string, Level: G/C

Location of the shell script for initializing database templates, default is pg-init. This script is copied to /pg/bin/pg-init and then executed.

This script is located at roles/pgsql/templates/pg-init

You can add your own logic to this script, or provide a new script in the templates/ directory and set pg_init to the new script name. When using a custom script, please preserve the existing initialization logic.

pg_default_roles

Parameter Name: pg_default_roles, Type: role[], Level: G/C

Default roles and users in Postgres cluster.

Pigsty has a built-in role system. See PGSQL Access Control: Role System 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

Parameter Name: pg_default_privileges, Type: string[], Level: G/C

Default privileges (DEFAULT PRIVILEGE) settings in each database:

pg_default_privileges:            # default privileges when admin user creates objects
  - 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 provides matching default privileges for the built-in role system. See PGSQL Access Control: Default Privileges for details.

pg_default_schemas

Parameter Name: pg_default_schemas, Type: string[], Level: G/C

Default schemas to create, default value is: [ monitor ]. This will create a monitor schema on all databases for placing various monitoring extensions, tables, views, and functions.

pg_default_extensions

Parameter Name: pg_default_extensions, Type: extension[], Level: G/C

List of extensions to be created and enabled by default in all databases, 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 third-party extension is pg_repack, which is important for database maintenance. All other extensions are built-in PostgreSQL Contrib extensions.

Monitoring-related extensions are installed in the monitor schema by default, which is created by pg_default_schemas.

pg_reload

Parameter Name: pg_reload, Type: bool, Level: A

Reload PostgreSQL after HBA changes, default value is true.

Set it to false to disable automatic configuration reload when you want to check before applying HBA changes.

pg_default_hba_rules

Parameter Name: pg_default_hba_rules, Type: hba[], Level: G/C

PostgreSQL host-based authentication rules, global default rules definition. Default value is:

pg_default_hba_rules:             # postgres default host-based authentication rules, ordered by `order`
  - {user: '${dbsu}'    ,db: all         ,addr: local     ,auth: ident ,title: 'dbsu access via local os user ident'  ,order: 100}
  - {user: '${dbsu}'    ,db: replication ,addr: local     ,auth: ident ,title: 'dbsu replication from local os ident' ,order: 150}
  - {user: '${repl}'    ,db: replication ,addr: localhost ,auth: pwd   ,title: 'replicator replication from localhost',order: 200}
  - {user: '${repl}'    ,db: replication ,addr: intra     ,auth: pwd   ,title: 'replicator replication from intranet' ,order: 250}
  - {user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: pwd   ,title: 'replicator postgres db from intranet' ,order: 300}
  - {user: '${monitor}' ,db: all         ,addr: localhost ,auth: pwd   ,title: 'monitor from localhost with password' ,order: 350}
  - {user: '${monitor}' ,db: all         ,addr: infra     ,auth: pwd   ,title: 'monitor from infra host with password',order: 400}
  - {user: '${admin}'   ,db: all         ,addr: infra     ,auth: ssl   ,title: 'admin @ infra nodes with pwd & ssl'   ,order: 450}
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: ssl   ,title: 'admin @ everywhere with ssl & pwd'    ,order: 500}
  - {user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: pwd   ,title: 'pgbouncer read/write via local socket',order: 550}
  - {user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: pwd   ,title: 'read/write biz user via password'     ,order: 600}
  - {user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: pwd   ,title: 'allow etl offline tasks from intranet',order: 650}

The defaults target ordinary deployments on a trusted intranet; they are not a hardened baseline for public or regulated environments. The default +dbrole_offline rule has no role field and therefore applies to every instance. For instance isolation, copy the complete default list and set that rule to role: offline. See Authentication and Access Control: Offline Role and Instance Isolation.

This parameter is an array of HBA rule objects, identical in format to pg_hba_rules. It’s recommended to configure unified pg_default_hba_rules globally, and use pg_hba_rules for additional customization on specific clusters. Rules from both parameters are applied sequentially, with the latter having higher priority.

pgb_default_hba_rules

Parameter Name: pgb_default_hba_rules, Type: hba[], Level: G/C

Pgbouncer default host-based authentication rules, array of HBA rule objects.

Default value provides a fair security level for common scenarios. Check PGSQL Authentication for details.

pgb_default_hba_rules:            # pgbouncer default host-based authentication rules, ordered by `order`
  - {user: '${dbsu}'    ,db: pgbouncer   ,addr: local     ,auth: peer  ,title: 'dbsu local admin access with os ident',order: 100}
  - {user: 'all'        ,db: all         ,addr: localhost ,auth: pwd   ,title: 'allow all user local access with pwd' ,order: 150}
  - {user: '${monitor}' ,db: pgbouncer   ,addr: intra     ,auth: pwd   ,title: 'monitor access via intranet with pwd' ,order: 200}
  - {user: '${monitor}' ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other monitor access addr' ,order: 250}
  - {user: '${admin}'   ,db: all         ,addr: intra     ,auth: pwd   ,title: 'admin access via intranet with pwd'   ,order: 300}
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other admin access addr'   ,order: 350}
  - {user: 'all'        ,db: all         ,addr: intra     ,auth: pwd   ,title: 'allow all user intra access with pwd' ,order: 400}

The default Pgbouncer HBA rules are simple:

1. Allow login from localhost with password
2. Allow login from intranet with password

Users can customize according to their own needs.

This parameter is identical in format to pgb_hba_rules. It’s recommended to configure unified pgb_default_hba_rules globally, and use pgb_hba_rules for additional customization on specific clusters. Rules from both parameters are applied sequentially, with the latter having higher priority.

PG_BACKUP

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

Check PGSQL Backup & PITR for detailed information.

pgbackrest_enabled: true          # enable pgBackRest on pgsql host?
pgbackrest_log_dir: /pg/log/pgbackrest # pgbackrest log dir, default is `/pg/log/pgbackrest`
pgbackrest_method: local          # pgbackrest repo method: local, minio, [user defined...]
pgbackrest_init_backup: true      # perform a full backup immediately after pgbackrest init?
pgbackrest_repo:                  # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
  local:                          # default pgbackrest repo with local posix filesystem
    path: /pg/backup              # local backup directory, default is `/pg/backup`
    retention_full_type: count    # retain full backup by count
    retention_full: 2             # keep at most 3 full backups when using local filesystem repo, at least 2
  minio:                          # optional minio repo for pgbackrest
    type: s3                      # minio is s3-compatible, so use s3
    s3_endpoint: sss.pigsty       # minio endpoint domain, default is `sss.pigsty`
    s3_region: us-east-1          # minio region, default is us-east-1, not effective for minio
    s3_bucket: pgsql              # minio bucket name, default is `pgsql`
    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, instead of host style
    path: /pgbackrest             # minio backup path, default is `/pgbackrest`
    storage_port: 9000            # minio port, default is 9000
    storage_ca_file: /etc/pki/ca.crt  # minio ca file path, default is `/etc/pki/ca.crt`
    block: y                      # enable block-level incremental backup (pgBackRest 2.46+)
    bundle: y                     # bundle small files into one file
    bundle_limit: 20MiB           # object storage file bundling threshold, default 20MiB
    bundle_size: 128MiB           # object storage file bundling target size, default 128MiB
    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     # retain full backup by time on minio repo
    retention_full: 14            # keep full backups from the past 14 days

pgbackrest_enabled

Parameter Name: pgbackrest_enabled, Type: bool, Level: C

Enable pgBackRest on PGSQL nodes? Default value is: true

When enabled, every node receives pgBackRest configuration. With the local filesystem repository (local), each member creates its own local stanza. Initial and scheduled backups run only on the current primary; pg-backup exits after its role check on a replica. A non-local shared stanza is initialized only on a primary without pg_upstream.

pgbackrest_log_dir

Parameter Name: pgbackrest_log_dir, Type: path, Level: C

pgBackRest log directory, default is /pg/log/pgbackrest. The Vector log agent references this parameter for log collection.

pgbackrest_method

Parameter Name: pgbackrest_method, Type: enum, Level: C

pgBackRest repository method: default options are local, minio, or other user-defined methods, default is local.

This parameter determines which repository to use for pgBackRest. All available repository methods are defined in pgbackrest_repo.

Pigsty uses the local backup repository by default, which creates a backup repository in the /pg/backup directory on the primary instance. The underlying storage path is specified by pg_fs_backup.

pgbackrest_init_backup

Parameter Name: pgbackrest_init_backup, Type: bool, Level: C

Perform a full backup immediately after pgBackRest initialization completes? Default is true.

The task attempts this only on the cluster primary when pg_upstream is not defined. It uses ignore_errors for backup failures, so enabling the parameter does not guarantee that a base backup exists. /etc/pgbackrest/initial.done is written only after the command succeeds. Verify the repository afterward with pig pb info (or pb info).

pgbackrest_repo

Parameter Name: pgbackrest_repo, Type: dict, Level: G/C

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

The default value contains local and minio as candidate definitions. pgbackrest_method selects one of them, and the v4.5.0 template renders only that selected entry as pgBackRest repo1; listing both keys is not a dual-repository backup configuration:

pgbackrest_repo:                  # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
  local:                          # default pgbackrest repo with local posix filesystem
    path: /pg/backup              # local backup directory, default is `/pg/backup`
    retention_full_type: count    # retain full backup by count
    retention_full: 2             # keep at most 3 full backups when using local filesystem repo, at least 2
  minio:                          # optional minio repo for pgbackrest
    type: s3                      # minio is s3-compatible, so use s3
    s3_endpoint: sss.pigsty       # minio endpoint domain, default is `sss.pigsty`
    s3_region: us-east-1          # minio region, default is us-east-1, not effective for minio
    s3_bucket: pgsql              # minio bucket name, default is `pgsql`
    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, instead of host style
    path: /pgbackrest             # minio backup path, default is `/pgbackrest`
    storage_port: 9000            # minio port, default is 9000
    storage_ca_file: /etc/pki/ca.crt  # minio ca file path, default is `/etc/pki/ca.crt`
    block: y                      # enable block-level incremental backup (pgBackRest 2.46+)
    bundle: y                     # bundle small files into one file
    bundle_limit: 20MiB           # object storage file bundling threshold, default 20MiB
    bundle_size: 128MiB           # object storage file bundling target size, default 128MiB
    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     # retain full backup by time on minio repo
    retention_full: 14            # keep full backups from the past 14 days

You can define new backup repositories, such as using AWS S3, GCP, or other cloud providers’ S3-compatible storage services.

Block Incremental Backup: Starting from pgBackRest 2.46, the block: y option enables block-level incremental backup. This means during incremental backups, pgBackRest only backs up changed data blocks instead of entire changed files, significantly reducing backup data volume and backup time. This feature is particularly useful for large databases, and it’s recommended to enable this option on object storage repositories.

PG_ACCESS

This section handles database access paths, including:

• Deploy Pgbouncer connection pooler on each PGSQL node and set default behavior
• Publish service ports through local or dedicated haproxy nodes
• Bind optional L2 VIP and register DNS records

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
pgbouncer_ignore_param: [ extra_float_digits, application_name, TimeZone, DateStyle, IntervalStyle, search_path ]
pg_weight: 100          #INSTANCE # relative load balance weight in service, 100 by default, 0-255
pg_service_provider: ''           # dedicate haproxy node group name, or empty string for local nodes by default
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: auto            # vip network interface to listen, auto by default
pg_dns_suffix: ''                 # pgsql dns suffix, '' by default
pg_dns_target: auto               # auto, primary, vip, none, or ad hoc ip

pgbouncer_enabled

Parameter Name: pgbouncer_enabled, Type: bool, Level: C

Default value is true. If disabled, the Pgbouncer connection pooler will not be configured on PGSQL nodes.

pgbouncer_port

Parameter Name: pgbouncer_port, Type: port, Level: C

Pgbouncer listen port, default is 6432.

pgbouncer_log_dir

Parameter Name: pgbouncer_log_dir, Type: path, Level: C

Pgbouncer log directory, default is /pg/log/pgbouncer. The Vector log agent collects Pgbouncer logs based on this parameter.

pgbouncer_auth_query

Parameter Name: pgbouncer_auth_query, Type: bool, Level: C

Allow Pgbouncer to query PostgreSQL to allow users not explicitly listed to access PostgreSQL through the connection pool? Default value is false.

If enabled, pgbouncer users will authenticate against the postgres database using SELECT username, password FROM monitor.pgbouncer_auth($1). Otherwise, only business users with pgbouncer: true are allowed to connect to the Pgbouncer connection pool.

pgbouncer_poolmode

Parameter Name: pgbouncer_poolmode, Type: enum, Level: C

Pgbouncer connection pool pooling mode: transaction, session, statement, default is transaction.

• session: Session-level pooling with best feature compatibility.
• transaction: Transaction-level pooling with better performance (many small connections), may break some session-level features like NOTIFY/LISTEN, etc.
• statements: Statement-level pooling for simple read-only queries.

If your application has feature compatibility issues, consider changing this parameter to session.

pgbouncer_sslmode

Parameter Name: pgbouncer_sslmode, Type: enum, Level: C

Pgbouncer client SSL mode, default is disable.

Note that enabling SSL may have a significant performance impact on your pgbouncer.

• disable: Ignore if client requests TLS (default)
• allow: Use TLS if client requests it. Use plain TCP if not. Does not verify client certificate.
• prefer: Same as allow.
• require: Client must use TLS. Reject client connection if not. Does not verify client certificate.
• verify-ca: Client must use TLS with a valid client certificate.
• verify-full: Same as verify-ca.

pgbouncer_ignore_param

Parameter Name: pgbouncer_ignore_param, Type: string[], Level: C

List of startup parameters ignored by PgBouncer, default value is:

[ extra_float_digits, application_name, TimeZone, DateStyle, IntervalStyle, search_path ]

These parameters are configured in the ignore_startup_parameters option in the PgBouncer configuration file. When clients set these parameters during connection, PgBouncer will not create new connections due to parameter mismatch in the connection pool.

This allows different clients to use the same connection pool even if they set different values for these parameters. This parameter was added in Pigsty v3.5.

pg_weight

Parameter Name: pg_weight, Type: int, Level: I

Relative load balancing weight in service, default is 100, range 0-255.

Default value: 100. You must define it in instance variables and reload service for it to take effect.

pg_service_provider

Parameter Name: pg_service_provider, Type: string, Level: G/C

Dedicated haproxy node group name, or empty string for local nodes by default.

If specified, PostgreSQL services will be registered to the dedicated haproxy node group instead of the current PGSQL cluster nodes.

Remember to allocate unique ports for each service on the dedicated haproxy nodes!

For example, if we define the following parameters on a 3-node pg-test cluster:

pg_service_provider: infra       # use load balancer on group `infra`
pg_default_services:             # alloc port 10001 and 10002 for pg-test primary/replica service
  - { name: primary ,port: 10001 ,dest: postgres  ,check: /primary   ,selector: "[]" }
  - { name: replica ,port: 10002 ,dest: postgres  ,check: /read-only ,selector: "[]" , backup: "[? pg_role == `primary` || pg_role == `offline` ]" }

pg_default_service_dest

Parameter Name: pg_default_service_dest, Type: enum, Level: G/C

When defining a service, if svc.dest='default', this parameter will be used as the default value.

Default value: pgbouncer, meaning the 5433 primary service and 5434 replica service will route traffic to pgbouncer by default.

If you don’t want to use pgbouncer, set it to postgres. Traffic will be routed directly to postgres.

pg_default_services

Parameter Name: pg_default_services, Type: service[], Level: G/C