PostgreSQL Identity

How to assign identity for postgres entities

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

Name Attribute Description Example
pg_cluster MUST, cluster level Cluster name pg-test
pg_role MUST, instance level Instance Role primary, replica
pg_seq MUST, instance level Instance number 1, 2, 3,...

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.

  vars: {pg_cluster: pg-test1, pg_shard: test, pg_sindex: 1}
  hosts: { {pg_seq: 1, pg_role: primary}}
  vars: {pg_cluster: pg-test1, pg_shard: test, pg_sindex: 2}
  hosts: { {pg_seq: 1, pg_role: primary}}
  vars: {pg_cluster: pg-test1, pg_shard: test, pg_sindex: 3}
  hosts: { {pg_seq: 1, pg_role: primary}}
  vars: {pg_cluster: pg-test1, pg_shard: test, pg_sindex: 4}
  hosts: { {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)