Google Cloud PostgreSQL Setup Guide

Introduction

Google Cloud SQL is a fully managed database service that makes it easy to set up, maintain, manage, and administer your relational databases on the Google Cloud Platform (GCP).
Cloud SQL has the ability to expose WAL publications/replications in their instances. This feature can be enabled by configuring logical decoding in Cloud SQL instances.
This article provides step-by-step instructions on how to configure logical decoding in Cloud SQL.

Prerequisites

• Make sure you're running PostgreSQL 10 or higher.
• Server and Database permissions are required to change server configuration (depending on the PostgreSQL cloud/on-prem provider).

Step 1: Open Cloud SQL

To configure logical decoding in Cloud SQL, the first step is to log in to your GCP project and go to the Cloud SQL page.

Step 2: Choose the Cloud SQL Instances

Once you are on the Cloud SQL page, choose the Cloud SQL instances that you want to connect to CDC. Open the instance and click Edit.

Step 3: Add Flag for Logical Decoding

Navigate to the Flags section, and select Add Flag. Set up the cloudsql.logical_decoding flag to enable seamless logical WAL level in the background. Also, make sure to set the wal_sender_timeout to a value of 0 to establish a dependable connector when routing our CDC to a Google Cloud SQL Postgres server.

Step 4: Restart the Cloud SQL Instance

After setting up the logical decoding flag, restart the Cloud SQL instance.

Step 5: Create SQL Replication User

To enable replication slots, the REPLICATION role must be granted permission to the Rivery user. If the server does not already have a REPLICATION role, create one with the following SQL command:
Please note that the <replication_role_name> must be substituted with a role name of your choice.

CREATE ROLE <replication_role_name> REPLICATION LOGIN;

Once the role has been created, execute the following SQL command to create a new login for Rivery's Log-Based extraction if it doesn't already exist:

CREATE USER rivery WITH <replication_role_name>;

In case the Rivery user already exists, use the following SQL command to assign the new role to the user:

ALTER USER rivery WITH <replication_role_name>;

Connect to Rivery

Follow the PostgreSQL Connection documentation to connect to the Rivery console.