# Parameter > customize postgres cluster with 121 parameters --- There are **121** parameters about the `PGSQL` module. | Section | Count | Description | |---------------------------------|-------|----------------------------------------------------------------------------------------------------------------------------------------------------| | [`PG_ID`](#pg_id) | 11 | Calculate & Check Postgres Identity - parameters for identifying PGSQL entities like instances and services | | [`PG_BUSINESS`](#pg_business) | 12 | Postgres Business Object Definition - configuration for business users, databases, services, and authentication | | [`PG_INSTALL`](#pg_install) | 10 | Install PGSQL Packages & Extensions - settings for database user setup, version selection, and package installation | | [`PG_BOOTSTRAP`](#pg_bootstrap) | 35 | Init a HA Postgres Cluster with Patroni - comprehensive cluster initialization including data directories, networking, and high availability setup | | [`PG_PROVISION`](#pg_provision) | 9 | Create users, databases, and in-database objects - post-bootstrap provisioning of database objects and default configurations | | [`PG_BACKUP`](#pg_backup) | 6 | Setup backup repo with pgbackrest - backup and recovery configuration using pgbackrest | | [`PG_ACCESS`](#pg_access) | 16 | Exposing pg service, bind vip and register DNS - service exposure, load balancing, VIP management, and DNS registration | | [`PG_MONITOR`](#pg_exporter) | 18 | Add Monitor for PGSQL Instance - monitoring setup with various exporters for metrics collection | | [`PG_REMOVE`](#pg_remove) : Remove a Postgres Cluster | Name | Type | Level | Comment | |---------------------------------------|--------|-------|----------------------------------------------------------------| | [`pg_safeguard`](#pg_safeguard) | `bool` | G/C/A | stop removal when enabled; false by default | | [`pg_rm_data`](#pg_rm_data) | `bool` | G/C/A | remove postgres data during removal; true by default | | [`pg_rm_backup`](#pg_rm_backup) | `bool` | G/C/A | remove primary pgBackRest backup during removal; true by default | | [`pg_rm_pkg`](#pg_rm_pkg) | `bool` | G/C/A | uninstall postgres packages during removal; true by default | ------ ## `PG_ID` Here are some common parameters used to identify PGSQL [entities](/docs/pgsql/config#identity-parameters): 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`](#pg_shard) and [`pg_group`](#pg_group) will be required for horizontal sharding clusters. ------ ### `pg_cluster` name: `pg_cluster`, type: `string`, level: `C` pgsql cluster name, REQUIRED identity parameter The cluster name will be used as the namespace for PGSQL related resources within that cluster. The naming needs to follow the specific naming pattern: `[a-z][a-z0-9-]*` to be compatible with the requirements of different constraints on the identity. ------ ### `pg_seq` name: `pg_seq`, type: `int`, level: `I` pgsql instance seq number, REQUIRED identity parameter A serial number to identify these instances, unique within its **cluster**, starting from 0 or 1. ------ ### `pg_role` name: `pg_role`, type: `enum`, level: `I` pgsql role, REQUIRED, could be primary,replica,offline Roles for PGSQL instance, can be: `primary`, `replica`, `standby` or `offline`. - `primary`: Primary, there is one and only one primary in a cluster. - `replica`: Replica for carrying online read-only traffic, there may be a slight replication delay through (10ms~100ms, 100KB). - `standby`: Special replica that is always synced with primary, there’s no replication delay & data loss on this replica. (currently same as `replica`) - `offline`: Offline replica for taking on offline read-only traffic, such as statistical analysis/ETL/personal queries, etc. **Identity params, required params, and instance-level params.** ------ ### `pg_instances` name: `pg_instances`, type: `dict`, level: `I` define multiple pg instances on node in `{port:ins_vars}` format. This parameter is reserved for multi-instance deployment on a single node which is not implemented in Pigsty yet. ------ ### `pg_upstream` name: `pg_upstream`, type: `ip`, level: `I` Upstream ip address for standby cluster or cascade replica Setting `pg_upstream` is set on `primary` instance indicate that this cluster is a [**Standby Cluster**](/docs/pgsql/config/#standby-cluster), and will receiving changes from upstream instance, thus the `primary` is actually a `standby leader`. Setting `pg_upstream` for a non-primary instance will explicitly set a replication upstream instance, if it is different from the primary IP Address, this instance will become a **cascade replica**. And it’s user’s responsibility to ensure that the upstream IP addr is another instance in the same cluster. ------ ### `pg_shard` name: `pg_shard`, type: `string`, level: `C` pgsql shard name, required identity parameter for sharding clusters (e.g. citus cluster), optional for common pgsql clusters. When multiple pgsql clusters serve the same business together in a horizontally sharding style, Pigsty will mark this group of clusters as a **Sharding Group**. [`pg_shard`](#pg_shard) is the name of the shard group name. It’s usually the prefix of [`pg_cluster`](#pg_cluster). For example, if we have a sharding group `pg-citus`, and 4 clusters in it, there identity params will be: ``` cls pg_shard: pg-citus cls pg_group = 0: pg-citus0 cls pg_group = 1: pg-citus1 cls pg_group = 2: pg-citus2 cls pg_group = 3: pg-citus3 ``` ------ ### `pg_group` name: `pg_group`, type: `int`, level: `C` pgsql shard index number, required identity for sharding clusters, optional for common pgsql clusters. Sharding cluster index of a sharding group, used in pairs with [pg_shard](#pg_shard). You can use any non-negative integer as the index number. ------ ### `gp_role` name: `gp_role`, type: `enum`, level: `C` greenplum/matrixdb role of this cluster, could be `master` or `segment` - `master`: mark the postgres cluster as greenplum master, which is the default value - `segment` mark the postgres cluster as greenplum segment This parameter is only used for greenplum & derived databases, and is ignored for common pgsql cluster. ------ ### `pg_exporters` name: `pg_exporters`, type: `dict`, level: `C` additional pg_exporters to monitor remote postgres instances, default values: `{}` If you wish to monitor remote postgres instances, define them in `pg_exporters` and load them with [`pgsql-monitor.yml`](/docs/pgsql/playbook/#pgsql-monitoryml) playbook. ```yaml 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](/docs/pgsql/monitor/) for details. ------ ### `pg_offline_query` name: `pg_offline_query`, type: `bool`, level: `I` set to true to enable offline queries on this instance default value is `false` When this parameter is enabled for a PostgreSQL instance, users belonging to the `dbrole_offline` group can directly connect to that PostgreSQL instance to perform offline queries (slow queries, interactive queries, ETL/analytical queries). Instances with this flag are functionally similar to setting `pg_role` = `offline`, with the only difference being that `offline` instances by default do not handle `replica` service requests, as they exist specifically as dedicated offline/analytical replica instances. If you don’t have spare instances that can be dedicated to this purpose, you can select a regular replica and enable this parameter at the instance level to accommodate offline queries when needed. ------ ## `PG_BUSINESS` Database credentials, In-Database Objects that need to be taken care of by Users. - Define Business Users: [`pg_users`](#pg_users) - Define Business Databases: [`pg_databases`](#pg_databases) - Define Cluster Services: [`pg_services`](#pg_services) (Global Definition: [`pg_default_services`](#pg_default_services)) - Ad-Hoc PostgreSQL HBA Rules: [`pg_default_services`](#pg_default_services) - Ad-Hoc Pgbouncer HBA Rules: [`pgb_hba_rules`](#pgb_hba_rules) [Default Database Users](/docs/pgsql/acl#default-users): - Administrator: [`pg_admin_username`](#pg_admin_username) / [`pg_admin_password`](#pg_admin_password) - Replication User: [`pg_replication_username`](#pg_replication_username) / [`pg_replication_password`](#pg_replication_password) - Monitor User: [`pg_monitor_username`](#pg_monitor_username) / [`pg_monitor_password`](#pg_monitor_password) > WARNING: YOU HAVE TO CHANGE THESE DEFAULT **PASSWORD**s in production environment. ```yaml # postgres business object definition, overwrite in group vars pg_users: [] # postgres business users pg_databases: [] # postgres business databases pg_services: [] # postgres business services pg_hba_rules: [] # business hba rules for postgres pgb_hba_rules: [] # business hba rules for pgbouncer # global credentials, overwrite in global vars pg_dbsu_password: '' # dbsu password, empty string means no dbsu password by default pg_replication_username: replicator pg_replication_password: DBUser.Replicator pg_admin_username: dbuser_dba pg_admin_password: DBUser.DBA pg_monitor_username: dbuser_monitor pg_monitor_password: DBUser.Monitor ``` ------ ### `pg_users` name: `pg_users`, type: `user[]`, level: `C` postgres business users, defined at cluster level. default values: `[]`, each object in the array defines a [User/Role](/docs/pgsql/user). Examples: ```yaml - name: dbuser_meta # REQUIRED, `name` is the only mandatory field of a user definition password: DBUser.Meta # optional, the password, can be a scram-sha-256 hash string or plain text login: true # optional, can log in, true by default (new biz ROLE should be false) superuser: false # optional, is superuser? false by default createdb: false # optional, can create database? false by default createrole: false # optional, can create role? false by default inherit: true # optional, can this role use inherited privileges? true by default replication: false # optional, can this role do replication? false by default bypassrls: false # optional, can this role bypass row level security? false by default pgbouncer: true # optional, add this user to pgbouncer userlist? false by default (production user should be true explicitly) connlimit: -1 # optional, user connection limit, default -1 disable limit expire_in: 3650 # optional, now + n days when this role is expired (OVERWRITE expire_at) expire_at: '2030-12-31' # optional, YYYY-MM-DD 'timestamp' when this role is expired (OVERWRITTEN by expire_in) comment: pigsty admin user # optional, comment string for this user/role roles: [dbrole_admin] # optional, belonged roles. default roles are: dbrole_{admin,readonly,readwrite,offline} parameters: {} # optional, role level parameters with `ALTER ROLE SET` pool_mode: transaction # optional, pgbouncer pool mode at user level, transaction by default pool_connlimit: -1 # optional, max database connections at user level, default -1 disable limit search_path: public # key value config parameters according to postgresql documentation (e.g: use pigsty as default search_path) ``` The only mandatory field of a user definition is `name`, and the rest are optional. ------ ### `pg_databases` name: `pg_databases`, type: `database[]`, level: `C` postgres business databases, defined at cluster level. default values: `[]`, each object in the array defines a [Database](/docs/pgsql/db). Examples: ```yaml - name: meta # REQUIRED, `name` is the only mandatory field of a database definition baseline: cmdb.sql # optional, database sql baseline path, (relative path among ansible search path, e.g files/) pgbouncer: true # optional, add this database to pgbouncer database list? true by default schemas: [pigsty] # optional, additional schemas to be created, array of schema names extensions: # optional, additional extensions to be installed: array of `{name[,schema]}` - { name: postgis , schema: public } # You can specify which schema to install the extension in, or leave it unspecified (if unspecified, it will be installed in the first schema of search_path) - { name: timescaledb } # For example, some extensions will create and use fixed schemas, so you don't need to specify a schema. - vector # You can also directly use a string to specify the extension name comment: pigsty meta database # optional, comment string for this database owner: postgres # optional, database owner, postgres by default template: template1 # optional, which template to use, template1 by default encoding: UTF8 # optional, database encoding, UTF8 by default. (MUST same as template database) locale: C # optional, database locale, C by default. (MUST same as template database) lc_collate: C # optional, database collate, C by default. (MUST same as template database) lc_ctype: C # optional, database ctype, C by default. (MUST same as template database) tablespace: pg_default # optional, default tablespace, 'pg_default' by default. allowconn: true # optional, allow connection, true by default. false will disable connect at all revokeconn: false # optional, revoke public connection privilege. false by default. (leave connect with grant option to owner) register_datasource: true # optional, register this database to grafana datasources? true by default connlimit: -1 # optional, database connection limit, default -1 disable limit pool_auth_user: dbuser_meta # optional, all connection to this pgbouncer database will be authenticated by this user pool_mode: transaction # optional, pgbouncer pool mode at database level, default transaction pool_size: 64 # optional, pgbouncer pool size at database level, default 64 pool_size_reserve: 32 # optional, pgbouncer pool size reserve at database level, default 32 pool_size_min: 0 # optional, pgbouncer pool size min at database level, default 0 pool_max_db_conn: 100 # optional, max database connections at database level, default 100 ``` In each database definition, the DB `name` is mandatory and the rest are optional. ------ ### `pg_services` name: `pg_services`, type: `service[]`, level: `C` postgres business services exposed via haproxy, has to be defined at cluster level. You can define ad hoc services with [`pg_services`](#pg_services) in additional to default [`pg_default_services`](#pg_default_services) default values: `[]`, each object in the array defines a [**Service**](/docs/pgsql/service/#define-service). Examples: ```yaml - 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|, '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](/docs/pgsql/hba#define-hba) object, each hba object may look like ```yaml # RAW HBA RULES - title: allow intranet password access role: common rules: - host all all 10.0.0.0/8 md5 - host all all 172.16.0.0/12 md5 - host all all 192.168.0.0/16 md5 ``` - `title`: Rule Title, transform into comment in the hba file - `rules`: Array of strings, each string is a raw hba rule record - `role` : Applied roles, where to install these hba rules - `common`: apply for all instances - `primary`, `replica`,`standby`, `offline`: apply on corresponding instances with that [`pg_role`](#pg_role). - special case: HBA rule with `role == 'offline'` will be installed on instance with [`pg_offline_query`](#pg_offline_query) flag or you can use another alias form ```yaml - addr: 'intra' # world|intra|infra|admin|local|localhost|cluster| auth: 'pwd' # trust|pwd|ssl|cert|deny| user: 'all' # all|${dbsu}|${repl}|${admin}|${monitor}|| db: 'all' # all|replication|.... rules: [] # raw hba string precedence over above all title: allow intranet password access ``` [`pg_default_hba_rules`](#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`](#pg_hba_rules), array of [hba](/docs/pgsql/hba/#define-hba) rule object, except this is for pgbouncer. ------ ### `pg_replication_username` name: `pg_replication_username`, type: `username`, level: `G` postgres replication username, `replicator` by default This parameter is globally used, it is not wise to change it. ------ ### `pg_replication_password` name: `pg_replication_password`, type: `password`, level: `G` postgres replication password, `DBUser.Replicator` by default > WARNING: CHANGE THIS IN PRODUCTION ENVIRONMENT!!!! ------ ### `pg_admin_username` name: `pg_admin_username`, type: `username`, level: `G` postgres admin username, `dbuser_dba` by default, which is a global postgres superuser. default values: `dbuser_dba` ------ ### `pg_admin_password` name: `pg_admin_password`, type: `password`, level: `G` postgres admin password in plain text, `DBUser.DBA` by default > WARNING: CHANGE THIS IN PRODUCTION ENVIRONMENT!!!! ------ ### `pg_monitor_username` name: `pg_monitor_username`, type: `username`, level: `G` postgres monitor username, `dbuser_monitor` by default, which is a global monitoring user. ------ ### `pg_monitor_password` name: `pg_monitor_password`, type: `password`, level: `G` postgres monitor password, `DBUser.Monitor` by default. Try not using the `@:/` character in the password to avoid problems with PGURL string. > WARNING: CHANGE THIS IN PRODUCTION ENVIRONMENT!!!! ------ ### `pg_dbsu_password` name: `pg_dbsu_password`, type: `password`, level: `G/C` PostgreSQL dbsu password for [`pg_dbsu`](#pg_dbsu), empty string means no dbsu password, which is the default behavior. **Set this password will allow a well-know dbsu login from remote!** It's not recommended to set the well-known dbsu (`postgres`) password for common PGSQL clusters, except for a good reason, such as using [`pg_mode`](#pg_mode) = `citus`. ------ ## `PG_INSTALL` This section is responsible for installing PostgreSQL & Extensions. If you wish to install a different major version, make sure repo packages exists and overwrite [`pg_version`](#pg_version) on cluster level. To install extra extensions, overwrite [`pg_extensions`](#pg_extensions) on cluster level. Beware that not all extensions are available with other major versions. ```yaml pg_dbsu: postgres # os dbsu name, postgres by default, better not change it pg_dbsu_uid: 26 # os dbsu uid and gid, 26 for default postgres users and groups pg_dbsu_sudo: limit # dbsu sudo privilege, none,limit,all,nopass. limit by default pg_dbsu_home: /var/lib/pgsql # postgresql home directory, `/var/lib/pgsql` by default pg_dbsu_ssh_exchange: true # exchange postgres dbsu ssh key among same pgsql cluster pg_version: 18 # postgres major version to be installed, 18 by default pg_bin_dir: /usr/pgsql/bin # postgres binary dir, `/usr/pgsql/bin` by default pg_log_dir: /pg/log/postgres # postgres log dir, `/pg/log/postgres` by default pg_packages: # pg packages to be installed, alias can be used - pgsql-main pgsql-common pg_extensions: [] # pg extensions to be installed, alias can be used ``` ------ ### `pg_dbsu` name: `pg_dbsu`, type: `username`, level: `C` OS DBSU name, `postgres` by default, it’s not wise to change it. When installing Greenplum / MatrixDB, set this parameter to the corresponding default value: `gpadmin|mxadmin`. ------ ### `pg_dbsu_uid` name: `pg_dbsu_uid`, type: `int`, level: `C` OS DBSU uid and gid, `26` for default postgres users and groups, which is consistent with the official pgdg RPM. For Ubuntu/Debian, there’s no default postgres UID/GID, consider using another ad hoc value, such as `543` instead. ------ ### `pg_dbsu_sudo` name: `pg_dbsu_sudo`, type: `enum`, level: `C` OS DBSU sudo privilege, could be `none`, `limit` ,`all` ,`nopass`. `limit` by default - `none`: No Sudo privilege - `limit`: Limited sudo privilege to execute systemctl commands for database-related components, default. - `all`: Full `sudo` privilege, password required. - `nopass`: Full `sudo` privileges without a password (not recommended). default values: `limit`, which only allow `sudo systemctl ` Available sudo services: - patroni - pgbouncer - postgres - pg_exporter - pgbackrest - pgbouncer_exporter - pgbackrest_exporter - vip-manager - haproxy (reload only) ------ ### `pg_dbsu_home` name: `pg_dbsu_home`, type: `path`, level: `C` postgresql home directory, `/var/lib/pgsql` by default, which is consistent with the official pgdg RPM. ------ ### `pg_dbsu_ssh_exchange` name: `pg_dbsu_ssh_exchange`, type: `bool`, level: `C` exchange postgres os dbsu ssh key among pgsql instances? default value is `true`, means the dbsu can ssh to each other among the playbook execution hosts. For scenarios where ssh access is strictly limited, you can set it to `false`. Please note that SSH key exchange occurs between instances that are executing the same playbook. If you run the `pgsql` role for a single PostgreSQL cluster, the key exchange will occur between all instances in that cluster. If you run the `pgsql` role for all PostgreSQL clusters, the key exchange will occur between **all** instances, which can lead to severe combinatorial explosions for large clusters. If any instance involved in the key exchange does not have the [`pg_dbsu`](#pg_dbsu) user, the key exchange will fail for that instance, but will not affect other instances. ------ ### `pg_version` name: `pg_version`, type: `enum`, level: `C` postgres major version to be installed, `18` by default Note that PostgreSQL physical stream replication cannot cross major versions, so do not configure this on instance level. You can use the parameters in [`pg_packages`](#pg_packages) and [`pg_extensions`](#pg_extensions) to install rpm/deb for the specific pg major version. ------ ### `pg_bin_dir` name: `pg_bin_dir`, type: `path`, level: `C` postgres binary dir, `/usr/pgsql/bin` by default The default value is a soft link created manually during the installation process, pointing to the specific Postgres version dir installed. For example `/usr/pgsql -> /usr/pgsql-17`. For more details, check [PGSQL File Structure](/docs/pgsql) 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`](#pg_data) it will not be created explicitly (it will be created by postgres itself then). ------ ### `pg_packages` name: `pg_packages`, type: `string[]`, level: `C` PostgreSQL packages (rpm/deb) to be installed. This is an array of package names, where each element is a comma or space-separated list of PG package names or aliases. Default value: `[ pgsql-main pgsql-common ]` These default values are two aliases that are translated through [**alias mapping**](/docs/pgsql/extension/pkg) into the main RPM/DEB package names for the current PG major version, as well as version-independent common components (such as Patroni, PgBackrest, etc.) Since Pigsty v3, you can use the alias lists specified in the system configuration in [`roles/node_id/vars`](https://github.com/pgsty/pigsty/tree/v3.7.0/roles/node_id/vars/) for this parameter. The advantage of using package aliases is that you don’t need to worry about package names, architectures, and major version numbers for PostgreSQL-related packages across different system platforms, thus abstracting away differences between operating systems: Packages defined here will first be translated through the `package_map`, then undergo PG major version number substitution, and finally install the actual RPM/DEB packages. You can also directly specify the final RPM/DEB package names to be installed, where version placeholders like `${pg_version}` or `$v` in the package name will be replaced with the specific major version number [`pg_version`](#pg_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`](#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`. ```yaml pg_extensions: [] ``` The complete list of extensions can be found in [auto generated](https://github.com/pgsty/pigsty/tree/v3.7.0/roles/node_id/vars) config The full extension list can be found in [`roles/node_id/vars`](https://github.com/pgsty/pigsty/tree/v3.7.0/roles/node_id/vars/) and listed in [Extension List](https://pgext.cloud/list/). ------ ## `PG_BOOTSTRAP` Bootstrap postgres cluster with patroni. It also init cluster template databases with default roles, schemas & extensions & default privileges specified in [`PG_PROVISION`](#pg_provision) ```yaml pg_data: /pg/data # postgres data directory, `/pg/data` by default pg_fs_main: /data/postgres # postgres main data directory, `/data/postgres` by default pg_fs_backup: /data/backups # postgres backup data directory, `/data/backups` by default pg_storage_type: SSD # storage type for pg main data, SSD,HDD, SSD by default pg_dummy_filesize: 64MiB # size of `/pg/dummy`, hold 64MB disk space for emergency use pg_listen: '0.0.0.0' # postgres/pgbouncer listen addresses, comma separated list pg_port: 5432 # postgres listen port, 5432 by default pg_localhost: /var/run/postgresql # postgres unix socket dir for localhost connection patroni_enabled: true # if disabled, no postgres cluster will be created during init patroni_mode: default # patroni working mode: default,pause,remove pg_namespace: /pg # top level key namespace in etcd, used by patroni & vip patroni_port: 8008 # patroni listen port, 8008 by default patroni_log_dir: /pg/log/patroni # patroni log dir, `/pg/log/patroni` by default patroni_ssl_enabled: false # secure patroni RestAPI communications with SSL? patroni_watchdog_mode: off # patroni watchdog mode: automatic, required, off. off by default patroni_username: postgres # patroni restapi username, `postgres` by default patroni_password: Patroni.API # patroni restapi password, `Patroni.API` by default pg_primary_db: postgres # primary database name, used by citus,etc... postgres by default pg_parameters: {} # extra parameters in postgresql.auto.conf pg_files: [] # extra files to be copied to postgres data directory (e.g. license) pg_conf: oltp.yml # config template: oltp,olap,crit,tiny. `oltp.yml` by default pg_max_conn: auto # postgres max connections, `auto` will use recommended value pg_shared_buffer_ratio: 0.25 # postgres shared buffers ratio, 0.25 by default, 0.1~0.4 pg_rto: 30 # recovery time objective in seconds, `30s` by default pg_rpo: 1048576 # recovery point objective in bytes, `1MiB` at most by default pg_libs: 'pg_stat_statements, auto_explain' # preloaded libraries, `pg_stat_statements,auto_explain` by default pg_delay: 0 # replications apply delay for standby cluster leader pg_checksum: true # enable data checksum for postgres cluster? pg_pwd_enc: scram-sha-256 # passwords encryption algorithm: md5,scram-sha-256 pg_encoding: UTF8 # database cluster encoding, `UTF8` by default pg_locale: C # database cluster local, `C` by default pg_lc_collate: C # database cluster collate, `C` by default pg_lc_ctype: C # database character type, `C` by default #pgsodium_key: "" # pgsodium key, 64 hex digits, default to sha256(pg_cluster) #pgsodium_getkey_script: "" # pgsodium getkey script path, pgsodium_getkey by default ``` ------ ### `pg_data` name: `pg_data`, type: `path`, level: `C` postgres data directory, `/pg/data` by default default values: `/pg/data`, DO NOT CHANGE IT. It’s a soft link that points to the underlying data directory. ------ ### `pg_fs_main` name: `pg_fs_main`, type: `path`, level: `C` postgres main data directory, `/data/postgres` by default. This directory will be created and owned by the [`pg_dbsu`](#pg_dbsu) user, and it will be used as the main data directory for postgres. If your main data directory [`node_data`](/docs/node/param#node_data) is changed, consider changing this parameter as well. It’s recommended to use NVME SSD for postgres main data storage, Pigsty is optimized for SSD storage by default. If you are using HDD storage, consider changing the [`pg_storage_type`](#pg_storage_type) to `HDD` to optimize for HDD storage. ------ ### `pg_fs_backup` name: `pg_fs_backup`, type: `path`, level: `C` postgres backup data directory, `/data/backups` by default This directory will be created and owned by the [`pg_dbsu`](#pg_dbsu) user, and it will be used as the local backup storage for postgres. Local backup is enabled by default on primary pg cluster. If you are using the default [`pgbackrest_method`](#pgbackrest_method) = `local`, it is recommended to have a separate disk for backup storage. The backup disk should be large enough to hold all your backups, at least enough for 3 base backups + 2-day WAL archive. This is usually not a problem since you can use affordable and large HDD for that. It's optional if you are using remote / centralized backup storage (e.g. [`pgbackrest_method`](#pgbackrest_method) = `minio`). ------ ### `pg_storage_type` name: `pg_storage_type`, type: `enum`, level: `C` storage type for pg main data, `SSD`,`HDD`, `SSD` by default default values: `SSD`, it will affect some tuning parameters, such as `random_page_cost` & `effective_io_concurrency` ------ ### `pg_dummy_filesize` name: `pg_dummy_filesize`, type: `size`, level: `C` size of `/pg/dummy`, default values: `64MiB`, which hold 64MB disk space for emergency use When the disk is full, removing the placeholder file can free up some space for emergency use, it is recommended to set at least `8GiB` for production use. ------ ### `pg_listen` name: `pg_listen`, type: `ip`, level: `C` postgres/pgbouncer listen address, `0.0.0.0` (all ipv4 addr) by default You can use placeholder in this variable: - `${ip}`: translate to inventory_hostname, which is primary private IP address in the inventory - `${vip}`: if [`pg_vip_enabled`](#pg_vip_enabled), this will translate to host part of [`pg_vip_address`](#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`](#pg_exporter) and patroni. ------ ### `pg_namespace` name: `pg_namespace`, type: `path`, level: `C` top level key namespace in etcd, used by patroni & vip, default values is: `/pg` , and it’s not recommended to change it. ------ ### `patroni_enabled` name: `patroni_enabled`, type: `bool`, level: `C` if disabled, no postgres cluster will be created during init default value is `true`, If disabled, Pigsty will skip pulling up patroni (thus postgres). This option is useful when trying to add some components to an existing postgres instance. ------ ### `patroni_mode` name: `patroni_mode`, type: `enum`, level: `C` patroni working mode: `default`, `pause`, `remove` default values: `default` - `default`: Bootstrap PostgreSQL cluster with Patroni - `pause`: Just like `default`, but entering maintenance mode after bootstrap - `remove`: Init the cluster with Patroni, them remove Patroni and use raw PostgreSQL instead. ------ ### `patroni_port` name: `patroni_port`, type: `port`, level: `C` patroni listening port, `8008` by default, changing it is not recommended. The Patroni API server listens to this port for health checking & API requests. ------ ### `patroni_log_dir` name: `patroni_log_dir`, type: `path`, level: `C` patroni log dir, `/pg/log/patroni` by default, which will be collected by [`promtail`](/docs/node/param#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](https://patroni.readthedocs.io/en/latest/watchdog.html) 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](/docs/pgsql/service/#access-service) 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_password) Patroni unsafe RESTAPI is protected by username/password by default, check [Config Cluster](/docs/pgsql/admin#config-cluster) and [Patroni RESTAPI](https://patroni.readthedocs.io/en/latest/rest_api.html) 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. ```yaml 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](https://patroni.readthedocs.io/en/latest/patroni_configuration.html#important-rules) 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`: ```yaml 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](/docs/config/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/.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`](#pg_conf) and [`pg_default_service_dest`](#pg_default_service_dest). - tiny: 250 - olap: 500 - crit: 500 (pgbouncer) / 1000 (postgres) - oltp: 500 (pgbouncer) / 1000 (postgres) It’s not recommended to set this value greater than 5000, otherwise you have to increase the haproxy service connection limit manually as well. Pgbouncer’s transaction pooling can alleviate the problem of too many OLTP connections, but it’s not recommended to use it in OLAP scenarios. ------ ### `pg_shared_buffer_ratio` name: `pg_shared_buffer_ratio`, type: `float`, level: `C` postgres shared buffer memory ratio, 0.25 by default, 0.1~0.4 default values: `0.25`, means 25% of node memory will be used as PostgreSQL shard buffers. Setting this value greater than 0.4 (40%) is usually not a good idea. Note that shared buffer is only part of shared memory in PostgreSQL, to calculate the total shared memory, use `show shared_memory_size_in_huge_pages;`. ------ ### `pg_rto` name: `pg_rto`, type: `int`, level: `C` recovery time objective in seconds, This will be used as Patroni TTL value, `30`s by default. If a primary instance is missing for such a long time, a new leader election will be triggered. Decreasing the value can reduce the unavailable time (unable to write) of the cluster during failover, but it will make the cluster more sensitive to network jitter, thus increase the chance of false-positive failover. Config this according to your network condition and expectation to **trade-off between chance and impact**, the default value is 30s, and it will be populated to the following patroni parameters: ```yaml # the TTL to acquire the leader lock (in seconds). Think of it as the length of time before initiation of the automatic failover process. Default value: 30 ttl: {{ pg_rto }} # the number of seconds the loop will sleep. Default value: 10 , this is patroni check loop interval loop_wait: {{ (pg_rto / 3)|round(0, 'ceil')|int }} # timeout for DCS and PostgreSQL operation retries (in seconds). DCS or network issues shorter than this will not cause Patroni to demote the leader. Default value: 10 retry_timeout: {{ (pg_rto / 3)|round(0, 'ceil')|int }} # the amount of time a primary is allowed to recover from failures before failover is triggered (in seconds), Max RTO: 2 loop wait + primary_start_timeout primary_start_timeout: {{ (pg_rto / 3)|round(0, 'ceil')|int }} ``` ------ ### `pg_rpo` name: `pg_rpo`, type: `int`, level: `C` recovery point objective in bytes, `1MiB` at most by default default values: `1048576`, which will tolerate at most 1MiB data loss during failover. when the primary is down and all replicas are lagged, you have to make a tough choice to **trade off between Availability and Consistency**: - Promote a replica to be the new primary and bring the system back online ASAP, with the price of an acceptable data loss (e.g. less than 1MB). - Wait for the primary to come back (which may never be) or human intervention to avoid any data loss. You can use `crit.yml` [conf](#pg_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](/docs/pgsql/admin#config-cluster) 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: ```bash citus,timescaledb,pg_stat_statements,auto_explain ``` Other extensions that need to be loaded can also be added to this list, such as `pg_cron`, `pgml`, etc. Generally, `citus` and `timescaledb` have the highest priority and should be added to the top of the list. ------ ### `pg_delay` name: `pg_delay`, type: `interval`, level: `I` replications apply delay for standby cluster leader, default values: `0`. if this value is set to a positive value, the standby cluster leader will be delayed for this time before apply WAL changes. Check [delayed standby cluster](/docs/pgsql/config#delayed-cluster) for details. ------ ### `pg_checksum` name: `pg_checksum`, type: `bool`, level: `C` enable data checksum for postgres cluster? The v3.7.0 default value is `true`. This parameter can only be set before PGSQL deployment. (but you can enable it manually later) If [`pg_conf`](#pg_conf) `crit.yml` template is used, data checksum is always enabled regardless of this parameter to ensure data integrity. ------ ### `pg_pwd_enc` name: `pg_pwd_enc`, type: `enum`, level: `C` password encryption algorithm: md5, scram-sha-256 default values: `scram-sha-256`, if you have compatibility issues with old clients, you can set it to `md5` instead. **md5 encryption is deprecated!** The `md5` option is deprecated but remains available in v3.7.0 for legacy clients; prefer `scram-sha-256`. ------ ### `pg_encoding` name: `pg_encoding`, type: `enum`, level: `C` database cluster encoding, `UTF8` by default ------ ### `pg_locale` name: `pg_locale`, type: `enum`, level: `C` The locale set for PostgreSQL, default is `C`. When `configure` detects that the current PG version is greater than or equal to 17, or the current system explicitly supports `C.utf8`, it will automatically configure this parameter to `C.UTF-8`. When the PostgreSQL version is greater than or equal to 17, the `C` and `C.UTF-8` configurations will use the PostgreSQL internal Locale Provider. Unless you are very clear about what you are doing, it is strongly recommended to use the default `C` or `C.UTF-8` configuration. ------ ### `pg_lc_collate` name: `pg_lc_collate`, type: `enum`, level: `C` The locale set for PostgreSQL, default is `C`. When `configure` detects that the current PG version is greater than or equal to 17, or the current system explicitly supports `C.utf8`, it will automatically configure this parameter to `C.UTF-8`. Unless you are very clear about what you are doing, it is strongly recommended to use the default `C` or `C.UTF-8` configuration. The parameter behaves like [`pg_locale`](#pg_locale), but for collate. ------ ### `pg_lc_ctype` name: `pg_lc_ctype`, type: `enum`, level: `C` The locale set for PostgreSQL, default is `C`. When `configure` detects that the current PG version is greater than or equal to 17, or the current system explicitly supports `C.utf8`, it will automatically configure this parameter to `C.UTF-8`. When the PostgreSQL version is greater than or equal to 17, the `C` and `C.UTF-8` configurations will use the PostgreSQL internal Locale Provider. This parameter behaves like [`pg_locale`](#pg_locale), but for ctype. Unless you are very clear about what you are doing, it is strongly recommended to use the default `C` or `C.UTF-8` configuration. ------ ### `pgsodium_key` name: `pgsodium_key`, type: `string`, level: `C` Default value is not defined, which will use the SHA256 hash of the [`pg_cluster`](#pg_cluster) as the key. You can provide a custom [pgsodium](https://github.com/michelp/pgsodium) key, which should be a 64 hex digit string. The key will be written to `/pg/conf/pgsodium.key`. ------ ### `pgsodium_getkey_script` name: `pgsodium_getkey_script`, type: `path`, level: `C` default value is `pgsodium_getkey`, which render the [`roles/pgsql/templates/pgsodium_getkey`](https://github.com/pgsty/pigsty/blob/v3.7.0/roles/pgsql/templates/pgsodium_getkey) to `/pg/bin/pgsodium_getkey`. The default getkey script will just read the [`pgsodium_key`](#pgsodium_key) from `/pg/conf/pgsodium.key`, and return it. If your key is managed by external system like KMS, IAM, ..., you can implement your own getkey script to fetch the key from there: [examples](https://github.com/michelp/pgsodium/tree/main/getkey_scripts). ------ ## `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](/docs/pgsql/acl#default-roles) - [Default Users](/docs/pgsql/acl#default-users) - [Default Privileges](/docs/pgsql/acl#privileges) - [Default HBA Rules](/docs/pgsql/hba/#default-hba) - Default Schemas - Default Extensions ```yaml pg_provision: true # provision postgres cluster after bootstrap pg_init: pg-init # provision init script for cluster template, `pg-init` by default pg_default_roles: # default roles and users in postgres cluster - { name: dbrole_readonly ,login: false ,comment: role for global read-only access } - { name: dbrole_offline ,login: false ,comment: role for restricted read-only access } - { name: dbrole_readwrite ,login: false ,roles: [dbrole_readonly] ,comment: role for global read-write access } - { name: dbrole_admin ,login: false ,roles: [pg_monitor, dbrole_readwrite] ,comment: role for object creation } - { name: postgres ,superuser: true ,comment: system superuser } - { name: replicator ,replication: true ,roles: [pg_monitor, dbrole_readonly] ,comment: system replicator } - { name: dbuser_dba ,superuser: true ,roles: [dbrole_admin] ,pgbouncer: true ,pool_mode: session, pool_connlimit: 16 , comment: pgsql admin user } - { name: dbuser_monitor ,roles: [pg_monitor, dbrole_readonly] ,pgbouncer: true ,parameters: {log_min_duration_statement: 1000 } ,pool_mode: session ,pool_connlimit: 8 ,comment: pgsql monitor user } pg_default_privileges: # default privileges when created by admin user - GRANT USAGE ON SCHEMAS TO dbrole_readonly - GRANT SELECT ON TABLES TO dbrole_readonly - GRANT SELECT ON SEQUENCES TO dbrole_readonly - GRANT EXECUTE ON FUNCTIONS TO dbrole_readonly - GRANT USAGE ON SCHEMAS TO dbrole_offline - GRANT SELECT ON TABLES TO dbrole_offline - GRANT SELECT ON SEQUENCES TO dbrole_offline - GRANT EXECUTE ON FUNCTIONS TO dbrole_offline - GRANT INSERT ON TABLES TO dbrole_readwrite - GRANT UPDATE ON TABLES TO dbrole_readwrite - GRANT DELETE ON TABLES TO dbrole_readwrite - GRANT USAGE ON SEQUENCES TO dbrole_readwrite - GRANT UPDATE ON SEQUENCES TO dbrole_readwrite - GRANT TRUNCATE ON TABLES TO dbrole_admin - GRANT REFERENCES ON TABLES TO dbrole_admin - GRANT TRIGGER ON TABLES TO dbrole_admin - GRANT CREATE ON SCHEMAS TO dbrole_admin pg_default_schemas: [ monitor ] # default schemas to be created pg_default_extensions: # default extensions to be created - { name: pg_stat_statements ,schema: monitor } - { name: pgstattuple ,schema: monitor } - { name: pg_buffercache ,schema: monitor } - { name: pageinspect ,schema: monitor } - { name: pg_prewarm ,schema: monitor } - { name: pg_visibility ,schema: monitor } - { name: pg_freespacemap ,schema: monitor } - { name: postgres_fdw ,schema: public } - { name: file_fdw ,schema: public } - { name: btree_gist ,schema: public } - { name: btree_gin ,schema: public } - { name: pg_trgm ,schema: public } - { name: intagg ,schema: public } - { name: intarray ,schema: public } - { name: pg_repack } pg_reload: true # reload postgres/pgbouncer/vip after conf changes pg_default_hba_rules: # postgres default host-based authentication rules - {user: '${dbsu}' ,db: all ,addr: local ,auth: ident ,title: 'dbsu access via local os user ident' } - {user: '${dbsu}' ,db: replication ,addr: local ,auth: ident ,title: 'dbsu replication from local os ident' } - {user: '${repl}' ,db: replication ,addr: localhost ,auth: pwd ,title: 'replicator replication from localhost'} - {user: '${repl}' ,db: replication ,addr: intra ,auth: pwd ,title: 'replicator replication from intranet' } - {user: '${repl}' ,db: postgres ,addr: intra ,auth: pwd ,title: 'replicator postgres db from intranet' } - {user: '${monitor}' ,db: all ,addr: localhost ,auth: pwd ,title: 'monitor from localhost with password' } - {user: '${monitor}' ,db: all ,addr: infra ,auth: pwd ,title: 'monitor from infra host with password'} - {user: '${admin}' ,db: all ,addr: infra ,auth: ssl ,title: 'admin @ infra nodes with pwd & ssl' } - {user: '${admin}' ,db: all ,addr: world ,auth: ssl ,title: 'admin @ everywhere with ssl & pwd' } - {user: '+dbrole_readonly',db: all ,addr: localhost ,auth: pwd ,title: 'pgbouncer read/write via local socket'} - {user: '+dbrole_readonly',db: all ,addr: intra ,auth: pwd ,title: 'read/write biz user via password' } - {user: '+dbrole_offline' ,db: all ,addr: intra ,auth: pwd ,title: 'allow etl offline tasks from intranet'} pgb_default_hba_rules: # pgbouncer default host-based authentication rules - {user: '${dbsu}' ,db: pgbouncer ,addr: local ,auth: peer ,title: 'dbsu local admin access with os ident'} - {user: 'all' ,db: all ,addr: localhost ,auth: pwd ,title: 'allow all user local access with pwd' } - {user: '${monitor}' ,db: pgbouncer ,addr: intra ,auth: pwd ,title: 'monitor access via intranet with pwd' } - {user: '${monitor}' ,db: all ,addr: world ,auth: deny ,title: 'reject all other monitor access addr' } - {user: '${admin}' ,db: all ,addr: intra ,auth: pwd ,title: 'admin access via intranet with pwd' } - {user: '${admin}' ,db: all ,addr: world ,auth: deny ,title: 'reject all other admin access addr' } - {user: 'all' ,db: all ,addr: intra ,auth: pwd ,title: 'allow all user intra access with pwd' } ``` ------ ### `pg_provision` name: `pg_provision`, type: `bool`, level: `C` provision postgres cluster after bootstrap, default value is `true`. If disabled, postgres cluster will not be provisioned after bootstrap. ------ ### `pg_init` name: `pg_init`, type: `string`, level: `G/C` Provision init script for cluster template, `pg-init` by default, which is located in [`roles/pgsql/templates/pg-init`](https://github.com/pgsty/pigsty/blob/v3.7.0/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](/docs/pgsql/acl) for details. ```yaml pg_default_roles: # default roles and users in postgres cluster - { name: dbrole_readonly ,login: false ,comment: role for global read-only access } - { name: dbrole_offline ,login: false ,comment: role for restricted read-only access } - { name: dbrole_readwrite ,login: false ,roles: [dbrole_readonly] ,comment: role for global read-write access } - { name: dbrole_admin ,login: false ,roles: [pg_monitor, dbrole_readwrite] ,comment: role for object creation } - { name: postgres ,superuser: true ,comment: system superuser } - { name: replicator ,replication: true ,roles: [pg_monitor, dbrole_readonly] ,comment: system replicator } - { name: dbuser_dba ,superuser: true ,roles: [dbrole_admin] ,pgbouncer: true ,pool_mode: session, pool_connlimit: 16 , comment: pgsql admin user } - { name: dbuser_monitor ,roles: [pg_monitor, dbrole_readonly] ,pgbouncer: true ,parameters: {log_min_duration_statement: 1000 } ,pool_mode: session ,pool_connlimit: 8 ,comment: pgsql monitor user } ``` ------ ### `pg_default_privileges` name: `pg_default_privileges`, type: `string[]`, level: `G/C` default privileges for each databases: ```yaml pg_default_privileges: # default privileges when created by admin user - GRANT USAGE ON SCHEMAS TO dbrole_readonly - GRANT SELECT ON TABLES TO dbrole_readonly - GRANT SELECT ON SEQUENCES TO dbrole_readonly - GRANT EXECUTE ON FUNCTIONS TO dbrole_readonly - GRANT USAGE ON SCHEMAS TO dbrole_offline - GRANT SELECT ON TABLES TO dbrole_offline - GRANT SELECT ON SEQUENCES TO dbrole_offline - GRANT EXECUTE ON FUNCTIONS TO dbrole_offline - GRANT INSERT ON TABLES TO dbrole_readwrite - GRANT UPDATE ON TABLES TO dbrole_readwrite - GRANT DELETE ON TABLES TO dbrole_readwrite - GRANT USAGE ON SEQUENCES TO dbrole_readwrite - GRANT UPDATE ON SEQUENCES TO dbrole_readwrite - GRANT TRUNCATE ON TABLES TO dbrole_admin - GRANT REFERENCES ON TABLES TO dbrole_admin - GRANT TRIGGER ON TABLES TO dbrole_admin - GRANT CREATE ON SCHEMAS TO dbrole_admin ``` Pigsty has a built-in privileges based on the default role system, check [PGSQL Privileges](/docs/pgsql/acl#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: ```yaml pg_default_extensions: # default extensions to be created - { name: pg_stat_statements ,schema: monitor } - { name: pgstattuple ,schema: monitor } - { name: pg_buffercache ,schema: monitor } - { name: pageinspect ,schema: monitor } - { name: pg_prewarm ,schema: monitor } - { name: pg_visibility ,schema: monitor } - { name: pg_freespacemap ,schema: monitor } - { name: postgres_fdw ,schema: public } - { name: file_fdw ,schema: public } - { name: btree_gist ,schema: public } - { name: btree_gin ,schema: public } - { name: pg_trgm ,schema: public } - { name: intagg ,schema: public } - { name: intarray ,schema: public } - { name: pg_repack } ``` The only 3rd party extension is [`pg_repack`](https://pgext.cloud/e/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_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](/docs/pgsql/hba/#define-hba) rule object. default value provides a fair enough security level for common scenarios, check [PGSQL Authentication](/docs/pgsql/hba/) for details. ```yaml 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](/docs/pgsql/hba/#define-hba) rule object. default value provides a fair enough security level for common scenarios, check [PGSQL Authentication](/docs/pgsql/hba/) for details. ```yaml 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](https://pgbackrest.org/), which is used for PGSQL PITR (Point-In-Time-Recovery). Check [PGSQL Backup & PITR](/docs/pgsql/backup/) for details. ```yaml pgbackrest_enabled: true # enable pgbackrest on pgsql host? pgbackrest_clean: true # remove pg backup data during init? pgbackrest_log_dir: /pg/log/pgbackrest # pgbackrest log dir, `/pg/log/pgbackrest` by default pgbackrest_method: local # pgbackrest repo method: local,minio,[user-defined...] pgbackrest_init_backup: true # take a full backup after pgbackrest is initialized? pgbackrest_repo: # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository local: # default pgbackrest repo with local posix fs path: /pg/backup # local backup directory, `/pg/backup` by default retention_full_type: count # retention full backups by count retention_full: 2 # keep 2, at most 3 full backups when using local fs repo minio: # optional minio repo for pgbackrest type: s3 # minio is s3-compatible, so s3 is used s3_endpoint: sss.pigsty # minio endpoint domain name, `sss.pigsty` by default s3_region: us-east-1 # minio region, us-east-1 by default, useless for minio s3_bucket: pgsql # minio bucket name, `pgsql` by default s3_key: pgbackrest # minio user access key for pgbackrest s3_key_secret: S3User.Backup # minio user secret key for pgbackrest s3_uri_style: path # use path style uri for minio rather than host style path: /pgbackrest # minio backup path, default is `/pgbackrest` storage_port: 9000 # minio port, 9000 by default storage_ca_file: /etc/pki/ca.crt # minio ca file path, `/etc/pki/ca.crt` by default block: y # Enable block incremental backup bundle: y # bundle small files into a single file bundle_limit: 20MiB # Limit for file bundles, 20MiB for object storage bundle_size: 128MiB # Target size for file bundles, 128MiB for object storage cipher_type: aes-256-cbc # enable AES encryption for remote backup repo cipher_pass: pgBackRest # AES encryption password, default is 'pgBackRest' retention_full_type: time # retention full backup by time on minio repo retention_full: 14 # keep full backup for the last 14 days ``` ------ ### `pgbackrest_enabled` name: `pgbackrest_enabled`, type: `bool`, level: `C` enable pgBackRest on pgsql host? default value is `true` When using the local file system backup repository (`local`), only the primary instance of the cluster will actually enable `pgbackrest`. Other instances will only initialize an empty repository. ------ ### `pgbackrest_clean` name: `pgbackrest_clean`, type: `bool`, level: `C` remove pg backup data during init? default value is `true` ------ ### `pgbackrest_log_dir` name: `pgbackrest_log_dir`, type: `path`, level: `C` pgBackRest log dir, `/pg/log/pgbackrest` by default, which is referenced by [`promtail`](/docs/node/param#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`](#pgbackrest_repo). Pigsty will use `local` backup repo by default, which will create a backup repo on primary instance’s `/pg/backup` directory. The underlying storage is specified by [`pg_fs_backup`](#pg_fs_backup). ------ ### `pgbackrest_init_backup` name: `pgbackrest_init_backup`, type: `bool`, level: `C` Take a full backup after pgBackRest is initialized? default value is `true`. An initial pgbackrest backup is created after repo init if: - `pgbackrest_init_backup` is `true` (and `pgbackrest_enabled` is `true` of course) - The `/etc/pgbackrest/initial.done` marker file doesn't exist (will be created after the initial backup is done). If you don't want to take an initial full backup at all, just set this parameter to`false`. ------ ### `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: ```yaml pgbackrest_repo: # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository local: # default pgbackrest repo with local posix fs path: /pg/backup # local backup directory, `/pg/backup` by default retention_full_type: count # retention full backups by count retention_full: 2 # keep 2, at most 3 full backups when using local fs repo minio: # optional minio repo for pgbackrest type: s3 # minio is s3-compatible, so s3 is used s3_endpoint: sss.pigsty # minio endpoint domain name, `sss.pigsty` by default s3_region: us-east-1 # minio region, us-east-1 by default, useless for minio s3_bucket: pgsql # minio bucket name, `pgsql` by default s3_key: pgbackrest # minio user access key for pgbackrest s3_key_secret: S3User.Backup # minio user secret key for pgbackrest s3_uri_style: path # use path style uri for minio rather than host style path: /pgbackrest # minio backup path, default is `/pgbackrest` storage_port: 9000 # minio port, 9000 by default storage_ca_file: /etc/pki/ca.crt # minio ca file path, `/etc/pki/ca.crt` by default block: y # Enable block incremental backup bundle: y # bundle small files into a single file bundle_limit: 20MiB # Limit for file bundles, 20MiB for object storage bundle_size: 128MiB # Target size for file bundles, 128MiB for object storage cipher_type: aes-256-cbc # enable AES encryption for remote backup repo cipher_pass: pgBackRest # AES encryption password, default is 'pgBackRest' retention_full_type: time # retention full backup by time on minio repo retention_full: 14 # keep full backup for the last 14 days ``` You can define a new backup repository, for example, using AWS S3, GCP or another cloud provider’s S3-compatible storage service. In the backup repository definition parameters, you can use `${pg_cluster}` variable to reference the cluster name, for example, as part of the backup path or encryption key. But if you have cross-cluster PITR requirements, you should keep the backup repository path and encryption key the same. ------ ## `PG_ACCESS` This section is about exposing PostgreSQL service to the outside world: including: - Connection Pooling with `pgbouncer` - Exposing different PostgreSQL services on different ports with `haproxy` - Bind an optional L2 VIP to the primary instance with `vip-manager` - Register cluster/instance DNS records with to `dnsmasq` on infra nodes ```yaml pgbouncer_enabled: true # if disabled, pgbouncer will not be launched on pgsql host pgbouncer_port: 6432 # pgbouncer listen port, 6432 by default pgbouncer_log_dir: /pg/log/pgbouncer # pgbouncer log dir, `/pg/log/pgbouncer` by default pgbouncer_auth_query: false # query postgres to retrieve unlisted business users? pgbouncer_poolmode: transaction # pooling mode: transaction,session,statement, transaction by default pgbouncer_sslmode: disable # pgbouncer client ssl mode, disable by default pg_weight: 100 #INSTANCE # relative load balance weight in service, 100 by default, 0-255 pg_default_service_dest: pgbouncer # default service destination if svc.dest='default' pg_default_services: # postgres default service definitions - { name: primary ,port: 5433 ,dest: default ,check: /primary ,selector: "[]" } - { name: replica ,port: 5434 ,dest: default ,check: /read-only ,selector: "[]" , backup: "[? pg_role == `primary` || pg_role == `offline` ]" } - { name: default ,port: 5436 ,dest: postgres ,check: /primary ,selector: "[]" } - { name: offline ,port: 5438 ,dest: postgres ,check: /replica ,selector: "[? pg_role == `offline` || pg_offline_query ]" , backup: "[? pg_role == `replica` && !pg_offline_query]"} pg_vip_enabled: false # enable a l2 vip for pgsql primary? false by default pg_vip_address: 127.0.0.1/24 # vip address in `/` format, require if vip is enabled pg_vip_interface: eth0 # vip network interface to listen, eth0 by default pg_dns_suffix: '' # pgsql dns suffix, '' by default pg_dns_target: auto # auto, primary, vip, none, or ad hoc ip ``` ------ ### `pgbouncer_enabled` name: `pgbouncer_enabled`, type: `bool`, level: `C` default value is `true`, if disabled, pgbouncer will not be launched on pgsql host ------ ### `pgbouncer_port` name: `pgbouncer_port`, type: `port`, level: `C` pgbouncer listen port, `6432` by default ------ ### `pgbouncer_log_dir` name: `pgbouncer_log_dir`, type: `path`, level: `C` pgbouncer log dir, `/pg/log/pgbouncer` by default, referenced by promtail the logging agent. ------ ### `pgbouncer_auth_query` name: `pgbouncer_auth_query`, type: `bool`, level: `C` query postgres to retrieve unlisted business users? default value is `false` If enabled, pgbouncer user will be authenticated against postgres databases 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 your 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 a client requests TLS, it’s ignored. Default. - `allow`: If a 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. ------ ### `pgbouncer_ignore_param` name: `pgbouncer_ignore_param`, type: `string[]`, level: `G/C` default values: `[ extra_float_digits, application_name, TimeZone, DateStyle, IntervalStyle, search_path ]` This will be used as value of [**`ignore_startup_parameters`**](https://www.pgbouncer.org/config.html#ignore_startup_parameters) in pgbouncer. ------ ### `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](/docs/pgsql/admin#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 dedicated haproxy nodes for each service! For example, if we define the following parameters on 3-node `pg-test` cluster: ```yaml 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](/docs/pgsql/service/#define-service), if `svc.dest`= `default`, this parameter will be used as the default value. default values: `pgbouncer`, means 5433 the primary service and 5434 the replicas service will route traffic to pgbouncer by default. If you don’t want to use pgbouncer, set it to `postgres` instead. traffic will be routed 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 definitions, which are explained in [PGSQL Service](/docs/pgsql/service/#define-service) ```yaml 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 the same L2 network, which may incur extra restrictions on your network topology. ------ ### `pg_vip_address` name: `pg_vip_address`, type: `cidr4`, level: `C` vip address in `/` 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 nodes have different interface, you can override it on instance vars: ```yaml 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, empty string 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 - ``: bind to the given IP address ------ ## `PG_MONITOR` ```yaml 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 databases that WILL NOT be monitored during auto-discovery pg_exporter_include_database: '' # csv of databases 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 pgbackrest_exporter_enabled: true # enable pgbackrest_exporter on pgsql hosts? pgbackrest_exporter_port: 9854 # pgbackrest_exporter listen port, 9854 by default pgbackrest_exporter_options: '' # overwrite extra options for pgbackrest_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/.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. ```yaml 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) ``` This should be set in pair with [`prometheus_scrape_interval`](/zh/docs/infra/param#prometheus_scrape_interval) - fast : 1~10s, critical metrics, never cache - norm : same as prometheus scrape internal - slow : slow and bulky metrics like object metrics - slowest : very slow metrics like table size, bloat rate ------ ### `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. ```bash '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 databases 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 databases that WILL BE monitored during auto-discovery the default value is empty string. If 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: `200`ms , 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 the default value is empty string, which will fall back the following default options: ```bash {% if pg_exporter_port != '' %} PG_EXPORTER_OPTS='--web.listen-address=:{{ pg_exporter_port }} {{ pg_exporter_options }}' {% else %} PG_EXPORTER_OPTS='--web.listen-address=:{{ pg_exporter_port }} --log.level=info' {% endif %} ``` 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 ------ ### `pgbouncer_exporter_url` name: `pgbouncer_exporter_url`, type: `pgurl`, level: `C` overwrite auto-generate pgbouncer dsn if specified the default value is empty string, If specified, it will be used as the pgbouncer_exporter dsn instead of constructing from other parameters: ```bash '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. the default value is empty string, which will fall back the following default options: ```bash {% if pgbouncer_exporter_options != '' %} PG_EXPORTER_OPTS='--web.listen-address=:{{ pgbouncer_exporter_port }} {{ pgbouncer_exporter_options }}' {% else %} PG_EXPORTER_OPTS='--web.listen-address=:{{ pgbouncer_exporter_port }} --log.level=info' {% endif %} ``` If you want to customize logging options or other pgbouncer_exporter options, you can set it here. but do not overwrite [`pgbouncer_exporter_port`](#pgbouncer_exporter_port) here. ------ ### `pgbackrest_exporter_enabled` name: `pgbackrest_exporter_enabled`, type: `bool`, level: `C` enable pgbackrest_exporter on pgsql hosts? default value is `true` If [`pgbackrest_enabled`](#pgbackrest_enabled) is `false`, this parameter will be short-circuited and disabled. ------ ### `pgbackrest_exporter_port` name: `pgbackrest_exporter_port`, type: `port`, level: `C` pgbackrest_exporter listen port, `9854` by default ------ ### `pgbackrest_exporter_options` name: `pgbackrest_exporter_options`, type: `arg`, level: `C` extra cli args for pgbackrest_exporter, default value is empty string `""`. ------ ## `PG_REMOVE` These flags control the [`pgsql-rm.yml`](/docs/pgsql/playbook/#pgsql-rmyml) playbook and match `roles/pg_remove/defaults/main.yml` in v3.7.0. ```yaml pg_safeguard: false # abort removal when explicitly enabled pg_rm_data: true # remove postgres data during removal pg_rm_backup: true # remove primary pgBackRest backup during removal pg_rm_pkg: true # uninstall postgres packages during removal ``` ------ ### `pg_safeguard` name: `pg_safeguard`, type: `bool`, level: `G/C/A` When `true`, the `pgsql-rm.yml` playbook aborts before changing the cluster. The v3.7.0 default is `false`. ------ ### `pg_rm_data` name: `pg_rm_data`, type: `bool`, level: `G/C/A` Remove PostgreSQL data during removal. The default is `true`; set it to `false` to preserve the data directories. ------ ### `pg_rm_backup` name: `pg_rm_backup`, type: `bool`, level: `G/C/A` Remove the pgBackRest repository when removing a primary instance. The default is `true`; set it to `false` to preserve backups. ------ ### `pg_rm_pkg` name: `pg_rm_pkg`, type: `bool`, level: `G/C/A` Uninstall PostgreSQL and extension packages during removal. The v3.7.0 role default is `true`; set it to `false` to keep installed packages.