Configure subscriber cluster for logical replication

Select a documentation version:

This document provides examples that show you how to manually create and configure a subscriber cluster. A subscriber cluster is a database cluster that receives data replicated from a publisher cluster.

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

Before you begin

Create and configure the subscriber cluster

  1. Create a subscriber cluster.

    $ cat << EOF | kubectl apply -f -
apiVersion: v1
kind: Secret
metadata:
  name: db-pw-DB_CLUSTER_NAME
type: Opaque
data:
  DB_CLUSTER_NAME: "ENCODED_PASSWORD" # Password is odspassword
---
apiVersion: alloydbomni.dbadmin.goog/v1
kind: DBCluster
metadata:
  name: subscriber
spec:
  databaseVersion: "16.8.0"
  primarySpec:
    adminUser:
      passwordRef:
        name: db-pw-DB_CLUSTER_NAME
    resources:
      memory: MEMORY_SIZE
      cpu: CPU_COUNT
      disks:
      - name: DataDisk
        size: DISK_SIZE
EOF

    Replace the following:

    • DB_CLUSTER_NAME: the name of this database cluster—for example, subscriber-cluster.

    • ENCODED_PASSWORD: the database login password for the default postgres user role, encoded as a base64 string—for example, Q2hhbmdlTWUxMjM= for ChangeMe123.

    • CPU_COUNT: the number of CPUs available to each database instance in this database cluster.

    • MEMORY_SIZE: the amount of memory per database instance of this database cluster. We recommend setting this to 8 gigabytes per CPU. For example, if you set cpu to 2 earlier in this manifest, then we recommend setting memory to 16Gi.

    • DISK_SIZE: the disk size per database instance—for example, 10Gi.

  2. Find the pod that you need.

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

  3. 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 SUBSCRIBER_POD_NAME  -- /bin/bash
Defaulted container "database" out of: database, logrotate-agent, memoryagent, dbinit (init)
postgres@al-3513-subscriber-0:/$

    Replace SUBSCRIBER_POD_NAME with the name of the subscriber pod.

  4. Find the IP address of the load balancer on publisher DBcluster, 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

  5. 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 PUBLISHER_IP_ADDRESS -U postgres --create --schema-only customer > /tmp/customer.schema-only.sql

    Replace PUBLISHER_IP_ADDRESS with the IP address of the load balancer on publisher DBcluster.

  6. Apply the backup in the subscriber database.

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

  7. 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)

  8. Create a subscription for the database. Ensure that the publication is already created on the publisher DBCluster.

    postgres@al-3513-subscriber-0:/$ CREATE SUBSCRIPTION sub_customer CONNECTION 'host=PUBLISHER_IP_ADDRESS port=5432 user=REPLICATION_USER dbname=DATABASE_NAME password=PUBLISHER_CLUSTER_PASSWORD sslmode=require' PUBLICATION PUBLICATION_NAME WITH (slot_name='REPLICATION_SLOT_NAME');

    Replace the following:

    • REPLICATION_USER: the name of the user that connects to the replication slot.
    • DATABASE_NAME: set to the name of the database whose changes you want to stream from the replication slot.
    • PUBLISHER_CLUSTER_PASSWORD: the database login password for the postgres user of publisher DBCluster.
    • PUBLICATION_NAME: the publication name that subscriber subscribes to.
    • REPLICATION_SLOT_NAME: the name of the replication slot created on publisher DBCluster.

  9. Optional: Verify replication on the subscriber cluster.

    postgres@al-3513-subscriber-0:/$ psql -h localhost -U postgres DATABASE_NAME
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)

  10. 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 DATABASE_NAME
customer=# insert into TABLE_NAME (id, name, age, salary) values (6, 'Alex', 39, 100000);

    Replace the TABLE_NAME with the name of the table in the publisher DBCluster that the subscriber is subscribed to.

  11. 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 DATABASE_NAME
customer=# select * from TABLE_NAME;
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.

  1. 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

  2. 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