# Configure > Describe and configure PostgreSQL clusters --- You can define different types of instances & clusters. - [Identity Parameters](#identity-parameters): Parameters used for describing a PostgreSQL cluster - [Naming Convention](#naming-convention): Parameters used for describing a PostgreSQL cluster - [Primary](#primary): Define a single instance cluster. - [Replica](#replica): Define a basic HA cluster with one primary & one replica. - [Offline](#offline): Define a dedicated instance for OLAP/ETL/Interactive queries - [Sync Standby](#sync-standby): Enable synchronous commit to ensure no data loss. - [Quorum Commit](#quorum-commit): Use quorum sync commit for an even higher consistency level. - [Standby Cluster](#standby-cluster): Clone an existing cluster and follow it - [Delayed Cluster](#delayed-cluster): Clone an existing cluster for emergency data recovery - [Citus Cluster](#citus-cluster): Define a Citus distributed database cluster -------- ## Identity Parameters There are **4** **REQUIRED** parameters to describe a PostgreSQL Cluster: | Name | Type | Level | Description | |:---------------------------------------:|:--------:|:------------:|-------------------------------| | `inventory_hostname` | `ip` | **Instance** | **PG node IPv4 address** | | [`pg_cluster`](/docs/pgsql/param#pg_cluster) | `string` | **Cluster** | **PG database cluster name** | | [`pg_seq`](/docs/pgsql/param#pg_seq) | `number` | **Instance** | **PG database instance id** | | [`pg_role`](/docs/pgsql/param#pg_role) | `enum` | **Instance** | **PG database instance role** | - [`pg_cluster`](/docs/pgsql/param#pg_cluster): Name of the cluster, configured at the cluster level. - [`pg_role`](/docs/pgsql/param#pg_role): Configured at the instance level, identifies the role of the instance. - the `primary` role will mark this instance as cluster leader (initially). - the `replica` is the default role, which marks this instance as common read-only replica. - the `offline` marks this instance as special read-only replica that serves the `offline` service. - [`pg_seq`](/docs/pgsql/param#pg_seq): Used to identify the instance within the cluster, a non-negative integer - Start from 0 or 1, incremental allocation in sequence, never change once assigned, - `{{ pg_cluster }}-{{ pg_seq }}` is used to uniquely identify the instance, i.e. `pg_instance`. - `{{ pg_cluster }}-{{ pg_role }}` is used to identify the services within the cluster, i.e. `pg_service`. - [`pg_shard`](/docs/pgsql/param#pg_shard) and [`pg_group`](/docs/pgsql/param#pg_group) are used for horizontally sharding clusters, for citus & greenplum only. These identities will be used in the entire system, for example, the metrics may look like: ```yaml pg_up{cls="pg-test", ins="pg-test-1", ip="10.10.10.11", job="pgsql"} pg_up{cls="pg-test", ins="pg-test-2", ip="10.10.10.12", job="pgsql"} pg_up{cls="pg-test", ins="pg-test-3", ip="10.10.10.13", job="pgsql"} ``` ### Sharding Clusters You can use the **OPTIONAL** `pg_shard` and `pg_group` param to identify horizontal sharded clusters: | Name | Type | Level | Description | |:-----------------------------------:|:--------:|:-----:|----------------------------------------| | [`pg_shard`](/docs/pgsql/param#pg_shard) | `string` | **C** | **PG database shard name of cluster** | | [`pg_group`](/docs/pgsql/param#pg_group) | `number` | **C** | **PG database shard index of cluster** | For example, Horizontal sharding with citus, greenplum or sharding it manually ```yaml pg-citus: hosts: 10.10.10.10: { pg_group: 0, pg_cluster: pg-citus0 ,pg_seq: 1, pg_role: primary } 10.10.10.11: { pg_group: 0, pg_cluster: pg-citus0 ,pg_seq: 2, pg_role: replica } 10.10.10.12: { pg_group: 1, pg_cluster: pg-citus1 ,pg_seq: 1, pg_role: primary } 10.10.10.13: { pg_group: 2, pg_cluster: pg-citus2 ,pg_seq: 1, pg_role: primary } vars: pg_mode: citus # pgsql cluster mode: citus pg_shard: pg-citus # citus shard name: pg-citus ``` ------ ## Naming Convention - Cluster name should be a valid domain name matches `[a-zA-Z0-9-]+`, and ≤ 40 char - Service names are prefixed with cluster name, and suffixed with a single word join by `-` - Instance names are prefixed with cluster name and suffixed with an integer, join by `-` - Nodes are identified by its primary IPv4 address, hostname is used as secondary identifier | Entity | Naming Examples | |:------------:|:----------------------------------------------------------------------------------------------| | **Cluster** | `pg-meta`, `pg-test`, ... | | **Service** | `pg-meta-primary`, `pg-test-replica`, `pg-test-offline`, `pg-test-standby`, `pg-meta-default` | | **Instance** | `pg-meta-1`, `pg-test-1`, `pg-test-2`, `pg-test-3`,... | | **Node** | `10.10.10.10`, `10.10.10.11`, `10.10.10.12`, `10.10.10.13`... | ------ ## Version Policy Pigsty follows the [**PostgreSQL Version Policy**](https://www.postgresql.org/support/versioning/) and "Officially" support the following major versions. | Major | Minor | Comment | RPM EXT | DEB EXT | |-------|---------|----------------------------------------------|:-------:|:-------:| | `18` | `18.1` | The latest stable version (**RECOMMENDED**) | 392 | 390 | | `17` | `17.7` | The sendary stable version (**RECOMMENDED**) | 418 | 413 | | `16` | `16.11` | First release on 2023-09-14 | 420 | 412 | | `15` | `15.15` | First release on 2022-10-13 | 422 | 414 | | `14` | `14.20` | First release on 2021-09-30 | 410 | 402 | | `13` | `13.23` | First release on 2020-09-24, EOLed soon | 382 | 371 | Pigsty has PG 13 - 18 support. Lower major version (12-) "may" work, with no guarantee. For legacy PG version support, consider our [professional services](/docs/service). To use a different major version, configure the [`pg_version`](/docs/pgsql/param#pg_version) variable. Which can be globally [`configure`](/docs/config/configure) with `-v ` option. No further changed needed as long as they are available in local / upstream repo. ```yaml pg-v13: hosts: { 10.10.10.13: { pg_seq: 1 ,pg_role: primary } } vars: pg_cluster: pg-v13 pg_version: 13 pg-v14: hosts: { 10.10.10.14: { pg_seq: 1 ,pg_role: primary } } vars: pg_cluster: pg-v14 pg_version: 14 pg-v15: hosts: { 10.10.10.15: { pg_seq: 1 ,pg_role: primary } } vars: pg_cluster: pg-v15 pg_version: 15 pg-v16: hosts: { 10.10.10.16: { pg_seq: 1 ,pg_role: primary } } vars: pg_cluster: pg-v16 pg_version: 16 pg-v17: hosts: { 10.10.10.17: { pg_seq: 1 ,pg_role: primary } } vars: pg_cluster: pg-v17 pg_version: 17 ``` ------ ## Primary Let’s start with the simplest case, singleton meta: ```yaml pg-test: hosts: 10.10.10.11: { pg_seq: 1, pg_role: primary } vars: pg_cluster: pg-test ``` Use the following command to create a primary database instance on the `10.10.10.11` node. ```bash bin/pgsql-add pg-test ``` ------ ## Replica To add a physical replica, you can assign a new instance to `pg-test` with [`pg_role`](/docs/pgsql/param#pg_role) set to `replica` ```yaml pg-test: hosts: 10.10.10.11: { pg_seq: 1, pg_role: primary } 10.10.10.12: { pg_seq: 2, pg_role: replica } # <--- newly added vars: pg_cluster: pg-test ``` You can [create](/docs/pgsql/admin#create-cluster) an entire cluster or [append](/docs/pgsql/admin#append-replica) a replica to the existing cluster: ```bash bin/pgsql-add pg-test # init entire cluster in one-pass bin/pgsql-add pg-test 10.10.10.12 # add replica to existing cluster ``` ------ ## Offline The offline instance is a dedicated replica to serve slow queries, ETL, OLAP traffic and interactive queries, etc… To add an offline instance, assign a new instance with [`pg_role`](/docs/pgsql/param#pg_role) set to `offline`. ```yaml pg-test: hosts: 10.10.10.11: { pg_seq: 1, pg_role: primary } 10.10.10.12: { pg_seq: 2, pg_role: replica } 10.10.10.13: { pg_seq: 3, pg_role: offline } # <--- newly added vars: pg_cluster: pg-test ``` Offline instance works like common replica instances, but it is used as a backup server in `pg-test-replica` service. That is to say, offline and primary instances serve only when all `replica` instances are down. You can have ad hoc access control offline with [`pg_default_hba_rules`](/docs/pgsql/param#pg_default_hba_rules) and [`pg_hba_rules`](/docs/pgsql/param#pg_hba_rules). It will apply to the offline instance and any instances with [`pg_offline_query`](/docs/pgsql/param#pg_offline_query) flag. ------ ## Sync Standby Pigsty uses asynchronous stream replication by default, which may have a small replication lag (10KB / 10ms). A small window of data loss may occur when the primary fails (can be controlled with [`pg_rpo`](/docs/pgsql/param#pg_rpo)), but it is acceptable for most scenarios. But in some critical scenarios (e.g., financial transactions), data loss is totally unacceptable or read-your-write consistency is required. In this case, you can enable synchronous commit to ensure that. To enable sync standby mode, you can simply use `crit.yml` template in [`pg_conf`](/docs/pgsql/param#pg_conf) ```yaml pg-test: hosts: 10.10.10.11: { pg_seq: 1, pg_role: primary } 10.10.10.12: { pg_seq: 2, pg_role: replica } 10.10.10.13: { pg_seq: 3, pg_role: replica } vars: pg_cluster: pg-test pg_conf: crit.yml # <--- use crit template ``` To enable sync standby on existing clusters, [config](/docs/pgsql/admin#config-cluster) the cluster and enable `synchronous_mode`: ```bash $ pg edit-config pg-test # run on admin node with admin user +++ -synchronous_mode: false # <--- old value +synchronous_mode: true # <--- new value synchronous_mode_strict: false Apply these changes? [y/N]: y ``` If `synchronous_mode: true`, the [`synchronous_standby_names`](https://www.postgresql.org/docs/current/runtime-config-replication.html#synchronous_standby_names) parameter will be managed by patroni. It will choose a sync standby from all available replicas and write its name to the primary’s configuration file. ------ ## Quorum Commit When [sync standby](#sync-standby) is enabled, PostgreSQL will pick one replica as the standby instance, and all other replicas as candidates. Primary will wait until the standby instance flushes to disk before a commit is confirmed, and the standby instance will always have the latest data without any lags. However, you can achieve an even higher/lower consistency level with the quorum commit (trade-off with availability). For example, to have **all** 2 replicas to confirm a commit: ```yaml synchronous_mode: true # make sure synchronous mode is enabled synchronous_node_count: 2 # at least 2 nodes to confirm a commit ``` If you have more replicas and wish to have more sync standby, increase `synchronous_node_count` accordingly. Beware of adjust `synchronous_node_count` accordingly when you [append](/docs/pgsql/admin#append-replica) or [remove](/docs/pgsql/admin#remove-replica) replicas. The postgres `synchronous_standby_names` parameter will be managed by patroni: ```yaml synchronous_standby_names = '2 ("pg-test-3","pg-test-2")' ``` The classic quorum commit is to use **majority** of replicas to confirm a commit. ```yaml synchronous_mode: quorum # use quorum commit postgresql: parameters: # change the PostgreSQL parameter `synchronous_standby_names`, use the `ANY n ()` notion synchronous_standby_names: 'ANY 1 (*)' # you can specify a list of standby names, or use `*` to match them all ``` ------ ## Standby Cluster You can clone an existing cluster and create a [standby cluster](#standby-cluster), which can be used for migration, horizontal split, multi-az deployment, or disaster recovery. A standby cluster’s definition is just the same as any other normal cluster, except there’s a [`pg_upstream`](/docs/pgsql/param#pg_upstream) defined on the primary instance. For example, you have a `pg-test` cluster, to create a standby cluster `pg-test2`, the inventory may look like this: ```yaml # pg-test is the original cluster pg-test: hosts: 10.10.10.11: { pg_seq: 1, pg_role: primary } vars: { pg_cluster: pg-test } # pg-test2 is a standby cluster of pg-test. pg-test2: hosts: 10.10.10.12: { pg_seq: 1, pg_role: primary , pg_upstream: 10.10.10.11 } # <--- pg_upstream is defined here 10.10.10.13: { pg_seq: 2, pg_role: replica } vars: { pg_cluster: pg-test2 } ``` And `pg-test2-1`, the primary of `pg-test2` will be a replica of `pg-test` and serve as a **Standby Leader** in `pg-test2`. Just make sure that the [`pg_upstream`](/docs/pgsql/param#pg_upstream) parameter is configured on the primary of the backup cluster to pull backups from the original upstream automatically. ```bash bin/pgsql-add pg-test # Creating the original cluster bin/pgsql-add pg-test2 # Creating a Backup Cluster ``` ------ ## Delayed Cluster A delayed cluster is a special type of standby cluster, which is used to recover “drop-by-accident” ASAP. For example, if you wish to have a cluster `pg-testdelay` which has the same data as 1-day ago `pg-test` cluster: ```yaml # pg-test is the original cluster pg-test: hosts: 10.10.10.11: { pg_seq: 1, pg_role: primary } vars: { pg_cluster: pg-test } # pg-testdelay is a delayed cluster of pg-test. pg-testdelay: hosts: 10.10.10.12: { pg_seq: 1, pg_role: primary , pg_upstream: 10.10.10.11, pg_delay: 1d } 10.10.10.13: { pg_seq: 2, pg_role: replica } vars: { pg_cluster: pg-test2 } ``` You can also [configure](/docs/pgsql/admin#config-cluster) a replication delay on the existing [standby cluster](#standby-cluster). ```bash $ pg edit-config pg-testdelay standby_cluster: create_replica_methods: - basebackup host: 10.10.10.11 port: 5432 + recovery_min_apply_delay: 1h # <--- add delay here Apply these changes? [y/N]: y ``` When some tuples & tables are dropped by accident, you can advance this delayed cluster to a proper time point and select data from it. It takes more resources, but can be much faster and have less impact than [PITR](/docs/feat/pitr) ------ ## Citus Cluster Pigsty has native citus support. Check the [`conf/citus.yml`](https://github.com/pgsty/pigsty/blob/v3.7.0/conf/citus.yml) example. To define a citus cluster, you have to specify the following parameters: - [`pg_mode`](/docs/pgsql/param#pg_mode) has to be set to `citus` instead of default `pgsql` - [`pg_shard`](/docs/pgsql/param#pg_shard) & [`pg_group`](/docs/pgsql/param#pg_group) has to be defined on each sharding cluster - [`patroni_primary_db`](/docs/pgsql/param#pg_primary_db) has to be defined to specify the database to be managed - [`pg_dbsu_password`](/docs/pgsql/param#pg_dbsu_password) has to be set to a non-empty string plain password if you want to use the [`pg_dbsu`](/docs/pgsql/param#pg_dbsu) `postgres` rather than default [`pg_admin_username`](/docs/pgsql/param#pg_admin_username) to perform admin commands Besides, extra hba rules that allow ssl access from local & other data nodes are required. Which may looks like this ```yaml all: children: pg-citus0: # citus data node 0 hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } } vars: { pg_cluster: pg-citus0 , pg_group: 0 } pg-citus1: # citus data node 1 hosts: { 10.10.10.11: { pg_seq: 1, pg_role: primary } } vars: { pg_cluster: pg-citus1 , pg_group: 1 } pg-citus2: # citus data node 2 hosts: { 10.10.10.12: { pg_seq: 1, pg_role: primary } } vars: { pg_cluster: pg-citus2 , pg_group: 2 } pg-citus3: # citus data node 3, with an extra replica hosts: 10.10.10.13: { pg_seq: 1, pg_role: primary } 10.10.10.14: { pg_seq: 2, pg_role: replica } vars: { pg_cluster: pg-citus3 , pg_group: 3 } vars: # global parameters for all citus clusters pg_mode: citus # pgsql cluster mode: citus pg_shard: pg-citus # citus shard name: pg-citus patroni_citus_db: meta # citus distributed database name pg_dbsu_password: DBUser.Postgres # all dbsu password access for citus cluster pg_users: [ { name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [ dbrole_admin ] } ] pg_databases: [ { name: meta ,extensions: [ { name: citus }, { name: postgis }, { name: timescaledb } ] } ] pg_hba_rules: - { user: 'all' ,db: all ,addr: 127.0.0.1/32 ,auth: ssl ,title: 'all user ssl access from localhost' } - { user: 'all' ,db: all ,addr: intra ,auth: ssl ,title: 'all user ssl access from intranet' } ``` And you can create distributed table & reference table on the coordinator node. Any data node can be used as the coordinator node since citus 11.2. ```bash SELECT create_distributed_table('pgbench_accounts', 'aid'); SELECT truncate_local_data_after_distributing_table($$public.pgbench_accounts$$); SELECT create_reference_table('pgbench_branches') ; SELECT truncate_local_data_after_distributing_table($$public.pgbench_branches$$); SELECT create_reference_table('pgbench_history') ; SELECT truncate_local_data_after_distributing_table($$public.pgbench_history$$); SELECT create_reference_table('pgbench_tellers') ; SELECT truncate_local_data_after_distributing_table($$public.pgbench_tellers$$); ```