Datastream from PostreSQL to BigQuery won't start

Google Cloud Serverless Applications
1

Hi Team,

I’ve setup the Datastream to my Cloud SQL - managed PostgreSQL database as the source profile. All regional IPs that Stream creation wizard has showed, were added to Authorized networks in the Cloud SQL networking tab. Both the Stream and Cloud SQL operate in the same datacenter (europe-west-1.) Additionally, I validated that

  1. Connectivity tests in my Cloud SQL instance pass when using one of the regional IPs shown in Stream creation wizard.

  2. Connectivity test Stream creation wizard also passes.

  3. Validate stream thing is also 100% green.

Despite the above, my stream won’t start. The error displayed is “An unexpected database connection error occurred while trying to connect to the data source. Make sure that the connection profile configuration is correct, or contact Google Support.”

Open to any suggestions on how to troubleshoot this case further.

Log snippet:

“original_message”: “timeout expired\n”,
“context”: “CONFIGURATION”,
“read_method”: “configuration”,
“event_code”: “FETCH_FAILED”,
“message”: “Fetch failed: An unexpected database connection error occurred while trying to connect to the data source. Make sure that the connection profile configuration is correct, or contact support..”
},
“resource”: {
“type”: “datastream.googleapis.com/Stream”,

2

After investigating this message, I was able to find a similar issue, and apparently the issue is due to a missing permission to your user (see “Enter a Username and Password to authenticate to your source database” step).

As such, I suggest assigning the SUPERUSER permission to your user. You can assign this permission by following the documentation* in your Cloud PostgreSQL instance.

I look forward to hearing from you. And if you have any questions or concerns, please feel free to reply with a longer snippet of your log.

*Google does not create or endorse the documentation above.

3

Thanks for the prompt reply. My user was created as a “superuser”. The command to do so was like this:

CREATE USER [MY_USER] WITH REPLICATION IN ROLE cloudsqlsuperuser
LOGIN PASSWORD ‘[MY_PASSWORD]’;

Quoting Cloud SQL documentation:

Cloud SQL for PostgreSQL is a managed service, so it restricts access to certain system procedures and tables that require advanced privileges. In Cloud SQL, customers cannot create or have access to users with superuser attributes. You cannot create database users that have superuser privileges. However, you can create database users with the cloudsqlsuperuser role, which has some superuser privileges, including:

  • Creating extensions that require superuser privileges
  • Creating event triggers
  • Creating replication users
  • Creating replication publications and subscriptions
  • Full access to the pg_largeobject catalog table

Best regards!

4

@obui
If you are using private connectivity then , can you plz check these things

RC1_1-1665757888107.png
RC1_1-1665757888107.png1806×742 207 KB

https://cloud.google.com/datastream/docs/diagnose-issues

Also try once connecting using ssh method , if these things don’t work out.

5

Thank you for your patience so far.

Could you please try with the query below in accordance with the documentation and share your results?

ALTER USER <the user> WITH REPLICATION;

I will be looking forward to your reply!

6

Thank you so much @comaro this actually resolved the problem, and my stream is now running.