Capturing Changes in Real-Time from Google Cloud SQL PostgreSQL
Use DataCater for streaming change events from Google Cloud SQL PostgreSQL to your data sinks.
Use DataCater for streaming change events from Google Cloud SQL PostgreSQL to your data sinks.
Log-based change data capture is the modern way for extracting changes from a database system, like PostgreSQL. It allows you to extract data change events (INSERTS, UPDATES, and DELETEs) in real-time, without having to change the schema of the database table or putting load on the database system.
In this article, we show you how to use DataCater for streaming data change events from a Google Cloud SQL PostgreSQL instance to downstream data sinks. For our tests, we used the recent PostgreSQL version 14. However, all PostgreSQL versions supported by Google Cloud that are equal to or greater than 10 should work.
We assume that you have an existing instance up and running. If not, please create a new Google Cloud SQL PostgreSQL instance and fill a database with some test data.
By default, Google Cloud SQL instances are not reachable by external networks. Let’s give DataCater access to our Cloud SQL instance.
Go to the instance in your cloud console and click on Connections.
Add a new network and provide DataCater Cloud as the name and
DataCater’s public IP address 20.79.84.135 for the network:
Save the network by pressing Done. Persist the changed configuration by pressing Save.
Note that, if you are using DataCater’s self-managed installation, you might prefer running the Cloud SQL Auth Proxy inside of your Kubernetes cluster and accessing Cloud SQL via the proxy.
In the next step, we need to enable logical replication.
Go to Overview and click Edit configuration.
Add a new database flag. Choose cloudsql.logical_decoding
as the flag and set its value to On:
Click Done. Click Save. Click Save and Restart.
Once your Cloud SQL instance has been restarted, logical replication is enabled and ready for usage.
We recommend creating a separate PostgreSQL user for DataCater.
Go to Users. Click Add user account and add a new user for DataCater:
Provide a username and password and click Add.
The user needs one additional PostgreSQL role for making use of logical replication.
Open a connection to the PostgreSQL instance using psql and execute the following query (please replace [user] with the user you created for DataCater):
ALTER ROLE [user] WITH REPLICATION;
That’s it. Now it is time to connect DataCater to your Cloud SQL instance.
Create a new data source of type PostgreSQL in DataCater and provide the connection credentials from your Cloud SQL instance. Once you created a pipeline for that data source, DataCater starts streaming data change events from your Cloud SQL PostgreSQL instance by reading its replication log.
Keen to try it out? Sign up for our free trial and experience real-time data streaming in a matter of minutes.
Try change data capture with your Cloud SQL PostgreSQL instance, for free
Get started with our plug & play CDC connector for PostgreSQL. Stream change events to your downstream applications in real-time.