This page describes how to configure change data capture (CDC) tostream data from an AlloyDB for PostgreSQL database to asupported destination,such as BigQuery or Cloud Storage.

Set up connectivity between Datastream and AlloyDB

To enable Datastream to connect to the AlloyDB instance, you need to set up a TCP proxy in the consumer project. TheTCP proxy image is publicly available to all customers.

1. Launch the proxy container:

   1. Activate Cloud Shell in the consumer project.
   2. Run the following script to create the TCP proxy and disable bridgenetworking to avoid connectivity issues:

   gcloudcomputeinstancescreate-with-container\--zone=REGION_IDVM_NAME\--container-imagegcr.io/dms-images/tcp-proxy\--tags=dms-tcp-proxy\--container-env=SOURCE_CONFIG=ALLOYDB_IP:ALLOYDB_PORT\--can-ip-forward\--network=SOURCE_AND_DEST_VPC\--machine-type=VM_TIER\--metadata=startup-script='#! /bin/bash  mkdir -p /etc/docker  cat <<EOF > /etc/docker/daemon.json  {"bridge":"none"}  EOF  systemctl restart docker'

   Replace the following:

   • REGION_ID: The region in which you want to create the TCP proxy.
   • VM_NAME: The name of the virtual machine.
   • ALLOYDB_IP: The IP address of the AlloyDB for PostgreSQL instance.
   • ALLOYDB_PORT: The port number of the AlloyDB for PostgreSQL instance.
   • SOURCE_AND_DEST_VPC: The VPC network to which the source and destinationare connected.
   • VM_TIER: The type of your virtual machine.

   An example command with updated parameters:

   gcloudcomputeinstancescreate-with-container\--zone=us-central1-cds-tcp-proxy\--container-imagegcr.io/dms-images/tcp-proxy\--tags=ds-tcp-proxy\--container-env=SOURCE_CONFIG=10.16.0.5:5432\--can-ip-forward\--network=default\--machine-type=e2-micro--metadata=startup-script='#! /bin/bash  mkdir -p /etc/docker  cat <<EOF > /etc/docker/daemon.json  {"bridge":"none"}  EOF  systemctl restart docker'

   You may receive a prompt to authorize Cloud Shell. ClickAUTHORIZE.

2. Run the script to print the IP address of the TCP Proxy with the following inputs:

   gcloudcomputeinstancesdescribeVM_NAME\--format="yaml(networkInterfaces[].networkIP)"\--zone=REGION_ID

   Replace the following:

   • VM_NAME: The name of your virtual machine.
   • REGION_ID: The region in which you created the TCP proxy.
   Use this IP address in the Datastreamconnection profile configuration.

3. (Optional) Create a firewall rule to limit ingress traffic to the TCP proxy:

   gcloudcomputefirewall-rulescreateFIREWALL_RULE_NAME\--direction=INGRESS\--priority=1000\--target-tags=dms-tcp-proxy\--network=SOURCE_VPC\--action=ALLOW\--rules=tcp:ALLOYDB_PORT\--source-ranges=IP_RANGE

   Replace the following:

   • FIREWALL_RULE_NAME: The name of your firewall rule.
   • SOURCE_VPC: The VPC network to which your source is connected.
   • ALLOYDB_PORT: The port number of the AlloyDB for PostgreSQL instance.
   • IP_RANGE: The IP address range to which you want to limit ingresstraffic.

   An example command to allow ingress traffic from all IP addresses:

   gcloudcomputefirewall-rulescreateds-proxy1\--direction=INGRESS\--priority=1000\--target-tags=ds-tcp-proxy\--network=default\--action=ALLOW\--rules=tcp:5432

Configure AlloyDB for replication

1. Enable logical decoding for the AlloyDB primary instance. In Google Cloud, setthe value for thealloydb.logical_decoding flag toON. For information aboutupdating the database flags, seeConfigure an instance's database flags.

   Note: Updating thealloydb.logical_decoding flag restarts the instance.

2. Connect to the AlloyDB instance using the TCP proxy IP address by running thefollowing command from any VM on the same VPC:

   psql-hPROXY_IP\-UDB_USER\-dDB_NAME

   Replace the following:

   • PROXY_IP: The IP address of the TCP proxy.
   • DB_USER: The username for the AlloyDB database.
   • DB_NAME: The AlloyDB database name.

3. Run the following command to grant replication privileges to your database user:

   ALTER USERDB_USER WITH REPLICATION;

4. Create a publication. We recommend that you create a publication only for thetables that you want to replicate. This allows Datastream to read-onlythe relevant data, and lowers the load on the database and Datastream:

   CREATE PUBLICATIONPUBLICATION_NAMEFOR TABLESCHEMA1.TABLE1,SCHEMA2.TABLE2;

   Replace the following:

   • PUBLICATION_NAME: The name of your publication. You'llneed to provide this name when you create a stream in the Datastreamstream creation wizard.
   • SCHEMA: The name of the schema that contains the table.
   • TABLE: The name of the table that you want to replicate.

   You can also create a publication for all tables in your database. Note thatthis approach increases the load on both the source database and Datastream:

   CREATE PUBLICATIONPUBLICATION_NAME FOR ALL TABLES;

   For PostgreSQL 15 and later, you can create a publication for all tablesin a schema. This approach lets you replicate changes for tables in thespecified list of schemas, including tables that you create in the future:

   CREATE PUBLICATIONPUBLICATION_NAMEFOR TABLES IN SCHEMASCHEMA1,SCHEMA2;

5. Create a replication slot by executing the following command.

   SELECT PG_CREATE_LOGICAL_REPLICATION_SLOT('REPLICATION_SLOT_NAME', 'pgoutput');

   Replace the following:

   • REPLICATION_SLOT_NAME: The name of your replication slot.You'll need to provide this name when you create a stream in the Datastreamstream creation wizard.

Create a Datastream user

1. To create a Datastream user, enter the following PostgreSQL command:

   CREATE USERUSER_NAME WITH REPLICATION LOGIN PASSWORD 'USER_PASSWORD';

   Replace the following:

   • USER_NAME: The name of the Datastream user that you wantto create.
   • USER_PASSWORD: The login password for the Datastream userthat you want to create.

2. Grant the following privileges to the user you created:

   GRANT SELECT ON ALL TABLES IN SCHEMASCHEMA_NAME TOUSER_NAME;GRANT USAGE ON SCHEMASCHEMA_NAME TOUSER_NAME;ALTER DEFAULT PRIVILEGES IN SCHEMASCHEMA_NAME GRANT SELECT ON TABLES TOUSER_NAME;

   Replace the following:

   • SCHEMA_NAME: The name of the schema to which you want to grantthe privileges.
   • USER_NAME: The user to whom you want to grant the privileges.
   The first command might display warnings, however it is safe to ignore them.

What's next

• Learn more about how Datastream works withPostgreSQL sources.