pgbouncer read write in one place

[dragoangel]

• Which image of the operator are you using? ghcr.io/zalando/postgres-operator:v1.12.2
• Where do you run it - cloud or metal? Kubernetes or OpenShift? Bare Metal K8s
• Are you running Postgres Operator in production? no
• Type of issue? feature request

PgBouncer allows setting in one instance different hosts for reads and writes, and technically there no need in enableReplicaConnectionPooler, because one pgbouncer are handling both reads and writes in a way:

[databases]
reads = host=mycluster-repl port=5432 auth_user=connpuller
writes = host=mycluster port=5432 auth_user=connpuller

To achieve it we need:

1. update acid/pgbouncer docker image to accept PGHOST_READS and PGHOST_WRITES
2. change /etc/pgbouncer/pgbouncer.ini.tmpl to include proposed reads\writes separation
3. change postgres-operator to remove or depracate enableReplicaConnectionPooler (we can in depracate mode create service that will route to same pgbouncer that handles reads\writes)
4. change postgres-operator to use newer version of acid/pgbouncer and set both PGHOST_READS and PGHOST_WRITES. If cluster has only 1 node, which means only one master without replicas - we can set reads and writes to master for simplicity

[dragoangel]

Looks like I missread some post, it can't do this way