Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more

Kubernetes

Multi-Cluster Postgres Federation on Kubernetes with Postgres 14 Using GitOp

Avatar for Jonathan S. Katz

Jonathan S. Katz

11 min read

One of the reasons why I like deploying Postgres on Kubernetes with PGO, the open source Postgres Operator, is that it makes it easier to set up distributed database systems. One example is using Postgres for edge computing, and you can build an edge computing architecture with both Postgres and Debezium on Kubernetes.

You can also do some fun things with logical replication and many read/write nodes. I posted an example of doing this last year about how you could set up an active-active federated Postgres cluster on Kubernetes. With the release of version 5 of open source PGO, I thought it'd be neat to update the example to show how you can create an active-active federated Postgres cluster using GitOps.

Even better, we'll see how to do this with Postgres 14 and so we can take advantage of some of its logical replication improvements.

Set Up the Postgres Clusters

Similar to the previous example, let's create three Postgres clusters called hippo-east, hippo-central, and hippo-west. We can do so with the following manifests:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo-east
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:centos8-14.0-0
  postgresVersion: 14
  instances:
    - dataVolumeClaimSpec:
        accessModes:
          - 'ReadWriteOnce'
        resources:
          requests:
            storage: 1Gi
  users:
    - name: postgres
  monitoring:
    pgmonitor:
      exporter:
        image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres-exporter:ubi8-5.0.3-0
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:centos8-2.35-0
      repos:
        - name: repo1
          volume:
            volumeClaimSpec:
              accessModes:
                - 'ReadWriteOnce'
              resources:
                requests:
                  storage: 1Gi
---
apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo-central
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:centos8-14.0-0
  postgresVersion: 14
  instances:
    - dataVolumeClaimSpec:
        accessModes:
          - 'ReadWriteOnce'
        resources:
          requests:
            storage: 1Gi
  users:
    - name: postgres
  monitoring:
    pgmonitor:
      exporter:
        image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres-exporter:ubi8-5.0.3-0
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:centos8-2.35-0
      repos:
        - name: repo1
          volume:
            volumeClaimSpec:
              accessModes:
                - 'ReadWriteOnce'
              resources:
                requests:
                  storage: 1Gi
---
apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo-west
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:centos8-14.0-0
  postgresVersion: 14
  instances:
    - dataVolumeClaimSpec:
        accessModes:
          - 'ReadWriteOnce'
        resources:
          requests:
            storage: 1Gi
  users:
    - name: postgres
  monitoring:
    pgmonitor:
      exporter:
        image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres-exporter:ubi8-5.0.3-0
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:centos8-2.35-0
      repos:
        - name: repo1
          volume:
            volumeClaimSpec:
              accessModes:
                - 'ReadWriteOnce'
              resources:
                requests:
                  storage: 1Gi

Note that in each of the manifests, we are telling PGO to create a Kubernetes Secret postgres superuser. This allows us to login as postgres. This is for convenience: in a production cluster you would lock this down a bit further. We also enable monitoring to ensure that certain Kubernetes introspection functions are available via pgnodemx.

For convenience, let's set the password for the postgres Postgres user to be the same in each of these clusters. PGO can set custom passwords so long as the verifier attribute in the user Secret is set (setting password is for convenience).

Let's set the password to datalake. For convenience, here is an example password/verifier encoded in base64 that can be used to authenticate using password datalake:

PASSWORD='ZGF0YWxha2U='
VERIFIER='U0NSQU0tU0hBLTI1NiQ0MDk2OkZBblNrVXJMM2pINkJwMTdQNUZUdVE9PSRCZWNMVTBZWHpCVXduVGszYjNnaElaNy96UzJYdkQ4d1g1MEh6NUpMMHE0PTpweHVFdG96WVRacEFjNndJTlY1M3NDcjRBZnhrOExMZm9yNU12a1lwMjFzPQ=='

You can then set the postgres user to use password datalake by executing the following commands:

kubectl patch secret -n postgres-operator hippo-east-pguser-postgres -p \\
   "{\\"data\\":{\\"password\\":\\"${PASSWORD}\\",\\"verifier\\":\\"${VERIFIER}\\"}}"
kubectl patch secret -n postgres-operator hippo-central-pguser-postgres -p \\
  "{\\"data\\":{\\"password\\":\\"${PASSWORD}\\",\\"verifier\\":\\"${VERIFIER}\\"}}"
