search

vitabaks / postgresql_cluster

PostgreSQL High-Availability Cluster (based on "Patroni" and DCS "etcd" or "consul"). Automating with Ansible.
MIT License
1.29k stars 352 forks source link

Have Different default_pool_size for two seperate database #631

Closed kumarashish071092 closed 3 weeks ago

kumarashish071092 commented 3 weeks ago

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 commented 3 weeks ago

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 commented 3 weeks ago

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

[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:

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 GitHubhttps://github.com/vitabaks/postgresql_cluster/issues/631#issuecomment-2064307991, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVXUU3Q54ETXCC6NTZV5UIDY57TGBAVCNFSM6AAAAABGNQ3UIGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANRUGMYDOOJZGE. You are receiving this because you authored the thread.Message ID: @.***>

vitabaks commented 3 weeks ago

Please attach pgbouncer.log

kumarashish071092 commented 3 weeks ago

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 GitHubhttps://github.com/vitabaks/postgresql_cluster/issues/631#issuecomment-2065896413, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVXUU3SV4ZGSYJMU3F4HK7DY6C7KVAVCNFSM6AAAAABGNQ3UIGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANRVHA4TMNBRGM. You are receiving this because you authored the thread.Message ID: @.***>

vitabaks commented 3 weeks ago

Replace default_pool_size to pool_size, see my example

kumarashish071092 commented 3 weeks ago

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 GitHubhttps://github.com/vitabaks/postgresql_cluster/issues/631#issuecomment-2066405680, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVXUU3TIPSLSX7WZWPPIGQLY6D77XAVCNFSM6AAAAABGNQ3UIGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANRWGQYDKNRYGA. You are receiving this because you authored the thread.Message ID: @.***>