Search code examples
javapostgresqlpgbouncer

pgbouncer with currentSchema

Java application works with two shemas: public and quartz. And i try connect to shema quartz with pgbouncer:

jdbc:postgresql://127.0.0.1:6432/test_db?currentSchema=quartz;prepareThreshold=0"

But my app failed startup. In logs i see error about table (qrtz_locks) doesn't exist (log follow)enter image description here

This table exist in shema quartz. I think that app ignored my props in jdbc - ?currentSchema=quartz and connect to public. If i used default postgres connect (port 5432), app works correctly.

How to fix my problem and connect to db via pgbouncer with my schema?

This is pgbouncer.ini config

[databases]
; fallback connect string
* = host=10.1.2.2 port=5432

[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

listen_addr = *
listen_port = 6432

auth_type = md5
auth_file = /etc/pgbouncer/userlist
auth_hba_file = /etc/pgbouncer/pb_hba.conf
admin_users = postgres
stats_users = postgres

pool_mode = transaction
ignore_startup_parameters = extra_float_digits,search_path
application_name_add_host = 1
max_client_conn = 500
default_pool_size = 15
reserve_pool_size = 10
reserve_pool_timeout = 3

server_lifetime = 300
server_idle_timeout = 120
server_connect_timeout = 5
server_login_retry = 1

query_timeout = 60
query_wait_timeout = 60

client_idle_timeout = 60
client_login_timeout = 60
Solution

  • pgbouncer does not support this directly.

    You could make your system issue "set search_path=quartz" as the first statement every time a new connection is obtained. Or you could use the other approach you have already described but didn't like.