pgbouncer with currentSchema

Question

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

Answer 1

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.

Answer 2

Transaction connection mode pooling may cause the 'currentschema='xxxx'' parameter specified in JDBC to be ineffective, resulting in connections to the default 'public' schema of the database.

The solution is to specify the default login schema for the relevant account.

Using your JDBC example:

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

Modify this string to:

jdbc:postgresql://[email protected]:6432/test_db 
 ?currentSchema=quartz;prepareThreshold=0"

The added pg_quartz_admin is the login account, and it should have the necessary permissions for operations under the quartz schema.

Then, use the postgres account to change the default login schema for pg_quartz_admin:

ALTER ROLE pg_quartz_admin IN DATABASE test_db SET search_path TO quartz;

Creating a new account and setting the default login schema might seem like a cumbersome way to manage accounts, but it's currently the best solution I can think of.Hope for a better solution.