Thread: Again, problem with pgbouncer
Hi, - PG 9.0.10 - Pgbouncer version 1.4.2 Not long ago, during the last server reboot for us, we had fixed the really painful (and largely mysterious) process of setting up pgbouncer. File permissions and other mysteries were solved with help from Raghavendra: http://permalink.gmane.org/gmane.comp.db.postgresql.pgbouncer.general/854 After a long we rebooted our server today and again, as if on cue, pgbouncer has problems yet again :( PG itself is running without problems. The Pgbouncer process starts properly too. All the auth file, log file etc are setup as mentioned in that URL above. We haven't changed anything at all! At first, just connecting via pgbouncer port was giving the "no user" error. Which is funny, because the authfile has been working without problems forever. The .pgpass file had the same problems, and is still the same all this time. So, upon reading that old thread again, I guessed that the "postgres" user permissions were needed, so I did this: chown -R postgres:postgres /etc/pgbouncer chown -R postgres:postgres /var/run/pgbouncer/ chown postgres:postgres /var/log/pgbouncer.log chown postgres:postgres /var/lib/pgsql/pgbouncer.txt Then restarted both PG and Pgbouncer. Now pgbouncer won't do anything at all. Trying to connect to psql via the pgbouncer port gives this error: psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.6789"? And in the log is this line: 2012-10-01 06:12:00.703 3754 FATAL @src/main.c:553 in function write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: Permission denied [13] What now? Would appreciate some pointers. Thanks.
On Mon, Oct 1, 2012 at 3:56 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
Hi,
- PG 9.0.10
- Pgbouncer version 1.4.2
Not long ago, during the last server reboot for us, we had fixed the
really painful (and largely mysterious) process of setting up
pgbouncer.
File permissions and other mysteries were solved with help from Raghavendra:
http://permalink.gmane.org/gmane.comp.db.postgresql.pgbouncer.general/854
After a long we rebooted our server today and again, as if on cue,
pgbouncer has problems yet again :(
PG itself is running without problems.
The Pgbouncer process starts properly too. All the auth file, log file
etc are setup as mentioned in that URL above. We haven't changed
anything at all!
At first, just connecting via pgbouncer port was giving the "no user"
error. Which is funny, because the authfile has been working without
problems forever. The .pgpass file had the same problems, and is still
the same all this time.
So, upon reading that old thread again, I guessed that the "postgres"
user permissions were needed, so I did this:
chown -R postgres:postgres /etc/pgbouncer
chown -R postgres:postgres /var/run/pgbouncer/
chown postgres:postgres /var/log/pgbouncer.log
chown postgres:postgres /var/lib/pgsql/pgbouncer.txt
Then restarted both PG and Pgbouncer.
Now pgbouncer won't do anything at all. Trying to connect to psql via
the pgbouncer port gives this error:
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.6789"?
And in the log is this line:
2012-10-01 06:12:00.703 3754 FATAL @src/main.c:553 in function
write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: Permission denied
[13]
What now? Would appreciate some pointers.
Thanks.
Could you please check permission of /var/run/pgbouncer/ directory. If pgbouncer directory does not have "postgres" user permissions,please assign it and then start the pgbouncer.
Looking to the error thrown by pgbouncer the port shown up as 6789, but whereas the link of pgbouncer.ini file it has 6389. Please mention appropriate port while connecting via pgbouncer and give port number which is in pgbouncer.ini file.
--
Thanks & Regards,
Raghu Ram
EnterpriseDB Corporation
skypeid: raghu.ramedb
Blog:http://raghurc.blogspot.in/
> Could you please check permission of /var/run/pgbouncer/ directory. If > pgbouncer directory does not have "postgres" user permissions,please assign > it and then start the pgbouncer. The /var/run/pgbouncer/ directory has chown -R postgres:postgres .. The port number everywhere is already 6789. What else? Thanks.
On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>> Could you please check permission of /var/run/pgbouncer/ directory. If
>> pgbouncer directory does not have "postgres" user permissions,please assign
>> it and then start the pgbouncer.
>
>
> The /var/run/pgbouncer/ directory has
>
> chown -R postgres:postgres ..
>
> The port number everywhere is already 6789.
>
> What else?
And just to be safe, I also added pgbouncer user to postgres group:
usermod -a -G postgres pgbouncer
Now when I restart the pgbouncess service, it fails. The log has this message:
2012-10-01 23:25:24.004 21037 FATAL
Cannot open logfile: '/var/log/pgbouncer.log':
Permission denied
That file is owned by "postgres:postgres" as indicated in a gazillion
threads and documentation online (none of which is comprehensive) but
just to be sure I also did this:
chown :postgres /var/log/pgbouncer.log
Still the same permission error. Seriously, why can't the log message
be a little more useful? Why can't it say clearly WHICH USER is
looking for permission to the log file? Both "pgbouncer" and
"postgres" have permissions (through the group "postgres") on that
file. So which is it?
Much appreciate any pointers.
Thanks.
On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>>> Could you please check permission of /var/run/pgbouncer/ directory. If
>>> pgbouncer directory does not have "postgres" user permissions,please assign
>>> it and then start the pgbouncer.
>>
>>
>> The /var/run/pgbouncer/ directory has
>>
>> chown -R postgres:postgres ..
>>
>> The port number everywhere is already 6789.
>>
>> What else?
>
>
>
> And just to be safe, I also added pgbouncer user to postgres group:
>
>
> usermod -a -G postgres pgbouncer
>
>
> Now when I restart the pgbouncess service, it fails. The log has this message:
>
>
> 2012-10-01 23:25:24.004 21037 FATAL
> Cannot open logfile: '/var/log/pgbouncer.log':
> Permission denied
>
>
> That file is owned by "postgres:postgres" as indicated in a gazillion
> threads and documentation online (none of which is comprehensive) but
> just to be sure I also did this:
>
>
> chown :postgres /var/log/pgbouncer.log
>
>
> Still the same permission error. Seriously, why can't the log message
> be a little more useful? Why can't it say clearly WHICH USER is
> looking for permission to the log file? Both "pgbouncer" and
> "postgres" have permissions (through the group "postgres") on that
> file. So which is it?
I made the port number 6389 everywhere. I changed the permissions of
the pgbouncer.log to:
chown pgbouncer:postgres /var/log/pgbouncer.log
Now at least the service starts. But when I try and connect via the
pgbouncer ID:
psql -p 6389 -U snipurl_snipurl snipurl
I get this error:
psql: ERROR: No such user: MYSITE_MYSITE
And yet, the authfile has this:
"MYSITE_MYSITE" "<md5 of raw password>"
"MYSITE_MYSITE" "<raw password>"
"postgres" "<md5 of string>"
"MYSITE_pgbouncer" ""
The authfile permissions are:
283377983 -rw-r--r-- 1 pgbouncer postgres 262 Apr 14 11:15
/var/lib/pgsql/pgbouncer.txt
What else?
On Tue, Oct 2, 2012 at 5:27 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: > On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: >> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: >>>> Could you please check permission of /var/run/pgbouncer/ directory. If >>>> pgbouncer directory does not have "postgres" user permissions,please assign >>>> it and then start the pgbouncer. >>> >>> >>> The /var/run/pgbouncer/ directory has >>> >>> chown -R postgres:postgres .. >>> >>> The port number everywhere is already 6789. >>> >>> What else? >> >> >> >> And just to be safe, I also added pgbouncer user to postgres group: >> >> >> usermod -a -G postgres pgbouncer >> >> >> Now when I restart the pgbouncess service, it fails. The log has this message: >> >> >> 2012-10-01 23:25:24.004 21037 FATAL >> Cannot open logfile: '/var/log/pgbouncer.log': >> Permission denied >> >> >> That file is owned by "postgres:postgres" as indicated in a gazillion >> threads and documentation online (none of which is comprehensive) but >> just to be sure I also did this: >> >> >> chown :postgres /var/log/pgbouncer.log >> >> >> Still the same permission error. Seriously, why can't the log message >> be a little more useful? Why can't it say clearly WHICH USER is >> looking for permission to the log file? Both "pgbouncer" and >> "postgres" have permissions (through the group "postgres") on that >> file. So which is it? > > > > I made the port number 6389 everywhere. I changed the permissions of > the pgbouncer.log to: > > chown pgbouncer:postgres /var/log/pgbouncer.log > > Now at least the service starts. But when I try and connect via the > pgbouncer ID: > > psql -p 6389 -U snipurl_snipurl snipurl > > I get this error: > > psql: ERROR: No such user: MYSITE_MYSITE > > And yet, the authfile has this: > > "MYSITE_MYSITE" "<md5 of raw password>" > "MYSITE_MYSITE" "<raw password>" > "postgres" "<md5 of string>" > "MYSITE_pgbouncer" "" > > > The authfile permissions are: > > 283377983 -rw-r--r-- 1 pgbouncer postgres 262 Apr 14 11:15 > /var/lib/pgsql/pgbouncer.txt > > > What else? No response. Is there anyone who can help me with pgbouncer? What are the permissions for the authfile, etc?
Hi,
Do you have "MYSITE_MYSITE" user at your database.
Please login to the database directly (I mean, without any pgbouncer and check once.
select* from pg_user where usename ~~* 'MYSITE_MYSITE'; And also please check your's pgbouncer.ini admin users list also.
Please login to the database directly (I mean, without any pgbouncer and check once.
select* from pg_user where usename ~~* 'MYSITE_MYSITE'; And also please check your's pgbouncer.ini admin users list also.
Best Regards,
Dinesh
On Wed, Oct 3, 2012 at 11:31 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
No response.On Tue, Oct 2, 2012 at 5:27 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>>>> Could you please check permission of /var/run/pgbouncer/ directory. If
>>>> pgbouncer directory does not have "postgres" user permissions,please assign
>>>> it and then start the pgbouncer.
>>>
>>>
>>> The /var/run/pgbouncer/ directory has
>>>
>>> chown -R postgres:postgres ..
>>>
>>> The port number everywhere is already 6789.
>>>
>>> What else?
>>
>>
>>
>> And just to be safe, I also added pgbouncer user to postgres group:
>>
>>
>> usermod -a -G postgres pgbouncer
>>
>>
>> Now when I restart the pgbouncess service, it fails. The log has this message:
>>
>>
>> 2012-10-01 23:25:24.004 21037 FATAL
>> Cannot open logfile: '/var/log/pgbouncer.log':
>> Permission denied
>>
>>
>> That file is owned by "postgres:postgres" as indicated in a gazillion
>> threads and documentation online (none of which is comprehensive) but
>> just to be sure I also did this:
>>
>>
>> chown :postgres /var/log/pgbouncer.log
>>
>>
>> Still the same permission error. Seriously, why can't the log message
>> be a little more useful? Why can't it say clearly WHICH USER is
>> looking for permission to the log file? Both "pgbouncer" and
>> "postgres" have permissions (through the group "postgres") on that
>> file. So which is it?
>
>
>
> I made the port number 6389 everywhere. I changed the permissions of
> the pgbouncer.log to:
>
> chown pgbouncer:postgres /var/log/pgbouncer.log
>
> Now at least the service starts. But when I try and connect via the
> pgbouncer ID:
>
> psql -p 6389 -U snipurl_snipurl snipurl
>
> I get this error:
>
> psql: ERROR: No such user: MYSITE_MYSITE
>
> And yet, the authfile has this:
>
> "MYSITE_MYSITE" "<md5 of raw password>"
> "MYSITE_MYSITE" "<raw password>"
> "postgres" "<md5 of string>"
> "MYSITE_pgbouncer" ""
>
>
> The authfile permissions are:
>
> 283377983 -rw-r--r-- 1 pgbouncer postgres 262 Apr 14 11:15
> /var/lib/pgsql/pgbouncer.txt
>
>
> What else?
Is there anyone who can help me with pgbouncer?
What are the permissions for the authfile, etc?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I use pgpool but some of the problem you listed are same as I had with pgpool I would not run pgbouner in /var/run/pbbouner. Every time you reboot the directory will get deleted. I set my parameter toanother directory the would not get deleted after a reboot. /var/log/pgbouncer.log: what is the permission on /var/log? If you don't have write permission on the directory then you cannot write to the file. Psql: ERROR: No such user: You have to create the user in postgres, check you users postgres=# /du Role name -------------- testuser Wolfgang Schwurack DBA/SA UEN -----Original Message----- From: pgbouncer-general-bounces@pgfoundry.org [mailto:pgbouncer-general-bounces@pgfoundry.org] On Behalf Of Phoenix Kiula Sent: Wednesday, October 03, 2012 12:02 PM To: raghu ram Cc: pgbouncer-general@pgfoundry.org; PG-General Mailing List Subject: Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer On Tue, Oct 2, 2012 at 5:27 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: > On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: >> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: >>>> Could you please check permission of /var/run/pgbouncer/ directory. >>>> If pgbouncer directory does not have "postgres" user >>>> permissions,please assign it and then start the pgbouncer. >>> >>> >>> The /var/run/pgbouncer/ directory has >>> >>> chown -R postgres:postgres .. >>> >>> The port number everywhere is already 6789. >>> >>> What else? >> >> >> >> And just to be safe, I also added pgbouncer user to postgres group: >> >> >> usermod -a -G postgres pgbouncer >> >> >> Now when I restart the pgbouncess service, it fails. The log has this message: >> >> >> 2012-10-01 23:25:24.004 21037 FATAL >> Cannot open logfile: '/var/log/pgbouncer.log': >> Permission denied >> >> >> That file is owned by "postgres:postgres" as indicated in a gazillion >> threads and documentation online (none of which is comprehensive) but >> just to be sure I also did this: >> >> >> chown :postgres /var/log/pgbouncer.log >> >> >> Still the same permission error. Seriously, why can't the log message >> be a little more useful? Why can't it say clearly WHICH USER is >> looking for permission to the log file? Both "pgbouncer" and >> "postgres" have permissions (through the group "postgres") on that >> file. So which is it? > > > > I made the port number 6389 everywhere. I changed the permissions of > the pgbouncer.log to: > > chown pgbouncer:postgres /var/log/pgbouncer.log > > Now at least the service starts. But when I try and connect via the > pgbouncer ID: > > psql -p 6389 -U snipurl_snipurl snipurl > > I get this error: > > psql: ERROR: No such user: MYSITE_MYSITE > > And yet, the authfile has this: > > "MYSITE_MYSITE" "<md5 of raw password>" > "MYSITE_MYSITE" "<raw password>" > "postgres" "<md5 of string>" > "MYSITE_pgbouncer" "" > > > The authfile permissions are: > > 283377983 -rw-r--r-- 1 pgbouncer postgres 262 Apr 14 11:15 > /var/lib/pgsql/pgbouncer.txt > > > What else? No response. Is there anyone who can help me with pgbouncer? What are the permissions for the authfile, etc? _______________________________________________ Pgbouncer-general mailing list Pgbouncer-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgbouncer-general
On Thu, Oct 4, 2012 at 2:50 AM, Wolf Schwurack <wolf@uen.org> wrote:
> I use pgpool but some of the problem you listed are same as I had with pgpool
Thanks Wolf, for the thoughts.
> I would not run pgbouner in /var/run/pbbouner. Every time you reboot the
> directory will get deleted. I set my parameter to another directory the would not
> get deleted after a reboot.
OK, but this is not a showstopper here. Right?
> /var/log/pgbouncer.log:
> what is the permission on /var/log? If you don't have write permission on the directory then you cannot write to the
file.
Permissions:
/var/run/pgbouncer --
70058074 drwxr-xr-x 2 pgbouncer postgres 4.0K Oct 2 06:17 pgbouncer/
/var/log --
145686529 drwxr-xr-x 17 root root 4.0K Oct 5 04:29 log/
Please note that whatever the settings, they were working before a
server reboot. What settings do I need to give "/var/log" (currently
root) so the pgbouncer process can write to it? Why are these special
permissions needed-- I mean Apache, MysQL, Nginx etc...all of them can
write to the logs in this log folder.
> Psql: ERROR: No such user:
> You have to create the user in postgres, check you users
>
> postgres=# /du
>
Yes, this user exists in the postgres database.
List of roles
Role name | Attributes | Member of
-----------------+-----------------------------------+-----------
postgres | Superuser, Create role, Create DB | {}
rvadmin | | {}
MYSITE | | {}
MYSITE_MYSITE | Superuser, Create DB | {}
And the authfile also has permissions for "pgbouncer:postgres".
What else?
-- OK, but this is not a showstopper here. Right?
Your right - just a thought
-- What settings do I need to give "/var/log" (currently root) so the pgbouncer process can write to it? Why are these
specialpermissions needed
You need to have a pgbouner directory in /var/log and have the owner pgbouncer. This is easy to test try creating a
filein /var/log as the user pgbouncer. It should fail because pgbouncer does not have writer permissions to /var/log.
Asroot create a directory /var/log/pgbouncer, change owner to pgbouncer. Set your parameter for pgbouncer.log to
/var/log/pgbouncer.Then test by creating a file in /var/log/pgbouncer as user pgbouncer
If the user exists in the postgres then I'm not sure why it fails.
Wolf
-----Original Message-----
From: Phoenix Kiula [mailto:phoenix.kiula@gmail.com]
Sent: Friday, October 05, 2012 9:37 AM
To: Wolf Schwurack
Cc: raghu ram; pgbouncer-general@pgfoundry.org; PG-General Mailing List
Subject: Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer
On Thu, Oct 4, 2012 at 2:50 AM, Wolf Schwurack <wolf@uen.org> wrote:
> I use pgpool but some of the problem you listed are same as I had with
> pgpool
Thanks Wolf, for the thoughts.
> I would not run pgbouner in /var/run/pbbouner. Every time you reboot
> the directory will get deleted. I set my parameter to another
> directory the would not get deleted after a reboot.
OK, but this is not a showstopper here. Right?
> /var/log/pgbouncer.log:
> what is the permission on /var/log? If you don't have write permission on the directory then you cannot write to the
file.
Permissions:
/var/run/pgbouncer --
70058074 drwxr-xr-x 2 pgbouncer postgres 4.0K Oct 2 06:17 pgbouncer/
/var/log --
145686529 drwxr-xr-x 17 root root 4.0K Oct 5 04:29 log/
Please note that whatever the settings, they were working before a server reboot. What settings do I need to give
"/var/log"(currently
root) so the pgbouncer process can write to it? Why are these special permissions needed-- I mean Apache, MysQL, Nginx
etc...allof them can write to the logs in this log folder.
> Psql: ERROR: No such user:
> You have to create the user in postgres, check you users
>
> postgres=# /du
>
Yes, this user exists in the postgres database.
List of roles
Role name | Attributes | Member of
-----------------+-----------------------------------+-----------
postgres | Superuser, Create role, Create DB | {}
rvadmin | | {}
MYSITE | | {}
MYSITE_MYSITE | Superuser, Create DB | {}
And the authfile also has permissions for "pgbouncer:postgres".
What else?
On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack <wolf@uen.org> wrote: .... > You need to have a pgbouner directory in /var/log and have the owner pgbouncer. This is easy to test try creating a filein /var/log as the user pgbouncer. It should fail because pgbouncer does not have writer permissions to /var/log. Asroot create a directory /var/log/pgbouncer, change owner to pgbouncer. Set your parameter for pgbouncer.log to /var/log/pgbouncer.Then test by creating a file in /var/log/pgbouncer as user pgbouncer Wolf, I think you missed the earlier posts in this thread. The "/var/log/pgbouncer.log" already has those permissions. Note this important fact: the same permissions have been working for nearly 2 years. Anyway, I created a directory: /var/log/pgbouncer/, put the pgbouncer.log file in it. chown -R pgbouncer:postgres /var/log/pgbouncer chown pgbouncer:postgres /var/log/pgbouncer/pgbouncer.log chmod 777 /var/log/pgbouncer/pgbouncer.log As was already happening, pgbouncer starts. No problem. It's now that I cannot connect to PSQL via pgbouncer (of course I can connect to psql directly) because it fails with this error: psql: ERROR: No such user: MYSITE_MYSITE Which is weird, because that user does exist. Both inside the postgres database when I do "\du" as you suggested, and of course in the pgbouncer authfile too -- > chown pgbouncer:postgres /var/lib/pgsql/pgbouncer.txt > cat /var/lib/pgsql/pgbouncer.txt "MYSITE_MYSITE" "md5 pass" "MYSITE_MYSITE" "raw pass" "postgres" "md5fd6313191fec7887f88c31a85c43df21" So now. What? Why is this otherwise very useful tool coded so poorly that there's reams of such permissions and all of these threads online? Would love to have some help or guidance. Thanks.
On 10/05/2012 07:00 PM, Phoenix Kiula wrote: > On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack <wolf@uen.org> wrote: > .... >> You need to have a pgbouner directory in /var/log and have the owner pgbouncer. This is easy to test try creating a filein /var/log as the user pgbouncer. It should fail because pgbouncer does not have writer permissions to /var/log. Asroot create a directory /var/log/pgbouncer, change owner to pgbouncer. Set your parameter for pgbouncer.log to /var/log/pgbouncer.Then test by creating a file in /var/log/pgbouncer as user pgbouncer > > > > Wolf, I think you missed the earlier posts in this thread. The > "/var/log/pgbouncer.log" already has those permissions. > > Note this important fact: the same permissions have been working for > nearly 2 years. > > Anyway, I created a directory: /var/log/pgbouncer/, put the > pgbouncer.log file in it. > > chown -R pgbouncer:postgres /var/log/pgbouncer > chown pgbouncer:postgres /var/log/pgbouncer/pgbouncer.log > chmod 777 /var/log/pgbouncer/pgbouncer.log > > As was already happening, pgbouncer starts. No problem. > > It's now that I cannot connect to PSQL via pgbouncer (of course I can > connect to psql directly) because it fails with this error: > > psql: ERROR: No such user: MYSITE_MYSITE > > > Which is weird, because that user does exist. Both inside the postgres > database when I do "\du" as you suggested, and of course in the > pgbouncer authfile too -- > > > > chown pgbouncer:postgres /var/lib/pgsql/pgbouncer.txt > > > cat /var/lib/pgsql/pgbouncer.txt > > "MYSITE_MYSITE" "md5 pass" > "MYSITE_MYSITE" "raw pass" > "postgres" "md5fd6313191fec7887f88c31a85c43df21" > > > So now. What? Why is this otherwise very useful tool coded so poorly > that there's reams of such permissions and all of these threads > online? Would love to have some help or guidance. What are the contents of your pgbouncer.ini file? > > Thanks. > > -- Adrian Klaver adrian.klaver@gmail.com
On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: ...snip... > What are the contents of your pgbouncer.ini file? >> Thanks Adrian. I mentioned the full ini file details above in the thread, but here they are again. (Please do not comment about port numbers. This is a public list so I change the numbers, but they are very much on track everywhere they need to be.) Thanks for any pointers... [databases] * = host=127.0.0.1 port=5432 [pgbouncer] listen_addr = * listen_port = 6389 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt logfile = /var/log/pgbouncer/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid admin_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer stats_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer,stats,root pool_mode = transaction server_reset_query = DISCARD ALL; server_check_query = select 1 server_check_delay = 10 max_client_conn = 800 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 unix_socket_dir = /tmp ignore_startup_parameters = application_name
On 10/05/2012 07:23 PM, Phoenix Kiula wrote: > On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > > ...snip... > >> What are the contents of your pgbouncer.ini file? >>> > > > > Thanks Adrian. > > I mentioned the full ini file details above in the thread, but here > they are again. (Please do not comment about port numbers. This is a > public list so I change the numbers, but they are very much on track > everywhere they need to be.) > > Thanks for any pointers... > > > > [databases] > * = host=127.0.0.1 port=5432 One thing I see above: http://pgbouncer.projects.postgresql.org/doc/config.html ""\*" acts as fallback database" Notice the backslash. > > [pgbouncer] > listen_addr = * > listen_port = 6389 > auth_type = trust > auth_file = /var/lib/pgsql/pgbouncer.txt > logfile = /var/log/pgbouncer/pgbouncer.log > pidfile = /var/run/pgbouncer/pgbouncer.pid > admin_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer > stats_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer,stats,root > pool_mode = transaction > server_reset_query = DISCARD ALL; > server_check_query = select 1 > server_check_delay = 10 > max_client_conn = 800 > default_pool_size = 20 > log_connections = 0 > log_disconnections = 0 > log_pooler_errors = 1 > unix_socket_dir = /tmp > ignore_startup_parameters = application_name > > -- Adrian Klaver adrian.klaver@gmail.com
On Sat, Oct 6, 2012 at 10:24 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > > One thing I see above: > http://pgbouncer.projects.postgresql.org/doc/config.html > ""\*" acts as fallback database" > > Notice the backslash. > Ok, but: (1) The exact same INI file was working so far. (2) Why do I need a fallback database? I want to be precise about database names if possible. (3) I did try and change the config to have the backslash, but when I restart, I get this error: 2012-10-05 22:30:06.882 27442 ERROR syntax error in configuration (/etc/pgbouncer/pgbouncer.ini:2), stopping loading Now?
On 10/05/2012 07:30 PM, Phoenix Kiula wrote: > On Sat, Oct 6, 2012 at 10:24 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: >> >> One thing I see above: >> http://pgbouncer.projects.postgresql.org/doc/config.html >> ""\*" acts as fallback database" >> >> Notice the backslash. >> > > > Ok, but: > > (1) The exact same INI file was working so far. > > (2) Why do I need a fallback database? I want to be precise about > database names if possible. So why was a database name not specified? > > (3) I did try and change the config to have the backslash, but when I > restart, I get this error: > > 2012-10-05 22:30:06.882 27442 ERROR > syntax error in configuration (/etc/pgbouncer/pgbouncer.ini:2), > stopping loading Well it did not like that. > > > Now? What is the connection string you are using to make the connection to pPgbouncer? > > -- Adrian Klaver adrian.klaver@gmail.com
On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 10/05/2012 07:00 PM, Phoenix Kiula wrote: >> >> On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack <wolf@uen.org> wrote: >> .... >>> >>> You need to have a pgbouner directory in /var/log and have the owner >>> pgbouncer. This is easy to test try creating a file in /var/log as the user >>> pgbouncer. It should fail because pgbouncer does not have writer permissions >>> to /var/log. As root create a directory /var/log/pgbouncer, change owner to >>> pgbouncer. Set your parameter for pgbouncer.log to /var/log/pgbouncer. Then >>> test by creating a file in /var/log/pgbouncer as user pgbouncer >> >> >> >> >> Wolf, I think you missed the earlier posts in this thread. The >> "/var/log/pgbouncer.log" already has those permissions. >> >> Note this important fact: the same permissions have been working for >> nearly 2 years. >> >> Anyway, I created a directory: /var/log/pgbouncer/, put the >> pgbouncer.log file in it. >> >> chown -R pgbouncer:postgres /var/log/pgbouncer >> chown pgbouncer:postgres /var/log/pgbouncer/pgbouncer.log >> chmod 777 /var/log/pgbouncer/pgbouncer.log >> >> As was already happening, pgbouncer starts. No problem. >> >> It's now that I cannot connect to PSQL via pgbouncer (of course I can >> connect to psql directly) because it fails with this error: >> >> psql: ERROR: No such user: MYSITE_MYSITE >> >> >> Which is weird, because that user does exist. Both inside the postgres >> database when I do "\du" as you suggested, and of course in the >> pgbouncer authfile too -- >> >> >> > chown pgbouncer:postgres /var/lib/pgsql/pgbouncer.txt >> >> > cat /var/lib/pgsql/pgbouncer.txt >> >> "MYSITE_MYSITE" "md5 pass" >> "MYSITE_MYSITE" "raw pass" >> "postgres" "md5fd6313191fec7887f88c31a85c43df21" >> >> >> So now. What? Why is this otherwise very useful tool coded so poorly >> that there's reams of such permissions and all of these threads >> online? Would love to have some help or guidance. > > > What are the contents of your pgbouncer.ini file? Mentioned above in the thread, but here they are again. Please do not comment about port numbers. This is a public list so I change the numbers, but they are very much on track everywhere they need to be. Thanks for any pointers. [databases] * = host=127.0.0.1 port=5432 [pgbouncer] listen_addr = * listen_port = 6389 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt logfile = /var/log/pgbouncer/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid admin_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer stats_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer,stats,root pool_mode = transaction server_reset_query = DISCARD ALL; server_check_query = select 1 server_check_delay = 10 max_client_conn = 800 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 unix_socket_dir = /tmp ignore_startup_parameters = application_name
Since it started happening after reboot, I wonder if it's selinux related.
If you previously did "setenforce 0", that wouldn't persist across reboot.
On Oct 5, 2012 10:01 PM, "Phoenix Kiula" <phoenix.kiula@gmail.com> wrote:
On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack <wolf@uen.org> wrote:
....
> You need to have a pgbouner directory in /var/log and have the owner pgbouncer. This is easy to test try creating a file in /var/log as the user pgbouncer. It should fail because pgbouncer does not have writer permissions to /var/log. As root create a directory /var/log/pgbouncer, change owner to pgbouncer. Set your parameter for pgbouncer.log to /var/log/pgbouncer. Then test by creating a file in /var/log/pgbouncer as user pgbouncer
Wolf, I think you missed the earlier posts in this thread. The
"/var/log/pgbouncer.log" already has those permissions.
Note this important fact: the same permissions have been working for
nearly 2 years.
Anyway, I created a directory: /var/log/pgbouncer/, put the
pgbouncer.log file in it.
chown -R pgbouncer:postgres /var/log/pgbouncer
chown pgbouncer:postgres /var/log/pgbouncer/pgbouncer.log
chmod 777 /var/log/pgbouncer/pgbouncer.log
As was already happening, pgbouncer starts. No problem.
It's now that I cannot connect to PSQL via pgbouncer (of course I can
connect to psql directly) because it fails with this error:
psql: ERROR: No such user: MYSITE_MYSITE
Which is weird, because that user does exist. Both inside the postgres
database when I do "\du" as you suggested, and of course in the
pgbouncer authfile too --
> chown pgbouncer:postgres /var/lib/pgsql/pgbouncer.txt
> cat /var/lib/pgsql/pgbouncer.txt
"MYSITE_MYSITE" "md5 pass"
"MYSITE_MYSITE" "raw pass"
"postgres" "md5fd6313191fec7887f88c31a85c43df21"
So now. What? Why is this otherwise very useful tool coded so poorly
that there's reams of such permissions and all of these threads
online? Would love to have some help or guidance.
Thanks.
_______________________________________________
Pgbouncer-general mailing list
Pgbouncer-general@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgbouncer-general
Sorry but you are showing two different paths Your first email showed this path /var/log/pgbouncer.log And now you are stating this, which would be correct /var/log/pgbouncer/pgbouncer.log Wolf -----Original Message----- From: Phoenix Kiula [mailto:phoenix.kiula@gmail.com] Sent: Friday, October 05, 2012 8:01 PM To: Wolf Schwurack Cc: raghu ram; pgbouncer-general@pgfoundry.org; PG-General Mailing List Subject: Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack <wolf@uen.org> wrote: .... > You need to have a pgbouner directory in /var/log and have the owner > pgbouncer. This is easy to test try creating a file in /var/log as the > user pgbouncer. It should fail because pgbouncer does not have writer > permissions to /var/log. As root create a directory > /var/log/pgbouncer, change owner to pgbouncer. Set your parameter for > pgbouncer.log to /var/log/pgbouncer. Then test by creating a file in > /var/log/pgbouncer as user pgbouncer Wolf, I think you missed the earlier posts in this thread. The "/var/log/pgbouncer.log" already has those permissions. Note this important fact: the same permissions have been working for nearly 2 years. Anyway, I created a directory: /var/log/pgbouncer/, put the pgbouncer.log file in it. chown -R pgbouncer:postgres /var/log/pgbouncer chown pgbouncer:postgres /var/log/pgbouncer/pgbouncer.log chmod 777 /var/log/pgbouncer/pgbouncer.log As was already happening, pgbouncer starts. No problem. It's now that I cannot connect to PSQL via pgbouncer (of course I can connect to psql directly) because it fails with thiserror: psql: ERROR: No such user: MYSITE_MYSITE Which is weird, because that user does exist. Both inside the postgres database when I do "\du" as you suggested, and ofcourse in the pgbouncer authfile too -- > chown pgbouncer:postgres /var/lib/pgsql/pgbouncer.txt > cat /var/lib/pgsql/pgbouncer.txt "MYSITE_MYSITE" "md5 pass" "MYSITE_MYSITE" "raw pass" "postgres" "md5fd6313191fec7887f88c31a85c43df21" So now. What? Why is this otherwise very useful tool coded so poorly that there's reams of such permissions and all of thesethreads online? Would love to have some help or guidance. Thanks.
On Tue, Oct 2, 2012 at 12:27 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: > I get this error: > > psql: ERROR: No such user: MYSITE_MYSITE > > And yet, the authfile has this: > > "MYSITE_MYSITE" "<md5 of raw password>" > "MYSITE_MYSITE" "<raw password>" > "postgres" "<md5 of string>" > "MYSITE_pgbouncer" "" Because of data sanitizing I cannot see actual problem, but few hints: - Don't give one username several times (pgbouncer uses just one of them) - Usernames are case-sensitive - Username max length is 63 chars -- marko
On Sat, Oct 6, 2012 at 5:24 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > One thing I see above: > http://pgbouncer.projects.postgresql.org/doc/config.html > ""\*" acts as fallback database" > > Notice the backslash. The backslash is asciidoc/docbook accident, it should be plain * there. -- marko