kubectl patch secret -n postgres-operator hippo-west-pguser-postgres -p \\
   "{\\"data\\":{\\"password\\":\\"${PASSWORD}\\",\\"verifier\\":\\"${VERIFIER}\\"}}"

You can test the password was set by connecting to one or all of the Postgres clusters. Using the port-forward technique, you can test this by doing the following:

PG_CLUSTER_NAME="hippo-east"
PG_CLUSTER_PRIMARY_POD=$(kubectl get pod -n postgres-operator -o name \\
  -l postgres-operator.crunchydata.com/cluster=$PG_CLUSTER_NAME,postgres-operator.crunchydata.com/role=master)
kubectl -n postgres-operator port-forward "${PG_CLUSTER_PRIMARY_POD}" 5432:5432

In a separate terminal, run the following:

PGPASSWORD="datalake" psql -h localhost -U postgres -c 'SELECT version();'

which should yield something similar to:

version
-----------------------------
PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit

We now need to provide a way for each Postgres cluster to identify itself. This can be accomplished using some of the helper functions available in pgnodemx, which comes with PGO. For PGO v5, the helper function to accomplish this looks like this:

CREATE EXTENSION IF NOT EXISTS pgnodemx;

CREATE OR REPLACE FUNCTION get_node_name()
RETURNS text
AS $$
  SELECT val FROM kdapi_setof_kv('labels') WHERE key\='postgres-operator.crunchydata.com/cluster';
$$ LANGUAGE SQL SECURITY DEFINER IMMUTABLE;

If you have trouble adding it, try dropping and recreating the pgnodemx extension.

You will need to run the following in the postgres database on hippo-east, hippo-central, and hippo-west. You can verify the function works by executing hippo.get_node_name(), for example on hippo-east:

SELECT hippo.get_node_name();

which should yield:

     get_node_name --------------- hippo-east(1 row)

(Just for fun, if you're on Crunchy Bridge you can use the equivalent function:

CREATE OR REPLACE FUNCTION get_node_name()
RETURNS text
AS $$
  SELECT regexp_replace(pg_read_file('/etc/hostname'), '\\n', '');
$$ LANGUAGE SQL SECURITY DEFINER IMMUTABLE;

).

With the basis of the Postgres clusters set up, let's now create the schema that lets us build out an active-active federated cluster!

Creating the Schema

We will use a similar schema that we created in the previous example, though to add a Postgres 14 twist, we'll include a multirange data type (and a Postgres 13 twist of the gen_random_uuid() being available without the pgcrypto extension):

CREATE TABLE hippos (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    node_name text,
    value datemultirange,
    created_at timestamptz
) PARTITION BY LIST (node_name);

CREATE TABLE hippo_default PARTITION of hippos (PRIMARY KEY (id)) DEFAULT;
CREATE TABLE hippo_east PARTITION OF hippos (PRIMARY KEY (id)) FOR VALUES IN ('hippo-east');
CREATE TABLE hippo_central PARTITION OF hippos (PRIMARY KEY (id)) FOR VALUES IN ('hippo-central');
CREATE TABLE hippo_west PARTITION OF hippos (PRIMARY KEY (id)) FOR VALUES IN ('hippo-west');

CREATE OR REPLACE FUNCTION add_node_name()
RETURNS trigger AS $$
BEGIN
  UPDATE hippos
    SET node_name \= get_node_name()
    WHERE node_name IS NULL;

  RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER add_node_name
AFTER INSERT ON hippos
  FOR EACH STATEMENT
  EXECUTE FUNCTION add_node_name();

What do we have here? Each partition represents the data that is stored in each Postgres cluster. This means that hippo-east Postgres cluster will only write data to the hippo-east partition, the hippo-central cluster will only write to hippo-central partition, and the hippo-west cluster will only write to the hippo-west partition.

You can actually bootstrap this data with PGO from a SQL file, but for the purposes of this example I wanted to spell it out.

By setting up our table in this way, we will ensure that data in each active instance is only written to its designated area locally, and then can be distributed to the other nodes. Let's look at how we can distribute the data.

Distributing Writes

We can distribute the data to each instance using logical replication. Unlike physical replication, Postgres logical replication allows for subsets of data to be sent between Postgres instances, and also allows data to be sent between writable nodes.

