Thread: PGBouncer help (how to get it working)

PGBouncer help (how to get it working)

From
Phoenix Kiula
Date:
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!

Re: PGBouncer help (how to get it working)

From
Scott Marlowe
Date:
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.

Re: PGBouncer help (how to get it working)

From
Phoenix Kiula
Date:
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?

Re: PGBouncer help (how to get it working)

From
Raghavendra
Date:

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


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

Re: PGBouncer help (how to get it working)

From
Raghavendra
Date:
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

Re: PGBouncer help (how to get it working)

From
Phoenix Kiula
Date:
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?

Re: [Pgbouncer-general] PGBouncer help (how to get it working)

From
Clodoaldo Neto
Date:
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
 
Thanks for any tips!
_______________________________________________
Pgbouncer-general mailing list
Pgbouncer-general@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgbouncer-general

Re: [Pgbouncer-general] PGBouncer help (how to get it working)

From
raghu ram
Date:


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

Re: [Pgbouncer-general] PGBouncer help (how to get it working)

From
Clodoaldo Neto
Date:
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
 
--

Thanks & Regards,

Raghu Ram

EnterpriseDB: http://www.enterprisedb.com


Re: [Pgbouncer-general] PGBouncer help (how to get it working)

From
raghu ram
Date:


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