Can't connect to pgBouncer running in Docker

All we need is an easy explanation of the problem, so here it is.

I’m not great at Docker so please bear with me.

I have a Postgres database running and exposed to the public, I now want to use PgBouncer to connect to it. The catch is that I can’t use docker compose.

So I tried creating a PgBouncer container that would connect to my Postgres database, and while the image builds and connects, I can’t seem to connect to PgBouncer via psql.

My dockerfile looks like this:

FROM bitnami/pgbouncer

ENV POSTGRESQL_HOST=<secret>
ENV POSTGRESQL_USERNAME=postgres
ENV POSTGRESQL_PASSWORD=<secret>
ENV POSTGRESQL_DATABASE=railway
ENV PGBOUNCER_POOL_MODE=transaction
ENV PGBOUNCER_SERVER_TLS_SSLMODE=require
ENV PGBOUNCER_SERVER_TLS_PROTOCOLS=secure
ENV POSTGRESQL_PORT=7572

EXPOSE 6432

Build logs:

#1 [internal] load build definition from Dockerfile
#1 sha256:2b27c413d808045052959fb406a7d3ed92ffddabd2ecfd627fa02f788a0c7366
#1 transferring dockerfile: 384B done
#1 DONE 0.0s
 
#2 [internal] load .dockerignore
#2 sha256:29bf812758b207a972198ba4784186f6645c84609135258456eb758891acd7cf
#2 transferring context: 2B done
#2 DONE 0.0s
 
#3 [internal] load metadata for docker.io/bitnami/pgbouncer:latest
#3 sha256:f38155f2559bdbd5f40684555bcdd58ccfa2ab0a2764734b64850a545be81c17
 
#3 DONE 0.6s
 
#4 [1/1] FROM docker.io/bitnami/[email protected]:ccc5862c8e4e43dcbb730f90701aff86be952b0fab3e138b31d2d41a1bb7c689
#4 sha256:7630e64dd99e4fbc6ab874e4cd719620eb003609829983be3f6695f575822a04
#4 CACHED
 
#5 exporting to image
#5 sha256:e8c613e07b0b7ff33893b694f7759a10d42e180f2b4dc349fb57dc6b71dcab00
#5 exporting layers done
#5 writing image sha256:89d159428b1b4bca553be65a13986c93a1a4014e22deb6fe0ac7403e1ea03376
 
#5 writing image sha256:89d159428b1b4bca553be65a13986c93a1a4014e22deb6fe0ac7403e1ea03376 done
#5 naming to us-west1-docker.pkg.dev/railway-infra/railway-docker-users/project/aea3b6e0-9ab7-4346-a190-0064d10a64d0/service/c0ca4b29-8cda-4f35-9ab2-b50e0804a553:d62e4c6b-e377-4fbe-a6b6-859b91bd37af 0.0s done
#5 DONE 0.1s
 
Build time: 0.91 seconds
================
Publishing Image
================
The push refers to repository [us-west1-docker.pkg.dev/railway-infra/railway-docker-users/project/aea3b6e0-9ab7-4346-a190-0064d10a64d0/service/c0ca4b29-8cda-4f35-9ab2-b50e0804a553]
Preparing  1795c8adceb1
Preparing  a4811567fbf9
Preparing  26a547a84d64
Preparing  09bcb36a7b66
Preparing  27cfc9e1efa1
Preparing  5f134c42cbad
Preparing  96b99c45d67e
Preparing  4bf0deac05dd
Preparing  06c4ec15ddca
Preparing  42b498a289ec
Preparing  d2b20bf7d5e5
Waiting  26a547a84d64
Preparing  f9f2f9d11759
Preparing  595166e73b5f
Waiting  09bcb36a7b66
Preparing  d135133038c3
Preparing  0d00bc84dc6a
Waiting  96b99c45d67e
Waiting  27cfc9e1efa1
Preparing  34751ae2f9ce
Waiting  5f134c42cbad
Waiting  4bf0deac05dd
Preparing  a3cff24d533b
Waiting  06c4ec15ddca
Waiting  42b498a289ec
Waiting  595166e73b5f
Waiting  d2b20bf7d5e5
Waiting  f9f2f9d11759
Waiting  d135133038c3
Waiting  0d00bc84dc6a
Waiting  34751ae2f9ce
Waiting  a3cff24d533b
Layer already exists  1795c8adceb1
Layer already exists  a4811567fbf9
Layer already exists  09bcb36a7b66
Layer already exists  26a547a84d64
Layer already exists  27cfc9e1efa1
Layer already exists  5f134c42cbad
Layer already exists  96b99c45d67e
Layer already exists  4bf0deac05dd
Layer already exists  06c4ec15ddca
Layer already exists  d2b20bf7d5e5
Layer already exists  42b498a289ec
Layer already exists  f9f2f9d11759
Layer already exists  595166e73b5f
Layer already exists  d135133038c3
Layer already exists  0d00bc84dc6a
Layer already exists  34751ae2f9ce
Layer already exists  a3cff24d533b
d62e4c6b-e377-4fbe-a6b6-859b91bd37af: digest: sha256:1a7f8a751055e8a67b6ca63f20479e67b8b64c3256d54f6a3ea3f5e66dc5abc9 size: 3880

