This document describes how to create logical replication slots in AlloyDB Omni. Logical replication in AlloyDB Omni is used to continuously stream specific data changes from the source database (publisher) to other databases or applications (subscribers). The streamed changes can be individual row updates, insertions, or deletions. Subscribers connect to the publisher through a unique replication slot that ensures a persistent connection. A persistent connection maintains the data streaming state, so if a disruption occurs, streaming resumes from where it left off.
For more information about logical replication in PostgreSQL, see Logical Replication.
Configure publisher and subscriber cluster
Before you create the replication slots, you must create the publisher and subscriber cluster with logical replication enabled. The wal_level
parameter must be set logical
in the respective manifests.
To create publisher database cluster with logical replication enabled, create and apply a DBCluster
manifest.
The following is an example publisher manifest:
apiVersion: v1
kind: Secret
metadata:
name: db-pw-mydbc
type: Opaque
data:
mydbc: "Q2hhbmdlTWUxMjM="
---
apiVersion: alloydbomni.dbadmin.goog/v1
kind: DBCluster
metadata:
name: mydbc
spec:
databaseVersion: "15.7.0"
spec:
availability:
numberOfStandbys: 1
primarySpec:
parameters:
wal_level: "logical"
adminUser:
passwordRef:
name: db-pw-mydbc
resources:
cpu: 1
memory: 8Gi
disks:
- name: DataDisk
size: 10Gi
To create subscriber database cluster with logical replication enabled, create and apply a DBCluster
manifest.
The following is an example subscriber manifest:
apiVersion: v1
kind: Secret
metadata:
name: db-pw-subscriber
type: Opaque
data:
subscriber: "Q2hhbmdlTWUxMjM=" #ChangeMe123
---
apiVersion: alloydbomni.dbadmin.goog/v1
kind: DBCluster
metadata:
name: subscriber
spec:
databaseVersion: "15.7.0"
primarySpec:
parameters:
wal_level: "logical"
adminUser:
passwordRef:
name: db-pw-subscriber
resources:
memory: 10Gi
cpu: 1
disks:
- name: DataDisk
size: 40Gi
—
Create a replication slot
After you create the publisher and subscriber clusters, you can create a logical replication slot using the Replication
resource in the publisher cluster. Each Replication
resource is associated with a corresponding database cluster resource. A database cluster can have multiple logical replication resources associated with it.
To configure a replication slot in your publisher cluster, apply the following manifest:
apiVersion: v1
kind: Secret
metadata:
name: USER_PASSWORD_SECRET_NAME
namespace: USER_PASSWORD_SECRET_NAMESPACE
type: Opaque
data:
rep-user-pw: BASE64_ENCODED_PASSWORD
---
apiVersion: alloydbomni.dbadmin.goog/v1
kind: Replication
metadata:
name: REPLICATION_NAME
namespace: NAMESPACE
spec:
dbcluster:
name: DB_CLUSTER_NAME
upstream:
logicalReplication:
pluginName: DECODER_PLUGIN
databaseName: DATABASE_NAME
applicationName: APPLICATION_NAME
replicationSlotName: REPLICATION_SLOT_NAME
synchronous: "REPLICATION_MODE"
username: APPLICATION_USER
password:
name: USER_PASSWORD_SECRET_NAME
namespace: USER_PASSWORD_SECRET_NAMESPACE
Replace the following:
- REPLICATION_NAME: a name for this
Replication
resource—for example,replication-1
. - NAMESPACE: the Kubernetes namespace for this
Replication
resource. It must match the namespace of the database cluster. - DB_CLUSTER_NAME: the name of your database cluster, which you assigned when you created it.
- DECODER_PLUGIN: set to the decoding plugin, such as
pgoutput
, that you want to use for logical replication. For more information about various decoding plugins, see Output plugins. - DATABASE_NAME: set to the name of the database whose changes you want to stream to the replication slot. Ensure that the database is already created in the publisher cluster.
- APPLICATION_NAME (Optional): set to the application name that will connect to the replication slot. This field is required when the streaming mode is set to synchronous.
- REPLICATION_MODE (Optional): set to
false
for asynchronous replication. If you want to enable synchronous replication, but at the expense of speed, then set this value astrue
. The default value isfalse
, if not set explicitly. - REPLICATION_SLOT_NAME: the name of the replication slot that will be created, and used by the subscriber.
- REPLICATION_USER (Optional): the name of the user that connects to the replication slot. If you set the replication user, then setting the secret name, namespace, and password is required.
- USER_PASSWORD_SECRET_NAME (Optional): the application user's Kubernetes Secret name. Required, if application user is set.
- USER_PASSWORD_SECRET_NAMESPACE (Optional): the namespace where the Kubernetes secret for the application user is located. Required, if application user is set.
- BASE64_ENCODED_PASSWORD (Optional): the base64-encoded password of the application user. Required, if application user is set.
Grant permissions to the replication user
To grant replication and publication permissions to the replication user in the publisher cluster, follow these steps:
Connect to the primary pod on publisher cluster using
psql
:psql -h IP_ADDRESS -U USERNAME -d DATABASE_NAME
Replace the following:
- IP_ADDRESS: the IP address of the primary pod of the publisher cluster.
- USERNAME: the postgres user of the database.
- DATABASE_NAME: the database to which the subscriber wants to subscribe.
Grant the permissions:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO REPLICATION_USER; GRANT USAGE ON SCHEMA public TO REPLICATION_USER; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO REPLICATION_USER;
Create publication and subscription
Create publication
To create a publication in the publisher cluster, follow these steps:
Connect to the primary pod on publisher cluster using
psql
:psql -h IP_ADDRESS -U USERNAME -d DATABASE_NAME
Replace the following:
- IP_ADDRESS: the IP address of the primary pod of the publisher cluster.
- USERNAME: the postgres user of the database.
- DATABASE_NAME: the database to which the subscriber wants to subscribe.
Create publication by running the following command:
CREATE PUBLICATION PUBLICATION_NAME; ALTER PUBLICATION PUBLICATION_NAME ADD TABLE TABLE_NAME;
Replace the following:
- PUBLICATION_NAME: the publication name that subscriber will use to subscribe.
- TABLE_NAME: the table to which the subscriber wants to subscribe.
Create subscription
To create a subscription in the subscriber cluster, follow these steps:
Connect to the primary pod on subscriber cluster using
psql
:psql -h IP_ADDRESS -U USERNAME -d DATABASE_NAME
Replace the following:
- IP_ADDRESS: the IP address of the primary pod of the subscriber cluster.
- USERNAME: the postgres user of the database.
- DATABASE_NAME: the database to which the subscriber wants to subscribe.
Create a subscription by running the following command:
CREATE SUBSCRIPTION SUBSCRIPTION_NAME CONNECTION 'host=IP_ADDRESS port=PORT user=REPLICATION_USER dbname=DATABASE_NAME password=PASSWORD sslmode=require' PUBLICATION PUBLICATION_NAME WITH (slot_name=REPLICATION_SLOT_NAME, create_slot=false); alter subscription SUBSCRIPTION_NAME refresh publication ;
Replace the following:
- SUBSCRIPTION_NAME: the name of the subscription.
- IP_ADDRESS: the IP address of the primary pod of the publisher cluster.
Any updates to the table in the publisher cluster are replicated to the table in the subscriber cluster.
View replication slot status
To view the status of the replication slots, run the following command:
kubectl get replication.alloydbomni.dbadmin.goog REPLICATION_NAME -n NAMESPACE -oyaml
The status
field along with other details is included in the response:
apiVersion: alloydbomni.dbadmin.goog/v1
kind: Replication
metadata:
name: REPLICATION_NAME
namespace: NAMESPACE
...
...
status:
conditions:
- lastTransitionTime: "2025-01-25T06:49:25Z"
message: Ready for replication
reason: Ready
status: "True"
type: Ready
- lastTransitionTime: "2025-01-25T06:49:25Z"
message: Replication slot is not being used
reason: Unhealthy
status: "False"
type: Healthy
observedGeneration: 2
upstream:
host: DATABASE_ENDPOINT
password:
name: USER_PASSWORD_SECRET_NAME
namespace: USER_PASSWORD_SECRET_NAMESPACE
port: DATABASE_PORT
replicationSlotName: REPLICATION_SLOT_NAME
username: APPLICATION_USER
The DATABASE_ENDPOINT
shows the IP address that you use to connect to the database. The status TRUE
in the READY
column indicates that the slot is ready to stream. When the application connects to the replication slot, the status in the HEALTHY
column changes to TRUE
.
Limitations
Updates to the replication slot configuration aren't supported. To update the configuration, drop the replication slot, and re-create it with updated configuration.
To drop the replication slot, run the following command: kubectl delete replication.alloydbomni.dbadmin.goog REPLICATION_NAME -n NAMESPACE
You can only configure the logical replication slot on the publisher database. The subscriber configurations aren't supported.
If the database cluster referenced by the replication object is configured for high availability, then the logical replication slot is re-created on the promoted standby after a failover. After the replication slot is recreated, the position of the stream in the slot is no longer available, and any applications subscribing to the stream must reconnect and replay the stream.