Wiring up datadog with render's pgbouncer

[minsoo_belfry]

Hi,

We are using render-oss/docker-pgbouncer for connection pooling.

We ran into a max client connection issue that brought down our system, so we wanted to integrate pgbouncer with datadog for better observability.

We want to give datadog username access to pgbouncer so that the datadog agent has permissions to start publishing pgbouncer metrics onto its platform. We’ve also modified the datadog agent deployed through render by adding the pgbouncer specific config file to our datadog agent config as instructed in the configuration section

To achieve this, we wanted to stand up pgbouncer via render.yaml like so:

  - type: pserv
    name: pgbouncer2
    region: ohio
    runtime: docker
    plan: standard
    dockerfilePath: ./backend/pgbouncer/pgbouncer.dockerfile
    dockerContext: ./backend
    envVars:
      - key: DATABASE_URL
        fromDatabase:
          name: postgres_prod
          property: connectionString
      - key: POOL_MODE
        value: transaction
      - key: SERVER_RESET_QUERY
        value: DISCARD ALL
      - key: MAX_CLIENT_CONN
        value: 500
      - key: DEFAULT_POOL_SIZE
        value: 30
      - key: STATS_USERS
        value: datadog

where docker file: backend/pgbouncer/pgbouncer.dockerfile is defined as:

FROM edoburu/pgbouncer
COPY ./pgbouncer/userlist.txt /etc/pgbouncer/

and the ./pgbouncer/userlist.txt contains the username and password (“datadog” user and password) as instructed in the render pgbouncer github repo.

When we deploy the resulting render.yaml in our preview env, we get the following error message:

/entrypoint.sh: line 56: can't create /etc/pgbouncer/userlist.txt: Permission denied

It appears that we lack the permissions to create our own userlist even though the render github recommends it as a viable option.

