Thread: Installed. Now what?

Hi.

I use CentOS 5, 64bit.

PG is 9.0.5.

I did "yum install pgbouncer" and got this:


---------------
Running Transaction
  Installing     : libevent
                                                     1/2
  Installing     : pgbouncer
                                                     2/2
warning: /etc/pgbouncer.ini created as /etc/pgbouncer.ini.rpmnew

Installed:
  pgbouncer.x86_64 0:1.4.2-1.rhel5

Dependency Installed:
  libevent.x86_64 0:2.0.12-1.rhel5

---------------



Now what?

1. Do I need to set up the "/etc/pgbouncer.ini.rpmnew" as
"/etc/pgbouncer.ini" and then change settings in it? What do I change?
How? The FAQ is super geeky and unhelpful. As is the sparse info on
the PG Wiki on pgbouncer. How can I tune pgbouner settings?

2. Does pgbouncer start automatically every time PG starts, or do I
have to setup a script to do so? How does pgbouncer start and keep
running?

3. How do I access pgbouncer inside my PHP code? Do I need to change
anything at all, can I just use the usual "pg_connect()" function?

Thanks!

> 1. Do I need to set up the "/etc/pgbouncer.ini.rpmnew" as
> "/etc/pgbouncer.ini" and then change settings in it? What do I change?
> How? The FAQ is super geeky and unhelpful. As is the sparse info on
> the PG Wiki on pgbouncer. How can I tune pgbouner settings?



Just a quick update. By googling for an hour, I basically set up a
working ini file. It looks like this:


[pgbouncer]
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
; ip address or * which means all ip-s
listen_addr = 127.0.0.1
listen_port = 6543
auth_type = trust
auth_file = /var/lib/pgsql/pgbouncer.txt

admin_users = postgres
stats_users = stats, root
pool_mode = session
server_reset_query = DISCARD ALL

;;; Connection limits
; total number of clients that can connect
max_client_conn = 100
default_pool_size = 20



So now pgbouncer basically starts. Both processes are running (psql
and pgbouncer) --


service postgres start
service pgbouncer start


When the two "services" are started like the above, are they working
together? The manual says psql should be restarted with the pgbouncer
port number, for these to be working together. But what if my server
does not have a "psql" process, but a service of postgres?

From within my PHP code, if I add the port number of pgbouncer in my
"pg_connect()" function, it does not work.

Thanks for any insight.

On Saturday, November 19, 2011 12:20:07 am Phoenix Kiula wrote:

>
> service postgres start
> service pgbouncer start
>
>
> When the two "services" are started like the above, are they working
> together? The manual says psql should be restarted with the pgbouncer
> port number, for these to be working together. But what if my server
> does not have a "psql" process, but a service of postgres?

Not all that confusing. Clients talk to pgbouncer, which in turn talks to
server. All the manual is saying is that you need to redirect your requests to
the pgbouncer port from the Postgres port, using psql as an example.

>
> From within my PHP code, if I add the port number of pgbouncer in my
> "pg_connect()" function, it does not work.

Did you take a look at:

http://pgbouncer.projects.postgresql.org/doc/config.html

I have never used pgbouncer, but from above it would seem you need to set up a
[databases] section to tie pgbouncer to the Postgres server.
See:
SECTION [databases]


>
> Thanks for any insight.

--
Adrian Klaver
adrian.klaver@gmail.com

On Sun, Nov 20, 2011 at 2:13 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
> http://pgbouncer.projects.postgresql.org/doc/config.html
>
> I have never used pgbouncer, but from above it would seem you need to set up a
> [databases] section to tie pgbouncer to the Postgres server.
> See:
> SECTION [databases]



Thanks Adrian. All this is done.