Deploy logs:

pgbouncer 14:52:57.05 
pgbouncer 14:52:57.06 Welcome to the Bitnami pgbouncer container
pgbouncer 14:52:57.06 Subscribe to project updates by watching https://github.com/bitnami/containers
pgbouncer 14:52:57.06 Submit issues and feature requests at https://github.com/bitnami/containers/issues
pgbouncer 14:52:57.06 
pgbouncer 14:52:57.08 INFO  ==> ** Starting PgBouncer setup **
pgbouncer 14:52:57.10 INFO  ==> Validating settings in PGBOUNCER_* env vars...
pgbouncer 14:52:57.11 INFO  ==> Initializing PgBouncer...
pgbouncer 14:52:57.13 INFO  ==> Waiting for PostgreSQL backend to be accessible
pgbouncer 14:52:57.15 INFO  ==> Backend containers-us-west-76.railway.app:7572 accessible
pgbouncer 14:52:57.15 INFO  ==> Configuring credentials
pgbouncer 14:52:57.15 INFO  ==> Creating configuration file
pgbouncer 14:52:57.47 INFO  ==> Loading custom scripts...
pgbouncer 14:52:57.47 INFO  ==> ** PgBouncer setup finished! **
pgbouncer 14:52:57.50 INFO  ==> ** Starting PgBouncer **
2022-07-15 14:52:57.514 UTC [1] LOG kernel file descriptor limit: 1048576 (hard: 1048576); max_client_conn: 100, max expected fd use: 152
2022-07-15 14:52:57.515 UTC [1] LOG listening on 0.0.0.0:6432
2022-07-15 14:52:57.515 UTC [1] LOG listening on unix:/tmp/.s.PGSQL.6432
2022-07-15 14:52:57.516 UTC [1] LOG process up: PgBouncer 1.17.0, libevent 2.1.12-stable (epoll), adns: c-ares 1.17.1, tls: OpenSSL 1.1.1n  15 Mar 2022
2022-07-15 14:53:57.518 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-07-15 14:54:57.517 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-07-15 14:55:57.514 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-07-15 14:56:57.518 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-07-15 14:57:57.518 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-07-15 14:58:57.514 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-07-15 14:59:57.517 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-07-15 15:00:57.514 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us

If I try to connect with:

psql postgresql://postgres:<postgres-password>@<pgbouncer-host>:6432/railway

My terminal just hangs, no error, no success.

Am I misunderstanding how to install PgBouncer or is there an issue with my configuration?
The container is hosted by railway.app.

EDIT:

After much fiddling I am able to get psql to time out the connection:

psql: error: connection to server at "<pgbouncer-host>" (<ip>), port 6432 failed: Connection timed out
        Is the server running on that host and accepting TCP/IP connections?

PGbouncer is logging the following at regular intervals:

2022-07-19 09:40:00.382 UTC [1] DEBUG C-0x55eb947d7780: (nodb)/(nouser)@10.10.10.12:43202 failed connection attempt
2022-07-19 09:40:14.084 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-07-19 09:41:14.084 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us

The failed connection attempt are logged regardless of if I try to connect or not and do not happen when psql times out so I’m absolutely baffled.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

So, there is nothing wrong with my code. The issue was with the platform 🤷‍♂️.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply