To replicate data from an AlloyDB for PostgreSQL database to a [supported
destination](https://docs.cloud.google.com/datastream/docs/destinations) such as BigQuery or
Cloud Storage, you must first prepare your source instance for
Datastream. Setting up a TCP proxy, enabling logical decoding, creating
a publication and replication slot, and configuring a Datastream
user let you capture ongoing database changes.

## 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. The [TCP proxy image](http://gcr.io/dms-images/tcp-proxy) 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 a Container-Optimized OS VM that runs the TCP proxy container on the host network:

   ```bash
   gcloud compute instances create VM_NAME \
     --zone=REGION_ID \
     --image-family=cos-stable \
     --image-project=cos-cloud \
     --tags=dms-tcp-proxy \
     --can-ip-forward \
     --network=SOURCE_AND_DEST_VPC \
     --machine-type=VM_TIER \
     --metadata=google-logging-enabled=true,startup-script='#! /bin/bash
     iptables -w -A INPUT -p tcp --dport 5432 -j ACCEPT
     docker rm -f dms-tcp-proxy 2>/dev/null
     docker run -d --name=dms-tcp-proxy --restart=always --network=host \
       -e SOURCE_CONFIG=ALLOYDB_IP:ALLOYDB_PORT \
       gcr.io/dms-images/tcp-proxy'
   ```

   Replace the following:
   - <var translate="no">REGION_ID</var>: The region in which you want to create the TCP proxy.
   - <var translate="no">VM_NAME</var>: The name of the virtual machine.
   - <var translate="no">ALLOYDB_IP</var>: The IP address of the AlloyDB for PostgreSQL instance.
   - <var translate="no">ALLOYDB_PORT</var>: The port number of the AlloyDB for PostgreSQL instance.
   - <var translate="no">SOURCE_AND_DEST_VPC</var>: The VPC network to which the source and destination are connected.
   - <var translate="no">VM_TIER</var>: The type of your virtual machine.

   An example command with updated parameters:

   ```bash
   gcloud compute instances create ds-tcp-proxy \
     --zone=us-central1-c \
     --image-family=cos-stable \
     --image-project=cos-cloud \
     --tags=ds-tcp-proxy \
     --can-ip-forward \
     --network=default \
     --machine-type=e2-micro \
     --metadata=google-logging-enabled=true,startup-script='#! /bin/bash
     iptables -w -A INPUT -p tcp --dport 5432 -j ACCEPT
     docker rm -f dms-tcp-proxy 2>/dev/null
     docker run -d --name=dms-tcp-proxy --restart=always --network=host \
       -e SOURCE_CONFIG=10.16.0.5:5432 \
       gcr.io/dms-images/tcp-proxy'
   ```

   > [!NOTE]
   > You may receive a prompt to authorize Cloud Shell. Click **AUTHORIZE**.

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

   ```bash
   gcloud compute instances describe VM_NAME \
     --format="yaml(networkInterfaces[].networkIP)" \
     --zone=REGION_ID
   ```

   Replace the following:
   - <var translate="no">VM_NAME</var>: The name of your virtual machine.
   - <var translate="no">REGION_ID</var>: The region in which you created the TCP proxy.

   > [!IMPORTANT]
   > Use this IP address in the Datastream [connection profile](https://docs.cloud.google.com/datastream/docs/create-connection-profiles.md.txt#cp4postgresdb) configuration.

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

   ```bash
   gcloud compute firewall-rules create FIREWALL_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:
   - <var translate="no">FIREWALL_RULE_NAME</var>: The name of your firewall rule.
   - <var translate="no">SOURCE_VPC</var>: The VPC network to which your source is connected.
   - <var translate="no">ALLOYDB_PORT</var>: The port number of the AlloyDB for PostgreSQL instance.
   - <var translate="no">IP_RANGE</var>: The IP address range to which you want to limit ingress traffic.

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

   ```bash
   gcloud compute firewall-rules create ds-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, set
   the value for the `alloydb.logical_decoding` flag to **ON** . For information about
   updating the database flags, see [Configure an instance's database flags](https://docs.cloud.google.com/alloydb/docs/instance-configure-database-flags.md.txt).

   > [!NOTE]
   > **Note:** Updating the `alloydb.logical_decoding` flag restarts the instance.

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

   ```bash
     psql -h PROXY_IP \
       -U DB_USER \
       -d DB_NAME
   ```

   Replace the following:
   - <var translate="no">PROXY_IP</var>: The IP address of the TCP proxy.
   - <var translate="no">DB_USER</var>: The username for the AlloyDB database.
   - <var translate="no">DB_NAME</var>: The AlloyDB database name.
3. Run the following command to grant replication privileges to your database user:

   ```
   ALTER USER DB_USER WITH REPLICATION;
   ```
4. Create a publication. We recommend that you create a publication only for the
   tables that you want to replicate. This allows Datastream to read-only
   the relevant data, and lowers the load on the database and Datastream:

   ```
   CREATE PUBLICATION PUBLICATION_NAME
   FOR TABLE SCHEMA1.TABLE1, SCHEMA2.TABLE2;
   ```

   Replace the following:
   - <var translate="no">PUBLICATION_NAME</var>: The name of your publication. You'll need to provide this name when you create a stream in the Datastream stream creation wizard.
   - <var translate="no">SCHEMA</var>: The name of the schema that contains the table.
   - <var translate="no">TABLE</var>: The name of the table that you want to replicate.

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

   ```
   CREATE PUBLICATION PUBLICATION_NAME FOR ALL TABLES;
   ```

   > [!NOTE]
   > For PostgreSQL 15 and later, you can create a publication for all tables in a schema. This approach lets you replicate changes for tables in the specified list of schemas, including tables that you create in the future:
   >
   > ```
   > CREATE PUBLICATION PUBLICATION_NAME
   > FOR TABLES IN SCHEMA SCHEMA1, SCHEMA2;
   > ```

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

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

   Replace the following:
   - <var translate="no">REPLICATION_SLOT_NAME</var>: The name of your replication slot. You'll need to provide this name when you create a stream in the Datastream stream creation wizard.

## Create a Datastream user

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

   ```
   CREATE USER USER_NAME WITH REPLICATION LOGIN PASSWORD 'USER_PASSWORD';
   ```

   Replace the following:
   - <var translate="no">USER_NAME</var>: The name of the Datastream user that you want to create.
   - <var translate="no">USER_PASSWORD</var>: The login password for the Datastream user that you want to create.
2. Grant the following privileges to the user you created:

   ```
   GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA_NAME TO USER_NAME;
   GRANT USAGE ON SCHEMA SCHEMA_NAME TO USER_NAME;
   ALTER DEFAULT PRIVILEGES IN SCHEMA SCHEMA_NAME GRANT SELECT ON TABLES TO USER_NAME;
   ```

   Replace the following:
   - <var translate="no">SCHEMA_NAME</var>: The name of the schema to which you want to grant the privileges.
   - <var translate="no">USER_NAME</var>: The user to whom you want to grant the privileges.

   > [!NOTE]
   > The first command might display warnings, however it is safe to ignore them.

## What's next

- Learn more about how Datastream works with [PostgreSQL sources](https://docs.cloud.google.com/datastream/docs/sources-postgresql.md.txt).