The config file link just describes what each option means. There's
zero information about how to actually tweak or wisely set the stuff!
:(

Anyway, with half a day of googling or so, and looking at sundry blogs
and such, I have pgbouncer running on port 6432. PG runs on the usual
5432.

I still keep seeing the "Sorry, too many clients already" error.

From my PHP code, what line should I use? This does NOT work:

  $link   = pg_connect("host=localhost dbname=$db user=$user password=$pass");

If I remove the port number, it works. Is it then connecting straight
to the DB? What am I missing? Pgbouncer is working, but not accepting
PHP pg_connect() call. The username and password are correct for sure.

Any thoughts?

On Sun, Nov 20, 2011 at 2:39 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On Sun, Nov 20, 2011 at 2:13 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>>
>> http://pgbouncer.projects.postgresql.org/doc/config.html
>>
>> I have never used pgbouncer, but from above it would seem you need to set up a
>> [databases] section to tie pgbouncer to the Postgres server.
>> See:
>> SECTION [databases]
>
>
>
> Thanks Adrian. All this is done.
>
> The config file link just describes what each option means. There's
> zero information about how to actually tweak or wisely set the stuff!
> :(
>
> Anyway, with half a day of googling or so, and looking at sundry blogs
> and such, I have pgbouncer running on port 6432. PG runs on the usual
> 5432.
>
> I still keep seeing the "Sorry, too many clients already" error.
>
> From my PHP code, what line should I use? This does NOT work:
>
>  $link   = pg_connect("host=localhost dbname=$db user=$user password=$pass");
>
> If I remove the port number, it works. Is it then connecting straight
> to the DB? What am I missing? Pgbouncer is working, but not accepting
> PHP pg_connect() call. The username and password are correct for sure.
>
> Any thoughts?


I mean this does not work:

   $link   = pg_connect("host=localhost port=6432 dbname=$db
user=$user password=$pass");

When I remove that port number, it works. I suppose it connects
directly to PG. And this is still leading to too many connections.

Also, this does NOT work:

   psql snipurl -E "snipurl_snipurl" -p 6543

Shows me this error:

   psql: ERROR:  no working server connection

How come? The pgbouncer is on!

   > ps aux | grep pgbouncer
   postgres  5567  0.0  0.0  16880   508 ?        R    13:50   0:00
pgbouncer -d /etc/pgbouncer.ini
   root      5583  0.0  0.0  61188   764 pts/2    R+   13:50   0:00
grep pgbouncer


Any thoughts? How can I make my PHP connect to the pgbouncer?

On Saturday, November 19, 2011 10:39:42 am Phoenix Kiula wrote:
> On Sun, Nov 20, 2011 at 2:13 AM, Adrian Klaver <adrian.klaver@gmail.com>
wrote:
> > http://pgbouncer.projects.postgresql.org/doc/config.html
> >
> > I have never used pgbouncer, but from above it would seem you need to set
> > up a [databases] section to tie pgbouncer to the Postgres server.
> > See:
> > SECTION [databases]
>
> Thanks Adrian. All this is done.

Well in the .ini file you posted there is no [databases] section. From what I
read lack of one would explain the problem you are seeing.

--
Adrian Klaver
adrian.klaver@gmail.com

On Sun, Nov 20, 2011 at 3:35 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
> Well in the .ini file you posted there is no [databases] section. From what I
> read lack of one would explain the problem you are seeing.



Yes. Because that's private to post on a public mailing list like this.

Here's my INI file below, with the private DB name etc sanitizes --
and trust me, all info related to password and ports is absolutely
correctly entered. Both pgbouncer and postgresql are live and running.

Just that pg_connect() function in PHP is not working if I point is to
pgbouncer's port instead of the direct postgresql port.




[databases]
MYDB  = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432

;; Configuation section
[pgbouncer]
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
; ip address or * which means all ip-s
listen_addr = 127.0.0.1
listen_port = 6543
auth_type = trust
auth_file = /var/lib/pgsql/pgbouncer.txt

admin_users = postgres
stats_users = stats, root
pool_mode = session
server_reset_query = DISCARD ALL

;;; Connection limits
; total number of clients that can connect
max_client_conn = 1500
default_pool_size = 50

On Saturday, November 19, 2011 2:44:04 pm Phoenix Kiula wrote:
> On Sun, Nov 20, 2011 at 3:35 AM, Adrian Klaver <adrian.klaver@gmail.com>
wrote:
> > Well in the .ini file you posted there is no [databases] section. From
> > what I read lack of one would explain the problem you are seeing.
>
> Yes. Because that's private to post on a public mailing list like this.
>
> Here's my INI file below, with the private DB name etc sanitizes --
> and trust me, all info related to password and ports is absolutely
> correctly entered. Both pgbouncer and postgresql are live and running.
>
> Just that pg_connect() function in PHP is not working if I point is to
> pgbouncer's port instead of the direct postgresql port.


I would first work on establishing that psql works.

From a previous post:
"
Also, this does NOT work:

   psql snipurl -E "snipurl_snipurl" -p 6543

Shows me this error:

   psql: ERROR:  no working server connection

How come? The pgbouncer is on!
"

Not sure what platform you are on but:
http://www.postgresql.org/docs/9.0/interactive/app-psql.html
"Not all of these options are required; there are useful defaults. If you omit
the host name, psql will connect via a Unix-domain socket to a server on the
local host, or via TCP/IP to localhost on machines that don't have Unix-domain
sockets"

You have pgbouncer listening on 127.0.0.1. In your psql connection string you
are not specifying a host, so if you are on a Unix platform it is trying to
connect to a socket which would account for the error. I found when working with
new software explicit is better than implicit. Eliminate possible sources of
error by fully qualifying everything.



--
Adrian Klaver
adrian.klaver@gmail.com

On Sun, Nov 20, 2011 at 8:08 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
> You have pgbouncer listening on 127.0.0.1. In your psql connection string you
> are not specifying a host, so if you are on a Unix platform it is trying to
> connect to a socket which would account for the error. I found when working with
> new software explicit is better than implicit. Eliminate possible sources of
> error by fully qualifying everything.
>


Thanks for bearing.

Specifying the host is not it.


> psql -h 127.0.0.1 MYDB -E "MYDB_MYDB" -p 6543
psql: ERROR:  no working server connection


> ps aux | grep pgbou
postgres  5567  0.0  0.0  17096   960 ?        S    13:50   0:00
pgbouncer -d /etc/pgbouncer.ini
root     24437  0.0  0.0  61192   788 pts/0    S+   21:31   0:00 grep pgbou


In the "/var/log/pgbouncer.log" I see a message about failing password.

The pgbouncer password in the "auth_file", does it need to be plain
text? Auth_type in my case is "trust". Do I need to md5 the password?

On Sun, Nov 20, 2011 at 10:33 AM, Amitabh Kant <amitabhkant@gmail.com> wrote:
>
> I am assuming the difference in the port numbers between your config file
> and php code is a typing error.
> Does the auth file (/var/lib/pgsql/pgbouncer.txt) contain valid login
> credentials to your database? If I remember correctly, it should have the
> username and password to your database.


Port numbers are correct.

Auth_file has text in this format:


"username" "password in plain text"
"username2" "password2 in plain text"
..

Is this incorrect?

On Saturday, November 19, 2011 6:35:11 pm Phoenix Kiula wrote:
> On Sun, Nov 20, 2011 at 8:08 AM, Adrian Klaver <adrian.klaver@gmail.com>
wrote:
> > You have pgbouncer listening on 127.0.0.1. In your psql connection string
> > you are not specifying a host, so if you are on a Unix platform it is
> > trying to connect to a socket which would account for the error. I found
> > when working with new software explicit is better than implicit.
> > Eliminate possible sources of error by fully qualifying everything.
>
> Thanks for bearing.
>
> Specifying the host is not it.
>
> > psql -h 127.0.0.1 MYDB -E "MYDB_MYDB" -p 6543
>
> psql: ERROR:  no working server connection

I don't see a user specified. You sure you are connecting as correct user?
Remember absent a -U the user will be either your system user name or what is
specified in a ENV variable.

>
> > ps aux | grep pgbou
>
> postgres  5567  0.0  0.0  17096   960 ?        S    13:50   0:00
> pgbouncer -d /etc/pgbouncer.ini
> root     24437  0.0  0.0  61192   788 pts/0    S+   21:31   0:00 grep pgbou
>
>
> In the "/var/log/pgbouncer.log" I see a message about failing password.
>
> The pgbouncer password in the "auth_file", does it need to be plain
> text? Auth_type in my case is "trust". Do I need to md5 the password?

According to docs:
http://pgbouncer.projects.postgresql.org/doc/config.html#_generic_settings
"auth_type

How to authenticate users.

md5: Use MD5-based password check. auth_file may contain both MD5-encrypted or
plain-text passwords. This is the default authentication method.

crypt

    Use crypt(3) based password check. auth_file must contain plain-text
passwords.
plain

    Clear-text password is sent over wire.
trust

    No authentication is done. Username must still exist in auth_file.
any

    Like the trust method, but the username given is ignored. Requires that all
databases are configured to log in as specific user. Additionally, the console
database allows any user to log in as admin.
"

--
Adrian Klaver
adrian.klaver@gmail.com

On Sun, Nov 20, 2011 at 10:37 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On Sun, Nov 20, 2011 at 10:33 AM, Amitabh Kant <amitabhkant@gmail.com> wrote:
>>
>> I am assuming the difference in the port numbers between your config file
>> and php code is a typing error.
>> Does the auth file (/var/lib/pgsql/pgbouncer.txt) contain valid login
>> credentials to your database? If I remember correctly, it should have the
>> username and password to your database.
>
>
> Port numbers are correct.
>
> Auth_file has text in this format:
>
>
> "username" "password in plain text"
> "username2" "password2 in plain text"
> ..
>
> Is this incorrect?



I just did some testing.

If the password is wrong, then it shows me the "authentication failed"
message right in the terminal window, immediately.

If the password is correct (plain text or md5 of that plain text --
both have similar requests), it shows me the second error "no working
connection" below.



[host] >  psql -h 127.0.0.1 MYDB -E "MYDB_MYDB" -p 6543psql: ERROR:
password authentication failed for user "MYDB_MYDB"[coco] ~ > [coco] ~
> pico  /var/lib/pgsql/pgbouncer.txt

[host] ~ >  psql -h 127.0.0.1 MYDB -E "MYDB_MYDB" -p 6543
psql: ERROR:  no working server connection


But in the second case, the error in the pgbouncer log is the same --
authentication is failing.

Why this inconsistent and utterly inane behavior from pgbouncer? Why
can't we see transparently what the error is?

Nowhere in the docs does it clearly specify with an example how the
auth_file format should be.

Any pointers please? I'm fresh out of google keywords to search for,
two days later.

Thank you!

On Sun, Nov 20, 2011 at 10:51 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
> I don't see a user specified. You sure you are connecting as correct user?
> Remember absent a -U the user will be either your system user name or what is
> specified in a ENV variable.


Adrian, all this is not helping.

To be sure, I tried this. Hope this command is MUCH simpler and puts
this to rest:

> psql --host=127.0.0.1 --dbname=MYDB --username="MYDB_MYDB" --port=6543
psql: ERROR:  no working server connection

> tail -4 /var/log/pgbouncer.log
2011-11-19 22:16:49.139 26439 WARNING server login failed: FATAL
password authentication failed for user "MYDB_MYDB"
2011-11-19 22:16:49.139 26439 LOG S-0x15b61fe0:
MYDB/MYDB_MYDB@127.0.0.1:5432 closing because: login failed (age=0)
2011-11-19 22:17:13.490 26439 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us


Please note that the word "MYDB" is a replacement of my private actual
word. As you can see, the password is failing.

I have read the segment of the manual you copy pasted, of course. I have

   auth_type = any
   auth_file = /var/lib/pgsql/pgbouncer.txt

I have tried "trust" and "md5" too. Same results as previously posted.
Just for convenience, here's how the file looks:

   > cat /var/lib/pgsql/pgbouncer.txt
   "MYDB_MYDB" "mypassword here"


Anything else?

Dne 20.11.2011 04:21, Phoenix Kiula napsal(a):
>> tail -4 /var/log/pgbouncer.log
> 2011-11-19 22:16:49.139 26439 WARNING server login failed: FATAL
> password authentication failed for user "MYDB_MYDB"
> 2011-11-19 22:16:49.139 26439 LOG S-0x15b61fe0:
> MYDB/MYDB_MYDB@127.0.0.1:5432 closing because: login failed (age=0)
> 2011-11-19 22:17:13.490 26439 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
>
>
> Please note that the word "MYDB" is a replacement of my private actual
> word. As you can see, the password is failing.
>
> I have read the segment of the manual you copy pasted, of course. I have
>
>    auth_type = any
>    auth_file = /var/lib/pgsql/pgbouncer.txt
>
> I have tried "trust" and "md5" too. Same results as previously posted.
> Just for convenience, here's how the file looks:
>
>    > cat /var/lib/pgsql/pgbouncer.txt
>    "MYDB_MYDB" "mypassword here"

My guess is that you actually require a password when connecting to the
database, but you haven't specified a password in the pgbouncer.ini
file. You have to specify it in the MYDB line, i.e. something like

[databases]
MYDB  = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8
port=5432 password='mypassword'


The auth_file is used only for connecting to the pgbouncer, it's not
forwarded to the database server - the pgbouncer opens the connection on
behalf of the users, and you may actually have a completely different
users on the connection pooler.

Tomas

On Sun, Nov 20, 2011 at 1:57 PM, Amitabh Kant <amitabhkant@gmail.com> wrote:
>
> On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>>
>> Dne 20.11.2011 04:21, Phoenix Kiula napsal(a):
>> <snip>
>> My guess is that you actually require a password when connecting to the
>> database, but you haven't specified a password in the pgbouncer.ini
>> file. You have to specify it in the MYDB line, i.e. something like
>>
>> [databases]
>> MYDB  = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8
>> port=5432 password='mypassword'
>>
>>
>> The auth_file is used only for connecting to the pgbouncer, it's not
>> forwarded to the database server - the pgbouncer opens the connection on
>> behalf of the users, and you may actually have a completely different
>> users on the connection pooler.


OK. So I specified the password enclosed in double quotes.

[databases]
MYDB  = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8
port=5432 password="mypassword"


Then restarted pgbouncer:

   service pgbouncer restart

And this shows up as this:

   > lsof -i | grep pgbouncer
   pgbouncer  8558 postgres    7u  IPv4 26187618       TCP
localhost:lds-distrib (LISTEN)

Is this normal? Shouldn't the port number be somewhere? What's "lds-distrib"?

Thanks for all the help.

On Sun, Nov 20, 2011 at 2:16 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On Sun, Nov 20, 2011 at 1:57 PM, Amitabh Kant <amitabhkant@gmail.com> wrote:
>>
>> On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>>>
>>> Dne 20.11.2011 04:21, Phoenix Kiula napsal(a):
>>> <snip>
>>> My guess is that you actually require a password when connecting to the
>>> database, but you haven't specified a password in the pgbouncer.ini
>>> file. You have to specify it in the MYDB line, i.e. something like
>>>
>>> [databases]
>>> MYDB  = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8
>>> port=5432 password='mypassword'
>>>
>>>
>>> The auth_file is used only for connecting to the pgbouncer, it's not
>>> forwarded to the database server - the pgbouncer opens the connection on
>>> behalf of the users, and you may actually have a completely different
>>> users on the connection pooler.
>
>
> OK. So I specified the password enclosed in double quotes.
>
> [databases]
> MYDB  = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8
> port=5432 password="mypassword"
>
>
> Then restarted pgbouncer:
>
>   service pgbouncer restart
>
> And this shows up as this:
>
>   > lsof -i | grep pgbouncer
>   pgbouncer  8558 postgres    7u  IPv4 26187618       TCP
> localhost:lds-distrib (LISTEN)
>
> Is this normal? Shouldn't the port number be somewhere? What's "lds-distrib"?



I changed the port to the usual 6432 in the pgbouncer.ini. Restarted.
Now I see this:


> lsof -i | grep pgbounc
pgbouncer 10854 postgres    7u  IPv4 26257796       TCP localhost:6432 (LISTEN)


So this is live and working. Pgbouncer is working. And yet, this is a problem:


> psql MYDB -E "MYDB_MYDB" -p 6432 -W
Password for user MYDB_MYDB:
psql: ERROR:  no working server connection


From the log file:


2011-11-20 01:28:57.775 10854 WARNING server login failed: FATAL
password authentication failed for user "MYDB_MYDB"
2011-11-20 01:28:57.775 10854 LOG S-0x1ae2efc0:
MYDB/MYDB_MYDB@127.0.0.1:5432 closing because: login failed (age=0)
2011-11-20 01:29:46.413 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us


The password I am entering in the terminal is right for sure. I've
tried it a few times, checked the caps lock, etc. Also, if the log
carries this "FATAL password authentication failed", why does the
terminal give the vague error "no working server connection"?

Thanks.

On Sun, Nov 20, 2011 at 2:45 PM, Amitabh Kant <amitabhkant@gmail.com> wrote:
> On Sun, Nov 20, 2011 at 12:02 PM, Phoenix Kiula <phoenix.kiula@gmail.com>
> wrote:
>
> Just a trial: try password without quotes in your pgbouncer config file.
> That's how I have specified in mine, and it is working.


Already done. Same problem.

On Sun, Nov 20, 2011 at 2:32 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On Sun, Nov 20, 2011 at 2:16 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>> On Sun, Nov 20, 2011 at 1:57 PM, Amitabh Kant <amitabhkant@gmail.com> wrote:
>>>
>>> On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>>>>
>>>> Dne 20.11.2011 04:21, Phoenix Kiula napsal(a):
>>>> <snip>
>>>> My guess is that you actually require a password when connecting to the
>>>> database, but you haven't specified a password in the pgbouncer.ini
>>>> file. You have to specify it in the MYDB line, i.e. something like
>>>>
>>>> [databases]
>>>> MYDB  = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8
>>>> port=5432 password='mypassword'
>>>>
>>>>
>>>> The auth_file is used only for connecting to the pgbouncer, it's not
>>>> forwarded to the database server - the pgbouncer opens the connection on
>>>> behalf of the users, and you may actually have a completely different
>>>> users on the connection pooler.
>>
>>
>> OK. So I specified the password enclosed in double quotes.
>>
>> [databases]
>> MYDB  = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8
>> port=5432 password="mypassword"
>>
>>
>> Then restarted pgbouncer:
>>
>>   service pgbouncer restart
>>
>> And this shows up as this:
>>
>>   > lsof -i | grep pgbouncer
>>   pgbouncer  8558 postgres    7u  IPv4 26187618       TCP
>> localhost:lds-distrib (LISTEN)
>>
>> Is this normal? Shouldn't the port number be somewhere? What's "lds-distrib"?
>
>
>
> I changed the port to the usual 6432 in the pgbouncer.ini. Restarted.
> Now I see this:
>
>
>> lsof -i | grep pgbounc
> pgbouncer 10854 postgres    7u  IPv4 26257796       TCP localhost:6432 (LISTEN)
>
>
> So this is live and working. Pgbouncer is working. And yet, this is a problem:
>
>
>> psql MYDB -E "MYDB_MYDB" -p 6432 -W
> Password for user MYDB_MYDB:
> psql: ERROR:  no working server connection
>
>
> From the log file:
>
>
> 2011-11-20 01:28:57.775 10854 WARNING server login failed: FATAL
> password authentication failed for user "MYDB_MYDB"
> 2011-11-20 01:28:57.775 10854 LOG S-0x1ae2efc0:
> MYDB/MYDB_MYDB@127.0.0.1:5432 closing because: login failed (age=0)
> 2011-11-20 01:29:46.413 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
>
>
> The password I am entering in the terminal is right for sure. I've
> tried it a few times, checked the caps lock, etc. Also, if the log
> carries this "FATAL password authentication failed", why does the
> terminal give the vague error "no working server connection"?
>
> Thanks.
>





Another idea.

I use CSF/LFD firewall.

For TCP_IN, I have enabled "6432" port number.

Do I also need to enable it elsewhere, such as TCP_OUT or UDP_IN etc?

On 11/19/11 11:42 PM, Phoenix Kiula wrote:
> I use CSF/LFD firewall.
>
> For TCP_IN, I have enabled "6432" port number.
>
> Do I also need to enable it elsewhere, such as TCP_OUT or UDP_IN etc?

does this firewall block localhost at all?  many don't.  (I'm not at all
familiar with this CSF/LFD thing)

if you enable a port for TCP_IN, does it automatically allow replies back?

postgres uses no UDP.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

On Sun, Nov 20, 2011 at 4:49 PM, John R Pierce <pierce@hogranch.com> wrote:
> On 11/19/11 11:42 PM, Phoenix Kiula wrote:
>
> does this firewall block localhost at all?  many don't.  (I'm not at all
> familiar with this CSF/LFD thing)
>
> if you enable a port for TCP_IN, does it automatically allow replies back?
>
> postgres uses no UDP.



The firewall is set to:

1. Ignore the process pgbouncer (in fact the entire directory in which
pgbouncer sits)

2. Allow 127.0.0.1 for everything, no limitations

3. Yes, it can allow replies back (the same settings work with
postgresql, should pgbouncer be any different?)

I tried disabling the firewall completely. Same thing -- pgbouncer
still does not work.

It's not the firewall. It isn't blocking anything. Nothing in the logs
related to pgbouncer. I merely mentioned it as a step.

On Sun, Nov 20, 2011 at 8:32 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> The password I am entering in the terminal is right for sure. I've
> tried it a few times, checked the caps lock, etc. Also, if the log
> carries this "FATAL password authentication failed", why does the
> terminal give the vague error "no working server connection"?

"no working connection" means that client logged into pgbouncer successfully,
but pgbouncer cannot log into server.

Please look into Postrgres log file for details.

If you see no failures there, you have wrong connect string in pgbouncer.ini.

--
marko

On Sun, Nov 20, 2011 at 7:43 PM, Scott Mead <scottm@openscg.com> wrote:
>
>
> On Sun, Nov 20, 2011 at 6:21 AM, Marko Kreen <markokr@gmail.com> wrote:
>>
>> On Sun, Nov 20, 2011 at 8:32 AM, Phoenix Kiula <phoenix.kiula@gmail.com>
>> wrote:
>> > The password I am entering in the terminal is right for sure. I've
>> > tried it a few times, checked the caps lock, etc. Also, if the log
>> > carries this "FATAL password authentication failed", why does the
>> > terminal give the vague error "no working server connection"?
>
> ISTM that either your connect string is bad to the database or you already
> have too many clients connected to the db.  Have you tried:
>   show max_clients;
>   select count(1) from pg_stat_activity;
>   In postgres?  Is it possible that there are just too many clients already
> connected?


You may be on to something. And the queries results are below. (5
connections are reserved for "superusers" so you may be right.)


MYDB=# show max_connections;
 max_connections
-----------------
 150
(1 row)

Time: 0.517 ms


MYDB=#   select count(1) from pg_stat_activity;
 count
-------
   144
(1 row)

Time: 1.541 ms



But isn't the point to connect to pgbouncer (instead of PG directly)
and have it manage connections? Even when I restart PG so that its
connection count is fresh and low, and immediately try to connect to
pgbouncer, it still shows me an error.

How can I debug that the connections are the problem?

The error message in the pgbouncer log points to some "FATAL password
authentication".



If not, then it's probably just your connect string ( in
> pgbouncer.ini) not being quite
 right.  You are using 127.0.0.1 for
