patternsqlMinor
Is it possible to exclude specific users in log activity of PostgreSQL?
Viewed 0 times
postgresqllogpossiblespecificactivityusersexclude
Problem
I need to monitor the activity of users in our databases. I have set the following parameters in
However, I realize that most of the log files are filled with statements executed by the
The result is daily log files having over 12 Mb in size.
Is there a way to exclude specific users' activity from the log?
postgresql.conf:log_min_duration_statement = 0
log_connections = on
log_disconnections = on
log_line_prefix = '%t %a %d %h %u |'However, I realize that most of the log files are filled with statements executed by the
postgres user, which is used by scripts I wrote for maintenance tasks: recalculate materialized views, pg_dump, pg_restore, extract views as tabulated files, etc.The result is daily log files having over 12 Mb in size.
Is there a way to exclude specific users' activity from the log?
Solution
Using the
Setting log_min_duration_statement=-1 (1st login):
psql console
Result on the log:
Note that within that session, all statements are visible in the log even after setting log_min_duration_statement=-1.
Seeing the effect of log_min_duration_statement (2nd login):
psql console
Result on the log:
As expected none of the statements are logged.
ALTER ROLE ... SET parameter; command, it was possible to tailor user-specific log parameters. Note that the parameter only takes effect after logout.Setting log_min_duration_statement=-1 (1st login):
psql console
P:\>psql -U postgres -h 132.156.208.45 -d my_db
my_db=# SHOW log_min_duration_statement;
log_min_duration_statement
----------------------------
0
(1 ligne)
my_db=# SELECT COUNT(*) FROM organisms;
count
-------
153
(1 ligne)
my_db=# ALTER ROLE postgres SET log_min_duration_statement=-1;
ALTER ROLE
my_db=# SHOW log_min_duration_statement;
log_min_duration_statement
----------------------------
0
(1 ligne)
my_db=# SELECT COUNT(*) FROM mv_rings;
count
--------
115270
(1 ligne)
my_db=# \qResult on the log:
Note that within that session, all statements are visible in the log even after setting log_min_duration_statement=-1.
2015-10-15 14:14:01 EDT [unknown] my_db 123.456.789.012 postgres |LOG: connection authorized: user=postgres database=my_db
2015-10-15 14:14:08 EDT psql my_db 123.456.789.012 postgres |LOG: duration: 0.000 ms statement: SHOW log_min_duration_statement;
2015-10-15 14:15:26 EDT psql my_db 123.456.789.012 postgres |LOG: duration: 32.000 ms statement: SELECT COUNT(*) FROM organisms;
2015-10-15 14:15:45 EDT psql my_db 123.456.789.012 postgres |LOG: duration: 31.000 ms statement: ALTER ROLE postgres SET log_min_duration_statement=-1;
2015-10-15 14:16:08 EDT psql my_db 123.456.789.012 postgres |LOG: duration: 0.000 ms statement: SHOW log_min_duration_statement;
2015-10-15 14:17:10 EDT psql my_db 123.456.789.012 postgres |LOG: duration: 2059.000 ms statement: SELECT COUNT(*) FROM mv_rings;
2015-10-15 14:17:29 EDT psql my_db 123.456.789.012 postgres |LOG: disconnection: session time: 0:03:27.450 user=postgres database=my_db host=123.456.789.012 port=65269Seeing the effect of log_min_duration_statement (2nd login):
psql console
P:\>psql -U postgres -h 132.156.208.45 -d my_db
my_db=# SHOW log_min_duration_statement;
log_min_duration_statement
----------------------------
-1
(1 ligne)
my_db=# SELECT COUNT(*) FROM germplasms;
count
--------
475290
(1 ligne)
my_db=# \qResult on the log:
As expected none of the statements are logged.
2015-10-15 14:17:44 EDT [unknown] my_db 123.456.789.012 postgres |LOG: connection authorized: user=postgres database=my_db
2015-10-15 14:20:27 EDT psql my_db 123.456.789.012 postgres |LOG: disconnection: session time: 0:02:43.333 user=postgres database=my_db host=123.456.789.012 port=49372Code Snippets
P:\>psql -U postgres -h 132.156.208.45 -d my_db
my_db=# SHOW log_min_duration_statement;
log_min_duration_statement
----------------------------
0
(1 ligne)
my_db=# SELECT COUNT(*) FROM organisms;
count
-------
153
(1 ligne)
my_db=# ALTER ROLE postgres SET log_min_duration_statement=-1;
ALTER ROLE
my_db=# SHOW log_min_duration_statement;
log_min_duration_statement
----------------------------
0
(1 ligne)
my_db=# SELECT COUNT(*) FROM mv_rings;
count
--------
115270
(1 ligne)
my_db=# \q2015-10-15 14:14:01 EDT [unknown] my_db 123.456.789.012 postgres |LOG: connection authorized: user=postgres database=my_db
2015-10-15 14:14:08 EDT psql my_db 123.456.789.012 postgres |LOG: duration: 0.000 ms statement: SHOW log_min_duration_statement;
2015-10-15 14:15:26 EDT psql my_db 123.456.789.012 postgres |LOG: duration: 32.000 ms statement: SELECT COUNT(*) FROM organisms;
2015-10-15 14:15:45 EDT psql my_db 123.456.789.012 postgres |LOG: duration: 31.000 ms statement: ALTER ROLE postgres SET log_min_duration_statement=-1;
2015-10-15 14:16:08 EDT psql my_db 123.456.789.012 postgres |LOG: duration: 0.000 ms statement: SHOW log_min_duration_statement;
2015-10-15 14:17:10 EDT psql my_db 123.456.789.012 postgres |LOG: duration: 2059.000 ms statement: SELECT COUNT(*) FROM mv_rings;
2015-10-15 14:17:29 EDT psql my_db 123.456.789.012 postgres |LOG: disconnection: session time: 0:03:27.450 user=postgres database=my_db host=123.456.789.012 port=65269P:\>psql -U postgres -h 132.156.208.45 -d my_db
my_db=# SHOW log_min_duration_statement;
log_min_duration_statement
----------------------------
-1
(1 ligne)
my_db=# SELECT COUNT(*) FROM germplasms;
count
--------
475290
(1 ligne)
my_db=# \q2015-10-15 14:17:44 EDT [unknown] my_db 123.456.789.012 postgres |LOG: connection authorized: user=postgres database=my_db
2015-10-15 14:20:27 EDT psql my_db 123.456.789.012 postgres |LOG: disconnection: session time: 0:02:43.333 user=postgres database=my_db host=123.456.789.012 port=49372Context
StackExchange Database Administrators Q#118018, answer score: 7
Revisions (0)
No revisions yet.