Question 270808 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – Unable to connect to postgres database AWS RDS using pgx

I am trying to connect to postgres database in AWS RDS in a Go program using github.com/jackc/pgx/v5 package, but the following error occurs:

failed to connect to `host=xxxxx.xxxxxxx.us-east-1.rds.amazonaws.com user=golangpg database=golangpg`: server error (FATAL: database "golangpg" does not exist (SQLSTATE 3D000))
exit status 1

Even its explicit that database not exist is the error but I have already created the RDS database and I also can connect to that database through pgAdmin as well. But when I tried to connect in my Go code, I got the above mentioned error.

I have checked the following points

  1. Security group is allowing all traffic through 5432 port
  2. Public access is enabled in the RDS
  3. Able to connect through pgAdmin to the same database.
     
    package database
    
    import (
        "context"
        "fmt"
        "os"
        "strconv"
    
        "github.com/jackc/pgx/v5"
        "github.com/joho/godotenv"
    )
    
    func PGConnection() *pgx.Conn {
        envErr := godotenv.Load()
        if envErr != nil {
            fmt.Println("Error in loading .env file, ", envErr)
        }
    
        host := os.Getenv("DB_URL")
        port := os.Getenv("DB_PORT")
        user := os.Getenv("DB_USERNAME")
        password := os.Getenv("DB_PASSWORD")
        dbname := os.Getenv("DB_NAME")
    
        portInt, er := strconv.Atoi(port)
        if er != nil {
            fmt.Println("Error in converting string to number", er)
        }
    
        var err error
    
        pgConnString := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=require", host, portInt, user, password, dbname)
    
        pgConn, err := pgx.Connect(context.Background(), pgConnString)
    
        if err != nil {
            fmt.Printf("Unable to connection to database: n%vn", err)
            os.Exit(1)
        } else {
            fmt.Println("Database connection establised to db ", dbname)
        }
    
        return pgConn
    }
    
    var Connection *pgx.Conn = PGConnection()

What could be the error here. Even from pgAdmin I can able to create tables but couldn’t connect from my Go code.

enter image description here

enter image description here

Tags:

2

Answers


  1. 0

    Try database=%s instead of dbname=%s, and pass necessary certs when using sslmode=require.

    Login or Signup to reply.
  2. 0

    The issue is because database "golangpg" does not exist in your RDS PostgreSQL host.

    RDS identifier != database name

    Here I re-create your setup:

    PostgreSQL RDS

    enter image description here

    Executed the same code and got the same error:

    ▶ go run main.go
Unable to connection to database: 
failed to connect to `host=golangdb.xxxx.eu-central-1.rds.amazonaws.com user=postgres database=golangdb`: server error (FATAL: database "golangdb" does not exist (SQLSTATE 3D000))
exit status 1

    The reason is database "golangdb" does not exist doesn’t exist in my PostgreSQL RDS.

    Connect to the database host and list the available databases.

    ▶ psql --host=golangdb.xxxxx.eu-central-1.rds.amazonaws.com --username=postgres

postgres=> l
                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype     
-----------+----------+----------+-----------------+-------------+-------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 
 rdsadmin  | rdsadmin | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8

    This is also why using dbname := "postgres" works, because the database exist in the host.

    ▶ go run main.go
Database connection establised to db  postgres

    Create new database

    postgres=> CREATE DATABASE golangdb;
CREATE DATABASE
postgres=> l
                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    
-----------+----------+----------+-----------------+-------------+-------------
 golangdb  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 
 rdsadmin  | rdsadmin | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8

    Retry go code

    ▶ go run main.go
Database connection establised to db  golangdb
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search