How to setup the logical replication in Google cloud SQL (PostgreSQL DB)

[akhilesh_arya2]

@All
I, need to set up a logical replication in the Cloud SQL DB (PostgreSQL) to my DWH which is also an GCP managed PgSQL Cloud SQL instance but the source database has SSL configurations and needs to pass the certificates for creating the Subscription, to connect to the publication server, but the Cloud SQL doesn't let me grant the cloudsqlSuperUser permissions to the Postgres User and thus I am encountering errors during Subscription creation as only Superuser can take the certificates for connection to the source DB.
please provide any suggestions to create a logical replication.

[ms4446]

Setting up logical replication with SSL connections in Google Cloud SQL for PostgreSQL requires careful consideration due to certain limitations and security implications. Here are some refined suggestions to help you create the replication:

1. Use the pglogical Extension:

   • The pglogical extension is a practical solution for setting up logical replication in PostgreSQL, especially within the constraints of Google Cloud SQL. It allows replication without superuser privileges and is compatible with SSL connections.
   • For detailed guidance on setting up pglogical in Cloud SQL, refer to the Google Cloud documentation: Configure Logical Replication in Cloud SQL.

2. Understand the Limitations of Superuser Roles:

   • In Google Cloud SQL, true superuser access is not provided for security reasons. This limitation means that some native PostgreSQL replication features requiring superuser privileges might not be feasible.
   • While users can have elevated privileges, they do not equate to full superuser capabilities found in self-managed PostgreSQL instances.

3. Consider Alternative Authentication Methods:

   • If SSL certificate management poses challenges, exploring other authentication methods like password authentication or IAM database authentication could be beneficial. However, it's essential to balance ease of setup with the security requirements of your data.
   • Keep in mind that SSL/TLS is generally recommended for secure connections, particularly when handling sensitive information.

4. Explore Third-Party Tools for Replication:

   • Various third-party tools offer advanced features and capabilities for logical replication with Google Cloud SQL. One notable options is Striim.
   • Be aware that these tools might add complexity to your setup and have their own dependencies and considerations.

5. Additional Resources:

   • For more information on replication options and best practices in Google Cloud SQL, the following resources can be helpful:
     • Google Cloud SQL Replication
     • pglogical GitHub Repository

6. Security and Performance Considerations:

   • It's crucial to prioritize security and performance needs when implementing your chosen solution. Understand the trade-offs of each method and ensure they align with your organizational policies and requirements.

While setting up logical replication in Google Cloud SQL with SSL connections has its challenges, there are several approaches you can take. Each has its advantages and limitations, so it's important to choose the one that best fits your technical and security needs.

[akhilesh_arya2]

Sir, I am facing this error.

ERROR: Non-superusers cannot use certificate in the connection setting.certificate is not allowed

ERROR: certificate is not allowed
SQL state: 2F003
Detail: Non-superusers cannot use certificate in the connection setting.

while creating subscription from the Cloud SQL PostgreSQL instance

[ms4446]

Here are some potential solutions to address this error:

1. Use the pglogical Extension:

   • The pglogical extension is a suitable alternative for setting up logical replication in Cloud SQL. It's designed to work within the constraints of Cloud SQL, supporting logical replication without requiring superuser privileges. This extension is compatible with SSL connections and can be a practical solution in your scenario.

2. Configure IAM Database Authentication:

   • Consider using Google Cloud's IAM database authentication as an alternative to SSL certificates. This method simplifies the setup by eliminating the need for certificate-based authentication, though it may not be suitable for all replication scenarios that specifically require SSL configurations.

3. Explore Alternative Replication Tools:

   • Investigate third-party tools like Striim, which offer more flexibility and capabilities for logical replication with Cloud SQL. These tools often provide solutions for managing certificates and authentication without requiring superuser privileges.

4. Contact Google Cloud Support:

   • If the above methods do not resolve the error, it's advisable to contact Google Cloud Support for expert guidance. They can assist with troubleshooting specific issues related to your Cloud SQL instance and replication configuration.