Cannot connect to postgres cloudsql from app engine

Question

I am making my first app engine project using Go. I have a cloud sql instance and app engine in the same project inside cloud console.

I have the following inside my app.yaml file:

service: myservice
runtime: go
api_version: go1

env_variables:
  #for deploying
  #POSTGRES_CONNECTION: "user=postgres password=<redacted> dbname=my_database host=/cloudsql/myproject:us-west1:myserver"

  #for testing locally via cloud proxy
  POSTGRES_CONNECTION: "user=postgres password=<redacted> dbname=mydatabase sslmode=disable"


beta_settings:
  cloud_sql_instances: myproject:us-west1:myserver

handlers:

# All URLs are handled by the Go application script
- url: /.*
  script: _go_app

In the code I open the connection like this:

datastoreName := os.Getenv("POSTGRES_CONNECTION")
db, err := sql.Open("postgres", datastoreName)

On my local machine I have setup cloud_sql_proxy like this:

./cloud_sql_proxy -instances=passio-nutrition-develop-bcb6:us-west1:nutrition-sqlite-source=tcp:5432

And with POSTGRES_CONNECTION set up for local testing, everything works perfectly well.

However, when I set POSTGRES_CONNECTION for deploying, and run gcloud app deploy, I get an error message when I attempt to hit an endpoint:

{"Op":"dial","Net":"unix","Source":null,"Addr":{"Name":"/cloudsql/myproject:us-west1:myserver/.s.PGSQL.5432","Net":"unix"},"Err":{"Syscall":"socket","Err":1}}

I have tried various different versions of POSTGRES_CONNECTION, including adding =tcp:5432 to the end of the host and cloud_sql_instances, but I cannot get the connection to work.

If anyone could give me some pointers that would be great

Answer 1

Make sure you have the Cloud SQL Admin API enabled, and that your default App Engine service account has the correct IAM permissions (it needs to have the Cloud SQL Connect role or higher).

Answer 2

Example of a app.yaml

runtime: go

api_version: go1

handlers:

url: /stylesheets

static_dir: stylesheets

url: /(.*.(gif|png|jpg))$

static_files: static/\1

upload: static/.*.(gif|png|jpg)$

url: /.*

script: _go_app

Connecting to Postgresql

package main

import (

"database/sql"

"fmt"

_ "github.com/lib/pq"

)

const (

host = "localhost"

port = 5432

user = "postgres"

password = "your-password"

dbname = "calhounio_demo"

)

func main() {

psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+

"password=%s dbname=%s sslmode=disable",

host, port, user, password, dbname)

db, err := sql.Open("postgres", psqlInfo)

if err != nil {

panic(err)

}

defer db.Close()

err = db.Ping()

if err != nil {

panic(err)

}

fmt.Println("Successfully connected!")

}