Unable to connect to postgres database AWS RDS using pgx

php editor Zimo encountered a problem when using pgx to connect to the Postgres database AWS RDS. He found that he could not successfully establish a connection, even though he had configured it according to the guidance provided by the official documentation and other resources. He tried a variety of methods, including checking database permissions, confirming network connections, modifying configuration files, etc., but the problem persisted. He hopes to find an effective solution to this problem.

Question content

I tried to use the github.com/jackc/pgx/v5 package to connect to the postgres database in AWS RDS in the Go program, but the following error occurred ：

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 though it is an error to explicitly state that the database does not exist, I have created the RDS database and I can also connect to it via pgAdmin. But when I try to connect in Go code, I get the above error.

I checked the following points

1. Security group allows all traffic through port 5432
2. Enable public access in RDS
3. Ability to connect to the same database via pgAdmin.

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()

There may be something wrong here. Even from pgAdmin I can create the table but cannot connect from my Go code.

Solution

This problem is because the database "golangpg" does not exist in your RDS PostgreSQL host.

RDS identifier! =Database name

I recreate your settings here:

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 that the database "golangdb" does not exist does not exist in my PostgreSQL RDS.

Connect to the database host and list 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 exists on the host machine.

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

Create a 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

The above is the detailed content of Unable to connect to postgres database AWS RDS using pgx. For more information, please follow other related articles on the PHP Chinese website!