Unable to connect to postgres database AWS RDS using pgx

Question

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%v\n", 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.

Answer 1

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

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

Answer 2

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