Related github post (Failure to generate userlist.txt · Issue #33 · edoburu/docker-pgbouncer · GitHub) appears to have a solution for Kubernetes, but I wanted to learn whether:

1. There is a workaround for the permissions issue around creating our own userlist in the render context.
2. Are we even on the right track? Do you have a canonical way of integrating pgbouncer with datadog in render context that you recommend?

Thank you for your time!

[mmaddex]

Hi there,

Thanks for all the details. I’ll take a stab at setting this up and let you know if I find a solution.

Regards,

Matt

[minsoo_belfry]

Thank you Matt! And also appreciate your help via the email and your recommendations - we’re trying various approaches at the moment to get a better handle on this area of our system.

[mmaddex]

Hello again,

I see those instructions in the README, which actually come from the original repo. So troubleshooting that approach might make more sense with a Github issue to that original repo.

However, I took a quick look and I’m wondering if a simpler approach would be to just fork the example repo and make a few additions.

It looks like userlist.txt is being populated in entrypoint.sh: https://github.com/render-oss/docker-pgbouncer/blob/master/entrypoint.sh#L56.

If it were me, I would try to mimic that approach and just add another similar line for your datadog user. You could base the username/password off of environment variables which you could configure in the Dashboard.

Let me know what you think about that approach.

Regards,

Matt

[minsoo_belfry]

Thank you Matt! I’ll take a stab at that and report back here.

[minsoo_belfry]

Hi Matt,

Thanks again for your suggestion! Your recommendation worked beautifully. Documenting the solution here for the future.

Steps:

1. I forked GitHub - edoburu/docker-pgbouncer: Minimal PgBouncer image that is easy to configure and made the following change in docker-pgbouncer/entrypoint.sh:

  ...
  echo "\"$DB_USER\" \"$pass\"" >> ${PG_CONFIG_DIR}/userlist.txt  
  # Start fork logic - Belfry
  STAT_DB_USER="datadog"
  if [-n $STAT_DB_PASSWORD]; then
    MD5_STAT_PASSWORD="md5$(echo -n "$STAT_DB_PASSWORD$STAT_DB_USER" | md5sum | cut -f 1 -d ' ')"
    echo "\"$STAT_DB_USER\" \"$MD5_STAT_PASSWORD\"" >> ${PG_CONFIG_DIR}/userlist.txt
  fi
  # End fork logic - Belfry
  ...
  echo "Wrote authentication credentials to ${PG_CONFIG_DIR}/userlist.txt"

2. In order for $STAT_DB_PASSWORD to be read, I added the STAT_DB_PASSWORD as an env var as follows:

  - type: pserv
    name: pgbouncer_async_2
    region: ohio
    runtime: docker
    plan: standard
    repo: https://github.com/BelfrySoftware/docker-pgbouncer
    envVars:
      - key: DATABASE_URL
        fromDatabase:
          name: postgres_prod
          property: connectionString
      - key: POOL_MODE
        value: session
      - key: SERVER_RESET_QUERY
        value: DISCARD ALL
      - key: MAX_CLIENT_CONN
        value: 500
      - key: DEFAULT_POOL_SIZE
        value: 40
      - key: IGNORE_STARTUP_PARAMETERS
        value: jit
      - key: STATS_USERS
        value: datadog
      - key: STAT_DB_PASSWORD
        generateValue: true

3. I have not set up docker secrets yet, but as a matter of end-to-end transparency, here is the current datadog agent setup in render.yaml. I use the generated password and pass it to datadog agent:

 - type: pserv
    name: datadog
    env: docker
    region: ohio
    plan: standard
    previewPlan: starter
    dockerfilePath: ./backend/datadog.dockerfile
    dockerContext: ./backend
    numInstances: 1
    buildFilter:
      paths:
        - backend/datadog.dockerfile
    envVars:
      - fromGroup: Prod
      - key: DD_LOG_LEVEL
        value: info
        previewValue: off
      - key: DD_APM_ENABLED
        value: true
        previewValue: false
      - key: PGBOUNCER_ASYNC_SERVER
        fromService:
          name: pgbouncer_async_2
          type: pserv
          property: host
      - key: POSTGRES_DB
        fromDatabase:
          name: postgres_prod
          property: database
      - key: POSTGRES_PORT
        fromDatabase:
          name: postgres_prod
          property: port
      - key: STAT_DB_PASSWORD
        fromService:
          name: pgbouncer_async_2
          type: pserv
          envVarKey: STAT_DB_PASSWORD

4. My datadog.dockerfile looks as follows:

FROM datadog/agent:7
ARG PGBOUNCER_ASYNC_SERVER
ARG POSTGRES_DB
ARG POSTGRES_PORT
ARG STAT_DB_PASSWORD

# An attempt to setup pgbouncer config
ENV DATABASE_URL=postgresql://datadog:$STAT_DB_PASSWORD@$PGBOUNCER_ASYNC_SERVER:$POSTGRES_PORT/pgbouncer
COPY ./pgbouncer_dd.yaml /etc/datadog-agent/conf.d/pgbouncer.d/conf.yaml
RUN sed -i "s|DATABASE_URL|${DATABASE_URL}|g" /etc/datadog-agent/conf.d/pgbouncer.d/conf.yaml
...

pgbouncer_dd.yaml:

init_config:

instances:
  - database_url: DATABASE_URL

5. When deployed to preview env, you can run cat /etc/pgbouncer/userlist.txt on your pgbouncer resource within the terminal to ensure that the datadog user + generated password is populated. Within the datadog agent resource, you can run agent status to check to make sure that datadog agent was able to successfully access pgbouncer.

pgbouncer (6.1.0)
    -----------------
      Instance ID: pgbouncer:6ee89549c365f1a6 [OK]
      Configuration Source: file:/etc/datadog-agent/conf.d/pgbouncer.d/conf.yaml
      Total Runs: 6
      Metric Samples: Last Run: 4, Total: 24
      Events: Last Run: 0, Total: 0
      Service Checks: Last Run: 1, Total: 6
      Average Execution Time : 63ms
      Last Execution Date : 2024-02-14 18:35:28 UTC (1707935728000)
      Last Successful Execution Date : 2024-02-14 18:35:28 UTC (1707935728000)
      metadata:
        version.major: 1
        version.minor: 21
        version.patch: 0
        version.raw: 1.21.0
        version.scheme: semver

6. Finally, in the datadog UI, navigate to Integrations and install the PgBouncer integration. You should then be able to see a dashboard that looks like below.
   
   

Thanks again for your help and I hope that this helps someone else in the future.

Best,

Minsoo