Thread: log database in which error occurs
Hello.
We have postgresql 8.4 server with about 40 databases. Postgres logs
error statements, but it's quite difficult to determine which users on
which databases caused error. For example, this is a piece of log:
May 11 01:36:46 pgsql pgsql[73794]: [7-1] ERROR: 42601: syntax error at
or near "(" at character 50
May 11 01:36:46 pgsql pgsql[73794]: [7-2] LOCATION: base_yyerror,
scan.l:907
May 11 01:36:46 pgsql pgsql[73794]: [7-3] STATEMENT: select count(*)
from forum_msg where grp_id=6709 (200 ok) ACCEPTED and is_blocked='f'
Are there any ways to log database, to which invalid query was issued ?
Are there any ways to separate general server errors and errors per each
database in several log files? I'd like general errors like SEVERE ones
to go to the /var/log/postgresql/general.log, and per-db errors on user
queries go to the /var/log/postgresql/db_$dbname.log .
--
Best regards,
Alexander Pyhalov,
system administrator of Computer Center of South Federal University
In response to Alexander Pyhalov :
> May 11 01:36:46 pgsql pgsql[73794]: [7-3] STATEMENT: select count(*)
> from forum_msg where grp_id=6709 (200 ok) ACCEPTED and is_blocked='f'
>
> Are there any ways to log database, to which invalid query was issued ?
Sure:
log_line_prefix = '%t ' # special values:
# %u = user name
# %d = database name
...
> Are there any ways to separate general server errors and errors per each
> database in several log files? I'd like general errors like SEVERE ones
> to go to the /var/log/postgresql/general.log, and per-db errors on user
> queries go to the /var/log/postgresql/db_$dbname.log .
IIRC no, but you can use tools like grep to achieve that.
Do you know pgfouine?
http://pgfouine.projects.postgresql.org/
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
A. Kretschmer wrote: >> May 11 01:36:46 pgsql pgsql[73794]: [7-3] STATEMENT: select count(*) >> from forum_msg where grp_id=6709 (200 ok) ACCEPTED and is_blocked='f' >> >> Are there any ways to log database, to which invalid query was issued ? > > Sure: > > log_line_prefix = '%t ' # special values: > # %u = user name > # %d = database name Thank you. Somehow I've missed this line in the manual. > Do you know pgfouine? > http://pgfouine.projects.postgresql.org/ Thanks for the hint. I've looked at it. Also looked at tail_n_mail (http://bucardo.org/downloads/tail_n_mail.pl ), but this may be too much for us. Especially, having php on db server is not desirable. Grep may be my best friend. -- С уважением, Александр Пыхалов, системный администратор ЮГИНФО ЮФУ.