> connecting, is postgres even listening?
>   netstat -lntp | grep 5432



Yes. It is.


> netstat -lntp | grep 5432
tcp        0      0 127.0.0.1:5432              0.0.0.0:*
     LISTEN      26220/postmaster
tcp        0      0 :::5432                     :::*
     LISTEN      26220/postmaster


> netstat -lntp | grep 6432
tcp        0      0 127.0.0.1:6432              0.0.0.0:*
     LISTEN      10854/pgbouncer


Any ideas?

On Sun, Nov 20, 2011 at 7:52 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On Sun, Nov 20, 2011 at 7:43 PM, Scott Mead <scottm@openscg.com> wrote:
>>
>>
>> On Sun, Nov 20, 2011 at 6:21 AM, Marko Kreen <markokr@gmail.com> wrote:
>>>
>>> On Sun, Nov 20, 2011 at 8:32 AM, Phoenix Kiula <phoenix.kiula@gmail.com>
>>> wrote:
>>> > The password I am entering in the terminal is right for sure. I've
>>> > tried it a few times, checked the caps lock, etc. Also, if the log
>>> > carries this "FATAL password authentication failed", why does the
>>> > terminal give the vague error "no working server connection"?
>>
>> ISTM that either your connect string is bad to the database or you already
>> have too many clients connected to the db.  Have you tried:
>>   show max_clients;
>>   select count(1) from pg_stat_activity;
>>   In postgres?  Is it possible that there are just too many clients already
>> connected?
>
>
> You may be on to something. And the queries results are below. (5
> connections are reserved for "superusers" so you may be right.)
>
>
> MYDB=# show max_connections;
>  max_connections
> -----------------
>  150
> (1 row)
>
> Time: 0.517 ms
>
>
> MYDB=#   select count(1) from pg_stat_activity;
>  count
> -------
>   144
> (1 row)
>
> Time: 1.541 ms
>
>
>
> But isn't the point to connect to pgbouncer (instead of PG directly)
> and have it manage connections? Even when I restart PG so that its
> connection count is fresh and low, and immediately try to connect to
> pgbouncer, it still shows me an error.
>
> How can I debug that the connections are the problem?
>
> The error message in the pgbouncer log points to some "FATAL password
> authentication".
>
>
>
> If not, then it's probably just your connect string ( in
>> pgbouncer.ini) not being quite
>  right.  You are using 127.0.0.1 for
>> connecting, is postgres even listening?
>>   netstat -lntp | grep 5432
>
>
>
> Yes. It is.
>
>
>> netstat -lntp | grep 5432
> tcp        0      0 127.0.0.1:5432              0.0.0.0:*
>     LISTEN      26220/postmaster
> tcp        0      0 :::5432                     :::*
>     LISTEN      26220/postmaster
>
>
>> netstat -lntp | grep 6432
> tcp        0      0 127.0.0.1:6432              0.0.0.0:*
>     LISTEN      10854/pgbouncer
>
>
> Any ideas?



