Questions tagged [pgbouncer]
For questions about PgBouncer, a connection pooler for PostgreSQL.
75
questions
0
votes
2
answers
55
views
PostgreSQL: Pgbouncer authentication
We are using authentication file mode in pgbouncer. The authentication part of Pgbouncer is so confusing. Would be helpful if anybody can throw some light on my below queries.
Are Pgbouncer ...
- 13
0
votes
1
answer
21
views
How to add information about two same named databases on different hosts in PgBouncer config file?
I want to add details for databases in a config file of pgbouncer.
I have two same database names but their hosts are different, as there is no information about server endpoints in the config file, ...
0
votes
0
answers
38
views
How to configure database specific settings with PgBouncer?
We have a server running multiple services and we would want to use pretty short client_idle_timeout for one backend database and unlimited client_idle_timeout for another backend database. However, ...
0
votes
0
answers
81
views
Upgrade Pgbouncer
I need some advice for upgrading pgbouncer. I couldn't find any document which tells me how to upgrade pgbouncer. I tried the command
apt install pgbouncer
I wonder if there is another good, ...
user274715
2
votes
1
answer
252
views
Can I have a PgBouncer connection pool per db instead of per role/db pair?
Users of my web app can create kubernetes jobs from the web app's UI. Each user has a dedicated role and password in a postgres database (hosted outside of the k8s cluster). Having a dedicated role ...
- 21
0
votes
1
answer
255
views
HA 3-Node Postgres cluster on VMs with Repmgr
We are considering a 3-node Postgres setup where 1-master and 2-standby with replication and master failover handled by repmgr and connection pooling with Pgbouncer.
Is there a way to let our ...
- 1
0
votes
1
answer
1k
views
Patroni Haproxy and pg-bouncer
I'm building a Patroni Postgres cluster; however, I can't decide which is the better architecture:
App > HAproxy > Pgbouncer > PostgreSQL (Patroni)
App > PGBouncer > HAproxy > ...
0
votes
1
answer
261
views
pgbouncer pool going over default_pool_size
Recently, our DB server got hit by high load. When I went to investigate, there were 49 queries from the same user for the same database. I checked our pgbouncer.ini, and sure enough, ...
- 203
0
votes
0
answers
782
views
Evaluating AWS RDS Proxy latency overhead
Evaluating some benchmark tests I noticed that there is latency overhead in the transactions to the database using the service provided by AWS: RDS Proxy.
The tests were performed against a Postgres ...
- 101
0
votes
1
answer
1k
views
Can't connect to pgBouncer running in Docker [closed]
I'm not great at Docker so please bear with me.
I have a Postgres database running and exposed to the public, I now want to use PgBouncer to connect to it. The catch is that I can't use docker compose....
0
votes
1
answer
306
views
Is there a way to get the IP address of a client virtual machine in Postgresql?
I can get the IP address if the Postgres connection is done from the 5432 port.
Now If I connect Postgres with 6432 port means the connection comes from pgbouncer at this time I'm not getting the IP ...
0
votes
2
answers
581
views
Parameterized Query without Prepared Statements and PgBouncer
my app is written in Go and I use PgBouncer as a connection pool to my Postgres Database. One downside of PgBouncer, in transaction mode, is that I can't use prepared statements.
So I have 2 options, ...
- 29
1
vote
1
answer
1k
views
PgBouncer accumulating multiple connections in IDLE
I use a PostgreSQL database managed by DigitalOcean, and to connect to it I use the connection pool provided by them.
The pooling system they use is PgBouncer, and the pooling model I use is ...
- 408
2
votes
1
answer
3k
views
Postgres connections are in "idle" state but cannot find any connected client
I have a single Postgres server and a set of PgBouncers configured to help me with connection pooling.
Recently, I discovered that the database connections are getting accumulated in "idle" ...
- 123
0
votes
1
answer
150
views
Restricted Functions for Use with Sessions in PostgreSQL + PgBouncer
I've always used PgBouncer in session mode, but its performance isn't so good, as PHP scripts that run for a long time end up with a long session connected to the database (even for minutes), without ...
- 408
1
vote
0
answers
3k
views
Is it good practice to use both PGBouncer and PGPool together?
We have setup a streaming replication on Postgres 13 and it all works fine. Current setup is as follows.
We have one Primary Postgres and one Secondary Postgres connected via streaming replication. ...
- 111
0
votes
0
answers
458
views
PostgreSQL issues in big production environment
I really someone can help me out.
We have a PGSQL database running in a production environment. There are about 1000+ users, spread over +/- 20 terminal servers.
I'm running into few issues, but the ...
- 11
0
votes
1
answer
646
views
What is the correct setting for max_client_conn using session pool mode in pgBouncer?
Without diving so much into web applications or database performance tuning etc, I wish to consult on one thing. pgBouncer has the following settings:
;; Total number of clients that can connect
...
- 115
2
votes
1
answer
3k
views
What is the difference between "server_lifetime" and "server_idle_timeout"?
I am trying to tweak my pgbouncer.ini file in Pgbouncer and came up with server_lifetime and server_idle_timeout parameters which I felt the same.
In the official configuration document it says:
...
- 123
1
vote
1
answer
678
views
Will pgbouncer allow multiple pools to same database pool name but different users?
or, in other words, is it possible to do this:
[databases]
db1 = user=usr1 pool_size=10 application_name=app1 dbname=data1 ...
db1 = user=usr2 pool_size=20 application_name=app2 dbname=data1 ...
...
- 299
0
votes
1
answer
2k
views
pgBouncer receiving Error Query_Wait_Timeout in log - when running pgBench on Postgres 9.6
I am testing pbBouncer by utilising pgBench with a very large database.
I initially setup pgBench with a large database (731GB) - turns outs its just a huge accounts table (626GB)
./pgbench -h 172.10....
- 429
0
votes
1
answer
4k
views
Get the last ID after INSERT the PostgreSQL database with PgBouncer
Previously we used PostgreSQL 9 with PgBouncer in session mode, and with that was able to easily create a new record and get the last ID with pg_get_serial_sequence and CURRVAL.
INSERT INTO "...
- 408
0
votes
1
answer
1k
views
pgBouncer - multiple servers with same username but different password
I have an active pgBouncer setup with one PostgreSQL server.
We are planning to add one more PostgreSQL server to the same pgBouncer. But the problem is that both databases servers have the same ...
- 1,910
1
vote
1
answer
360
views
Reusing the same connection to read uncommited transaction
My question is similar to: how to spy on a transaction that is not yet committed in postgres for debugging
What I tried:
I start a pgbouncer instance, and set the max-db-connection to 1
From my node ...
- 11
1
vote
3
answers
3k
views
Prepared statements with pgBouncer
I have a PostgreSQL server with pgBouncer as connection pooler.
My application is running on Elixir.
This is my config file for pgBouncer:
* = host=X.X.X.X port=5432
logfile = /var/log/postgresql/...
- 1,910
1
vote
2
answers
31
views
Understanding “server”
pgBouncer docs note:
pool_mode
Specifies when a server connection can be reused by other clients.
session:
Server is released back to pool after client disconnects. Default.
transaction:
...
- 495
3
votes
1
answer
18k
views
PgBouncer logging details for `query_wait_timeout` error
Regarding this type of error in PgBouncer log:
2020-03-10 03:46:01.730 EDT [25305] LOG C-0xa5cf00: testdb/[email protected]:49214 closing because: query_wait_timeout (age=120s)
2020-03-10 03:46:...
- 397
0
votes
1
answer
3k
views
Can't accept pgbouncer 6432 port on PostgreSQL server
pgbouncer server (440a:90a0:302:4525:lda0:1:2:3)
pgbouncer.ini
[databases]
app_db = host=pg_server port=5432 dbname=app_db
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
...
- 309
0
votes
2
answers
371
views
Is it possible to make a high availability PostgreSQL database cluster by open source softwares?
If use these tools can make a great modern database cluster with full features:
pgbouncer (with HAProxy for HA)
repmgr
barman
The main database will run on the repmgr servers. It has primary and ...
- 309
1
vote
1
answer
2k
views
Why can't start pgbouncer process when setting logfile to /var/log/ path?
This pgbouncer.ini setting can't start pgbouncer:
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = userlist....
- 309
0
votes
1
answer
488
views
Why Jira can't connect database with pgBouncer?
pgbouncer.ini
[databases]
jiradb = host=127.0.0.1 port=5432 dbname=jiradb
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = userlist.txt
logfile = pgbouncer.log
...
- 309
0
votes
1
answer
326
views
Handle no more connections or out of shared memory
I have a golang program that does computation on data in multiple threads at once, all pulling their data from Postgres. The number of threads depends on a previous result. There can therefore be ...
- 5
1
vote
1
answer
4k
views
How to do automatic failover for PostgreSQL with application automatic switch?
There are two ways to try.
Method 1
Use JDBC connect the PostgreSQL cluster (primary, standby 2 servers only):
jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/jiradb
But failed:
The ...
- 309
2
votes
1
answer
4k
views
Is it possible to configure pgbouncer to distribute queries among master and slave?
We have master (write-read) and slave (readonly) Postgres 10 databases. Slave is online replication from master.
Now our application (c# webapi) connects to master only directly. We want to execute ...
- 121
0
votes
0
answers
2k
views
pgBouncer exceeds connection limit even with idle connections
I'm using pgBouncer to balance connections in PostgreSQL.
Sets a limit of 2048 connections in the PostgreSQL main configuration and also in pgBouncer.
It happens that there are several connections ...
- 408
7
votes
2
answers
18k
views
Pgbouncer getting auth failing when trying to connect using psql
I'm trying to configure the latest pgbouncer to work with postgres 9. I can connect to my db using psql with the right password but when I use psql -p 6432 I can't connect with the error message of ...
- 111
0
votes
1
answer
2k
views
HA PostgreSQL installation using patroni and pgbouncer
I'm not having a problem yet, just searching for advise
I want to build installation of PostgreSQL on 3 nodes using patroni: master and 2 standbys (for failover only). There are PostgreSQL and ...
2
votes
0
answers
530
views
Should I connect to my replica through PgBouncer?
We're using a setup of one Postgresql master server and a several replicas to be able to withstand master failure. Connection to master server is made through PgBouncer.
Currently the idea is that ...
- 127
8
votes
1
answer
15k
views
PgBouncer "no more connections allowed (max_client_conn)"
I am using PostgreSQL with PgBouncer, and receive the following error in my application:
08P01: no more connections allowed (max_client_conn)
pgbouncer.ini has the following configuration:
...
- 247
1
vote
0
answers
740
views
"Unclean server" connections not being cleaned up
I have an application connecting to a PostgreSQL server via PgBouncer, all running in Kubernetes. Every couple of minutes, the PgBouncer process seems to crash, and Kubernetes restarts it. Just before ...
- 111
0
votes
1
answer
2k
views
pgbouncer bind(): Permission denied
I have pgbouncer running locally with config:
[databases]
* = host=myremoteIP port=5432 user=postgres
listen_addr = *
listen_port = 6432
When I connect using pgsql using:
psql -h 127.0.0.1 -p 6432 -...
- 247
1
vote
0
answers
10k
views
org.postgresql.util.PSQLException: This connection has been closed. error for long running transactions
We are getting "org.postgresql.util.PSQLException: This connection has been closed." on one of our deployments for only long running transactions (more than a few minutes):
Caused by: org.hibernate....
- 111
2
votes
1
answer
2k
views
Improve pgBouncer configuration
What is the best configuration for pgBouncer in my case?
I have only 1 database, 1 user and the max_connections in postgresql.conf is 1024.
The currently config of my pgbouncer.config is:
...
- 408
1
vote
0
answers
77
views
How Can I change pgbouncer pooling Point? Please help
Good morning,
I have more important questions that PGbouncer in postgreSQL module.
I saw lot of many threads, but I can't solved it.
so, I'm crave answer my question.
The questions are: I am using ...
- 11
0
votes
1
answer
2k
views
Load Balancing PG Bouncer With HA Proxy
I have a server with two instances of PG Bouncer running on different ports. Both are using session mode to pool. I set up HA Proxy to load balance incoming connections between these two processes to ...
- 101
5
votes
0
answers
5k
views
PgBouncer cannot connect to server
I have configured PgBouncer with PostgreSQL 9.6 on windows 7 machine. Here are the pgbouncer configuration settings;
[databases]
test = host=127.0.0.1 port=5432 dbname=master_db user=postgres ...
- 171
1
vote
2
answers
6k
views
Psycopg2 - PGBouncer - Postgresql > Server does not support SSL but SSL was required
I've setup my Django application to use SSL while connecting to the Postgresql database via pgbouncer. I'm using Psycopg2 library.
I've done this before successfully, so I just did the same steps ...
- 91
1
vote
1
answer
2k
views
Postgresql: newly created database does not exist
Recently I faced strange error. I'm running Postgresql behind pgBouncer. I created new database in pgAdmin (connected through pgBouncer). It shows up on the list, but when I try to select it I get ...
- 183
3
votes
1
answer
3k
views
What is the sense to use server_lifetime with PgBouncer?
I've found server_lifetime option in PgBouncer config, and I'm trying to undestand if it is really needed in our case.
https://pgbouncer.github.io/config.html
The pooler will try to close server ...
- 213
1
vote
1
answer
7k
views
PgBouncer log management
My pgbouncer.log grows endlessly.
How do I enable filename rollover or limit the logfile size for pgbouncer on Ubuntu?
- 1,959