To use logical replication, you need to set up publications on the source servers, and subscriptions on the destinations.

Let's set up the publications. First on hippo-east, create the following publication:

CREATE PUBLICATION pub_hippo_east FOR TABLE hippo_east;

Next, set up the publication on hippo-central:

CREATE PUBLICATION pub_hippo_central FOR TABLE hippo_central;

Finally, set up the publication on hippo-west:

CREATE PUBLICATION pub_hippo_west FOR TABLE hippo_west;

We can now set up the subscriptions between all of the instances. We're going to do something special with the connections: because PGO v5 comes with TLS enabled by default, we're going to use stronger TLS protection for our connections.

First, let's create the subscriptions on hippo-east:

CREATE SUBSCRIPTION sub_hippo_east_hippo_central
  CONNECTION 'dbname=postgres host=hippo-central-primary.postgres-operator.svc user=postgres password=datalake sslrootcert=/pgconf/tls/ca.crt sslmode=verify-full'
  PUBLICATION pub_hippo_central;

CREATE SUBSCRIPTION sub_hippo_east_hippo_west
  CONNECTION 'dbname=postgres host=hippo-west-primary.postgres-operator.svc user=postgres password=datalake sslrootcert=/pgconf/tls/ca.crt sslmode=verify-full'
  PUBLICATION pub_hippo_west;

Note the sslrootcert=/pgconf/tls/ca.crt sslmode=verify-full section. We need the TLS CA to perform a full verification of the source instances identity (verify-full). The TLS CA is mounted to /pgconf/tls/ca.crt, so we can reference it from there. We can get the host name both from the user Secret or from the list of Kubernetes standard FQDNs.

Let's set up the remaining subscriptions. First, set them up on hippo-central:

CREATE SUBSCRIPTION sub_hippo_central_hippo_east
  CONNECTION 'dbname=postgres host=hippo-east-primary.postgres-operator.svc user=postgres password=datalake sslrootcert=/pgconf/tls/ca.crt sslmode=verify-full'
  PUBLICATION pub_hippo_east;

CREATE SUBSCRIPTION sub_hippo_central_hippo_west
  CONNECTION 'dbname=postgres host=hippo-west-primary.postgres-operator.svc user=postgres password=datalake sslrootcert=/pgconf/tls/ca.crt sslmode=verify-full'
  PUBLICATION pub_hippo_west;

And finally on hippo-west:

CREATE SUBSCRIPTION sub_hippo_west_hippo_east
  CONNECTION 'dbname=postgres host=hippo-east-primary.postgres-operator.svc user=postgres password=datalake sslrootcert=/pgconf/tls/ca.crt sslmode=verify-full'
  PUBLICATION pub_hippo_east;

CREATE SUBSCRIPTION sub_hippo_west_hippo_central
  CONNECTION 'dbname=postgres host=hippo-central-primary.postgres-operator.svc user=postgres password=datalake sslrootcert=/pgconf/tls/ca.crt sslmode=verify-full'
  PUBLICATION pub_hippo_central;

With the partitions, publications, and subscriptions in place, now we can start the fun part: adding in the data!

Multiranges on Multi-Nodes!

The schema in place uses Postgres 14's new multirange data type. In essence, a multirange is an ordered list of noncontiguous ranges. For example:

SELECT datemultirange(
  daterange(CURRENT_DATE, CURRENT_DATE + 2),
  daterange(CURRENT_DATE + 5, CURRENT_DATE + 8)
);

may yield something like:

datemultirange
---------------------------------------------------
{[2021-10-12,2021-10-14),[2021-10-17,2021-10-20)}(1 row)

Postgres will detect if your ranges overlap, and will concatenate them within the multirange. For example:

SELECT datemultirange(
  daterange(CURRENT_DATE, CURRENT_DATE + 2),
  daterange(CURRENT_DATE + 1, CURRENT_DATE + 8)
);

may yield something like:

datemultirange
---------------------------
{[2021-10-12,2021-10-20)}(1 row)

As a final bonus, you can access the individual ranges using the unnest function:

SELECT unnest(datemultirange(
  daterange(CURRENT_DATE, CURRENT_DATE + 2),
  daterange(CURRENT_DATE + 5, CURRENT_DATE + 8)
));

may yield something like:

unnest
-------------------------
[2021-10-12,2021-10-14) [2021-10-17,2021-10-20)(2 rows)

