I'm creating a google cloud setup with multiple instance groups (3) in multiple regions. They are all in the same VPC.
Regions:
All three instance groups have 2 VMs. It's behand a HTTP(S) load balancer, and when I visit the public IP address of the load balancer, I see the apache welcome page (apache is installed on the VMs). So this works.
I also have a Cloud SQL (PostgreSQL) database. I want to access this database through it's private IP. I have associated the VPC-network the VMs use with the private IP of the Cloud SQL instance. This instance runs in us-central1 region.
Now, the problem is: it's not working. :) I did some research in the docs, and I found this:
You must choose a VPC network to use. The Google Cloud resources you will use to connect to your Cloud SQL instance (either Compute Engine instances [VMs] or Google Kubernetes Engine instances) must use this VPC network in order to be able to connect. These resources must also be in the same region as your Cloud SQL instance.
Source: https://cloud.google.com/sql/docs/postgres/configure-private-ip
To test, I add a new VM in the region us-central1 (not associated with an instance group) and with the correct VPC. When I install the postgresql-client on this VM, I can connect to the database. But when I create another VM in the region europe-north1 (also not associated with an instance group) and with the correct VPC, I can't connect to the database. So the documentation is (suprise) correct: instances in a different region than the region the Cloud SQL instance is running, can't connect to the Cloud SQL through a private IP.
My question now is: what is the best way to solve this issue so that all the VMs in the different instance groups can connect to the same database. Should I create multiple Cloud SQL instances in the different regions and copy each database whenever some data is inserted/updated/deleted? To me, this seems like a lot of unnecessary traffic and load.
Another option would be (I think) is to use Cloud Spanner. I just discovered this option, and I'm currently reading the docs. Is this the way to go?
Edit: Cloud Spanner seems promising, but it's also very expensive I see. 1 node with 1 GB storage is about $ 650/month.
Another option could be to access the cloud SQL database through the public IP. The problem is: I should whitelist every VM IP address, and because I use autoscaling, VMs are made on the go (with new Ip addresses). How can I tackle this problem, so that new VMs are automatically whitelisted to the database?
Edit: with Gabe Weiss' answer, I created a new (test) VM, and I installed the cloud SQL proxy and the postgresql-client. It works, when I make a connection or do a query, it's all good.
Now, the problem is: it does not work in my nodejs-app. I'm using node-postgres (https://node-postgres.com/) and my connection config is like this:
const db = new Pool({
host: '127.0.0.1',
port: 5432,
user: 'postgres',
password: 'postgres',
database: 'postgres'
})
I also tried replacing '127.0.0.1' to localhost, I removed the port, ... nothing works. The credentials are correct.
Edit 2: When I run the cloud_sql_proxy in the background, and I run my nodejs app as well, and I navigate to http://external_ip_address:8000/node, I see a log in the console:
2020/01/22 17:55:20 New connection for "project-id:region:instance-name"
But after 10 seconds, I get this:
2020/01/22 17:57:00 Client closed local connection on 127.0.0.1:5432
Edit 3:
I also tried changing the host to /cloudsql/instance_connection_name, but no result either.
const db = new Pool({
host: '/cloudsql/[project-id]:[region]:[instance-name]',
port: 5432,
user: 'postgres',
password: 'postgres',
database: 'postgres'
})
Solution
So the solution is pretty simple: it had nothing to do with some configuration, but with my nodejs code. I forgot that a postgresql query is a promise, so I have to wait for the results to return. Adding async/await solved the problem.