Just to add, the connection string I try for pgbouncer is EXACTLY the
same as the one I use to connect directly to PG, but I add the port
number.

For Direct PG  (works) --
pg_connect("host=localhost dbname=$db user=myuser password=mypass");

For Pgbouncer  (does NOT work) --
pg_connect("host=localhost dbname=$db port=6432 user=myuser password=mypass");

Given that both PG and postgresql are alive and kicking on 5432 and
6432 ports respectively, as shown in the netstat output above, I
wonder if the connection string is the problem.

On Sun, Nov 20, 2011 at 4:52 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

> How can I debug that the connections are the problem?

Take a look at pg_stat_activity, specifically the fields client_addr,
client_port, and client_hostname.

On Sunday, November 20, 2011 3:56:18 am Phoenix Kiula wrote:

> > Any ideas?
>
> Just to add, the connection string I try for pgbouncer is EXACTLY the
> same as the one I use to connect directly to PG, but I add the port
> number.


That may be the problem. The Postgres server and pgbouncer are not the same
thing. Visual aids:

Client          -->             pgbouncer          -->       Postgres server

Client credentials        pgbouncer auth               Postgres auth

                                   auth file                         Pg pg_shadow


The  authentication chain is

Client send credentials to pgbouncer.
pgbouncer checks against its list of approved users and method of
authentication.
If client passes that then pgbouncer tries to open connection to database
specified by client, using credentials listed in connection for that database in
the [databases] section of ini file.
If those credentials match those in Postgres server then a connection is
allowed.

