Preparing Your Google Cloud SQL PostgreSQL Database for Logical Replication to Linode Managed Database

Logical replication continuously synchronizes database tables, allowing you to prepare the destination database in advance. This approach minimizes downtime when you switch application traffic and retire the source database.

This guide explains how to prepare a Google Cloud SQL for PostgreSQL for logical replication to a Linode Managed Database. Follow this guide before returning to the Logical Replication to a Linode Managed PostgreSQL Database guide to create the subscription on Akamai Cloud.

Follow the steps in this guide to:

• Configure your Cloud SQL instance to support logical replication.
• Ensure secure network access from Linode.
• Create a dedicated replication user.
• Set up a publication for the tables you wish to replicate.

After completing these steps, return to Logical Replication to a Linode Managed PostgreSQL Database to configure the subscriber and finalize the setup.

Before You Begin

1. Follow the Logical Replication to a Linode Managed PostgreSQL Database guide up to the Prepare the Source Database for Logical Replication section to obtain the public IP address or CIDR range of your Linode Managed Database.

2. Ensure that you have administrative access to your GCP project, including permissions to modify Cloud SQL instance flags and authorized networks.

3. Install and authenticate the Google Cloud CLI (gcloud) on your local machine.

Placeholders and Examples

The following placeholders and example values are used in commands throughout this guide:

Replace these placeholders with your own connection details when running commands in your environment.

Additionally, the examples used in this guide assume the source database contains three tables (customers, products, and orders) that you want to replicate to a Linode Managed Database.

Configure Database Flags

Logical replication requires enabling specific PostgreSQL flags on your Cloud SQL for PostgreSQL instance. These flags can be configured using either the Google Cloud Console or the gcloud CLI.

gcloud sql instances patch GCP_INSTANCE_NAME \
  --database-flags=cloudsql.logical_decoding=on,max_replication_slots=10,max_wal_senders=10

The following message will be used for the patch API method.

{
  "name": "source-database",
  "settings": {
    "databaseFlags": [
      {"name": "cloudsql.logical_decoding", "value": "off"},
      {"name": "max_replication_slots", "value": "10"},
      {"name": "max_wal_senders", "value": "10"}
    ]
  }
}

WARNING: This patch modifies database flag values, which may require your
instance to be restarted. Check the list of supported flags -
https://cloud.google.com/sql/docs/postgres/flags - to see if your
instance will be restarted when this patch is submitted.

Do you want to continue (Y/n)?

Configure Network Access

Ensure that your Cloud SQL instance allows network access from the Linode Managed Database.

gcloud sql instances patch GCP_INSTANCE_NAME \
  --authorized-networks="DEST_IP/32"

gcloud sql instances patch GCP_INSTANCE_NAME \
  --authorized-networks="172.232.188.122/32,172.232.189.35/32"

With network access configured, your Linode Managed Database can reach the Cloud SQL instance during the subscription creation step in Logical Replication to a Linode Managed PostgreSQL Database.

Create a Replication User

While logical replication can technically be performed using the primary database user, it’s best practice to create a dedicated replication user. This user should have the REPLICATION privilege and SELECT access only to the tables being published.

Follow the steps below to create this dedicated user on your Cloud SQL instance.

1. Connect to your source PostgreSQL instance using the psql client. Replace SOURCE_HOST (e.g., 35.227.90.130), SOURCE_PORT (e.g., 5432), SOURCE_USER (e.g., postgres), and SOURCE_DB (e.g., postgres) with your own values. You can find the connection details under Connections > Summary in the Cloud SQL console.

   psql \
     -h SOURCE_HOST \
     -p SOURCE_PORT \
     -U SOURCE_USER \
     -d SOURCE_DB \
     "sslmode=require"

   When prompted, enter your SOURCE_PASSWORD (e.g., thisismysourcepassword).

2. Run the following commands from the source psql prompt. Replace REPL_USER (e.g., linode_replicator) and REPL_PASSWORD (e.g., thisismyreplicatorpassword) with your own values. For simplicity, this example assumes a public schema and three sample tables (customers, products, and orders). Replace the table names with your actual schema as needed.

   Source psql Prompt

   CREATE ROLE REPL_USER
          WITH REPLICATION
          LOGIN PASSWORD 'REPL_PASSWORD';
   GRANT SELECT ON customers, products, orders TO REPL_USER;

   CREATE ROLE
   GRANT

   Alternative: Grant on All Tables

   You can also grant privileges on all tables with the following command:

   Source psql Prompt

   GRANT SELECT ON ALL TABLES IN SCHEMA public TO REPL_USER;

   GRANT

The newly created user is referenced by the Linode Managed Database when creating the subscription in Logical Replication to a Linode Managed PostgreSQL Database.

Create a Publication

A publication defines which tables and changes (e.g., INSERT, UPDATE, and DELETE) should be streamed to the subscriber. At least one publication is required for logical replication, and the subscriber must have matching tables with compatible schemas for replication to succeed.

1. While still connected to your source database via the psql client, use the following command to create a publication. Replace PUBLICATION_NAME (e.g., my_publication) and the specific tables you want to replicate (e.g., customers, products, and orders):

   Source psql Prompt

   CREATE PUBLICATION PUBLICATION_NAME FOR TABLE customers, products, orders;

   CREATE PUBLICATION

   Alternative: Publish All Tables

   You can also create a publication for all tables in the database:

   Source psql Prompt

   CREATE PUBLICATION PUBLICATION_NAME FOR ALL TABLES;

2. Run the following command to view all existing publications:

   Source psql Prompt

   SELECT * FROM pg_publication_tables;

       pubname     | schemaname | tablename |                       attnames                        | rowfilter
   ----------------+------------+-----------+-------------------------------------------------------+-----------
    my_publication | public     | customers | {customer_id,name,email,created_at}                   |
    my_publication | public     | products  | {product_id,name,price,created_at}                    |
    my_publication | public     | orders    | {order_id,customer_id,product_id,quantity,created_at} |
   (3 rows)

3. Type \q and press Enter to exit the source psql shell.

Your Google Cloud source database is now ready for logical replication. Return to Logical Replication to a Linode Managed PostgreSQL Database to configure the Linode Managed Database and create the subscription.

More Information

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

• Set up logical replication and decoding
• Cloud SQL for PostgreSQL Documentation
• gcloud sql CLI Documentation