PGBouncer help (how to get it working)

On Thu, Apr 12, 2012 at 11:12 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

  WARNING: password file "/root/.pgpass" has group or world access;
  permissions should be u=rw (0600) or less
  psql: ERROR:  No such user: MYSITE_pgbouncer

Pretty sure the error is just the perms on that file. Set them to
0600 and try again.

On Fri, Apr 13, 2012 at 2:41 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Thu, Apr 12, 2012 at 11:12 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

  WARNING: password file "/root/.pgpass" has group or world access;
  permissions should be u=rw (0600) or less
  psql: ERROR:  No such user: MYSITE_pgbouncer

Pretty sure the error is just the perms on that file.  Set them to
0600 and try again.

I had already done this. Doesn't do anything. Pgbouncer starts
(service pgbouncer restart) but when I try to connect, it tells me

psql: ERROR:  No such user: MYSITE_pgbouncer

Where should i create the "MYSITE_pgbouncer" user?

On Fri, Apr 13, 2012 at 11:00 AM, Phoenix Kiula <phoenix.kiula@gmail.com>wrote:

On Fri, Apr 13, 2012 at 2:41 AM, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

On Thu, Apr 12, 2012 at 11:12 AM, Phoenix Kiula <phoenix.kiula@gmail.com>

wrote:

WARNING: password file "/root/.pgpass" has group or world access;
permissions should be u=rw (0600) or less
psql: ERROR: No such user: MYSITE_pgbouncer

Pretty sure the error is just the perms on that file. Set them to
0600 and try again.

I had already done this. Doesn't do anything. Pgbouncer starts
(service pgbouncer restart) but when I try to connect, it tells me

psql: ERROR: No such user: MYSITE_pgbouncer

Where should i create the "MYSITE_pgbouncer" user?

Add it in pgbouncer.auth file.... as per your .ini file parameter.

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

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Fri, Apr 13, 2012 at 11:00 AM, Phoenix Kiula <phoenix.kiula@gmail.com>wrote:

On Fri, Apr 13, 2012 at 2:41 AM, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

On Thu, Apr 12, 2012 at 11:12 AM, Phoenix Kiula <

phoenix.kiula@gmail.com> wrote:

WARNING: password file "/root/.pgpass" has group or world access;
permissions should be u=rw (0600) or less
psql: ERROR: No such user: MYSITE_pgbouncer

Pretty sure the error is just the perms on that file. Set them to
0600 and try again.

I had already done this. Doesn't do anything. Pgbouncer starts
(service pgbouncer restart) but when I try to connect, it tells me

psql: ERROR: No such user: MYSITE_pgbouncer

Where should i create the "MYSITE_pgbouncer" user?

Add it in pgbouncer.auth file.... as per your .ini file parameter.

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

Seems you already did this. I believe you are connecting as postgres user
not from root, if yes, then check .pgpass file too (it will be in postgres
user home directory).

--Raghav

On Fri, Apr 13, 2012 at 2:59 PM, Raghavendra
<raghavendra.rao@enterprisedb.com> wrote:

.....

Add it in pgbouncer.auth file.... as per your .ini file parameter.

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

Seems you already did this. I believe you are connecting as postgres user
not from root, if yes, then check .pgpass file too (it will be in postgres
user home directory).

Thanks, but this is not helping.

I have the exact same info in three files:

1. The root ".pgpass"
2. The ".pgpass" for the postgres user
3. The authfile specified inside pgbouncer -- "/var/lib/pgsql/pgbouncer.txt"

Now what?

It's not telling me that the user is unrecognized, so it's likely not
that md5 or trust or plain password issue. Pgbouncer is not even
recognizing the user!

What now?

Em 12 de abril de 2012 14:12, Phoenix Kiula <phoenix.kiula@gmail.com>escreveu:

I had pgbouncer working somehow, but we have switched servers recently
and now I cannot for the life of me figure out again how to set it up.

Online guides say things like "create a user ID". Well, where? Inside
PG the database? Or in my CentOS system?

Here's my "/etc/pgbouncer.ini":

[databases]
* = port = 5432

[pgbouncer]
listen_port = 6543
listen_addr = 127.0.0.1
auth_type = trust
auth_file = /var/lib/pgsql/pgbouncer.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = postgres,MYSITE_pgbouncer
pool_mode = transaction
server_reset_query = DISCARD ALL;
server_check_query = select 1
server_check_delay = 10
max_client_conn = 1000
default_pool_size = 20
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

And here's my authfile, "/var/lib/pgsql/pgbouncer.txt" --

"MYSITE_pgbouncer" ""

Is there something else I need to do? What steps am I missing? When I
start pgbouncer at the command line, I see this error:

WARNING: password file "/root/.pgpass" has group or world access;
permissions should be u=rw (0600) or less
psql: ERROR: No such user: MYSITE_pgbouncer

I had this error in the pgbouncer log file after updating to the last
version, 1.5, in Centos 6. For me the fix was to set the ownership of the
auth_file to pgbouncer. In the previous version, 1.4, it was working with
postgres as the owner of that file.

Regards, Clodoaldo

On Sat, Apr 14, 2012 at 4:31 PM, Clodoaldo Neto <
clodoaldo.pinto.neto@gmail.com> wrote:

Em 12 de abril de 2012 14:12, Phoenix Kiula <phoenix.kiula@gmail.com>escreveu:

I had pgbouncer working somehow, but we have switched servers recently
and now I cannot for the life of me figure out again how to set it up.