There is nothing that says the users  admitted by pgbouncer have to be the same
as those admitted by Postgres.

From what you reporting you are authenticating to pgbouncer and not to Postgres.
This was pointed out upstream by Marko.  I would do a careful review of what
user you are connecting as, to each program.  Also when posting the log results
please specify which program they are coming from, takes out the guess work:)



>
> For Direct PG  (works) --
> pg_connect("host=localhost dbname=$db user=myuser password=mypass");
>
> For Pgbouncer  (does NOT work) --
> pg_connect("host=localhost dbname=$db port=6432 user=myuser
> password=mypass");
>
> Given that both PG and postgresql are alive and kicking on 5432 and
> 6432 ports respectively, as shown in the netstat output above, I
> wonder if the connection string is the problem.

--
Adrian Klaver
adrian.klaver@gmail.com

On Mon, Nov 21, 2011 at 4:13 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On Sunday, November 20, 2011 3:56:18 am Phoenix Kiula wrote:
>
>> > Any ideas?
>>
>> Just to add, the connection string I try for pgbouncer is EXACTLY the
>> same as the one I use to connect directly to PG, but I add the port
>> number.
>
>
> That may be the problem. The Postgres server and pgbouncer are not the same
> thing. Visual aids:
>
> Client          -->             pgbouncer          -->       Postgres server
>
> Client credentials        pgbouncer auth               Postgres auth
>
>                                   auth file                         Pg pg_shadow



Thanks for this.

(1) Do I need to create a new user for Pgbouncer then?

(2) What info goes in the "auth_file" -- the Pgbouncer user/password
or the Postgres user/password?

In any case, I have kept both the user name and passwords the same for
now. But I have not created anything for Pgbouncer specifically other
than to put the info in auth_file. Have I missed a step?

Dne 20.11.2011 12:52, Phoenix Kiula napsal(a):
> You may be on to something. And the queries results are below. (5
> connections are reserved for "superusers" so you may be right.)
>
>
> MYDB=# show max_connections;
>  max_connections
> -----------------
>  150
> (1 row)
>
> Time: 0.517 ms
>
>
> MYDB=#   select count(1) from pg_stat_activity;
>  count
> -------
>    144
> (1 row)
>
> Time: 1.541 ms

The limit actually is not max_connections, as certain number of
connections is reserved for superusers (maintenance etc.). It's
specified by superuser_reserved_connections - by default it's set to 3,
so there are only 147 connections available.

> But isn't the point to connect to pgbouncer (instead of PG directly)
> and have it manage connections? Even when I restart PG so that its
> connection count is fresh and low, and immediately try to connect to
> pgbouncer, it still shows me an error.

Sure, but pgbouncer has to actually open a regular connection to the
database - those are regular connections, and the connection fails
because of reaching max_connections, pgbouncer can't do anything with it.

The point of connection pooler is that there'll be limited number of
pre-created connections, handed to clients. I see you have set

max_client_conn = 100
default_pool_size = 20

which means there will be at most 20 database connections, and 100
clients can connect to the pooler. Once all those 20 connections are
used, the other clients have to wait.

BTW "max_client_conn = 100" means that at most 100 clients can connect
to the pooler, if there are more clients the connection will fail with
the same error as when reaching max_connections. As you were getting
"too many clients" with max_connections=350, you should probably
significantly increase max_client_conn - e.g. to 400.

> How can I debug that the connections are the problem?

Check the postgresql log file?

> The error message in the pgbouncer log points to some "FATAL password
> authentication".

Then it probably is not caused by reaching max_connections. But I'm not
sure about this - maybe pgbouncer returns this when the database reaches
max_connections.

Tomas

Dne 21.11.2011 01:39, Phoenix Kiula napsal(a):
> On Mon, Nov 21, 2011 at 4:13 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>> On Sunday, November 20, 2011 3:56:18 am Phoenix Kiula wrote:
>>
>>>> Any ideas?
>>>
>>> Just to add, the connection string I try for pgbouncer is EXACTLY the
>>> same as the one I use to connect directly to PG, but I add the port
>>> number.
>>
>>
>> That may be the problem. The Postgres server and pgbouncer are not the same
>> thing. Visual aids:
>>
>> Client          -->             pgbouncer          -->       Postgres server
>>
>> Client credentials        pgbouncer auth               Postgres auth
>>
>>                                   auth file                         Pg pg_shadow
>
>
>
> Thanks for this.
>
> (1) Do I need to create a new user for Pgbouncer then?
>
> (2) What info goes in the "auth_file" -- the Pgbouncer user/password
> or the Postgres user/password?

Those users are completely different.

1) There's a user/password used to connect to the pgbouncer. This is the
user specified in the auth_file - how exactly is it interpreted, depends
on the auth_type value. With "trust", just an existence of the user name
is verified. With other auth types, the password is verified too.

