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)
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
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.