Troubles connecting via pgBouncer #4489
-
|
Has anyone been able to successfully connect through pgBouncer? I have a setup that works just fine if I use the normal We are running:
I am not sure if it's any combination of the above... but when the application starts it always gets here and then hangs: It gets shut down because the K8s probes aren't detecting a healthy container. I've set them to have a delay of 10 minutes, but the application will just sit and hang here. Configuration: We really want to connect via pgBouncer because of some issues on the Azure side where its beneficial for us to have all connections to the server going via pgBouncer. I've tried setting |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 4 replies
-
|
I think you need to configure PgBouncer with The following Docker Compose snippet works for me: services:
apiserver:
depends_on:
- postgres
environment:
ALPINE_DATABASE_MODE: "external"
ALPINE_DATABASE_URL: "jdbc:postgresql://pgbouncer:6432/dtrack"
ALPINE_DATABASE_DRIVER: "org.postgresql.Driver"
ALPINE_DATABASE_USERNAME: "dtrack"
ALPINE_DATABASE_PASSWORD: "dtrack"
ALPINE_DATABASE_POOL_ENABLED: "false"
postgres:
image: postgres:17-alpine
environment:
POSTGRES_DB: "dtrack"
POSTGRES_USER: "dtrack"
POSTGRES_PASSWORD: "dtrack"
volumes:
- "postgres-data:/var/lib/postgresql/data"
restart: unless-stopped
pgbouncer:
image: bitnami/pgbouncer:latest
depends_on:
- postgres
environment:
POSTGRESQL_HOST: "postgres"
POSTGRESQL_PORT: "5432"
POSTGRESQL_USERNAME: "dtrack"
POSTGRESQL_PASSWORD: "dtrack"
POSTGRESQL_DATABASE: "dtrack"
PGBOUNCER_DATABASE: "dtrack"
PGBOUNCER_PORT: "6432"
PGBOUNCER_POOL_MODE: "transaction"
PGBOUNCER_DEFAULT_POOL_SIZE: "30"
PGBOUNCER_IGNORE_STARTUP_PARAMETERS: "extra_float_digits"
PGBOUNCER_MAX_PREPARED_STATEMENTS: "15"
restart: unless-stopped |
Beta Was this translation helpful? Give feedback.
-
|
Thanks! It looks like it was the max prepared statements, which was set to One other question... if we are using pgbouncer should we disable the application-side pooling, i.e.. |
Beta Was this translation helpful? Give feedback.
-
Honestly, I don't know for sure. If the PgBouncer instance is shared with other apps, you definitely want to disable client-side pooling, otherwise you'll be keeping connections hostage that other apps might need. Without having done extensive testing, I'd say just disable client-side pooling. I can't imagine it going well to keep both enabled, especially since PgBouncer is instructed to de-allocate connections after transaction commit, but HikariCP is configured to always keep |
Beta Was this translation helpful? Give feedback.
-
|
Also note that setting |
Beta Was this translation helpful? Give feedback.
-
|
Thanks. Yeah we are using the defaults for Azure's managed pgBouncer, we likely need to do some tuning. Thanks for your help! |
Beta Was this translation helpful? Give feedback.
I think you need to configure PgBouncer with
pool_mode=transaction. You'll also want to ensure that you're running a PgBouncer version newer than 1.21.0, and that you configuredmax-prepared-statementvalue.The following Docker Compose snippet works for me: