Add a pgbouncer sidecar

[damendieta]

Hi, I want to implement a pgboucer sidecar to the kubegres main service.

There is any documentation for this.

Thanks.

[alex-arica]

Thank you for your message.

Kubegres does not support PgBouncer out of the box. You will need to deploy PgBouncer yourself.

For each cluster of Postgres, Kubegres creates 2 services: a Primary service and a Replica service. Each service has a hostname which can be used by client applications (e.g. a java service) to access to the cluster of Postgres.

What you can do is to deploy one PgBouncer Pod in Kubernetes and specify in its host parameter the host name of the Primary service created by Kubegres. And in the same way, you can deploy another PgBouncer Pod connected to the Replica service.

Your client applications can connect to the Primary PgBouncer Pod for write requests and Replica PgBouncer Pod for read-only requests. Alternatively, you can deploy only one PgBouncer Pod connected to the Primary service and your client apps can connect to it for read and write requests. In that case, no need to deploy a second PgBouncer for Replica pods.

Please let me know if that helps.

[alex-arica]

Please feel free to re-open this issue if you have additional questions.

[damendieta]

Hi, thanks.

Finally, I used pod affinity to ensure that the pgbouncer was on the same node as the kubegres primary.

And as I use several pgbouncer for session, transaction and replica, I just include all in the same deployment as sidecars.

It's not ideal, but it works.

Thanks.

[alex-arica]

Thanks for sharing those details.

There is a plan to enable PgBouncer with Kubegres. I will keep you posted when it's available.

[damendieta]

Hi, I really don't think that adding pgbouncer on kubegres will be the solution, documentation about adding pgbouncer with kubegres as a separated deployment with pod affinity may be enough.

The simplicity of Kubegres and the openness to use whatever tool you decide is really cool and keep it that way will really differentiate the project.

An example and documentation, maybe an image. I used edoburu/pgbouncer

For example, the following manifest configures 3 pgbouncer services, two for primary, as I need to access connections with session and others with transaction pooling and one for replica as I only need transaction pooling for replica in order to make reads to replica.

This is a kind of complex configuration, so, it think it may help in most cases.

The important part is the affinity, as it's important that the pgboucer service falls in the same host as the primary database service.

      affinity:
        podAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 100
            podAffinityTerm:
              labelSelector:
                matchExpressions:
                - key: app
                  operator: In
                  values:
                  - kubegres
                - key: replicationRole
                  operator: In
                  values:
                  - primary
              topologyKey: "kubernetes.io/hostname"

• pgbouncer.yaml

apiVersion: v1
kind: Service
metadata:
  name: postgresql-svc
  labels:
    app: postgresql
spec:
  ports:
    - name: transaction-prt
      port: 5432
      targetPort: transaction-prt
    - name: session-prt
      port: 5433
      targetPort: session-prt
    - name: replica-prt
      port: 5434
      targetPort: replica-prt
  selector:
    app: postgresql
  clusterIP: None
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgresql-dpy
spec:
  selector:
    matchLabels:
      app: postgresql
  replicas: 1
  template:
    metadata:
      labels:
        app: postgresql
    spec:
      affinity:
        podAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 100
            podAffinityTerm:
              labelSelector:
                matchExpressions:
                - key: app
                  operator: In
                  values:
                  - kubegres
                - key: replicationRole
                  operator: In
                  values:
                  - primary
              topologyKey: "kubernetes.io/hostname"
      containers:
      - name: transaction-ctn
        image: edoburu/pgbouncer:1.15.0
        ports:
          - containerPort: 5432
            name: transaction-prt
        env:
          - name: DB_USER
            valueFrom:
              secretKeyRef:
                key: DB_USER
                name: postgres-access-keys
          - name: DB_PASSWORD
            valueFrom:
              secretKeyRef:
                key: DB_PASSWORD
                name: postgres-access-keys
          - name: DB_HOST
            value: kubegres
          - name: POOL_MODE
            value: transaction
          - name: AUTH_TYPE
            value: plain
      - name: session-ctn
        image: edoburu/pgbouncer:1.15.0
        # ALYWAYS: pull if not present.
        imagePullPolicy: IfNotPresent
        resources:
          requests:
            cpu: 50m
            memory: 50Mi
        ports:
          - containerPort: 5433
            name: session-prt
        env:
          - name: LISTEN_PORT
            value: '5433'
          - name: DB_USER
            valueFrom:
              secretKeyRef:
                key: DB_USER
                name: postgres-access-keys
          - name: DB_PASSWORD
            valueFrom:
              secretKeyRef:
                key: DB_PASSWORD
                name: postgres-access-keys
          - name: DB_HOST
            value: kubegres
          - name: POOL_MODE
            value: session
          - name: AUTH_TYPE
            value: plain
      - name: replica-ctn
        image: edoburu/pgbouncer:1.15.0
        # ALYWAYS: pull if not present.
        imagePullPolicy: IfNotPresent
        resources:
          requests:
            cpu: 50m
            memory: 50Mi
        ports:
          - containerPort: 5434
            name: replica-prt
        env:
          - name: LISTEN_PORT
            value: '5434'
          - name: DB_USER
            valueFrom:
              secretKeyRef:
                key: DB_USER
                name: postgres-access-keys
          - name: DB_PASSWORD
            valueFrom:
              secretKeyRef:
                key: DB_PASSWORD
                name: postgres-access-keys
          - name: DB_HOST
            value: kubegres-replica
          - name: POOL_MODE
            value: transaction
          - name: AUTH_TYPE
            value: plain

I hope it helps someone, thanks for this great project.

[alex-arica]

Thank you for those details. We will add documentation using your suggestions.

I agree with you that the philosophy behind Kubegres is simplicity. And this is the reason why we haven't made assumptions on behalf of companies using PostgreSql.

[damendieta]

Thanks to you.

If I can be of any help, I'll be glad to cooperate.

Have a nice day.

[alex-arica]

Thank you for suggesting helping.

Kubegres website is a simple HTML page available in this repo: https://github.com/reactive-tech/kubegres-website

Currently we have the following sub-menu options under "Documentations" menu:

• Getting started
• All properties explained
• Custom env-vars
• Override default configs
• Replication and failover
• Enable back-up

Perhaps we can add a new sub-menu PGbouncer ?

Any suggest is welcome.