Question 419637 in PostgreSQL Question posted in
The official documentation can be found here.

How to ping postgreSQL using Golang

Hello esteemed colleagues

I’ve spent about a day trying to figure this out but im getting nowhere

how to ping postgreSQL using Golang just to check that they can talk to each other

This works:

`henry@vhost1:~/Eos$ pg_isready -h localhost -p 5432 -U eos_user -d eos_db
localhost:5432 - accepting connections

Can also read and write to it via command line:

henry@vhost1:~/Eos$ sudo -u eos_user psql -d eos_db -c "SELECT * FROM logs;"

sudo -u eos_user psql -d eos_db -c "INSERT INTO logs (level, message) VALUES ('INFO', 'Test log entry');"
 id |         timestamp          | level |    message
----+----------------------------+-------+----------------
  1 | 2024-12-22 14:21:41.265223 | INFO  | Test log entry
  2 | 2024-12-22 14:24:09.661304 | INFO  | Test log entry
(2 rows)

INSERT 0 1
henry@vhost1:~/Eos$ sudo -u eos_user psql -d eos_db -c "SELECT * FROM logs;"
 id |         timestamp          | level |    message
----+----------------------------+-------+----------------
  1 | 2024-12-22 14:21:41.265223 | INFO  | Test log entry
  2 | 2024-12-22 14:24:09.661304 | INFO  | Test log entry
  3 | 2024-12-22 17:09:57.48363  | INFO  | Test log entry
(3 rows)

But it just refuses to work in Golang:

henry@vhost1:~/Eos$ sudo -u eos_user go run testDbPing.go
2024/12/22 17:05:09 Database is not ready: pq: password authentication failed for user "eos_user"
exit status 1

Here is the programme I’m using to try figure this out:


`package main

import (
"database/sql"
"fmt"
"log"

    _ "github.com/lib/pq"

)

func main() {
// Connection details
host := "localhost"
port := "5432"
user := "eos_user"
dbname := "eos_db"

    // Connection string (no password for peer authentication)
    connStr := fmt.Sprintf("host=%s port=%s user=%s dbname=%s sslmode=disable", host, port, user, dbname)
    
    // Open a connection to the database
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatalf("Failed to open database connection: %v", err)
    }
    defer db.Close()
    
    // Ping the database to check readiness
    if err := db.Ping(); err != nil {
        log.Fatalf("Database is not ready: %v", err)
    }
    
    fmt.Println("Database is ready!")

}`

Merry xmas and a happy new year

I’ve had a look here:
https://hevodata.com/learn/golang-postgres/#c2

and maybe im missing somehting but i just cant get it to work and ChatGPT insists it is correct.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here's an update

    This was the answer "Use a unix socket to connect in your go program. Using host := "/var/run/postgresql/" should work"

    Here is the script I ended up using which worked:

    package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
)

func main() {
    // Define the connection parameters
    // Replace "<dbname>" and "<username>" with your PostgreSQL database and user.
    socketDir := "/var/run/postgresql"
    database := "eos_db"
    user := "eos_user"

    // Connection string
    connStr := fmt.Sprintf("host=%s dbname=%s user=%s sslmode=disable", socketDir, database, user)

    // Open a connection
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatalf("Failed to open a connection: %v", err)
    }
    defer db.Close()

    // Test the connection
    err = db.Ping()
    if err != nil {
        log.Fatalf("Failed to connect to the database: %v", err)
    }

    fmt.Println("Successfully connected to PostgreSQL over UNIX socket!")

    // Example query
    query := "SELECT current_date"
    var currentDate string
    err = db.QueryRow(query).Scan(&currentDate)
    if err != nil {
        log.Fatalf("Query failed: %v", err)
    }

    fmt.Printf("Current date from PostgreSQL: %sn", currentDate)
}

    And here is the terminal output

    henry@vhost1:~/Eos$ sudo -u eos_user go run postgresUnixSockTest.go
Successfully connected to PostgreSQL over UNIX socket!
Current date from PostgreSQL: 2024-12-24T00:00:00Z
henry@vhost1:~/Eos$

    Cheers


  2. 0

    Your two test commands are using different login methods and secondly, pg_ready does not care if a login fails, only that it could connect to the server to attempt a login.

    With your psql command you are logging in using a unix domain socket. This login method allows postgres to use the OS to authenticate the user of process that opened the connection. In this login method, no password or other authentication is needed. The OS has already verified that the process has permission to run as the given user.

    With pg_ready you are logging in using an inet socket over the loopback interface (localhost). Here postgres cannot use the OS to authenticate the identity of the user. As such is must use a different authentication method. As seen in your go program, it appears to be using password-based authentication when connecting over localhost. However, pg_ready does not care if a login is successful or not. Indeed, if the server responds that the login failed, then it stands to reason that the server is ready and accepting connections.

    If you try adding -h localhost to your psql command you will find that it similarly fails.

    You need to either:

    • Check your connection settings for connecting over internet sockets. Does it allow password authentication, and if so what is the correct password? To check such settings, open /var/lib/postgresql/data/pg_hba.conf and refer to https://www.postgresql.org/docs/current/auth-pg-hba-conf.html for what it all means.
    • Use a unix socket to connect in your go program. Using host := "/var/run/postgresql/" should work
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search