Configure logical replication in Kubernetes

In PostgreSQL, logical replication is a method for copying data changes from a publisher database to one or more subscribers, which can be databases or other applications. You can enable and configure logical replication on clusters that you create using the AlloyDB Omni Kubernetes Operator.

This document provides examples that show you how to create and configure a publisher cluster and a subscriber cluster. Before you read this document, you should be familiar with the AlloyDB Omni overview. You should also be aware of the limitations of PostgreSQL logical replication.

The code snippets on this page are examples that you can use as models, replacing the values with values for your AlloyDB Omni resources.

Create the clusters

Install Omni Operator on Kubernetes.

Create a publisher cluster.

$ cat << EOF | kubectl apply -f -
apiVersion: v1
kind: Secret
metadata:
  name: db-pw-publisher
type: Opaque
data:
  publisher: "b2RzcGFzc3dvcmQ=" # Password is odspassword
---
apiVersion: alloydbomni.dbadmin.goog/v1
kind: DBCluster
metadata:
  name: publisher
spec:
  primarySpec:
    adminUser:
      passwordRef:
        name: db-pw-publisher
    databaseVersion: "15.5.2"
    resources:
      memory: 10Gi
      cpu: 1
      disks:
      - name: DataDisk
        size: 40Gi
EOF

Create a subscriber cluster.

$ cat << EOF | kubectl apply -f -
apiVersion: v1
kind: Secret
metadata:
  name: db-pw-subscriber
type: Opaque
data:
  subscriber: "b2RzcGFzc3dvcmQ=" # Password is odspassword
---
apiVersion: alloydbomni.dbadmin.goog/v1
kind: DBCluster
metadata:
  name: subscriber
spec:
  primarySpec:
    adminUser:
      passwordRef:
        name: db-pw-subscriber
    databaseVersion: "15.5.2"
    resources:
      memory: 10Gi
      cpu: 1
      disks:
      - name: DataDisk
        size: 40Gi
EOF

Configure the publisher cluster

Configure the publisher cluster and create a table. Optionally, you can publish data as a test to make sure it is replicated to the subscriber.

Update parameter wal_level to logical.

$ kubectl patch dbclusters.al publisher  -p '{"spec":{"primarySpec":{"parameters":{"wal_level":"logical"}}}}' --type=merge

Find the pod that you need.

$ kubectl get pod -l "alloydbomni.internal.dbadmin.goog/dbcluster=publisher, alloydbomni.internal.dbadmin.goog/task-type=database, dbs.internal.dbadmin.goog/ha-role=Primary"

NAME                                          READY   STATUS    RESTARTS   AGE
al-2bce-publisher-0                           3/3     Running   0          36m
$ kubectl exec -ti al-2bce-publisher-0  -- /bin/bash

Create a database called customer.
```
CREATE DATABASE customer;
```

Optional: For test purposes, add a table to the database and insert some data. You can use this data to observe data replication from the publisher to the subscriber.

