Postgres-operator: [Pooler] Allow user to specify `ignore_startup_parameters` for pgbouncer ini

[RichieSams]

When connecting to the pgpooler instance with many modern pg clients, the connection fails, due to the clients setting extra_float_digits. The solution is to set

ignore_startup_parameters = extra_float_digits

Under the [pgbouncer] section. This tells pgbouncer to ignore that parameter and just pass it on to the db.

However, as far as I can tell, the zalando pgbouncer image doesn't allow extra parameters to be set. It uses a tmpl file to generate the ini file from ENV variables:

/ $ cat /etc/pgbouncer/pgbouncer.ini.tmpl 
# vim: set ft=dosini:

[databases]
* = host=$PGHOST port=$PGPORT auth_user=$PGUSER

[pgbouncer]
pool_mode = $CONNECTION_POOLER_MODE
listen_port = $CONNECTION_POOLER_PORT
listen_addr = *
auth_type = plain
auth_file = /etc/pgbouncer/auth_file.txt
admin_users = $PGUSER
auth_query = SELECT * FROM $PGSCHEMA.user_lookup($1)
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

server_tls_sslmode = require
server_tls_ca_file = /etc/ssl/certs/pgbouncer.crt
server_tls_protocols = secure
client_tls_sslmode = require
client_tls_key_file = /etc/ssl/certs/pgbouncer.key
client_tls_cert_file = /etc/ssl/certs/pgbouncer.crt

log_connections = 0
log_disconnections = 0

# How many server connections to allow per user/database pair.
default_pool_size = $CONNECTION_POOLER_DEFAULT_SIZE

# Add more server connections to pool if below this number. Improves behavior
# when usual load comes suddenly back after period of total inactivity.
min_pool_size = $CONNECTION_POOLER_MIN_SIZE

# How many additional connections to allow to a pool
reserve_pool_size = $CONNECTION_POOLER_RESERVE_SIZE

# Maximum number of client connections allowed.
max_client_conn = $CONNECTION_POOLER_MAX_CLIENT_CONN

# Do not allow more than this many connections per database (regardless of
# pool, i.e. user)
max_db_connections = $CONNECTION_POOLER_MAX_DB_CONN

# If a client has been in "idle in transaction" state longer, it will be
# disconnected. [seconds]
idle_transaction_timeout = 600

# If login failed, because of failure from connect() or authentication that
# pooler waits this much before retrying to connect. Set it a bit higher than
# leader key ttl. [seconds]
server_login_retry = 60

envsubst < /etc/pgbouncer/pgbouncer.ini.tmpl > /etc/pgbouncer/pgbouncer.ini

I'd be willing to add the appropriate changes to postgres-operator in a PR, if you guys can update the pgbouncer docker image to support passing ignore_startup_parameters via an ENV variable.

[Jan-M]

Thanks for raising, new image will ignore this parameter as this sounds like a safe default.

Please do keep in mind that you can always create your own Docker images.

[FxKu]

@RichieSams the new image is merged (tag: master-8) which sets:

ignore_startup_parameters = "extra_float_digits, options"

Still, not configurable, but I hope, it solves your issue.

[RichieSams]

Thanks!!

[FxKu]

Closing it then. Feel free to reopen if you need this to be configurable.

[ilya-lesikov]

@FxKu Hi, looks like it should be something like ignore_startup_parameters = extra_float_digits,options instead. Feels like it treats these parameters as a single string when you quote them. Quoted version didn't work for me at least.

[redscaresu]

@FxKu Hi, can confirm this issue is still occurring on the latest image master-8 having tested it today.