log only user queries in Postgres

Question

Hi, I've enabled logging in my Postgres database (running on Ubuntu 32 bits) and I'd like to log only queries that a re performed by user applications. I've 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've configured for every log made to local0 be redirected to /var/log/pgsql.

However, Postgres is logging a lot of queries which I don't care for, 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 any way to prevent these lines from being logged?

Thanks in advance

Diego

Answer 1

No, from the perspective of the PostgreSQL server, all queries are equal. Those come from some client, and there is no way for the server to know which you are interested in.

You're probably best off post-processing your logfiles - perhaps it's as simple as a "grep" on them?

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

Answer 2

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

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

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