Cluster Model

PGSQL for production environments is organized in clusters, which clusters are logical entities consisting of a set of database instances associated by primary-replica. Each database cluster is an autonomous serving unit consisting of at least one database instance (primary).

ER Diagram

Let’s get started with ER diagram. There are four types of core entities in Pigsty’s PGSQL module:

• PGSQL Cluster: An autonomous PostgreSQL business unit, used as the top-level namespace for other entities.
• PGSQL Service: A named abstraction of cluster ability, route traffics, and expose postgres services with node ports.
• PGSQL Instance: A single postgres server which is a group of running processes & database files on a single node.
• PGSQL Node: An abstraction of hardware resources, which can be bare metal, virtual machine, or even k8s pods.

Naming Convention

• The cluster name should be a valid domain name, without any dot: [a-zA-Z0-9-]+
• Service name should be prefixed with cluster name, and suffixed with a single word: such as primary, replica, offline, delayed, join by -
• Instance name is prefixed with cluster name and suffixed with an integer, join by -, e.g., ${cluster}-${seq}.
• Node is identified by its IP address, and its hostname is usually the same as the instance name since they are 1:1 deployed.

Identity Parameter

Pigsty uses identity parameters to identify entities: PG_ID.

In addition to the node IP address, three parameters: pg_cluster, pg_role, and pg_seq are the minimum set of parameters necessary to define a postgres cluster. Take the sandbox testing cluster pg-test as an example:

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

The three members of the cluster are identified as follows.

There are:

• One Cluster: The cluster is named as pg-test.
• Two Roles: primary and replica.
• Three Instances: The cluster consists of three instances: pg-test-1, pg-test-2, pg-test-3.
• Three Nodes: The cluster is deployed on three nodes: 10.10.10.11, 10.10.10.12, and 10.10.10.13.
• Four services:
  • read-write service: pg-test-primary
  • read-only service: pg-test-replica
  • directly connected management service: pg-test-default
  • offline read service: pg-test-offline

And in the monitoring system (Prometheus/Grafana/Loki), corresponding metrics will be labeled with these identities:

pg_up{cls="pg-meta", ins="pg-meta-1", ip="10.10.10.10", job="pgsql"}
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"}