So this works perfectly fine with auth_type=trust

"tomas" ""

and this works with auth_type=plain (with actual value of my password)

"tomas" "mypassword"

I could set auth_type=md5 and put there MD5 hash of "mypassword"

"tomas" "34819d7beeabb9260a5c854bc85b3e44"


2) Once you're connected to the pgbouncer, it has to handle you a
database connection. This has nothing to do with auth_file, the username
and password are encoded into the connection string (in the [databases]
section of the ini file).

[databases]
MYDB  = host=127.0.0.1 dbname=MYDB user=MYUSER password=MYPASSWORD
client_encoding=utf8 port=5432

> In any case, I have kept both the user name and passwords the same for
> now. But I have not created anything for Pgbouncer specifically other
> than to put the info in auth_file. Have I missed a step?

I'm really confused what the current config is. Do you have "password="
in the connection string (in 'databases' section of the ini file)?

In the previous post I've recommended to use double quotes to enclose
the password - that does not work, sorry. You may use single quotes or
no quotes (if the password does not contain spaces etc.).

Tomas

Dne 20.11.2011 03:33, Amitabh Kant napsal(a):
> On Sun, Nov 20, 2011 at 4:14 AM, Phoenix Kiula <phoenix.kiula@gmail.com

>
> Does the auth file (/var/lib/pgsql/pgbouncer.txt) contain valid login
> credentials to your database? If I remember correctly, it should have
> the username and password to your database.

