PostgreSQL Identity

How to assign identity for postgres entities

The Core Identity Parameters are information that must be provided when defining a PostgreSQL cluster.

The content of the identity parameter follows the entity naming pattern. Where pg_cluster, pg_role, and pg_seq belong to the core identity parameters, the minimum set of mandatory parameters required to define the database cluster and core identity parameters must be explicitly specified.

• pg_cluster identities the name of the cluster configured at the cluster level and serves as the top-level namespace for cluster resources.

• pg_role identities the role of the instance in the cluster, configured at the instance level, with optional values including:

  • primary: the only primary in the cluster, that provides writing services.
  • replica: the ordinary replica in the cluster, takes regular production read-only traffic.
  • offline: an offline replica in the cluster, takes ETL/SAGA/personal user/interactive/analytical queries.
  • standby: a standby replica in the cluster, with synchronous replication and no replication latency (reserved).
  • delayed: a delayed replica in the cluster, explicitly specifying replication delay, used to perform backtracking queries and data salvage (reserved).

• pg_seq is used to identify the instance within the cluster. Usually, an integer incrementing from 0 or 1 will not be changed once assigned.

• pg_shard is used to identify the upper-level shard cluster to which the cluster belongs, and only needs to be set if the cluster belongs to a horizontal sharding cluster.

• pg_sindex is used to identify the cluster’s slice cluster number and only needs to be set if the cluster belongs to a horizontal sharding cluster.

• pg_instance is the derived identity parameter that uniquely identifies a database instance, with the following composition rules

  {{ pg_cluster }}-{{ pg_seq }}. Since pg_seq is unique within the cluster, this identity is globally unique.

Sharding Cluster

pg_shard and pg_sindex define particular sharded clusters and are optional, currently reserved for Citus and Greenplum.

Suppose a user has a horizontal sharding sharded database cluster with the name test. This cluster consists of four separate clusters: pg-test1, pg-test2, pg-test3, and pg-test-4. The user can bind the identity of pg_shard: test to each database cluster and pg_sindex: 1|2|3|4 to each database cluster separately.

pg-test1:
  vars: {pg_cluster: pg-test1, pg_shard: test, pg_sindex: 1}
  hosts: {10.10.10.10: {pg_seq: 1, pg_role: primary}}
pg-test2:
  vars: {pg_cluster: pg-test1, pg_shard: test, pg_sindex: 2}
  hosts: {10.10.10.11: {pg_seq: 1, pg_role: primary}}
pg-test3:
  vars: {pg_cluster: pg-test1, pg_shard: test, pg_sindex: 3}
  hosts: {10.10.10.12: {pg_seq: 1, pg_role: primary}}
pg-test4:
  vars: {pg_cluster: pg-test1, pg_shard: test, pg_sindex: 4}
  hosts: {10.10.10.13: {pg_seq: 1, pg_role: primary}}

With this definition, you can easily observe the cross-sectional metrics comparison of four horizontal sharding clusters from the PGSQL Shard monitoring dashboard. The same functionality works for Citus and MatrixDB clusters as well.

Last modified 2022-06-04: fii en docs batch 2 (61bf601)