cloud sql for multiple regions

Question

I'm creating a google cloud setup with multiple instance groups (3) in multiple regions. They are all in the same VPC.

Regions:

us-central1
europe-north1
asia-east1

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'
})

Gabe Weiss Gabe Weiss · Accepted Answer · 2020-01-22T16:02:22

Spanner is definitely an option, as you'll likely get better latency for a globally-consistent setup with Spanner than you would with connecting to Cloud SQL cross-region.

Another option is to look into VPC Peering: https://cloud.google.com/vpc/docs/vpc-peering I THINK you can do that cross-region, although I haven't played with it to be sure, but that may then just "work" with Private IPs.

Or, if you're okay with some latency in the application, connecting via public IP to Cloud SQL is a fine option, but don't use allowed networks (whitelisting). Either use the Proxy whenever possible, or setup SSL connections (which is a bit of a pain, but definitely worth it)..

I have a detailed blog on using public IP + Cloud SQL Proxy here: https://medium.com/@GabeWeiss/connecting-cloud-sql-public-ip-sql-proxy-5513f59e5a9e, which is a relatively easy way to go from your VMs.

cloud sql for multiple regions

2 Answers