No, it shouldn't. It should contain credentials for connecting to the
pgbouncer. The database credentials should go to the connection string
in '[databases]' section of your ini file.

Tomas

Dne 20.11.2011 04:15, Phoenix Kiula napsal(a):
> I just did some testing.
>
> If the password is wrong, then it shows me the "authentication failed"
> message right in the terminal window, immediately.
>
> If the password is correct (plain text or md5 of that plain text --
> both have similar requests), it shows me the second error "no working
> connection" below.

Because it's failing at different times.

The first command fails because the pgbouncer verifies the password
against the auth_file, finds out it's incorrect and kicks you out.

The second command actually connects to pgbouncer (the password is
correct), attempts to open the connection to the database using the
connection string - AFAIK it's

MYDB  = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8
port=5432

and fails because there's no password or incorrect password.


You've used the same username and password both for the connection
pooler and for database, so it's rather confusing.

> [host] >  psql -h 127.0.0.1 MYDB -E "MYDB_MYDB" -p 6543psql: ERROR:
> password authentication failed for user "MYDB_MYDB"[coco] ~ > [coco] ~
>> pico  /var/lib/pgsql/pgbouncer.txt
>
> [host] ~ >  psql -h 127.0.0.1 MYDB -E "MYDB_MYDB" -p 6543
> psql: ERROR:  no working server connection
>
>
> But in the second case, the error in the pgbouncer log is the same --
> authentication is failing.

No it's not. When the authentication fails when connecting to pgbouncer,
the message is

   Pooler Error: Auth failed

but when the database authentication fails, the message is

   Pooler Error: password authentication failed for user "..."

In the first case you have to check the auth_file, in the second you
need to check the connection string in pgbouncer.ini.

> Why this inconsistent and utterly inane behavior from pgbouncer? Why
> can't we see transparently what the error is?

It's saying you exactly what's going on. You're confused because the
connection pooling is new to you and because you've decided to use the
same credentials both for DB and pgbouncer.

> Nowhere in the docs does it clearly specify with an example how the
> auth_file format should be.

Not sure which docs are you talking about, but the "quick start" in
doc/usage.txt shows an example of the file, and doc/config.txt (and the
man pages) state that the format is the same as pg_auth/pg_pwd.

Anyway it's quite trivial - two strings, first one is username, second
one is the password. It's either plain or hashed (depending on the
auth_type).

Tomas

On Mon, Nov 21, 2011 at 10:18 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
> Dne 20.11.2011 03:33, Amitabh Kant napsal(a):
>> On Sun, Nov 20, 2011 at 4:14 AM, Phoenix Kiula <phoenix.kiula@gmail.com
>
>>
>> Does the auth file (/var/lib/pgsql/pgbouncer.txt) contain valid login
>> credentials to your database? If I remember correctly, it should have
>> the username and password to your database.
>
> No, it shouldn't. It should contain credentials for connecting to the
> pgbouncer. The database credentials should go to the connection string
> in '[databases]' section of your ini file.



Thanks Tomas and everyone.

I have the following passwords:

1. Pgbouncer.ini file

[databases]
MYDB  = host=127.0.0.1 dbname=MYDB user=MYDB_MYDB client_encoding=utf8
port=5432 password=='bypass'


2. In the auth_file (with auth_type set to "md5")

auth_type = md5
auth_file = /var/lib/pgsql/pgbouncer.txt

Inside the auth_file:
"me" "<an md5 string>"


3. In the PHP file where I need to call with  pg_connect() function.
This is the postgresql database user as usual.

pg_connect("host=127.0.0.1 dbname=$db port=6432 user=$user password=$pass");



Questions:

a. For #2, the pgbouncer password, do I need to create this "me" user
somewhere, or just writing here in the auth_file is fine? I have not
"created" this user anywhere else yet. Just written the user name and
md5 of the password in the auth_file.

b. In the connection string in #3 above, I need to be mentioning the
pgbouncer user name, right? Will the password then be md5 as in
auth_file? Or nothing?

Dne 21.11.2011 02:44, Phoenix Kiula napsal(a):
> Thanks Tomas and everyone.
>
> I have the following passwords:
>
> 1. Pgbouncer.ini file
>
> [databases]
> MYDB  = host=127.0.0.1 dbname=MYDB user=MYDB_MYDB client_encoding=utf8
> port=5432 password=='bypass'
>
>
> 2. In the auth_file (with auth_type set to "md5")
>
> auth_type = md5
> auth_file = /var/lib/pgsql/pgbouncer.txt
>
> Inside the auth_file:
> "me" "<an md5 string>"
>
>
> 3. In the PHP file where I need to call with  pg_connect() function.
> This is the postgresql database user as usual.
>
> pg_connect("host=127.0.0.1 dbname=$db port=6432 user=$user password=$pass");

I guess the $user is 'me' (as stated in pgbouncer.txt) and the password
corresponds to pgbouncer.txt (i.e. when MD5-hashed the result is equal
to the value in the file).

> Questions:
>
> a. For #2, the pgbouncer password, do I need to create this "me" user
> somewhere, or just writing here in the auth_file is fine? I have not
> "created" this user anywhere else yet. Just written the user name and
> md5 of the password in the auth_file.

No. The user is "created" by listing the username/password in the auth_file.

> b. In the connection string in #3 above, I need to be mentioning the
> pgbouncer user name, right? Will the password then be md5 as in
> auth_file? Or nothing?

You need to put the pgbouncer user name (as listed in the auth_file).
The password has to be the actual value, not the hash. Otherwise it'd be
equal to auth_type=plain.

Tomáš

