Re: [GENERAL] Installed. Now what?
On Thu, Nov 24, 2011 at 10:42 AM, Adrian Klaver adrian.kla...@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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Tue, Nov 22, 2011 at 2:13 AM, Steve Crawford scrawf...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Thu, Nov 24, 2011 at 9:18 AM, Phoenix Kiula phoenix.ki...@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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Wednesday, November 23, 2011 5:31:10 pm Phoenix Kiula wrote: On Thu, Nov 24, 2011 at 9:18 AM, Phoenix Kiula phoenix.ki...@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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 1:12 PM, Phoenix Kiula phoenix.ki...@gmail.comwrote: snip 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? Could you just try disabling the firewall for once? Amitabh
Re: [GENERAL] Installed. Now what?
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 pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 4:49 PM, John R Pierce pie...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 8:32 AM, Phoenix Kiula phoenix.ki...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 6:21 AM, Marko Kreen mark...@gmail.com wrote: On Sun, Nov 20, 2011 at 8:32 AM, Phoenix Kiula phoenix.ki...@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? 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 Good luck. --Scott 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 7:43 PM, Scott Mead sco...@openscg.com wrote: On Sun, Nov 20, 2011 at 6:21 AM, Marko Kreen mark...@gmail.com wrote: On Sun, Nov 20, 2011 at 8:32 AM, Phoenix Kiula phoenix.ki...@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 tcp0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 26220/postmaster tcp0 0 :::5432 :::* LISTEN 26220/postmaster netstat -lntp | grep 6432 tcp0 0 127.0.0.1:6432 0.0.0.0:* LISTEN 10854/pgbouncer Any ideas? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 7:52 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 7:43 PM, Scott Mead sco...@openscg.com wrote: On Sun, Nov 20, 2011 at 6:21 AM, Marko Kreen mark...@gmail.com wrote: On Sun, Nov 20, 2011 at 8:32 AM, Phoenix Kiula phoenix.ki...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 4:52 AM, Phoenix Kiula phoenix.ki...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
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 credentialspgbouncer 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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Mon, Nov 21, 2011 at 4:13 AM, Adrian Klaver adrian.kla...@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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
Dne 21.11.2011 01:39, Phoenix Kiula napsal(a): On Mon, Nov 21, 2011 at 4:13 AM, Adrian Klaver adrian.kla...@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 credentialspgbouncer 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
Dne 20.11.2011 03:33, Amitabh Kant napsal(a): On Sun, Nov 20, 2011 at 4:14 AM, Phoenix Kiula phoenix.ki...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Mon, Nov 21, 2011 at 10:18 AM, Tomas Vondra t...@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.ki...@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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
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áš -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Mon, Nov 21, 2011 at 10:54 AM, Tomas Vondra t...@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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. 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? 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 2:13 AM, Adrian Klaver adrian.kla...@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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 2:39 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 2:13 AM, Adrian Klaver adrian.kla...@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 ?R13:50 0:00 pgbouncer -d /etc/pgbouncer.ini root 5583 0.0 0.0 61188 764 pts/2R+ 13:50 0:00 grep pgbouncer Any thoughts? How can I make my PHP connect to the pgbouncer? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Saturday, November 19, 2011 10:39:42 am Phoenix Kiula wrote: On Sun, Nov 20, 2011 at 2:13 AM, Adrian Klaver adrian.kla...@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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 3:35 AM, Adrian Klaver adrian.kla...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Saturday, November 19, 2011 2:44:04 pm Phoenix Kiula wrote: On Sun, Nov 20, 2011 at 3:35 AM, Adrian Klaver adrian.kla...@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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 4:14 AM, Phoenix Kiula phoenix.ki...@gmail.comwrote: On Sun, Nov 20, 2011 at 3:35 AM, Adrian Klaver adrian.kla...@gmail.com wrote:snip [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 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. Amitabh
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 8:08 AM, Adrian Klaver adrian.kla...@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 ?S13:50 0:00 pgbouncer -d /etc/pgbouncer.ini root 24437 0.0 0.0 61192 788 pts/0S+ 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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 10:33 AM, Amitabh Kant amitabhk...@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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Saturday, November 19, 2011 6:35:11 pm Phoenix Kiula wrote: On Sun, Nov 20, 2011 at 8:08 AM, Adrian Klaver adrian.kla...@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 ?S13:50 0:00 pgbouncer -d /etc/pgbouncer.ini root 24437 0.0 0.0 61192 788 pts/0S+ 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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 10:37 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 10:33 AM, Amitabh Kant amitabhk...@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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 10:51 AM, Adrian Klaver adrian.kla...@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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra t...@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. Tomas I just checked my pgbouncer config file, and ye it does require a password in the db connection line. Amitabh
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 1:57 PM, Amitabh Kant amitabhk...@gmail.com wrote: On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra t...@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 postgres7u 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 2:16 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 1:57 PM, Amitabh Kant amitabhk...@gmail.com wrote: On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra t...@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 postgres7u 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 12:02 PM, Phoenix Kiula phoenix.ki...@gmail.comwrote: On Sun, Nov 20, 2011 at 2:16 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 1:57 PM, Amitabh Kant amitabhk...@gmail.com wrote: On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra t...@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 postgres7u 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 postgres7u 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. Just a trial: try password without quotes in your pgbouncer config file. That's how I have specified in mine, and it is working. Amitabh
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 2:45 PM, Amitabh Kant amitabhk...@gmail.com wrote: On Sun, Nov 20, 2011 at 12:02 PM, Phoenix Kiula phoenix.ki...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 2:32 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 2:16 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 1:57 PM, Amitabh Kant amitabhk...@gmail.com wrote: On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra t...@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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
