Have Different default_pool_size for two seperate database

[kumarashish071092]

can we add settings in pgbouncer separately for a database which would be different from other databases:

I have two databases :

test
test1

Can I set something with test database is having default_pool_size =20 for all user/database set and test1 database having default_pool_size =40 >

I know it was implemented in few enterprise databases ( EDB,Crunchy etc) . Can we implement this in our playbooks ?

Thanks ,
Ashish

[vitabaks]

Yes, just describe the configuration of your pools in pgbouncer_pools variable, an example:

pgbouncer_pools:
  - { name: "postgres", dbname: "postgres", pool_parameters: "" }
  - { name: "test", dbname: "test", pool_parameters: "pool_size=20 pool_mode=transaction" }
  - { name: "test1", dbname: "test1", pool_parameters: "pool_size=40 pool_mode=transaction" }

Note: Pay attention to pool_mode, it is recommended to use "transaction" if your application supports this pool solver.

And run playbook config_pgcluster.yml to apply the changes

ansible-playbook config_pgcluster.yml --tags "pgbouncer_conf"

[kumarashish071092]

after making the changes in pgbouncer_pools value in main.yml file and reexecuting the playbooks , pgbouncer is not starting . below is the pgbouncer.ini [databases] postgres = host=127.0.0.1 port=35432 dbname=postgres prgx_scs_shared = host=127.0.0.1 port=35432 dbname=prgx_scs_shared default_pool_size=40 * = host=127.0.0.1 port=35432 [pgbouncer] logfile = /var/log/pgbouncer/pgbouncer.log pidfile = /run/pgbouncer/pgbouncer.pid listen_addr = 0.0.0.0 listen_port = 15432 unix_socket_dir = /var/run/postgresql auth_type = scram-sha-256 auth_user = pgbouncer auth_dbname = postgres auth_query = SELECT usename, passwd FROM user_search($1) admin_users = postgres stats_users = postgres ignore_startup_parameters = extra_float_digits,geqo,search_path pool_mode = transaction server_reset_query = DISCARD ALL max_client_conn = 5000 default_pool_size = 20 query_wait_timeout = 0 reserve_pool_size = 1 reserve_pool_timeout = 1 max_db_connections = 400 pkt_buf = 8192 listen_backlog = 4096 log_connections = 0 log_disconnections = 0 what I am missing here?

…

________________________________ From: Vitaliy Kukharik ***@***.***> Sent: Thursday, April 18, 2024 9:16 PM To: vitabaks/postgresql_cluster ***@***.***> Cc: Kumar Ashish ***@***.***>; Author ***@***.***> Subject: Re: [vitabaks/postgresql_cluster] Have Different default_pool_size for two seperate database (Issue #631) [Email from a non-Nagarro source: please exercise caution with links and attachments] Yes, just describe the configuration of your pools in pgbouncer_pools variable, an example: pgbouncer_pools: - { name: "postgres", dbname: "postgres", pool_parameters: "" } - { name: "test", dbname: "test", pool_parameters: "pool_size=20 pool_mode=transaction" } - { name: "test1", dbname: "test1", pool_parameters: "pool_size=40 pool_mode=transaction" } And run playbook config_pgcluster.yml to apply the changes ansible-playbook config_pgcluster.yml --tags "pgbouncer_conf" — Reply to this email directly, view it on GitHub<#631 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AVXUU3Q54ETXCC6NTZV5UIDY57TGBAVCNFSM6AAAAABGNQ3UIGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANRUGMYDOOJZGE>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[vitabaks]

Please attach pgbouncer.log

[kumarashish071092]

the logs are not having any data when pgbouncer is having below value : test= host=127.0.0.1 port=35432 dbname=test default_pool_size=40 this is happening because pgbouncer is not getting started. Apr 19 07:36:08 atl20lv8215pg01.amer.prgx.com pgbouncer[756361]: unrecognized connection parameter: default_pool_size Apr 19 07:36:08 atl20lv8215pg01.amer.prgx.com pgbouncer[756361]: invalid value "host=127.0.0.1 port=35432 dbname=test default_pool_size=40" for parameter test in configuration (/etc/pgbouncer/pgbouncer.ini:5) Apr 19 07:36:08 atl20lv8215pg01.amer.prgx.com pgbouncer[756361]: cannot load config file Apr 19 07:36:08 atl20lv8215pg01.amer.prgx.com systemd[1]: pgbouncer.service: Control process exited, code=exited, status=1/FAIL> Can we some other way to log the error?

…

________________________________ From: Vitaliy Kukharik ***@***.***> Sent: Friday, April 19, 2024 12:39 PM To: vitabaks/postgresql_cluster ***@***.***> Cc: Kumar Ashish ***@***.***>; Author ***@***.***> Subject: Re: [vitabaks/postgresql_cluster] Have Different default_pool_size for two seperate database (Issue #631) [Email from a non-Nagarro source: please exercise caution with links and attachments] Please attach pgbouncer.log — Reply to this email directly, view it on GitHub<#631 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AVXUU3SV4ZGSYJMU3F4HK7DY6C7KVAVCNFSM6AAAAABGNQ3UIGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANRVHA4TMNBRGM>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[vitabaks]

Replace default_pool_size to pool_size, see my example

[kumarashish071092]

worked now. thanks we can close it now

…

________________________________ From: Vitaliy Kukharik ***@***.***> Sent: Friday, April 19, 2024 5:18 PM To: vitabaks/postgresql_cluster ***@***.***> Cc: Kumar Ashish ***@***.***>; Author ***@***.***> Subject: Re: [vitabaks/postgresql_cluster] Have Different default_pool_size for two seperate database (Issue #631) [Email from a non-Nagarro source: please exercise caution with links and attachments] Replace default_pool_size to pool_size — Reply to this email directly, view it on GitHub<#631 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AVXUU3TIPSLSX7WZWPPIGQLY6D77XAVCNFSM6AAAAABGNQ3UIGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANRWGQYDKNRYGA>. You are receiving this because you authored the thread.Message ID: ***@***.***>