Connection between PostgreSQL and Cloud Data Fuson

[andres_artm]

I have created a Data Fusion instance and a PostgreSQL database in Google Cloud. When I make the connection from Data Fusion to the database in order to make a pipeline I get the following error:

Failed to create connection to database via connection string: jdbc:postgresql://IP/postgres and arguments: {user=USER}. Error: SocketTimeoutException: connect timed out. Make sure you specify the correct connection properties.

I have tried to modify permissions in IAM but I am not able to solve the problem. In case it was a PostgreSQL problem, I also tried to do it with SQL and I get the same problem.

Could someone please help me?

[ms4446]

The "SocketTimeoutException: connect timed out" error while connecting Data Fusion to your PostgreSQL database in Google Cloud suggests a network connectivity issue. Here are some steps to troubleshoot:

1. Verify Connection Details:

• JDBC URL: Can you please confirm the JDBC URL you're using in Data Fusion? It should look like this:

  jdbc:postgresql://<YOUR_POSTGRESQL_PUBLIC_IP_ADDRESS>/postgres
  

  Replace <YOUR_POSTGRESQL_PUBLIC_IP_ADDRESS> with the actual public IP address from your Cloud SQL console.

• Database Name: Is "postgres" the correct database name you created in Cloud SQL? If you're unsure, you can double-check this in the Cloud SQL console.

• Credentials: Are you absolutely certain the USER username and password you're entering in Data Fusion match the credentials you set up in Cloud SQL?

2. Network Configuration:

• Firewall Rules:

  • Cloud SQL: In the Cloud SQL console, under your PostgreSQL instance, go to the "Connections" tab. Is the IP address of your Data Fusion instance (or the IP range it might be in) listed under "Authorized networks"? If not, add it.
  • VPC Firewall: If both Data Fusion and Cloud SQL are in the same VPC, check the VPC firewall rules in the Google Cloud console. Make sure there are no rules blocking traffic on port 5432 between your Data Fusion instance and the Cloud SQL instance.

• Private IP: If you're trying to use the private IP of your Cloud SQL instance, confirm that you're using that private IP in the JDBC URL, not the public IP.

3. PostgreSQL Instance Settings:

• Public IP: Please confirm whether you've enabled the "Public IP" option in your Cloud SQL PostgreSQL instance settings. You can find this in the "Connections" tab.

• Connectivity: In the Cloud SQL console, check the "Overview" tab for your PostgreSQL instance. Does it show any errors or warnings about connectivity?

4. Data Fusion Instance:

• Network: If your Data Fusion instance is also in a VPC, make sure it has proper routes configured so it can reach the IP address (private or public) of your Cloud SQL instance.

5. IAM Permissions:

• Data Fusion Service Account: Navigate to the IAM & Admin section in the Cloud console. Find the service account that Data Fusion is using (you can find this in the Data Fusion instance details). Does this service account have both the roles/cloudsql.client and roles/compute.networkUser roles? If not, add them.

Additional Steps:

• Test with psql: Let's try this to rule out any Data Fusion-specific issues. On a machine (e.g., Cloud Shell) that has network access to your Cloud SQL instance, try the psql command I provided. Does this connection succeed?