Parameters

API Reference for PGSQL module:

• PG_ID : Calculate & Check Postgres Identity
• PG_BUSINESS : Postgres Business Object Definition
• PG_INSTALL : Install PGSQL Packages & Extensions
• PG_BOOTSTRAP : Init a HA Postgres Cluster with Patroni
• PG_PROVISION : Create users, databases, and in-database objects
• PG_BACKUP : Setup backup repo with pgbackrest
• PG_SERVICE : Exposing pg service, bind vip and register DNS
• PG_EXPORTER : Add Monitor for PGSQL Instance

Parameters

There are 112 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 of this instance, 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 addr, 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 sharding group, used in pair 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/matrixdb database, 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 monitoring 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 query on this instance

default value is false

When set to true, the user group dbrole_offline can connect to the ins and perform offline queries, regardless of the role of the current instance, just like a offline instance.

If you just have one replica or even one primary in your postgres cluster, adding this could mark it for accepting ETL, slow queries with interactive access.

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, has to be 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, password, can be a scram-sha-256 hash string or plain text
  login: true                     # optional, can log in, true by default  (new biz ROLE should be false)
  superuser: false                # optional, is superuser? false by default
  createdb: false                 # optional, can create database? false by default
  createrole: false               # optional, can create role? false by default
  inherit: true                   # optional, can this role use inherited privileges? true by default
  replication: false              # optional, can this role do replication? false by default
  bypassrls: false                # optional, can this role bypass row level security? false by default
  pgbouncer: true                 # optional, add this user to pgbouncer user-list? false by default (production user should be true explicitly)
  connlimit: -1                   # optional, user connection limit, default -1 disable limit
  expire_in: 3650                 # optional, now + n days when this role is expired (OVERWRITE expire_at)
  expire_at: '2030-12-31'         # optional, YYYY-MM-DD 'timestamp' when this role is expired  (OVERWRITTEN by expire_in)
  comment: pigsty admin user      # optional, comment string for this user/role
  roles: [dbrole_admin]           # optional, belonged roles. default roles are: dbrole_{admin,readonly,readwrite,offline}
  parameters: {}                  # optional, role level parameters with `ALTER ROLE SET`
  pool_mode: transaction          # optional, pgbouncer pool mode at user level, transaction by default
  pool_connlimit: -1              # optional, max database connections at user level, default -1 disable limit
  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, has to be 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 }
    - { name: timescaledb }
  comment: pigsty meta database   # optional, comment string for this database
  owner: postgres                 # optional, database owner, postgres by default
  template: template1             # optional, which template to use, template1 by default
  encoding: UTF8                  # optional, database encoding, UTF8 by default. (MUST same as template database)
  locale: C                       # optional, database locale, C by default.  (MUST same as template database)
  lc_collate: C                   # optional, database collate, C by default. (MUST same as template database)
  lc_ctype: C                     # optional, database ctype, C by default.   (MUST same as template database)
  tablespace: pg_default          # optional, default tablespace, 'pg_default' by default.
  allowconn: true                 # optional, allow connection, true by default. false will disable connect at all
  revokeconn: false               # optional, revoke public connection privilege. false by default. (leave connect with grant option to owner)
  register_datasource: true       # optional, register this database to grafana datasources? true by default
  connlimit: -1                   # optional, database connection limit, default -1 disable limit
  pool_auth_user: dbuser_meta     # optional, all connection to this pgbouncer database will be authenticated by this user
  pool_mode: transaction          # optional, pgbouncer pool mode at database level, default transaction
  pool_size: 64                   # optional, pgbouncer pool size at database level, default 64
  pool_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 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 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.

WARNING: It’s not recommend to set a dbsu password for common PGSQL clusters, except for pg_mode = citus.

PG_INSTALL

This section is responsible for installing PostgreSQL & Extensions.

If you wish to install a different major version, just 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: 16                    # postgres major version to be installed, 16 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
  - postgresql
  - patroni pgbouncer pgbackrest pg_exporter pgbadger vip-manager wal2json pg_repack
pg_extensions:                    # pg extensions to be installed, alias can be used
  - postgis timescaledb pgvector

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

dbsu sudo privilege, coud 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|...>

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 dbsu ssh key among same pgsql cluster?

default value is true, means the dbsu can ssh to each other among the same cluster.

pg_version

name: pg_version, type: enum, level: C

postgres major version to be installed, 15 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 rpms 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-15. 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 explicit (it will be created by postgres itself then).

pg_packages

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

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

