pgbouncer | lightweight connection pooler for PostgreSQL | Database library

Given a PostgreSQL database that is reasonably configured for its intended load what factors would contribute to selecting an external/middleware connection pool (i.e. pgBouncer, pgPool) vs a client-side connection pool (HikariCP, c3p0). Lastly, in what instances are you looking to apply both client-side and external connection pooling?

From my experience and understanding, the disadvantages of an external pool are:

• additional failure point (including from a security standpoint)
• additional latency
• additional complexity in deployment
• security complications w/ user credentials

In researching the question, I have come across instances where both client-side and external pooling are used. What is the motivation for such a deployment? In my mind that is compounding the majority of disadvantages for a gain that I appear to be missing.

I have a Dockerized Django application, which I'm orchestrating with Supervisor, which is not optimal but needed when hosting on Azure app services as their multi-app support with docker-compose is still in preview mode (aka. beta).

According to best-practises I have configured each application within supervisord to emit the logs to STDOUT. It works fine when I create the Docker image locally, run it and check the docker logs. However, when I have deployed it to Azure app services and check the logs, my web-application (Gunicorn) is logging as expected, however, the logs from NGINX don't appear at all.

I have tried different configurations in my Dockerfile for linking the log files generated by NGINX (linking to both /dev/stdout and /dev/fd/1 for example) and I have also gone into the the nginx.conf config and trying to log out directly to /dev/stdout. But whatever I do it work fine locally, but on Azure the logs don't show any NGINX-logs. I've pasted relevant configuration files, where you can see the commented lines with the options I've tried with. Hope someone can help me figure this one out.

EDIT: I've also tried logging the NGINX app to a log-file in the system, which also works fine locally, but not in Azure app-services. I tried deactivating the "user nginx" part in nginx.conf as I though it could have something to do with permissions, but that didn't help either.

EDIT 2: I also tried creating the log files in my home-directory in the web-app at Azure, thinking it may had to do with not being able to create logs in other directories - again, it works locally, but the logs in Azure are empty.

Dockerfile

I want to run supervisor to have multiple processes in the same container, as I can't use docker-compose in our current hosting environment. Things seems to work when I look in the docker logs, but I can't see the supervisor service inside the linux system when I've attached my terminal to the container.

When I check the logs for the container I get:

back ground info: recently we upgraded the airflow from 2.10.14 to 2.1.3, the pgbouncer was using customised container built from azure microsoft image (mcr.microsoft.com/azure-oss-db-tools/pgbouncer-sidecar:latest).

the customised pgbouncer stopped working, it instead connects to the main postgresql server now.

so I now try to use pgbouncer deployed by airflow 2.1.3 (helm chart 8.5.2) instead (https://artifacthub.io/packages/helm/airflow-helm/airflow/8.5.0#how-to-use-an-external-database), and have problems.

Below is the key info

in my values.yaml file, key info is like below

Can I make PgBouncer preserve the PGOPTIONS environment variable in transaction pooling to configure GUC parameters? Or is there another way to configure these parameters in PgBouncer so that it applies to all connections?

I specifically need to set some pg_trgm parameters

I have external Postgres and I want connect hem with me Kubernetes claster throuth pgbouncer. What i need configure Kubernetes claster with Postgres?.

I have managed to achieve the flow of creating sensitive resources in terraform, without revealing what the sensitive details are at any point and therefore won't be stored in plain text in our github repo. I have done this by letting TF create a service account, it's associated SA key, and then creating a GCP secret that references the output from the SA key for example.

I now want to see if there's any way to do the same for some pre-defined database passwords. The flow will be slightly different:

• Manually create the GCP secret (in secrets manager) which has a value of a list of plain text database passwords which our PGbouncer instance will use (more info later in the flow)
• I import this using terraform import so terraform state is now aware of this resource even though it was created outside of TF, but the secret version I've just added as secret_data = "" (otherwise putting the plain text password details here defeat the object!)
• I now want to grab the secret_data from the google_secret_manager_version to add into the kubernetes_secret so it can be used within our GKE cluster.

However, when I run terraform plan, it wants to change the value of my manually created GCP secret

This problem is driving me batty. I have a PHP script which connects to a postgresql database, and I have pgbouncer running for connection pooling.

I've tested it using two databases, and it works just fine for both when I connect directly. Here is my connection code:

So I am running a k8s cluster with 3 pod postgres cluster fronted by a 3 pod pgbouncer cluster. Connecting to that is a batch job with multiple parallel workers which stream data into the database via pgbouncer. If I run 10 of these batch job pods everything works smoothly. If I go up an order of magnitude to 100 job pods, a large portion of them fail to connect to the database with the error got error driver: bad connection. Multiple workers run on the same node (5 worker pods per node) So it's only ~26 pods in the k8s cluster.

What's maddening is I'm not seeing any postgres or pgbouncer error/warning logs in Kibana and their pods aren't failing. Also Prometheus logging shows it to be well under the max connections.

Below are the postgres and pgbouncer configs along with the connection code of the workers.

Relevant Connection Code From Worker: