Thread: PGBouncer help (how to get it working)

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?

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

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

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

Thanks for any tips!
_______________________________________________
Pgbouncer-general mailing list
Pgbouncer-general@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgbouncer-general

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