PostgreSQL units

Each high-availability /disaster-recovery implementation has primary and secondary (standby) sites.

A Charmed PostgreSQL cluster size can be easily scaled from 0 to 10 units. Contact us if you have a cluster with 10+ units.

It is recommended to use 3+ units cluster size in production (due to Raft consensus requirements). Those units type can be:

• Primary: unit which accepts all writes and guarantees no split-brain scenario .

• Sync Standby (synchronous copy) : designed for the fast automatic failover. Used for read-only queries and guaranties the latest transaction availability.

• Replica (asynchronous copy): designed for long-running and resource consuming queries without affecting Primary performance. Used for read-only queries without guaranties of the latest transaction availability.

All SQL transactions have to be confirmed by all Sync Standby unit(s) before Primary unit commit transaction to the client. Therefore, high-performance and high-availability is a trade-off between “sync standby” and “replica” unit count in the cluster.

All Charmed PostgreSQL 16 units are configured as Sync Standby members by default. It provides better guarantees for the data survival when two of three units gone simultaneously. Users can re-configure the necessary synchronous units count using Juju config option ‘synchronous-node-count ’.

Primary

The PostgreSQL primary server unit may or may not be the same as the juju leader unit .

The juju leader unit is the represented in juju status by an asterisk (*) next to its name:

Unit           Workload  Agent  Machine  Public address  Ports     Message
postgresql/0*  active    idle   0        10.189.210.53   5432/tcp  Primary <<<<<<<<<<<<<<
postgresql/1   active    idle   1        10.189.210.166  5432/tcp
postgresql/2   active    idle   2        10.189.210.188  5432/tcp

However, this information can be outdated as it is being updated only on the update-status Juju event.

The most up-to-date Primary unit number can be received using Juju action get-primary:

> juju run postgresql/leader get-primary
...
primary: postgresql/0

We highly recommend configuring the update-status hook to run frequently.

In addition to reporting the primary, secondaries, and other statuses, the status hook performs self-healing in the case of a network cut.

To change the frequency of the update-status hook, run

juju model-config update-status-hook-interval=<time(s/m/h)>

This hook executes a read query to PostgreSQL. On a production level server, this should be configured to occur at a frequency that doesn’t overload the server with read requests. Similarly, the hook should not be configured at too quick of a frequency, as this can delay other hooks from running.

It is also possible to retrieve this information using patronictl and the Patroni REST API. See Troubleshooting for more details.

Standby / replica

This information can be retrieved with patronictl and the Patroni REST API. See Troubleshooting for more details.

Example:

> ... patronictl ... list
+ Cluster: postgresql (7499430436963402504) ---+-----------+----+-----------+
| Member       | Host           | Role         | State     | TL | Lag in MB |
+--------------+----------------+--------------+-----------+----+-----------+
| postgresql-0 | 10.189.210.53  | Leader       | running   |  1 |           |
| postgresql-1 | 10.189.210.166 | Sync Standby | streaming |  1 |         0 |
| postgresql-2 | 10.189.210.188 | Replica      | streaming |  1 |         0 |
+--------------+----------------+--------------+-----------+----+-----------+

• postgresql-0 is a PostgreSQL Primary unit (Patroni Leader) which accepts all writes

• postgresql-1 is a PostgreSQL/Patroni Sync Standby unit which can be promoted as new primary using manual switchover (safe).

• postgresql-2 is a PostgreSQL/Patroni Replica unit which can NOT be directly promoted as a new Primary using manual switchover. The automatic promotion Replica=>Sync Standby is necessary to guarantee the latest SQL transactions availability on this unit to allow further promotion as a new Primary. Otherwise the manual failover can be performed to Replica unit accepting the risks of losing the last transactions(s) which lagged behind the Primary.

Replica lag distance

At the moment, it is only possible to retrieve this information using patronictl and the Patroni REST API. See Troubleshooting for more details.

Example:

$ ... patronictl ... list
+ Cluster: postgresql (7499430436963402504) ---+-----------+----+-----------+
| Member       | Host           | Role         | State     | TL | Lag in MB |
+--------------+----------------+--------------+-----------+----+-----------+
| postgresql-0 | 10.189.210.53  | Leader       | running   |  1 |           |
| ...
| postgresql-2 | 10.189.210.188 | Replica      | streaming |  1 |        42 |  <<<<<
+--------------+----------------+--------------+-----------+----+-----------+

$ curl ... x.x.x.x:8008/cluster | jq
  "members": [
    {
      "name": "postgresql-0",
      "role": "leader",
      "state": "running",
      ...
    },
...
    {
      "name": "postgresql-2",
      "role": "replica",
      "state": "streaming",
      ...
      "lag": 42 <<<<<<<<<<<< Lag in MB
    }