Thread: Can anyone help setting up pgbouncer?
Im trying to set up pgbouncer. Installation seemed to go well but when I try... $ pgbouncer -d pgbouncer.ini I get an error... 2009-11-13 02:02:35.170 7245 ERROR broken auth file 2009-11-13 02:02:35.170 7245 LOG File descriptor limit: 1024 (H:1024), max_client_conn: 100, max fds possible: 110 Here is my pgbouncer.ini file... databases] pgbouncer1 = host=127.0.0.1 port=5432 dbname=pgbouncer1 [pgbouncer] listen_port = 6543 listen_addr = 127.0.0.1 auth_type = md5 auth_file = users.txt logfile = pgbouncer.log pidfile = pgbouncer.pid admin_users = pguser1
Nick wrote: > Im trying to set up pgbouncer. Installation seemed to go well but when > I try... > > $ pgbouncer -d pgbouncer.ini > > I get an error... > > 2009-11-13 02:02:35.170 7245 ERROR broken auth file It's complaining about your auth file, rather than pgbouncer.ini > Here is my pgbouncer.ini file... > auth_file = users.txt 1. Perhaps have a full path to the file here. 2. Check permissions/ownership on users.txt 3. Check the format of the file - each line should be <user> <password> with space between and double-quotes around each. "myuser" "mypassword" 4. Run with -v rather than -d and see if you get any useful info on STDERR -- Richard Huxton Archonet Ltd
| Below is sample pgbouncer.ini file. postgres = host=127.0.0.1 port=5432 dbname=postgres postgres = host=127.0.0.1 dbname=postgres postgres = host=127.0.0.1 port=5432 user=sam password=jas client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' logfile = pgbouncer.log pidfile = pgbouncer.pid listen_addr = * listen_port = 6000 unix_socket_dir = /tmp auth_type = trust auth_file = /usr/local/pg8.3/userlist.txt admin_users = postgres stats_users = stats, root pool_mode = transaction server_reset_query = server_check_query = select 1 server_check_delay = 10 max_client_conn = 100 default_pool_size = 500 log_connections = 1 log_disconnections = 1 log_pooler_errors = 1 server_idle_timeout = 60 client_idle_timeout = 60 client_login_timeout = 60 sample of auth_file:: /usr/local/pg8.3/userlist.txt "postgres" "postgres" & then try with below command. pgbouncer -d -v pgbouncer.ini Hope above info. may help you. -- Thanks Sam Jas --- On Fri, 13/11/09, Nick <nboutelier@gmail.com> wrote:
|
Try the new Yahoo! India Homepage. Click here.
Thanks Richard, I updated my users.txt file to include quotes (it didn't) which fixed the broken auth file error, but now im getting this... 1518 ERROR unconfigured_file: No such file or directory which repeats over and over again when I try pgbouncer -v pgbouncer.ini I updated my ini file to this: bouncer1 = host=127.0.0.1 port=5432 dbname=bouncer1 bouncer1 = host=127.0.0.1 dbname=bouncer1 bouncer1 = host=127.0.0.1 port=5432 user=nboutelier password=password logfile = pgbouncer.log pidfile = pgbouncer.pid listen_port = 6543 listen_addr = 127.0.0.1 auth_type = trust auth_file = users.tx admin_users = nboutelier On Nov 13, 1:58 am, d...@archonet.com (Richard Huxton) wrote: > Nick wrote: > > Im trying to set up pgbouncer. Installation seemed to go well but when > > I try... > > > $ pgbouncer -d pgbouncer.ini > > > I get an error... > > > 2009-11-13 02:02:35.170 7245 ERROR broken auth file > > It's complaining about your auth file, rather than pgbouncer.ini > > > Here is my pgbouncer.ini file... > > auth_file = users.txt > > 1. Perhaps have a full path to the file here. > 2. Check permissions/ownership on users.txt > 3. Check the format of the file - each line should be <user> <password> > with space between and double-quotes around each. > "myuser" "mypassword" > 4. Run with -v rather than -d and see if you get any useful info on STDERR > > -- > Richard Huxton > Archonet Ltd > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
On Sat, Nov 14, 2009 at 00:17, Nick <nboutelier@gmail.com> wrote: > Thanks Richard, I updated my users.txt file to include quotes (it > didn't) which fixed the broken auth file error, but now im getting > this... > > 1518 ERROR unconfigured_file: No such file or directory [...] If you are on a system with strace, try this: $ strace -f -e open pgbouncer -v pgbouncer.ini This way, you will see what file pgbouncer tries to open (and fails on). -- - Rikard
Here's my output. Is it a permission error writing to etc?
$ strace -f -e open pgbouncer -v pgbouncer.ini
open("/etc/ld.so.cache", O_RDONLY) = 3
open("/usr/local/lib/libevent-1.4.so.2", O_RDONLY) = 3
open("/lib/libcrypt.so.1", O_RDONLY) = 3
open("/lib/libc.so.6", O_RDONLY) = 3
open("/lib/libnsl.so.1", O_RDONLY) = 3
open("/lib/librt.so.1", O_RDONLY) = 3
open("/lib/libresolv.so.2", O_RDONLY) = 3
open("/lib/libpthread.so.0", O_RDONLY) = 3
open("pgbouncer.ini", O_RDONLY|O_LARGEFILE) = 3
open("/etc/localtime", O_RDONLY) = 3
2009-11-17 03:10:28.852 24132 ERROR unconfigured_file: No such file or
directory
2009-11-17 03:10:28.852 24132 LOG File descriptor limit: 1024 (H:
1024), max_client_conn: 100, max fds possible: 110
2009-11-17 03:10:28.859 24132 LOG listening on unix:/tmp/.s.PGSQL.6432
2009-11-17 03:10:29.193 24132 ERROR unconfigured_file: No such file or
directory
2009-11-17 03:10:29.528 24132 ERROR unconfigured_file: No such file or
directory
2009-11-17 03:10:29.863 24132 ERROR unconfigured_file: No such file or
directory
On Nov 14, 12:02 am, rikard.bosnjako...@gmail.com (Rikard Bosnjakovic)
wrote:
> On Sat, Nov 14, 2009 at 00:17, Nick <nboutel...@gmail.com> wrote:
> > Thanks Richard, I updated my users.txt file to include quotes (it
> > didn't) which fixed the broken auth file error, but now im getting
> > this...
>
> > 1518 ERROR unconfigured_file: No such file or directory
>
> [...]
>
> If you are on a system with strace, try this:
>
> $ strace -f -e open pgbouncer -v pgbouncer.ini
>
> This way, you will see what file pgbouncer tries to open (and fails on).
>
> --
> - Rikard
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
On Tue, Nov 17, 2009 at 09:13, Nick <nboutelier@gmail.com> wrote: > Here's my output. Is it a permission error writing to etc? [...] All files that are tried to be opened (in read only-mode) are successful, but there's a lack of an strace-message prior to pgbouncer's error message. For that particular strace-output, the error message from pgbouncer really doesn't make any sense except that pgbouncer.ini exists. I haven't peeked the source for pgbouncer, but a guess is that they use the wrong error number for the message output. Sorry, wish I could have helped more. -- - Rikard
Nick wrote: > Thanks Richard, I updated my users.txt file to include quotes (it > didn't) which fixed the broken auth file error, but now im getting > this... > > 1518 ERROR unconfigured_file: No such file or directory > > which repeats over and over again when I try > > pgbouncer -v pgbouncer.ini Which is why you have the source. A quick search (grep will do, or better still ack/ack-grep) reveals "unconfigured_file" is only used in one place. That is initialising the auth-file filename. So - it isn't seeing the auth_file config setting. > auth_file = users.tx You have one, although I'm guessing it's supposed to be "users.txt". So - something else is causing it not to be seen. Do you still have the [...] heading lines? If not, that could well be it. It doesn't make sense to me that all you did was change the users.txt file and now you get this error. -- Richard Huxton Archonet Ltd
On Nov 17, 3:38 am, d...@archonet.com (Richard Huxton) wrote: > Nick wrote: > > Thanks Richard, I updated my users.txt file to include quotes (it > > didn't) which fixed the broken auth file error, but now im getting > > this... > > > 1518 ERROR unconfigured_file: No such file or directory > > > which repeats over and over again when I try > > > pgbouncer -v pgbouncer.ini > > Which is why you have the source. A quick search (grep will do, or > better still ack/ack-grep) reveals "unconfigured_file" is only used in > one place. That is initialising the auth-file filename. > > So - it isn't seeing the auth_file config setting. > > > auth_file = users.tx > > You have one, although I'm guessing it's supposed to be "users.txt". So > - something else is causing it not to be seen. > > Do you still have the [...] heading lines? If not, that could well be > it. It doesn't make sense to me that all you did was change the > users.txt file and now you get this error. > > -- > Richard Huxton > Archonet Ltd > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Do I need to open up port 6543 using iptables or something?
Richard, you are correct. I was missing the [] headings. Someone sent
me a sample file which didn't have them so I didnt think they were
necessary. I now get...
2009-11-17 12:00:35.809 19575 LOG File descriptor limit: 1024 (H:
1024), max_client_conn: 100, max fds possible: 130
So now the only step I have left is actually connecting. Im trying to
connect a php script to pgbouncer with
$conn = pg_connect("host=127.0.0.1 dbname=bouncer1 port=6543
user=nboutelier password=password");
which results in
Warning: pg_connect(): Unable to connect to PostgreSQL server: could
not connect to server: Connection refused Is the server running on
host "127.0.0.1" and accepting TCP/IP connections on port 6543
Thanks for you help and patience. -Nick
On Nov 17, 3:38 am, d...@archonet.com (Richard Huxton) wrote:
> Nick wrote:
> > Thanks Richard, I updated my users.txt file to include quotes (it
> > didn't) which fixed the broken auth file error, but now im getting
> > this...
>
> > 1518 ERROR unconfigured_file: No such file or directory
>
> > which repeats over and over again when I try
>
> > pgbouncer -v pgbouncer.ini
>
> Which is why you have the source. A quick search (grep will do, or
> better still ack/ack-grep) reveals "unconfigured_file" is only used in
> one place. That is initialising the auth-file filename.
>
> So - it isn't seeing the auth_file config setting.
>
> > auth_file = users.tx
>
> You have one, although I'm guessing it's supposed to be "users.txt". So
> - something else is causing it not to be seen.
>
> Do you still have the [...] heading lines? If not, that could well be
> it. It doesn't make sense to me that all you did was change the
> users.txt file and now you get this error.
>
> --
> Richard Huxton
> Archonet Ltd
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
Nick wrote:
> So now the only step I have left is actually connecting. Im trying to
> connect a php script to pgbouncer with
>
> $conn = pg_connect("host=127.0.0.1 dbname=bouncer1 port=6543
> user=nboutelier password=password");
>
> which results in
>
> Warning: pg_connect(): Unable to connect to PostgreSQL server: could
> not connect to server: Connection refused Is the server running on
> host "127.0.0.1" and accepting TCP/IP connections on port 6543
OK - so we have one of two possibilities:
1. pgbouncer isn't listening on localhost, port 6543
2. Some firewall is in the way
If you have root access then you can check #1. You'll get something like
this:
lsof -i | grep pgbouncer
pgbouncer ... TCP localhost:6432
For #2, check your configuration settings and logs to see if anything is
blocking you.
--
Richard Huxton
Archonet Ltd
On Nov 18, 12:43 pm, d...@archonet.com (Richard Huxton) wrote:
> Nick wrote:
> > So now the only step I have left is actually connecting. Im trying to
> > connect a php script topgbouncerwith
>
> > $conn = pg_connect("host=127.0.0.1 dbname=bouncer1 port=6543
> > user=nboutelier password=password");
>
> > which results in
>
> > Warning: pg_connect(): Unable to connect to PostgreSQL server: could
> > not connect to server: Connection refused Is the server running on
> > host "127.0.0.1" and accepting TCP/IP connections on port 6543
>
> OK - so we have one of two possibilities:
> 1.pgbouncerisn't listening on localhost, port 6543
> 2. Some firewall is in the way
>
> If you have root access then you can check #1. You'll get something like
> this:
>
> lsof -i | greppgbouncerpgbouncer ... TCP localhost:6432
>
> For #2, check your configuration settings and logs to see if anything is
> blocking you.
>
> --
> Richard Huxton
> Archonet Ltd
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
lsof -i | grep pgbouncer
results in no output.
Nick wrote: > > lsof -i | grep pgbouncer > > results in no output. Then it's either not running or not listening on a port (you did run that as root, didn't you?). Does "ps aux | grep pgbouncer" show it? Your pgbouncer logfile should show the "File descriptor limit" line followed by one or more "listening on ..." lines. These lines should mention the port you're trying to connect to. If that all looks fine, but you still can't connect, stop pgbouncer and start it again but without the "-d" option and with the "-v" option. This will stop it from running as a daemon but make it more verbose. Hopefully we can see what it's doing then. -- Richard Huxton Archonet Ltd
On Nov 18, 3:28 pm, d...@archonet.com (Richard Huxton) wrote: > Nick wrote: > > > lsof -i | greppgbouncer > > > results in no output. > > Then it's either not running or not listening on a port (you did run > that as root, didn't you?). Does "ps aux | greppgbouncer" show it? > > Yourpgbouncerlogfile should show the "File descriptor limit" line > followed by one or more "listening on ..." lines. These lines should > mention the port you're trying to connect to. > > If that all looks fine, but you still can't connect, stoppgbouncerand > start it again but without the "-d" option and with the "-v" option. > This will stop it from running as a daemon but make it more verbose. > Hopefully we can see what it's doing then. > > -- > Richard Huxton > Archonet Ltd > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Solved. I restarted with the -v option and noticed a permission error with the .pid file. I guess I installed pgbouncer as root and that created the permission conflict. Ive updated the permissions and everythings running smoothly now. Thank you so much Richard for you help and patience!
Nick wrote: > > Solved. > > I restarted with the -v option and noticed a permission error with > the .pid file. I guess I installed pgbouncer as root and that created > the permission conflict. Ive updated the permissions and everythings > running smoothly now. > > Thank you so much Richard for you help and patience! Yay! These things usually turn out to be a process of: 1. Spend ages finding trivial error. 2. Spend 1 minute fixing error. Now, don't forget to keep an eye on your pgbouncer setup* and make sure it's (a) still running (b) has the right number of connections in its pool. Other than that, it doesn't need much care and attention. * Try nagios or munin for monitoring systems. There are PostgreSQL related plugins for both. -- Richard Huxton Archonet Ltd