On Mon, Nov 21, 2011 at 10:54 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
> Dne 21.11.2011 02:44, Phoenix Kiula napsal(a):
>> Thanks Tomas and everyone.
>>
>> I have the following passwords:
>>
>> 1. Pgbouncer.ini file
>>
>> [databases]
>> MYDB  = host=127.0.0.1 dbname=MYDB user=MYDB_MYDB client_encoding=utf8
>> port=5432 password=='bypass'
>>
>>
>> 2. In the auth_file (with auth_type set to "md5")
>>
>> auth_type = md5
>> auth_file = /var/lib/pgsql/pgbouncer.txt
>>
>> Inside the auth_file:
>> "me" "<an md5 string>"
>>
>>
>> 3. In the PHP file where I need to call with  pg_connect() function.
>> This is the postgresql database user as usual.
>>
>> pg_connect("host=127.0.0.1 dbname=$db port=6432 user=$user password=$pass");
>
> I guess the $user is 'me' (as stated in pgbouncer.txt) and the password
> corresponds to pgbouncer.txt (i.e. when MD5-hashed the result is equal
> to the value in the file).
>
>> Questions:
>>
>> a. For #2, the pgbouncer password, do I need to create this "me" user
>> somewhere, or just writing here in the auth_file is fine? I have not
>> "created" this user anywhere else yet. Just written the user name and
>> md5 of the password in the auth_file.
>
> No. The user is "created" by listing the username/password in the auth_file.
>
>> b. In the connection string in #3 above, I need to be mentioning the
>> pgbouncer user name, right? Will the password then be md5 as in
>> auth_file? Or nothing?
>
> You need to put the pgbouncer user name (as listed in the auth_file).
> The password has to be the actual value, not the hash. Otherwise it'd be
> equal to auth_type=plain.
>




Very clear. So all the passwords are now correct.

Now, when I do "service pgbouncer restart", it shows me FAILED.

I'm on CentOS 5, 64 bit. PG is 9.0.5.

The PG log has nothing about this.

The pgbouncer log has nothing either, just a huge list of:


2011-11-20 09:03:46.855 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
2011-11-20 09:04:46.856 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
2011-11-20 09:05:46.856 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
2011-11-20 09:06:46.857 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us


Any ideas on how I can determine what's going wrong?

On 11/20/2011 06:21 PM, Phoenix Kiula wrote:

*SNIP*

Forgive me if I accidentally rehash something already discussed...

Divide an conquer:

First, verify that you can connect directly to your database *using
TCP*, i.e. "psql -h 127.0.0.1 -U youruser -p 5432 yourdb". If you are
using psql without -h for this test you will use unix sockets. We need
to be sure that you can connect in the same way that pgbouncer will
connect. Note that pg_hba.conf can have different settings for socket
connections than for TCP connections and you must be sure that
postgresql.conf is set to listen for TCP connections on localhost.

Next, your settings are too grand for learning/testing purposes. Set the
pool size in pgbouncer to something small - perhaps 5 for testing
purposes and make sure that you actually have 5 available connections on
the database. Note, pgbouncer will not actually make a server connection
until it gets a client request. But it will keep that connection open to
serve the next request. And it won't make a second server connection
till it actually needs two simultaneous connections so you won't see a
sudden flood of connections when you start pgbouncer.

Now try using psql to connect to pgbouncer - again using -h 127.0.0.1 as
you were doing. If it doesn't work, check pgbouncer's log and PostgreSQL's.

Once you get an actual working pgbouncer connection, work your settings
upward. I'd probably start with something like 30-40 for the pool size
and perhaps 60-80 clients.

The information in the pgbouncer pseudo-database is helpful, here (psql
-U youradminuser -h 127.0.0.1 pgbouncer).

The "SHOW HELP;" statement will get you started but "show lists" will
give you an idea of your utilization:
show lists;
      list      | items
---------------+-------
  databases     |     2
  users         |     2
  pools         |     2
  free_clients  |     0
  used_clients  |     1
  login_clients |     0
  free_servers  |     1
  used_servers  |     0

If, after watching for a while, you see you always have lots of free
servers then you can increase the number of clients connecting to
pg_bouncer. If you are running close to the edge or running out of
server connections altogether, you will need to decrease clients or
increase the pool size.

Cheers,
Steve

On Tue, Nov 22, 2011 at 2:13 AM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
>
......
> The information in the pgbouncer pseudo-database is helpful, here (psql -U
> youradminuser -h 127.0.0.1 pgbouncer).



Thanks, I finally got it connecting.

Where's the "pgbouncer" database. Do I need to install it? It's not installed.

On Thu, Nov 24, 2011 at 9:18 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On Tue, Nov 22, 2011 at 2:13 AM, Steve Crawford
...
>


Thanks, I finally got it connecting.

Where's the "pgbouncer" database. Do I need to install it? It's not
installed.  (How else should I tell the load and utilization?)

Also, how can I tell the pgbouncer log not to log proper connections
and their closing. Right now it's filling up with nonsense. I only
want it to log when there's a warning or error.

Thanks!

On Wednesday, November 23, 2011 5:31:10 pm Phoenix Kiula wrote:
> On Thu, Nov 24, 2011 at 9:18 AM, Phoenix Kiula <phoenix.kiula@gmail.com>
wrote:
> > On Tue, Nov 22, 2011 at 2:13 AM, Steve Crawford
>
> ...
>
>
>
> Thanks, I finally got it connecting.
>
> Where's the "pgbouncer" database. Do I need to install it? It's not
> installed.  (How else should I tell the load and utilization?)

It is a virtual database, see below for how to connect:
http://pgbouncer.projects.postgresql.org/doc/usage.html#_quick_start

>
> Also, how can I tell the pgbouncer log not to log proper connections
> and their closing. Right now it's filling up with nonsense. I only
> want it to log when there's a warning or error.

http://pgbouncer.projects.postgresql.org/doc/config.html#_log_settings

>
> Thanks!

--
Adrian Klaver
adrian.klaver@gmail.com

On Thu, Nov 24, 2011 at 10:42 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>>
>> Also, how can I tell the pgbouncer log not to log proper connections
>> and their closing. Right now it's filling up with nonsense. I only
>> want it to log when there's a warning or error.
>
> http://pgbouncer.projects.postgresql.org/doc/config.html#_log_settings



Thanks. Much nicer to NOT have the connect and disconnect.

Question: my log is filled up with these messages every few seconds:


---
2011-11-24 07:10:02.349 12713 LOG Stats: 0 req/s, in 49 b/s, out 70
b/s,query 10743 us"
---


Does the "0 reqs" mean that nothing is being server through PGBOUNCER?