Thread: Fwd: Two questions I would like insight on
Good morning,
--
I've been ramping up on the pg_bouncer product for a possible June production implementation date
I posed a couple questions to the pgbouncer-general list. I got this list from the pgbouncer wiki.
That list may be unattended as I haven't received any responses. Can someone please point me to the correct resource?
I've attached my questions for reference.
That list may be unattended as I haven't received any responses. Can someone please point me to the correct resource?
I've attached my questions for reference.
Thank you,
---------- Forwarded message ----------
From: Mark Steben <mark.steben@drivedominion.com>
Date: Tue, May 12, 2015 at 2:11 PM
Subject: Two questions I would like insight on
To: pgbouncer-general@lists.pgfoundry.org
we are planning to (hopefully) migrate to production next month:
1. MD5 AUTHENTICATION - the pgbouncer wiki states that a text file is used in postgres 8.x and older
but that in postgres 9.0 and newer the text files aren't used anymore. The auth file needs to be
generated the wiki states. However, we run postgres 9.2 and the text file that was described for
8.x works fine. It is in the format "username" "encrypted password" with that password
pulled from pg_shadow. If we go with this procedure, I would have to update the auth file
Any and all insight appreciated. Thank you,
--
From: Mark Steben <mark.steben@drivedominion.com>
Date: Tue, May 12, 2015 at 2:11 PM
Subject: Two questions I would like insight on
To: pgbouncer-general@lists.pgfoundry.org
Good afternoon,
I would appreciate some feedback on these two questions as every time the user password is changed, which I would like to avoid. Several years ago
I ran pgpool which had the option of authenticating directly from pg_hba.conf avoiding the necessity
of maintaining an auth file with password.
Can we execute a similar procedure in pg_bouncer or are we relegated with having to maintain
an auth file with password?
2. FATAL: remaining connection slots are reserved for non-replication super user connections error:
2. FATAL: remaining connection slots are reserved for non-replication super user connections error:
In my testing I purposely reduced the max_connection parameter to test the limits and functionality
of pg_bouncer I expected that pgbouncer would limit the pool connections number to a formula
similar to the following:
total pool connections allowed = max_connections - (superuser reserved connections + total slony connections)
(slony run outside of pg_bouncer control
This is pretty much what I got: Extra pool connections would be queued until one freed up (using session pooling)
What surprised me was the FATAL error above cropping up in the logs. I've always interpreted FATAL as the connection
being totally dead and lost. But my testing worked as I hoped for with all queries eventually being executed.
Is getting the FATAL: remaining connection ... error really ok? Is pg_bouncer simply creating another server connection
later to fulfill the query? Or am I missing something that I should be on the lookout for?
PGBOUNCER.INI FILE:
[databases]
slonytst = host=10.10.4.52 port=5433 dbname=slonytst pool_size = 8
[pgbouncer]
pool_mode = session
listen_port = 5432
listen_addr = *
auth_type = md5
auth_file = /home/postgres/pgbouncer/passwd.file
logfile = pgbouncer.log
pidfile = pgbouncer.pid
default_pool_size = 8
admin_users = postgres
stats_users = stat_collector
max_client_conn = 30
postgres max_connections = 24.slonytst = host=10.10.4.52 port=5433 dbname=slonytst pool_size = 8
[pgbouncer]
pool_mode = session
listen_port = 5432
listen_addr = *
auth_type = md5
auth_file = /home/postgres/pgbouncer/passwd.file
logfile = pgbouncer.log
pidfile = pgbouncer.pid
default_pool_size = 8
admin_users = postgres
stats_users = stat_collector
max_client_conn = 30
--
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com
--
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com