PostgreSQL 9.4.6 – Extensive Logfile Due to log_statement_stats on RHEL 7

postgresqlpostgresql-9.4

I got a newly installed database (PG 9.4.6 / RHEL 7) which logfile grows rapidly.

Most entries are like

2016-03-03 10:33:29.259 CET [56868] user@db STATEMENT:  SELECT field1 AS a1, field2 AS a2, field3 AS a3, field4 AS a4, field5 AS a5, field6 AS a6, MSISDN AS a7, field8 AS a8, RETRY_COUNT AS a9 FROM table WHERE ((field3 <= $1) AND (field8 IS NULL)) ORDER BY field3 ASC LIMIT $2 OFFSET $3
2016-03-03 10:33:29.259 CET [56868] user@db LOG:  EXECUTE MESSAGE STATISTICS
2016-03-03 10:33:29.259 CET [56868] user@db DETAIL:  ! system usage stats:
        !       0.000006 elapsed 0.000000 user 0.000000 system sec
        !       [0.526408 user 0.112125 sys total]
        !       0/0 [0/0] filesystem blocks in/out
        !       0/0 [0/1281] page faults/reclaims, 0 [0] swaps
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
        !       0/0 [3516/0] voluntary/involuntary context switches

I read through the documentation and found out, that it should be one of the log_*_stats parameters and despite the missing config entry in the postgresql.conf and postgresql.auto.conf

> egrep "log_.*stats" postgresql.*conf
postgresql.conf:#log_parser_stats = off
postgresql.conf:#log_planner_stats = off
postgresql.conf:#log_executor_stats = off
postgresql.conf:#log_statement_stats = off

the running instance shows that the parameter is set to "on".

> echo "show log_statement_stats" | psql
 log_statement_stats
---------------------
 on

I tried to change it by applying an "ALTER SYSTEM", but it doesn't help. The parameter stays "on".

I also reloaded the database (pg_ctl reload) and restarted it (its a pacemaker resource, so pcs resource restart postgre_db) – But nothing happens.

How do I turn off this setting?

Best Answer

There are multiple levels where you configuration options are set in PostgreSQL:

  1. system defaults
  2. values specified in postgresql.conf or the startup command line
  3. values preset for a database, user (maybe others, too) using, for example, ALTER DATABASE ... SET {option} ...
  4. values set for the current session

Going down the list from 2., you have fewer and fewer options to set. Furthermore, there are some that can only be set by a superuser (log_statement_stats) being one of them.

At the same time, the lower in the list an option is set, the higher its precedence: a session-level setting will override all the upper levels.

Speaking specifically about log_statement_stats, it is possible to set it from a session - but only a superuser can do it. If your application connects with a superuser role (which is not really recommended), it can set this as one of the first things after obtaining the connection. Look for it in your sources.

Ways of changing settings

The numbers refer to the levels listed above.

  1. Supplying defaults at compile time
  2. ALTER SYSTEM - this will store the settings in a postgresql.auto.conf file, which will be read in addition to postgresql.conf.
  3. ALTER DATABASE ... SET ... or ALTER ROLE ... [IN DATABASE database_name] SET .... Some settings can be set on database objects (like tables, functions, etc.), too.
  4. SET \[SESSION\] ... - to change things for the current connection (often dubbed 'session'). There is even a way to change things until the end of the current transaction: SET LOCAL ...
Related Question