Anyway, I digress. Let's now insert some actual data into our instances. First, log into hippo-east and run a query like this:

INSERT INTO hippos (value, created_at)
SELECT
  datemultirange(
    daterange(CURRENT_DATE + n, CURRENT_DATE + n + (100 \* random())::int),
    daterange(CURRENT_DATE + n + 100, CURRENT_DATE + n + 100 + (200 \* random())::int)
  ),
  clock_timestamp()
FROM generate_series(1,25) n;

This creates a bunch of randomly generated datemultirange types. Notice the use of clock_timestamp() -- this returns the actual clock time, vs. CURRENT_TIMESTAMP which returns the time of the start of the transaction.

Go ahead and execute this SQL on hippo-central and hippo-west.

Log back into hippo-east. Let's see if the data propagated:

TABLE hippos;

You should see all 75 results! For a more consolidated viewing:

SELECT node_name, count(\*)
FROM hippos
GROUP BY node_name;

should yield:

node_name   | count
---------------+-------
hippo-east    |    25
hippo-central |    25
hippo-west    |    25
(3 rows)

Run the query on hippo-central and hippo-west and see how your writes have propagated throughout your active-active cluster!

Scaling Up: Adding A New Instance

Let's say we want to add an additional node to our cluster. No problem. Let's call this node hippo-south. First, create hippo-south from a Kubernetes custom resource:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo-south
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:centos8-14.0-0
  postgresVersion: 14
  instances:
    - dataVolumeClaimSpec:
        accessModes:
          - 'ReadWriteOnce'
        resources:
          requests:
            storage: 1Gi
  users:
    - name: postgres
  monitoring:
    pgmonitor:
      exporter:
        image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres-exporter:ubi8-5.0.3-0
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:centos8-2.35-0
      repos:
        - name: repo1
          volume:
            volumeClaimSpec:
              accessModes:
                - 'ReadWriteOnce'
              resources:
                requests:
                  storage: 1Gi

Similar to before, set the password of the postgres account to datalake:

PASSWORD='ZGF0YWxha2U='
VERIFIER='U0NSQU0tU0hBLTI1NiQ0MDk2OkZBblNrVXJMM2pINkJwMTdQNUZUdVE9PSRCZWNMVTBZWHpCVXduVGszYjNnaElaNy96UzJYdkQ4d1g1MEh6NUpMMHE0PTpweHVFdG96WVRacEFjNndJTlY1M3NDcjRBZnhrOExMZm9yNU12a1lwMjFzPQ=='
kubectl patch secret -n postgres-operator hippo-south-pguser-postgres -p \\
   "{\\"data\\":{\\"password\\":\\"${PASSWORD}\\",\\"verifier\\":\\"${VERIFIER}\\"}}"

Once hippo-south is initialized, log in and add the partitioning function:

CREATE EXTENSION IF NOT EXISTS pgnodemx;

CREATE OR REPLACE FUNCTION get_node_name()
RETURNS text
AS $$
  SELECT val FROM kdapi_setof_kv('labels') WHERE key\='postgres-operator.crunchydata.com/cluster';
$$ LANGUAGE SQL SECURITY DEFINER IMMUTABLE;

Create the hippos data structure on the hippo-south instance. Note the additional of the hippo-south partition:

CREATE TABLE hippos (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    node_name text,
    value datemultirange,
    created_at timestamptz
) PARTITION BY LIST (node_name);

CREATE TABLE hippo_default PARTITION of hippos (PRIMARY KEY (id)) DEFAULT;
CREATE TABLE hippo_east PARTITION OF hippos (PRIMARY KEY (id)) FOR VALUES IN ('hippo-east');
CREATE TABLE hippo_central PARTITION OF hippos (PRIMARY KEY (id)) FOR VALUES IN ('hippo-central');
CREATE TABLE hippo_west PARTITION OF hippos (PRIMARY KEY (id)) FOR VALUES IN ('hippo-west');
CREATE TABLE hippo_south PARTITION OF hippos (PRIMARY KEY (id)) FOR VALUES IN ('hippo-south');

CREATE OR REPLACE FUNCTION add_node_name()
RETURNS trigger AS $$
BEGIN
  UPDATE hippos
    SET node_name \= get_node_name()
    WHERE node_name IS NULL;

  RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER add_node_name
