Facing Issue while using pgbouncer:- prepared statement does not exist

[tarunmittal24]

Please, answer some short questions which should help us to understand your problem / question better?

• Which image of the operator are you using?
  -----> registry.opensource.zalan.do/acid/postgres-operator:v1.9.0

• Where do you run it - cloud or metal? Kubernetes or OpenShift?
  ----> AWS K8s [EKS]

• Are you running Postgres Operator in production?
  ---> yes

• Type of issue? [Bug report, question, feature request, etc.]
  ----> question

Facing Issue:- Error fetching folios ERROR: prepared statement stmtcache_12 does not exist (SQLSTATE 26000)
After Checking get to know about the max_prepared_statements changes in the pgbouncer config file, but that feature is only available in the latest release of the pgbouncer [1.21.0] (https://www.pgbouncer.org/2023/10/pgbouncer-1-21-0)

Now the latest image of the zalando pgbouncer registry is:- registry.opensource.zalan.do/acid/pgbouncer:master-30, that is using the 1.20.1 version of the pgbouncer

Output

pgbouncer -V

PgBouncer 1.20.1
libevent 2.1.12-stable
adns: c-ares 1.19.1
tls: OpenSSL 3.1.3 19 Sep 2023

So can we get the new version of the pgbouncer or if any other way by which we can solve this

prepared statement does not exist issue

My Config-File:-

apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: postgres-cluster
spec:
  teamId: "postgres"
  volume:
    size: 30Gi
  numberOfInstances: 2
  users:
   testbetauser:
    - createdb
    test-superuser:
    - superuser
  preparedDatabases:
    test_database: {}
  enableLogicalBackup: true
  postgresql:
    version: "14"
    parameters:
      shared_preload_libraries: "pgaudit,pg_stat_statements,pg_stat_kcache"
  connectionPooler:
    numberOfInstances: 1
    mode: "transaction"
    schema: "pooler"
    user: "pooler"
  env:
## Below Values will be constant mostly, changed it if required 
  - name: WAL_BUCKET_SCOPE_SUFFIX
    value: ""
  - name: USE_WALG_BACKUP
    value: "true"
  - name: USE_WALG_RESTORE
    value: "true"
  - name: AWS_S3_FORCE_PATH_STYLE
    value: "true"
  - name: AWS_ENDPOINT
    value: https://s3.ap-south-1.amazonaws.com:443
  - name: AWS_REGION
    value: ap-south-1
  - name: WALG_DISABLE_S3_SSE
    value: "true"
  - name: CLONE_USE_WALG_RESTORE
    value:  "true"
  - name: CLONE_AWS_ENDPOINT
    value: https://s3.ap-south-1.amazonaws.com:443
  - name: CLONE_METHOD
    value: CLONE_WITH_WALE
  - name: CLONE_WAL_BUCKET_SCOPE_PREFIX
    value: ""
  - name: CLONE_AWS_S3_FORCE_PATH_STYLE
    value: "true"
  ## Change below values, as per need and requirement.
  - name: BACKUP_NUM_TO_RETAIN
    value: "10"
  - name: BACKUP_SCHEDULE
    value: '0 */12 * * *'
  - name: WAL_S3_BUCKET
    value: test-bucker
  - name: AWS_ACCESS_KEY_ID
    valueFrom:
      secretKeyRef:
        name: postgres-backup-to-s3-secret
        key: AWS_ACCESS_KEY_ID
  - name: AWS_SECRET_ACCESS_KEY
    valueFrom:
      secretKeyRef:
        name: postgres-backup-to-s3-secret
        key: AWS_SECRET_ACCESS_KEY

[FxKu]

Let me check if we can easily bump the pgbouncer version in our pooler image.

[tarunmittal24]

any updates @FxKu ??

[FxKu]

I have bumped our internal image to use version 1.22.0. I will bump the version in the config for the next operator release next week.

[wzrdtales]

support for max_prepared_statements is still not there though @FxKu or is there a generic way to pass unknown configs to the pooler

[tamcore]

Can confirm. Please reopen this issue, as it is not resolved.

pgbouncer master-32 is deployed, but Harbor is complaining about prepared statement ... does not exist

How can we configure the max_prepared_statements setting, required by pgbouncer?

[baznikin]

bump! some way needed to configure pgBouncer to use prepared statements

[baznikin]

Workaround: build own container

FROM registry.opensource.zalan.do/acid/pgbouncer:master-32
RUN echo "max_prepared_statements = 200" >> /etc/pgbouncer/pgbouncer.ini.tmpl