Online guides say things like "create a user ID". Well, where? Inside
PG the database? Or in my CentOS system?

Here's my "/etc/pgbouncer.ini":

[databases]
* = port = 5432

[pgbouncer]
listen_port = 6543
listen_addr = 127.0.0.1
auth_type = trust
auth_file = /var/lib/pgsql/pgbouncer.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = postgres,MYSITE_pgbouncer
pool_mode = transaction
server_reset_query = DISCARD ALL;
server_check_query = select 1
server_check_delay = 10
max_client_conn = 1000
default_pool_size = 20
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

And here's my authfile, "/var/lib/pgsql/pgbouncer.txt" --

"MYSITE_pgbouncer" ""

Is there something else I need to do? What steps am I missing? When I
start pgbouncer at the command line, I see this error:

WARNING: password file "/root/.pgpass" has group or world access;
permissions should be u=rw (0600) or less
psql: ERROR: No such user: MYSITE_pgbouncer

I had this error in the pgbouncer log file after updating to the last
version, 1.5, in Centos 6. For me the fix was to set the ownership of the
auth_file to pgbouncer. In the previous version, 1.4, it was working with
postgres as the owner of that file.

Regards, Clodoaldo

Could you please share stats_users information in pgbouncer.ini file ?

--

Thanks & Regards,

Raghu Ram

EnterpriseDB: http://www.enterprisedb.com

Em 14 de abril de 2012 09:39, raghu ram <raghuchennuru@gmail.com> escreveu:

On Sat, Apr 14, 2012 at 4:31 PM, Clodoaldo Neto <
clodoaldo.pinto.neto@gmail.com> wrote:

Em 12 de abril de 2012 14:12, Phoenix Kiula <phoenix.kiula@gmail.com>escreveu:

I had pgbouncer working somehow, but we have switched servers recently
and now I cannot for the life of me figure out again how to set it up.

Online guides say things like "create a user ID". Well, where? Inside
PG the database? Or in my CentOS system?

Here's my "/etc/pgbouncer.ini":

[databases]
* = port = 5432

[pgbouncer]
listen_port = 6543
listen_addr = 127.0.0.1
auth_type = trust
auth_file = /var/lib/pgsql/pgbouncer.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = postgres,MYSITE_pgbouncer
pool_mode = transaction
server_reset_query = DISCARD ALL;
server_check_query = select 1
server_check_delay = 10
max_client_conn = 1000
default_pool_size = 20
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

And here's my authfile, "/var/lib/pgsql/pgbouncer.txt" --

"MYSITE_pgbouncer" ""

Is there something else I need to do? What steps am I missing? When I
start pgbouncer at the command line, I see this error:

WARNING: password file "/root/.pgpass" has group or world access;
permissions should be u=rw (0600) or less
psql: ERROR: No such user: MYSITE_pgbouncer

I had this error in the pgbouncer log file after updating to the last
version, 1.5, in Centos 6. For me the fix was to set the ownership of the
auth_file to pgbouncer. In the previous version, 1.4, it was working with
postgres as the owner of that file.

Regards, Clodoaldo

Could you please share stats_users information in pgbouncer.ini file ?

That is the default:

stats_users = stats, root

Clodoaldo

On Sat, Apr 14, 2012 at 6:35 PM, Clodoaldo Neto <
clodoaldo.pinto.neto@gmail.com> wrote:

Em 14 de abril de 2012 09:39, raghu ram <raghuchennuru@gmail.com>escreveu:

On Sat, Apr 14, 2012 at 4:31 PM, Clodoaldo Neto <
clodoaldo.pinto.neto@gmail.com> wrote:

Em 12 de abril de 2012 14:12, Phoenix Kiula <phoenix.kiula@gmail.com>escreveu:

I had pgbouncer working somehow, but we have switched servers recently
and now I cannot for the life of me figure out again how to set it up.

Online guides say things like "create a user ID". Well, where? Inside
PG the database? Or in my CentOS system?

Here's my "/etc/pgbouncer.ini":

[databases]
* = port = 5432

[pgbouncer]
listen_port = 6543
listen_addr = 127.0.0.1
auth_type = trust
auth_file = /var/lib/pgsql/pgbouncer.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = postgres,MYSITE_pgbouncer
pool_mode = transaction
server_reset_query = DISCARD ALL;
server_check_query = select 1
server_check_delay = 10
max_client_conn = 1000
default_pool_size = 20
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

And here's my authfile, "/var/lib/pgsql/pgbouncer.txt" --

"MYSITE_pgbouncer" ""

Is there something else I need to do? What steps am I missing? When I
start pgbouncer at the command line, I see this error:

WARNING: password file "/root/.pgpass" has group or world access;
permissions should be u=rw (0600) or less
psql: ERROR: No such user: MYSITE_pgbouncer

I had this error in the pgbouncer log file after updating to the last
version, 1.5, in Centos 6. For me the fix was to set the ownership of the
auth_file to pgbouncer. In the previous version, 1.4, it was working with
postgres as the owner of that file.

Regards, Clodoaldo

Could you please share stats_users information in pgbouncer.ini file ?

That is the default:

stats_users = stats, root

Could you please modify and below parameters in pgbouncer.ini file:

admin_users = postgres,MYSITE_pgbouncer
stats_users = postgres,MYSITE_pgbouncer, stats, root

and then Restart the pgbouncer.

--

Thanks & Regards,

Raghu Ram

EnterpriseDB: http://www.enterprisedb.com