Fwd: Two questions I would like insight on - Mailing list pgsql-admin

From Mark Steben
Subject Fwd: Two questions I would like insight on
Date
Msg-id CADyzmyw7NkR+DdP6DA7dg0yxA8oA==Mor3GF47pvyM164p0-6w@mail.gmail.com
Whole thread Raw
List 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.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
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 | 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

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com





pgsql-admin by date:

Previous
From: Albe Laurenz
Date:
Subject: Re: Timestamp Shifts when calling to_timestamp(extract (epoch from timestamp))
Next
From: Ravi Krishna
Date:
Subject: WAL and O_DIRECT