From: | Mark Steben <mark(dot)steben(at)drivedominion(dot)com> |
---|---|
To: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Fwd: Two questions I would like insight on |
Date: | 2015-05-13 16:21:48 |
Message-ID: | CADyzmyw7NkR+DdP6DA7dg0yxA8oA==Mor3GF47pvyM164p0-6w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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.
Thank you,
---------- Forwarded message ----------
From: Mark Steben <mark(dot)steben(at)drivedominion(dot)com>
Date: Tue, May 12, 2015 at 2:11 PM
Subject: Two questions I would like insight on
To: pgbouncer-general(at)lists(dot)pgfoundry(dot)org
Good afternoon,
I would appreciate some feedback on these two questions as
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
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:
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.
Any and all insight appreciated. Thank you,
--
*Mark Steben*
Database Administrator
@utoRevenue <http://www.autorevenue.com/> | Autobase
<http://www.autobase.net/>
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 <http://www.autorevenue.com/>
<http://autobasedigital.net/marketing/DD12_sig.jpg>
--
*Mark Steben*
Database Administrator
@utoRevenue <http://www.autorevenue.com/> | Autobase
<http://www.autobase.net/>
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 <http://www.autorevenue.com/>
From | Date | Subject | |
---|---|---|---|
Next Message | Ravi Krishna | 2015-05-14 15:03:11 | WAL and O_DIRECT |
Previous Message | Albe Laurenz | 2015-05-13 08:00:49 | Re: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp)) |