AFTER INSERT ON hippos
  FOR EACH STATEMENT
  EXECUTE FUNCTION add_node_name();

Now, set up hippo-south so it can be added to the cluster. This involves creating a publication on hippo-south and subscribing it to the current nodes:

CREATE PUBLICATION pub_hippo_south FOR TABLE hippo_south;

CREATE SUBSCRIPTION sub_hippo_south_hippo_east
  CONNECTION 'dbname=postgres host=hippo-east-primary.postgres-operator.svc user=postgres password=datalake sslrootcert=/pgconf/tls/ca.crt sslmode=verify-full'
  PUBLICATION pub_hippo_east;

CREATE SUBSCRIPTION sub_hippo_south_hippo_central
  CONNECTION 'dbname=postgres host=hippo-central-primary.postgres-operator.svc user=postgres password=datalake sslrootcert=/pgconf/tls/ca.crt sslmode=verify-full'
  PUBLICATION pub_hippo_central;

CREATE SUBSCRIPTION sub_hippo_south_hippo_west
  CONNECTION 'dbname=postgres host=hippo-west-primary.postgres-operator.svc user=postgres password=datalake sslrootcert=/pgconf/tls/ca.crt sslmode=verify-full'
  PUBLICATION pub_hippo_west;

We can test that hippo-south is in the cluster. Try running our verification query:

SELECT node_name, count(\*)
FROM hippos
GROUP BY node_name;

This should yield:

node_name   | count
---------------+-------
hippo-east    |    25
hippo-central |    25
hippo-west    |    25
(3 rows)

Excellent. Let's insert some data into hippo-south:

INSERT INTO hippos (value, created_at)
SELECT
  datemultirange(
    daterange(CURRENT_DATE + n, CURRENT_DATE + n + (100 \* random())::int),
    daterange(CURRENT_DATE + n + 100, CURRENT_DATE + n + 100 + (200 \* random())::int)
  ),
  clock_timestamp()
FROM generate_series(1,25) n;

Now we have one final step: making the other instances aware of hippo-south. Log into hippo-east and execute:

CREATE TABLE hippo_south PARTITION OF hippos (PRIMARY KEY (id)) FOR VALUES IN ('hippo-south');

CREATE SUBSCRIPTION sub_hippo_east_hippo_south
  CONNECTION 'dbname=postgres host=hippo-south-primary.postgres-operator.svc user=postgres password=datalake sslrootcert=/pgconf/tls/ca.crt sslmode=verify-full'
  PUBLICATION pub_hippo_south;

On hippo-central:

CREATE TABLE hippo_south PARTITION OF hippos (PRIMARY KEY (id)) FOR VALUES IN ('hippo-south');

CREATE SUBSCRIPTION sub_hippo_central_hippo_south
  CONNECTION 'dbname=postgres host=hippo-south-primary.postgres-operator.svc user=postgres password=datalake sslrootcert=/pgconf/tls/ca.crt sslmode=verify-full'
  PUBLICATION pub_hippo_south;

And on hippo-west:

CREATE TABLE hippo_south PARTITION OF hippos (PRIMARY KEY (id)) FOR VALUES IN ('hippo-south');

CREATE SUBSCRIPTION sub_hippo_west_hippo_south
  CONNECTION 'dbname=postgres host=hippo-south-primary.postgres-operator.svc user=postgres password=datalake sslrootcert=/pgconf/tls/ca.crt sslmode=verify-full'
  PUBLICATION pub_hippo_south;

You can verify that hippo-east, hippo-central, and hippo-west are receiving writes from hippo-south by executing:

SELECT node_name, count(\*)
FROM hippos
GROUP BY node_name;

which should yield:

node_name   | count
---------------+-------
hippo-east    |    25
hippo-south   |    25
hippo-central |    25
hippo-west    |    25
(4 rows)

Next Steps

The above guide should get you on your way to building out Postgres clusters that can accept writes across multiple nodes and share them with each other. By using PGO, the open source Postgres Operator, you can also set up high availability, backups, and monitoring to make your cluster production ready.

Further more, you can bootstrap a lot of the schema work by telling PGO to set up a database according to a SQL file you provide. This makes setting up such clusters reproducible across environments, which is the GitOps way!

Kubernetes and PGO make it easier to set up distributed Postgres clusters and allow for designing and scaling different topologies for managing your data, from active-active federation to edge computing.

Related Articles