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

The connection is getting timeout with errors while making conenctions with pgbouncer #637

Open kumarashish071092 opened 3 weeks ago

kumarashish071092 commented 3 weeks ago

I am getting below errors in pgbouncer.log and at the same time , connection is timed out at application level.

2024-04-23 01:40:12.304 EDT [3702136] LOG S-0x56383eb72410: testclientc_supplier_connect_uat/devopsdeploy@127.0.0.1:35432 closing because: client disconnect while server was not ready (age=39s)
2024-04-23 01:40:12.447 EDT [3702136] LOG S-0x56383eb72ed0: testclientc_supplier_connect_uat/devopsdeploy@127.0.0.1:35432 closing because: client disconnect while server was not ready (age=39s)
2024-04-23 01:40:12.493 EDT [3702136] LOG S-0x56383eb72c20: testclientc_supplier_connect_uat/devopsdeploy@127.0.0.1:35432 closing because: client disconnect while server was not ready (age=39s)
2024-04-23 01:40:12.542 EDT [3702136] LOG S-0x56383eb726c0: testclientc_supplier_connect_uat/devopsdeploy@127.0.0.1:35432 closing because: client disconnect while server was not ready (age=39s)
2024-04-23 01:40:12.619 EDT [3702136] LOG S-0x56383eb72970: testclientc_supplier_connect_uat/devopsdeploy@127.0.0.1:35432 closing because: client disconnect while server was not ready (age=39s)
2024-04-23 01:40:12.704 EDT [3702136] LOG S-0x56383eb73180: testclientc_supplier_connect_uat/devopsdeploy@127.0.0.1:35432 closing because: client disconnect while server was not ready (age=38s).

pgbouncer.ini : 

[databases]
postgres = host=127.0.0.1 port=35432 dbname=postgres
prg_scs_shared_uat = host=127.0.0.1 port=35432 dbname=prg_scs_shared_uat pool_size=40 max_db_connections=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 = ''
max_prepared_statements = 50
max_client_conn = 2000
default_pool_size = 20
query_wait_timeout = 40
query_timeout = 50
reserve_pool_size = 0
reserve_pool_timeout = 0
max_db_connections = 20
pkt_buf = 8192
listen_backlog = 4096
log_connections = 1
log_disconnections = 1
server_idle_timeout = 600

we are using r2dbc with URL having prepareThreshold=0&pgbouncer=true&preparedStatementCacheQueries=0 in it

vitabaks commented 3 weeks ago

Hello @kumarashish071092 This discussion goes beyond automatization.

Contact your DBA for help, or consider purchasing a sponsorship subscription that includes individual support.