The default value is the PostgreSQL kernel, as well as patroni, pgbouncer, pg_exporter, … and two important extension pg_repack and wal2json.

pg_packages:                      # pg packages to be installed, alias can be used
  - postgresql
  - patroni pgbouncer pgbackrest pg_exporter pgbadger vip-manager wal2json pg_repack

Starting from Pigsty v3, you can use package aliases specified by pg_package_map in roles/node_id/vars to perform an alias mapping.

The benefit of using package aliases is that you don’t have to worry about the package names, architectures, and major version of PostgreSQL-related packages on different OS platforms, thus sealing off the differences between different OSs.

You can also use the raw package name directly, the ${pg_version} or $v version placeholder in the package name will be replaced with the actual PG major version 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:                    # pg extensions to be installed, alias can be used
  - postgis timescaledb pgvector  # replace postgis with postgis33 when using el7

The complete list of extensions can be found in auto generated config, there are EL9 extension list:

pg_extensions: # extensions to be installed on this cluster
  - timescaledb periods temporal_tables emaj table_version pg_cron pg_later pg_background pg_timetable
  - postgis pgrouting pointcloud pg_h3 q3c ogr_fdw geoip #pg_geohash #mobilitydb
  - pgvector pgvectorscale pg_vectorize pg_similarity pg_tiktoken pgml #smlar
  - pg_search pg_bigm zhparser hunspell
  - hydra pg_analytics pg_lakehouse pg_duckdb duckdb_fdw pg_fkpart pg_partman plproxy #pg_strom citus
  - pg_hint_plan age hll rum pg_graphql pg_jsonschema jsquery index_advisor hypopg imgsmlr pg_ivm pgmq pgq #rdkit
  - pg_tle plv8 pllua plprql pldebugger plpgsql_check plprofiler plsh #pljava plr pgtap faker dbt2
  - prefix semver pgunit md5hash asn1oid roaringbitmap pgfaceting pgsphere pg_country pg_currency pgmp numeral pg_rational pguint ip4r timestamp9 chkpass #pg_uri #pgemailaddr #acl #debversion #pg_rrule
  - topn pg_gzip pg_http pg_net pg_html5_email_address pgsql_tweaks pg_extra_time pg_timeit count_distinct extra_window_functions first_last_agg tdigest aggs_for_arrays pg_arraymath pg_idkit pg_uuidv7 permuteseq pg_hashids
  - sequential_uuids pg_math pg_random pg_base36 pg_base62 floatvec pg_financial pgjwt pg_hashlib shacrypt cryptint pg_ecdsa pgpcre icu_ext envvar url_encode #pg_zstd #aggs_for_vecs #quantile #lower_quantile #pgqr #pg_protobuf
  - pg_repack pg_squeeze pg_dirtyread pgfincore pgdd ddlx pg_prioritize pg_checksums pg_readonly safeupdate pg_permissions pgautofailover pg_catcheck preprepare pgcozy pg_orphaned pg_crash pg_cheat_funcs pg_savior table_log pg_fio #pgpool pgagent
  - pg_profile pg_show_plans pg_stat_kcache pg_stat_monitor pg_qualstats pg_store_plans pg_track_settings pg_wait_sampling system_stats pg_meta pgnodemx pg_sqlog bgw_replstatus pgmeminfo toastinfo pagevis powa pg_top #pg_statviz #pgexporter_ext #pg_mon
  - passwordcheck supautils pgsodium pg_vault anonymizer pg_tde pgsmcrypto pgaudit pgauditlogtofile pg_auth_mon credcheck pgcryptokey pg_jobmon logerrors login_hook set_user pg_snakeoil pgextwlist pg_auditor noset #sslutils
  - wrappers multicorn odbc_fdw mysql_fdw tds_fdw sqlite_fdw pgbouncer_fdw mongo_fdw redis_fdw pg_redis_pubsub kafka_fdw hdfs_fdw firebird_fdw aws_s3 log_fdw #oracle_fdw #db2_fdw #jdbc_fdw
  - orafce pgtt session_variable pg_statement_rollback pg_dbms_metadata pg_dbms_lock pgmemcache #pg_dbms_job #wiltondb
  - pglogical pgl_ddl_deploy pg_failover_slots wal2json wal2mongo decoderbufs decoder_raw mimeo pgcopydb pgloader pg_fact_loader pg_bulkload pg_comparator pgimportdoc pgexportdoc #repmgr #slony
  - gis-stack rag-stack fdw-stack fts-stack etl-stack feat-stack olap-stack supa-stack stat-stack json-stack

