0

I am trying to connect to my GAE Postgres SQL db using Go+Gin+PGX. I have the Postgres SQL api activated and this program runs on my local machine but does not run on GAE. I think it is not connecting the db via pgx but I am not sure.

main.go works on my local machine and instance of psql but after deploying to GAE cannot connect to db via the Public IP in GCP SQL instance. I have modified my working code to fix the MWE.

package main

import (
    "fmt"
    "os"
    "github.com/gin-gonic/gin"
    "github.com/jackc/pgx/v4"
    "golang.org/x/net/context"
)

func main() {
    conn, err := connectDB()
    if err != nil {
        os.Exit(1)
    }
    defer conn.Close(context.Background())

    router := gin.Default()
    router.GET("/", func(c *gin.Context) {
        c.JSON(200, gin.H{
            "message": "pong",
        })
    })
    router.Run(":8080")
}

func connectDB() (c *pgx.Conn, err error) {
    postgres := "postgresql://postgres:root@35.236.60.144:5432/goapi" //35.236.60.144
    conn, err := pgx.Connect(context.Background(), postgres)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to connect to database:\n\t%v\n", err.Error())
        return nil, err
    }
    err = conn.Ping(context.Background())
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to ping:\n\t%v\n", err.Error()) 
        return nil, err
    }
    return conn, err
}

If I use Postman to perform a GET to GAE website then it I get

<html>

<head>
    <meta http-equiv="content-type" content="text/html;charset=utf-8">
    <title>500 Server Error</title>
</head>

<body text=#000000 bgcolor=#ffffff>
    <h1>Error: Server Error</h1>
    <h2>The server encountered an error and could not complete your request.<p>Please try again in 30 seconds.</h2>
    <h2></h2>
</body>

</html>

The corresponding GAE error stack trace sample is

runtime error: invalid memory address or nil pointer dereference [signal SIGSEGV: segmentation violation code=0x1 addr=0x8 pc=0xaabec7]
     at
     github.com/jackc/pgx/v4.(*Conn).exec (conn.go:413)
     at
     github.com/jackc/pgx/v4.(*Conn).Exec (conn.go:396)
     at
     github.com/jackc/pgx/v4.(*Conn).Ping (conn.go:347)
     at
     main.connectDB (main.go:41)
     at
     main.main (main.go:15)

When I view the log I see...

Unable to connection to database: failed to connect to `host=35.236.60.144 user=postgres database=goapi`: dial error (dial tcp 35.236.60.144:5432: connect: connection timed out)
Bryce Wayne
  • 351
  • 5
  • 17
  • 1
    Is `pgx.Connect` returning an error? (you run the `conn.Ping` regardless of whether the connection succeeds or fails; if it failed then a `nil pointer deference` is not unexpected). – Brits Feb 17 '21 at 03:34
  • @Brits I found the errort in the logs. I will edit the post too. ` Unable to connection to database: failed to connect to `host=localhost user=postgres database=goapi`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)` – Bryce Wayne Feb 17 '21 at 04:44
  • 1
    OK - check the [instances IP address](https://cloud.google.com/sql/docs/postgres/connect-app-engine-standard) and replace the '127.0.0.1' in the connection string with that. – Brits Feb 17 '21 at 06:25
  • `Unable to connection to database: cannot parse `postgres://postgres:xxxxx@goapi-305007:us-west2:goapi:5432/goapi`: failed to parse as URL (failed to split host:port in 'goapi-305007:us-west2:goapi:5432', err: address goapi-305007:us-west2:goapi:5432: too many colons in address)` – Bryce Wayne Feb 17 '21 at 06:54
  • 1
    It looks like you are using a public IP so [Cloud SQL Proxy](https://cloud.google.com/sql/docs/postgres/sql-proxy#install) might be your best option (this will use `127.0.0.1` but you need the proxy installed) - see [this sample](https://github.com/GoogleCloudPlatform/golang-samples/blob/master/cloudsql/postgres/database-sql/README.md). Sorry - I don't know enough about GCP to assist. – Brits Feb 17 '21 at 19:48
  • I can connect this way on my local machine but if I deploy to GAE it does not work. – Bryce Wayne Feb 17 '21 at 21:10

0 Answers0