Documentation Confusion between Docker Compose and Kubernetes

[omarsumadi]

Hi all,

I have a small point of confusion when comparing the Docker Compose examples for Documentation and the Kubernetes example of Documentation.

See here: https://github.com/edoburu/docker-pgbouncer/blob/master/examples/docker-compose/docker-compose.yml

• In this example, you are asking for the Database Name.

In comparison: https://github.com/edoburu/docker-pgbouncer/blob/master/examples/kubernetes/singleuser/pgbouncer-example-env.yml

• In this example, you are not asking for the Database Name.

Is there a reason for this - for instance, is the DB Name handled when our application (like Django in which we specify the DB_Name) actually connects to PGBouncer rather than specifying it beforehand? Or should we be adding that ENV on-top of your suggestions?

Thanks,
Omar

[bemanuel]

It's possible to use DATABASE_URL or DB_USER, etc... -- DATABASE_URL overwrite the values defined on DB_USER,DB_PASSWORD. DB_HOST, DB_PORT

[bemanuel]

You can see it on README.md -> Usage

[omarsumadi]

@bemanuel Thanks for the feedback - I'm mainly confused about how in the Kubernetes deployments the documentation doesn't specify any Database Name.

If you look at the Docker vs Kubernetes example I gave, in Docker we provide the Database Name, but in Kubernetes (see the secrets generation), it's not expected.

In addition, DATABASE_URL would also fill Database Name, but I'm just confused as to why the Kubernetes Documentation Single User secrets only name:

DB_HOST=postgres.default
DB_USER=username
DB_PASSWORD=password

But no Database Name like Docker?

[omarsumadi]

Or is the name of the deployment the name taken for the Database Name?

[bemanuel]

Oh, I see, when you don't specify a database name the entrypoint generates a pgbouncer.ini for all databases.
So, on Kubernetes' example, the pgbouncer will permit connection to all the database under "host=postgres.default" using the user DB_USER and DB_PASSWORD.
This guarantee a pgbouncer for a "single user"
But I think you're right about misunderstood. The doc could explain better what happens. We can suggest some changes on the doc, now I'm solving some issues after I can try to do something about this and suggest a pull request. Do you mind suggesting some text explaining this usage?

[omarsumadi]

@bemanuel

Got it - TYSM. I removed the DB_NAME from my Docker Compose and it still allowed the connection to go through. So I guess, as you stated, if no DB_NAME is given, PGBouncer will scrape the host for all DB_NAME as create configuration to allow connection to any of them using PGBouncer. I haven't tested it for Kubernetes, but I'm sure it will work as well.

I don't know how to contribute to open source - I'm quite new to it all. Do you want me to write some text here and give it to you, or do you want me to directly make a pull request for the documentation?

[MathiasDrapier]

Hello, @omarsumadi
Did you succed to deploy pgBouncer with docker compose? If so can you provide me with your yml file please?

[omarsumadi]

@MathiasDrapier sure, I cut out some details though

version: '3'

volumes:
  local_postgres_data: {}
  local_postgres_data_backups: {}

services:
  django: &django
    build:
      context: .
      dockerfile: ./compose/local/django/Dockerfile
    image: edsproject_local_django
    container_name: django_local
    depends_on:
      - pgbouncer
    volumes:
      - .:/app:z
      - ./secrets_envs/test_secrets:/secrets:z
    env_file:
      - ./.envs/.local/.django
      - ./.envs/.local/.postgres
    ports:
      - "8000:8000"
      - "5000:5000"
      - "3000:3000"
      - "3035:3035"
    expose:
      - 3000
      - 3035
    command: /start
    
  postgres:
    build:
      context: .
      dockerfile: ./compose/production/postgres/Dockerfile
    image: edsproject_local_postgres
    container_name: postgres_local
    volumes:
      - local_postgres_data:/var/lib/postgresql/data:Z
      - local_postgres_data_backups:/backups:z
    environment:
       - DB_USER=user_admin
       - DB_PASSWORD=user_admin_password
       - DB_NAME=edsproject

  pgbouncer:
    image: edsproject_pgbouncer # I copied the userlist.txt and certificates inside, created a new image.
    environment:
       - DB_USER=user_admin
       - DB_PASSWORD=user_admin_password
       - DB_HOST=postgres
       - DB_NAME=edsproject # Not Neccessary, if not given, will allow access to any Database found in the host.
       - ADMIN_USERS=user_admin
       - POOL_MODE=session
       - DEFAULT_POOL_SIZE=10
       - CLIENT_TLS_SSLMODE=require
       - CLIENT_TLS_KEY_FILE=./ca-key.pem
       - CLIENT_TLS_CERT_FILE=./ca-cert.pem
    ports:
      - "5432:5432"
    depends_on:
      - postgres

[MathiasDrapier]

Thank you so much !
How did you create your own image by putting userlist.txt in it? I got an error:
ERROR could not open auth_file /etc/pgbouncer/userlist.txt: No such file or directory

[omarsumadi]

Here's the Docker Image Creation: (Dockerfile), then just run the docker build with the userlist.txt you want

from edoburu/pgbouncer:latest
# A Userlist is required for PGBouncer to work appropriately.
COPY ./userlist.txt /etc/pgbouncer/
# Gather the OpenSSL Certs and the AWS Cert (More Secure than Command Line Args)
COPY ./ca-certs/openssl /openssl_aws_certs
# Change Permissions
USER root
RUN chown -R postgres:postgres /openssl_aws_certs
USER postgres
# Finished