Connection test fails in Datastream for private connectivity option

[lorenh]

I am setting up a connection to a CloudSQL Postgres instance from Datastream via private connection. I have referred to the similar questions in the community I could find, and taken the following steps but still haven't been successful:

• allocated an IP range (/29) in my VPC for Datastream
• ensured the VPC has a firewall rule allowing ingress via TCP protocol over port 5432 with source filter set to the allocated IP range
• ensured export custom routes in Private Services Access for the VPC is enabled for both cloudsql-postgres-googleapis-com and servicenetworking-googleapis-com
• verified that the route which contains the internal IP address of the database appears in the Exported routes tab of the VPC Network Peering page.
• set up a reverse proxy to cloud sql in a new VM within the VPC to be peered based on these instructions (https://cloud.google.com/datastream/docs/private-connectivity#reverse-csql-proxy) and added a startup script as outlined here: https://cloud.google.com/compute/docs/instances/startup-scripts/linux#passing-local
• verified that the reverse proxy VM can psql into the CloudSQL instance via its the internal IP address. (Note: when i tried to ping the internal IP address from the reverse proxy VM, it did not return any packets)
• created a Datastream Private connectivity configuration for the VPC
• added custom advertised route for the IP range i allocated for Datastream (per https://cloud.google.com/network-connectivity/docs/router/how-to/advertising-subnets#console)
• tried to set up a connection profile using the private connectivity config, with the host set to the IP of the reverse proxy and port 5432

When I attempted to test the connection profile before adding the startup script to the reverse proxy, and adding custom advertised route, I got a timeout error. Now that I've taken those steps, I get the following error:
```
We can't connect to the source database using the specified hostname and port. Make sure that the hostname and port are correct and that the database can accept inbound connections.
```

My questions:

1) What do I need to change to get datastream connected to my cloudsql instance via private connectivity?

2) will a reverse proxy intercept all traffic to the db (i'm especially concerned about traffic from the app it is the backend db for), or will it be used only for datastream traffic? If all traffic, what impact might I expect on performance of requests to the db? Would using cloudsql auth proxy versus reverse proxy be more performant?

[ronnelg]

Hi @lorenh, 

Welcome to Google Cloud Community!

I understand that your test connection fails for connecting Datastream and PostgreSQL database using private connectivity. After reviewing the steps that you've taken, I can confirm that this should already have worked. Here are some additional troubleshooting points that may help:

• Verify that the IP address and credentials in creating the connection profile are correct.
• Review your database configuration to ensure if the database name, Datastream credentials such as username and password are correct.
• Verify that your proxy VM can communicate with the source database by running a ping or telnet command from the VM.
• Review your created startup script on your proxy VM.
• If there is a network connection issue, retrying test connection sometimes works.
• You can also check on Cloud Logging for the detailed logs of the test connection error. 

Also, there is already a filed feature request to allow the Datastream directly connect to PostgreSQL on private IP without a reverse proxy. You can make a follow up or vote on the request if the above troubleshooting still didn’t fix your issue. However, please be informed that I can’t provide a timeline as to when this will be implemented.

I hope the above information is helpful.

[lorenh]

Thanks @ronnelg, when i've tried to ping the internal IP address from the reverse proxy VM, it did not return any packets. do you have an idea why this might be, especially since i can successfully psql into the db instance from the reverse proxy VM via the private ip address for the cloud sql instance? 

Additionally, can you clarify how I might "verify that the reverse proxy forwards traffic to the source (and not from it)" as described in the reverse proxy documentation?

[lorenh · Accepted solution]

Update--we were able to get the connection working after restarting the proxy VM--this may be necessary to apply the newly added startup script.