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
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.4" resources: memory: 10Gi cpu: 1 disks: - name: DataDisk size: 40Gi EOFCreate 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.4" 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_leveltological.$ kubectl patch dbclusters.al publisher -p '{"spec":{"primarySpec":{"parameters":{"wal_level":"logical"}}}}' --type=mergeFind 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"Sign in to the database pod for the publisher cluster.
NAME READY STATUS RESTARTS AGE al-2bce-publisher-0 3/3 Running 0 36m $ kubectl exec -ti al-2bce-publisher-0 -- /bin/bashCreate 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
logicalreplicafor 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
customerdatabase.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_leveltologicalin the subscriber database.$ kubectl patch dbclusters.al subscriber -p '{"spec":{"primarySpec":{"parameters":{"wal_level":"logical"}}}}' --type=mergeFind 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 21hTake 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.sqlApply the backup in the subscriber database.
postgres@al-3513-subscriber-0:/$ psql -h localhost -U postgres < /tmp/customer.schema-only.sqlOptional: 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
financein thecustomerdatabase 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 PUBLICATIONWhen 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)