$ psql -h localhost -U postgres customer
customer=# CREATE TABLE COMPANY(
customer(#    ID INT PRIMARY KEY     NOT NULL,
customer(#    NAME           TEXT    NOT NULL,
customer(#    AGE            INT     NOT NULL,
customer(#    SALARY         REAL
customer(# );
CREATE TABLE
customer=# INSERT INTO COMPANY (ID,NAME,AGE,SALARY) VALUES
customer-# (1, 'Quinn', 25, 65000.00),
customer-# (2, 'Kim', 22, 72250.00),
customer-# (3, 'Bola', 31, 53000.00),
customer-# (4, 'Sasha', 33, 105000.00),
customer-# (5, 'Yuri', 27, 85000.00);
INSERT 0 5
customer=# \dt
          List of relations
Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
public | company | table | postgres
(1 row)

customer=# select * from company;
id | name  | age | salary
----+-------+-----+--------
  1 | Quinn  |  25 |  65000
  2 | Kim  |  22 |  72250
  3 | Bola   |  31 |  53000
  4 | Sasha |  33 | 105000
  5 | Yuri |  27 |  85000
(5 rows)

Create a user logicalreplica for replication and to grant permissions to.
```
CREATE USER logicalreplica WITH REPLICATION LOGIN PASSWORD '123';
```

Grant permissions. This example uses a public schema.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO logicalreplica;
GRANT USAGE ON SCHEMA public TO logicalreplica;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO logicalreplica;

Create a publication in the customer database.

CREATE PUBLICATION pub_customer;
ALTER PUBLICATION pub_customer ADD TABLE company;

Configure the subscriber cluster

Enable the subscriber cluster to receive data updates from the publisher cluster.

Set parameter wal_level to logical in the subscriber database.

$ kubectl patch dbclusters.al subscriber  -p '{"spec":{"primarySpec":{"parameters":{"wal_level":"logical"}}}}' --type=merge

Find the pod that you need.

$ kubectl get pod -l "alloydbomni.internal.dbadmin.goog/dbcluster=subscriber, alloydbomni.internal.dbadmin.goog/task-type=database, dbs.internal.dbadmin.goog/ha-role=Primary"

Log into the subscriber cluster database pod.

$ kubectl get pod
NAME                                          READY   STATUS    RESTARTS   AGE
al-2bce-publisher-0                           3/3     Running   0          20h

$ kubectl exec -ti al-3513-subscriber-0  -- /bin/bash
Defaulted container "database" out of: database, logrotate-agent, memoryagent, dbinit (init)
postgres@al-3513-subscriber-0:/$

Find the IP address of the publisher pod, like 10.116.14.190

$ kubectl get service
NAME                     TYPE           CLUSTER-IP      EXTERNAL-IP    PORT(S)          AGE
al-publisher-rw-ilb      ClusterIP      10.116.14.190   <none>         5432/TCP         21h

Take a schema backup from the publisher as an initial copy of the published data in the publisher database. Logical replication does not support DDL replication. A schema or table that you plan to replicate must exist in the destination (subscriber cluster) before logical replication starts.
```
postgres@al-3513-subscriber-0:/$ pg_dump -h 10.116.14.190 -U postgres --create --schema-only customer > /tmp/customer.schema-only.sql
```

Apply the backup in the subscriber database.

postgres@al-3513-subscriber-0:/$ psql -h localhost -U postgres < /tmp/customer.schema-only.sql

Optional: Verify that no data is in the table.

# There is no data in table company
customer=# select * from company;
id | name | age | salary
----+------+-----+--------
(0 rows)

Create a subscription for the database customer.

postgres@al-3513-subscriber-0:/$ psql -h localhost -U postgres customer
customer=# CREATE SUBSCRIPTION sub_customer CONNECTION 'host=10.116.14.190 port=5432 user=logicalreplica dbname=customer password=123' PUBLICATION pub_customer;

Optional: Verify replication on the subscriber cluster.

postgres@al-3513-subscriber-0:/$ psql -h localhost -U postgres customer
customer=# select * from public.company;
id | name  | age | salary
----+-------+-----+--------
  1 | Quinn |  25 |  65000
  2 | Kim   |  22 |  72250
  3 | Bola  |  31 |  53000
  4 | Sasha |  33 | 105000
  5 | Yuri  |  27 |  85000
(5 rows)

On the publisher cluster, add a row to the table.

# On the publisher database
$ kubectl exec -ti al-2bce-publisher-0  -- /bin/bash
Defaulted container "database" out of: database, logrotate-agent, memoryagent, dbinit (init)
postgres@al-2bce-publisher-0:/$ psql -h localhost -U postgres customer
customer=# insert into company(id, name, age, salary) values (6, 'Alex', 39, 100000);

On the subscriber cluster, verify that the row added to the table in the publisher cluster has been replicated to table in the subscriber cluster.

# On the subscriber database, data is synced.
postgres@al-3513-subscriber-0:/$ psql -h localhost -U postgres customer
customer=# select * from company;
id | name  | age | salary
----+-------+-----+--------
  1 | Quinn |  25 |  65000
  2 | Kim   |  22 |  72250
  3 | Bola  |  31 |  53000
  4 | Sasha |  33 | 105000
  5 | Yuri  |  27 |  85000
  6 | Alex  |  39 | 100000
(6 rows)

Manually create additional tables

Logical replication doesn't automatically synchronize DDL changes, unlike the replicate_ddl_command in pglogical. While the open-source tool pgl_ddl_deploy offers a solution, you can also execute DDL commands manually on the subscriber.

To illustrate this, create a new table called finance in the customer database on the publisher cluster.

# On the publisher database
$ kubectl exec -ti al-2bce-publisher-0  -- /bin/bash
Defaulted container "database" out of: database, logrotate-agent, memoryagent, dbinit (init)
postgres@al-2bce-publisher-0:/$ psql -h localhost -U postgres customer
customer=# create table finance (row text);
CREATE TABLE
customer=# insert into finance values ('critical data');
INSERT 0 1
customer=# ALTER PUBLICATION pub_customer ADD TABLE finance;
ALTER PUBLICATION

When a new table is added to the publisher cluster, you manually apply the DDL (table creation) in the subscriber, and then verify replication by running the following on the subscriber cluster.

postgres@al-3513-subscriber-0:/$ psql -h localhost -U postgres customer
customer=# create table finance (row text);
CREATE TABLE
customer=# ALTER SUBSCRIPTION sub_customer REFRESH PUBLICATION;
ALTER SUBSCRIPTION
customer=# select * from finance;
      row
---------------
critical data
(1 row)

What's next

Manage high availability (HA) in Kubernetes.