Can anyone help setting up pgbouncer?

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:

From: Nick <nboutelier@gmail.com>
Subject: [GENERAL] Can anyone help setting up pgbouncer?
To: pgsql-general@postgresql.org
Date: Friday, 13 November, 2009, 7:10 AM

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/

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 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 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

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

Do I need to open up port 6543 using iptables or something?

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