The full extension list can be found in roles/node_id/vars

PG_BOOTSTRAP

Bootstrap a postgres cluster with patroni, and setup pgbouncer connection pool along with it.

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

pg_safeguard: false               # prevent purging running postgres instance? false by default
pg_clean: true                    # purging existing postgres during pgsql init? true by default
pg_data: /pg/data                 # postgres data directory, `/pg/data` by default
pg_fs_main: /data                 # mountpoint/path for postgres main data, `/data` by default
pg_fs_bkup: /data/backups         # mountpoint/path for pg backup data, `/data/backup` 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                       # replication apply delay for standby cluster leader
pg_checksum: false                # 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: en_US.UTF8           # database character type, `en_US.UTF8` by default
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_safeguard

name: pg_safeguard, type: bool, level: G/C/A

prevent purging running postgres instance? false by default

If enabled, pgsql.yml & pgsql-rm.yml will abort immediately if any postgres instance is running.

pg_clean

name: pg_clean, type: bool, level: G/C/A

purging existing postgres during pgsql init? true by default

default value is true, it will purge existing postgres instance during pgsql.yml init. which makes the playbook idempotent.

if set to false, pgsql.yml will abort if there’s already a running postgres instance. and pgsql-rm.yml will NOT remove postgres data (only stop the server).

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 point to underlying data directory.

Check PGSQL File Structure for details.

pg_fs_main

name: pg_fs_main, type: path, level: C

mountpoint/path for postgres main data, /data by default

default values: /data, which will be used as parent dir of postgres main data directory: /data/postgres.

It’s recommended to use NVME SSD for postgres main data storage, Pigsty is optimized for SSD storage by default. But HDD is also supported, you can change pg_storage_type to HDD to optimize for HDD storage.

pg_fs_bkup

name: pg_fs_bkup, type: path, level: C

mountpoint/path for pg backup data, /data/backup by default

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 basebackups + 2 days WAL archive. This is usually not a problem since you can use cheap & large HDD for that.

It’s recommended to use a separate disk for backup storage, otherwise pigsty will fall back to the main data disk.

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 use 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 listen port, 8008 by default, changing it is not recommended.

The Patroni API server listens on 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, (1~8Core, 1~16GB)
• 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: 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

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.

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

replication 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?, default value is false.

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

passwords 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

database cluster local, C by default

pg_lc_collate

name: pg_lc_collate, type: enum, level: C

database cluster collate, C by default, It’s not recommended to change this value unless you know what you are doing.

pg_lc_ctype

name: pg_lc_ctype, type: enum, level: C

database character type, en_US.UTF8 by default

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

query postgres to retrieve unlisted business users? default value is false

If enabled, pgbouncer user will be authenticated against postgres database with SELECT username, password FROM monitor.pgbouncer_auth($1), otherwise, only the users with pgbouncer: true will be allowed to connect to pgbouncer.

pgbouncer_poolmode

name: pgbouncer_poolmode, type: enum, level: C

Pgbouncer pooling mode: transaction, session, statement, transaction by default

• session: Session-level pooling with the best compatibility.
• transaction: Transaction-level pooling with better performance (lots of small conns), could break some session level features such as notify/listen, etc…
• statements: Statement-level pooling which is used for simple read-only queries.

If you application has some compatibility issues with pgbouncer, you can try to change this value to session instead.

pgbouncer_sslmode

name: pgbouncer_sslmode, type: enum, level: C

pgbouncer client ssl mode, disable by default

default values: disable, beware that this may have a huge performance impact on your pgbouncer.

