Connecting to multiple endpoints for high availability system

[triscuitoraus]

Hi,

I'm using pgx v4.17.2 and sub package pgxpool and was wondering how I can connect to multiple endpoints in my high availability system.

I saw there used to be a supporting function 'ParseConnectionString', but don't see it supported anymore? Or possibly I am unable to find where it is documented with the later versions of pgx.

// ParseConnectionString supports specifying multiple hosts in similar manner to libpq.
// Host and port may include comma separated values that will be tried in order.
// This can be used as part of a high availability system.

What is the best practice for connecting to multiple endpoints while using pgxpool?

Thank you.

[jackc]

It is still supported in v4. https://pkg.go.dev/github.com/jackc/pgconn#ParseConfig.

[triscuitoraus]

Thanks for the response @jackc.

Right, so I see that pgconn.ParseConfig supports multiple hosts separated by comma.
In my database connection, I'm using pgxpool.Connect, which calls pgxpool.ParseConfig:
https://pkg.go.dev/github.com/jackc/pgx/[email protected]/pgxpool#ParseConfig

And in the documentation it says ' It parses connString with the same behavior as pgx.ParseConfig with the addition of the following variables...'
Does that mean it also supports multiple hosts separated by comma?

In my testing, I am coming across an error when I try to do this. It doesn't seem to be attempting connection in order of the hosts provided. Am I constructing the connection string correctly here? username/(no pass) is the same for each host. Thanks in advance for your help.

`
...
var connectStr = "postgres://postgres@test-database-server:6000/postgres,test-database-server:5432/postgres,172.17.0.30:6000/postgres,172.17.0.37:5432/postgres"

context := context.Background()
pool, connErr := pgxpool.Connect(context, connectStr)
...
`

error:

2023/08/14 22:56:40 Unable to connect to database: failed to connect tohost=test-database-server user=postgres database=postgres,test-database-server:5432/postgres,172.17.0.30:6000/postgres,172.17.0.37:5432/postgres: hostname resolving error (lookup test-database-server on 127.0.0.1:53: read udp 127.0.0.1:47162->127.0.0.1:53: i/o timeout)

[triscuitoraus]

I've corrected my connection string to the following, but I still don't see pgx cycling through the list of given hosts. It seems to be stuck connecting to the first one: 'test-database-server', which is currently does not exist, so I expect pgx to fail connecting (which it does) and attempt to connect to the next host. Currently I am giving it 5 seconds to connect before I attempt a connection retry. Any ideas what I am possible doing wrong here? Do we know how much time is given to each host before it tries connecting to the next one?

var connectStr = "postgres://postgres@test-database-server:6000,test-database-server:5432,172.17.0.30:6000,172.17.0.37:5432/postgres"
func ConnectDB(retryInterval int) {
	setupOnce.Do(func() {
		context := context.Background()
		pool, connErr := pgxpool.Connect(context, connectStr)

		for connErr != nil {
			Logger.Pac_Info_f("Unable to connect to database: %v\n", connErr)
			time.Sleep(time.Duration(retryInterval) * time.Second)
			Logger.Pac_Info("Reconnecting to Database...")
			pool, connErr = pgxpool.Connect(context, connectStr)
		}
		Logger.Pac_Info_f("Connected to database: %v\n", connectStr)

		SetupDBPool(pool, context)
	})
}

log output:

2023/08/15 04:38:05 Reconnecting to Database...
2023/08/15 04:38:07 Unable to connect to database: failed to connect to host=test-database-server user=postgres database=postgres: hostname resolving error (lookup test-database-server on 127.0.0.1:53: read udp 127.0.0.1:45556->127.0.0.1:53: i/o timeout)

...

2023/08/15 04:38:14 Reconnecting to Database...
2023/08/15 04:38:16 Unable to connect to database: failed to connect to host=test-database-server user=postgres database=postgres: hostname resolving error (lookup test-database-server on 127.0.0.1:53: server misbehaving)

[jackc]

Here are some example connection strings with multiple hosts from the test suite:

postgres://jack:secret@foo,bar,baz/mydb?sslmode=disable
postgres://jack:secret@foo:1,bar:2,baz:3/mydb?sslmode=disable
user=jack password=secret host=foo,bar,baz port=5432 dbname=mydb sslmode=disable
user=jack password=secret host=foo,bar,baz port=1,2,3 dbname=mydb sslmode=disable

But from your last set of error messages the problem looks like a failure to connect to the DNS server.

[triscuitoraus]

when it fails to connect to the hostname 'test-database-server', shouldn't it attempt connection to the next host? I have set it so that the host at the end of the connection string is valid.

[triscuitoraus]

thanks for the examples @jackc. I have tried DSN format as well:
user=postgres host=test-database-server,172.17.0.30,172.17.0.37 port=6000,6000,5432 dbname=postgres sslmode=disable

Unfortunately I'm still seeing 'pgxpool.Connect' returning an error after failing to connect to the first host. Is that the expected behavior? Still trying to figure out what is preventing the pgxpool.Connect function from attempting to connect to the other hosts provided once the first one fails.

[jackc]

You might want to step through pgconn.ConnectConfig in the debugger to confirm, but I think I know what's happening.

pgx handles multiple hosts by using a slice of configs, one for each possible connection attempt. It tries each one in the list until it gets a successful connection. So in your example you start with 3 different connection configs. However, resolving a domain name can yield multiple IP addresses. Each of them should be a separate connection attempt. So before pgx can make those requests, it first has to resolve the domain names and create a connection config for each individual IP address. Connection attempts can fail and pgx will try the next one, but it appears that a DNS failure while building the list of connection attempts is a fatal error.

[triscuitoraus]

hi @jackc, thanks again for the feedback. Yes, I started digging through pgconn and confirmed it is failing at 'expandWithIPs()' at the 'lookupFn' where I'm assuming it is attempting to resolve the domain names and is coming back with 'hostname resolving error'.

I'm running my service on a multiple system configurations, some where the hostname may exist and other where it will not. That is why I am interesting in providing a list of hosts to begin with. Just wanted to confirm with you that what is observed here is expected behavior and not a bug? Thanks again

[jackc]

Where feasible we try to match the C library libpq's behavior. My guess is that handles this situation properly. So I would consider the current behavior a bug or at least something that could be improved.

[triscuitoraus]

ok, do you mind if I spawn a new bug report for this issue?

[jackc]

Sure.