Cloud SQL proxy cheatsheet

cloud sql proxy

INSTANCE_CONNECTION_NAME: <projname>:<region>:<db-instance-name> (myproject:us-central1:myinstance)

# tcp socket 
/cloud_sql_proxy -instances=<INSTANCE_CONECTION_NAME>=tcp:<port-number> \
                 -crendential_file=<PATH_TO_KEY_FILE>
psql "host=127.0.0.1 sslmode=disable dbname=<DB_NAME> user=<USER_NAME>"

# unix socket
sudo mkdir /cloudsql; sudo chmod 777 /cloudsql
# service account based (explicit instance)
./cloud_sql_proxy -dir=/cloudsql -instances=<INSTANCE_CONNECTION_NAME> \
                  -credential_file=<PATH_TO_KEY_FILE>
# automatic instance discovery 
./cloud_sql_proxy -dir=/cloudsql
# metadata based (explicity instance)

psql "sslmode=disable host=/cloudsql/<INSTANCE_CONNECTION_NAME> user=<USERNAME> dbname=<DBNAME>"

# options for specifying instance
https://cloud.google.com/sql/docs/postgres/sql-proxy#instances-options

# systemd service file
[Unit]
Description=Google Cloud SQL Proxy
After=network.target
Requires=network.target
ConditionPathExists=/cloudsql

[Service]
User=ubuntu
Group=ubuntu
# for unix socket using instance metadata
# correspondingly, instance metadata should have a db key
# with its value in <INSTANCE_CONNECTION_NAME_1>,<INSTANCE_CONNECTION_NAME_2>
ExecStart=/usr/local/bin/cloud_sql_proxy \
        -dir=/cloudsql \
        -instances_metadata=instance/attributes/db \
Restart=always
RestartSec=5s

[Install]
WantedBy=multi-user.target

# pgpass file
# multiple lines can be specified
# line used is based on match against psql params called
hostname:port:database:username:password

use * for any match
hostname for unix socket will be socket path e.g. /cloudsql/<proj>\:<region>\:<instancename>