• disable: Plain TCP. If client requests TLS, it’s ignored. Default.
• allow: If client requests TLS, it is used. If not, plain TCP is used. If the client presents a client certificate, it is not validated.
• prefer: Same as allow.
• require: Client must use TLS. If not, the client connection is rejected. If the client presents a client certificate, it is not validated.
• verify-ca: Client must use TLS with valid client certificate.
• verify-full: Same as verify-ca.

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] ,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: adminpack          ,schema: pg_catalog }
  - { 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 after hba 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] ,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 base on 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: adminpack          ,schema: pg_catalog }
  - { 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_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 backup 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
    bundle: y                     # bundle small files into a single file
    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 last 14 days

pgbackrest_enabled

name: pgbackrest_enabled, type: bool, level: C

enable pgBackRest on pgsql host? default value is true

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_bkup.

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 backup 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
    bundle: y                     # bundle small files into a single file
    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 last 14 days

PG_SERVICE

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

• 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

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

pg_weight

name: pg_weight, type: int, level: G

relative load balance weight in service, 100 by default, 0-255

default values: 100. you have to define it at instance vars, and reload-service to take effect.

pg_service_provider

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

dedicate 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 this pgsql cluster nodes.

Do remember to allocate unique ports on dedicate haproxy nodes for each service!

For example, if we define following parameters on 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

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 values: pgbouncer, means 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 instead. traffic will be route to postgres directly.

pg_default_services

name: pg_default_services, type: service[], level: G/C

postgres default service definitions

default value is four default services definition, which is explained in PGSQL Service

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

name: pg_vip_enabled, type: bool, level: C

enable a l2 vip for pgsql primary?

default value is false, means no L2 VIP is created for this cluster.

L2 VIP can only be used in same L2 network, which may incurs extra restrictions on your network topology.

pg_vip_address

name: pg_vip_address, type: cidr4, level: C

vip address in <ipv4>/<mask> format, if vip is enabled, this parameter is required.

default values: 127.0.0.1/24. This value is consist of two parts: ipv4 and mask, separated by /.

pg_vip_interface

name: pg_vip_interface, type: string, level: C/I

vip network interface to listen, eth0 by default.

It should be the same primary intranet interface of your node, which is the IP address you used in the inventory file.

If your node have different interface, you can override it on instance vars:

pg-test:
    hosts:
        10.10.10.11: {pg_seq: 1, pg_role: replica ,pg_vip_interface: eth0 }
        10.10.10.12: {pg_seq: 2, pg_role: primary ,pg_vip_interface: eth1 }
        10.10.10.13: {pg_seq: 3, pg_role: replica ,pg_vip_interface: eth2 }
    vars:
        pg_vip_enabled: true          # enable L2 VIP for this cluster, bind to primary instance by default
        pg_vip_address: 10.10.10.3/24 # the L2 network CIDR: 10.10.10.0/24, the vip address: 10.10.10.3
        # pg_vip_interface: eth1      # if your node have non-uniform interface, you can define it here

pg_dns_suffix

name: pg_dns_suffix, type: string, level: C

pgsql dns suffix, ’’ by default, cluster DNS name is defined as {{ pg_cluster }}{{ pg_dns_suffix }}

For example, if you set pg_dns_suffix to .db.vip.company.tld for cluster pg-test, then the cluster DNS name will be pg-test.db.vip.company.tld

pg_dns_target

name: pg_dns_target, type: enum, level: C

Could be: auto, primary, vip, none, or an ad hoc ip address, which will be the target IP address of cluster DNS record.

default values: auto , which will bind to pg_vip_address if pg_vip_enabled, or fallback to cluster primary instance ip address.

• vip: bind to pg_vip_address
• primary: resolve to cluster primary instance ip address
• auto: resolve to pg_vip_address if pg_vip_enabled, or fallback to cluster primary instance ip address.
• none: do not bind to any ip address
• <ipv4>: bind to the given IP address

PG_EXPORTER

pg_exporter_enabled: true              # enable pg_exporter on pgsql hosts?
pg_exporter_config: pg_exporter.yml    # pg_exporter configuration file name
pg_exporter_cache_ttls: '1,10,60,300'  # pg_exporter collector ttl stage in seconds, '1,10,60,300' by default
pg_exporter_port: 9630                 # pg_exporter listen port, 9630 by default
pg_exporter_params: 'sslmode=disable'  # extra url parameters for pg_exporter dsn
pg_exporter_url: ''                    # overwrite auto-generate pg dsn if specified
pg_exporter_auto_discovery: true       # enable auto database discovery? enabled by default
pg_exporter_exclude_database: 'template0,template1,postgres' # csv of database that WILL NOT be monitored during auto-discovery
pg_exporter_include_database: ''       # csv of database that WILL BE monitored during auto-discovery
pg_exporter_connect_timeout: 200       # pg_exporter connect timeout in ms, 200 by default
pg_exporter_options: ''                # overwrite extra options for pg_exporter
pgbouncer_exporter_enabled: true       # enable pgbouncer_exporter on pgsql hosts?
pgbouncer_exporter_port: 9631          # pgbouncer_exporter listen port, 9631 by default
pgbouncer_exporter_url: ''             # overwrite auto-generate pgbouncer dsn if specified
pgbouncer_exporter_options: ''         # overwrite extra options for pgbouncer_exporter

pg_exporter_enabled

name: pg_exporter_enabled, type: bool, level: C

enable pg_exporter on pgsql hosts?

default value is true, if you don’t want to install pg_exporter, set it to false.

pg_exporter_config

name: pg_exporter_config, type: string, level: C

pg_exporter configuration file name, used by pg_exporter & pgbouncer_exporter

default values: pg_exporter.yml, if you want to use a custom configuration file, you can specify its relative path here.

Your config file should be placed in files/<filename>.yml. For example, if you want to monitor a remote PolarDB instance, you can use the sample config: files/polar_exporter.yml.

pg_exporter_cache_ttls

name: pg_exporter_cache_ttls, type: string, level: C

pg_exporter collector ttl stage in seconds, ‘1,10,60,300’ by default

default values: 1,10,60,300, which will use 1s, 10s, 60s, 300s for different metric collectors.

ttl_fast: "{{ pg_exporter_cache_ttls.split(',')[0]|int }}"         # critical queries
ttl_norm: "{{ pg_exporter_cache_ttls.split(',')[1]|int }}"         # common queries
ttl_slow: "{{ pg_exporter_cache_ttls.split(',')[2]|int }}"         # slow queries (e.g table size)
ttl_slowest: "{{ pg_exporter_cache_ttls.split(',')[3]|int }}"      # ver slow queries (e.g bloat)

pg_exporter_port

name: pg_exporter_port, type: port, level: C

pg_exporter listen port, 9630 by default

pg_exporter_params

name: pg_exporter_params, type: string, level: C

extra url parameters for pg_exporter dsn

default values: sslmode=disable, which will disable SSL for monitoring connection (since it’s local unix socket by default)

pg_exporter_url

name: pg_exporter_url, type: pgurl, level: C

overwrite auto-generate pg dsn if specified

default value is empty string, If specified, it will be used as the pg_exporter dsn instead of constructing from other parameters:

This could be useful if you want to monitor a remote pgsql instance, or you want to use a different user/password for monitoring.

'postgres://{{ pg_monitor_username }}:{{ pg_monitor_password }}@{{ pg_host }}:{{ pg_port }}/postgres{% if pg_exporter_params != '' %}?{{ pg_exporter_params }}{% endif %}'

pg_exporter_auto_discovery

name: pg_exporter_auto_discovery, type: bool, level: C

enable auto database discovery? enabled by default

default value is true, which will auto-discover all databases on the postgres server and spawn a new pg_exporter connection for each database.

pg_exporter_exclude_database

name: pg_exporter_exclude_database, type: string, level: C

csv of database that WILL NOT be monitored during auto-discovery

default values: template0,template1,postgres, which will be excluded for database auto discovery.

pg_exporter_include_database

name: pg_exporter_include_database, type: string, level: C

csv of database that WILL BE monitored during auto-discovery

default value is empty string. If this value is set, only the databases in this list will be monitored during auto discovery.

pg_exporter_connect_timeout

name: pg_exporter_connect_timeout, type: int, level: C

pg_exporter connect timeout in ms, 200 by default

default values: 200ms , which is enough for most cases.

If your remote pgsql server is in another continent, you may want to increase this value to avoid connection timeout.

pg_exporter_options

name: pg_exporter_options, type: arg, level: C

overwrite extra options for pg_exporter

default value is empty string, which will fall back the following default options:

--log.level=info

If you want to customize logging options or other pg_exporter options, you can set it here.

pgbouncer_exporter_enabled

name: pgbouncer_exporter_enabled, type: bool, level: C

enable pgbouncer_exporter on pgsql hosts?

default value is true, which will enable pg_exporter for pgbouncer connection pooler.

pgbouncer_exporter_port

name: pgbouncer_exporter_port, type: port, level: C

pgbouncer_exporter listen port, 9631 by default

default values: 9631

pgbouncer_exporter_url

name: pgbouncer_exporter_url, type: pgurl, level: C

overwrite auto-generate pgbouncer dsn if specified

default value is empty string, If specified, it will be used as the pgbouncer_exporter dsn instead of constructing from other parameters:

'postgres://{{ pg_monitor_username }}:{{ pg_monitor_password }}@:{{ pgbouncer_port }}/pgbouncer?host={{ pg_localhost }}&sslmode=disable'

This could be useful if you want to monitor a remote pgbouncer instance, or you want to use a different user/password for monitoring.

pgbouncer_exporter_options

name: pgbouncer_exporter_options, type: arg, level: C

overwrite extra options for pgbouncer_exporter, default value is empty string.

--log.level=info

default value is empty string, which will fall back the following default options:

If you want to customize logging options or other pgbouncer_exporter options, you can set it here.