Geek Answers Handbook

Register only user requests in Postgres

I have enabled logging into my Postgres database (runs on 32 bits of Ubuntu) and I would like to log only requests that are executed by user applications. I configured postgres as follows:

log_destination = 'syslog' syslog_facility = 'L*emphasized text*OCAL0' syslog_ident = 'postgres' log_min_messages = notice log_min_duration_statement = 0 log_duration = off log_line_prefix = 'user=%u,db=%d ' log_statement = 'none'

In syslog.conf I configured for each log made in local0 redirected to /var/log/pgsql .

However, Postgres logs a lot of requests that I don't need, for example:

 WHEN typbasetype=0 THEN oid else typbasetype END AS Sep 16 12:22:28 or-ubuntu postgres[14086]: [11-2] basetype Sep 16 12:22:28 or-ubuntu postgres[14086]: [11-3] ^I FROM pg_type WHERE oid=1043 Sep 16 12:22:28 or-ubuntu postgres[14086]: [12-1] user=postgres,db=prueba LOG: duración: 0.361 ms sentencia: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 2950 Sep 16 12:22:28 or-ubuntu postgres[14086]: [13-1] user=postgres,db=prueba LOG: duración: 0.348 ms sentencia: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS Sep 16 12:22:28 or-ubuntu postgres[14086]: [13-2] basetype Sep 16 12:22:28 or-ubuntu postgres[14086]: [13-3] ^I FROM pg_type WHERE oid=2950 Sep 16 12:22:28 or-ubuntu postgres[14086]: [14-1] user=postgres,db=prueba LOG: duración: 0.451 ms sentencia: SELECT format_type(oid,104) as typname FROM pg_type WHERE oid = Sep 16 12:22:28 or-ubuntu postgres[14086]: [14-2] 1043 Sep 16 12:22:28 or-ubuntu postgres[14086]: [15-1] user=postgres,db=prueba LOG: duración: 0.353 ms sentencia: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS Sep 16 12:22:28 or-ubuntu postgres[14086]: [15-2] basetype Sep 16 12:22:28 or-ubuntu postgres[14086]: [15-3] ^I FROM pg_type WHERE oid=1043

Is there a way to prevent these lines from being written?

Thank you in advance

Diego

+4
source share
2 answers

If your applications use their own roles (as they should), you can only change the appropriate settings for these roles with ALTER ROLE

 ALTER ROLE <account> SET log_statement = 'all';

(or "log_min_duration_statement = 0", as in your configuration).

+4
source

No, from the point of view of the PostgreSQL server, all queries are equal. They come from some client, and the server does not know what interests you.

Probably your best bet is to process your logfiles - maybe it's as simple as grep on them?

(I assume you want some requests to be logged - if not, you need to set log_min_duration_statement to -1)

0
source

More articles:


All Articles