5

I'm trying to set the logging level in postgres to "error" or turn it off altogether.

Relevant parts of postgresql.conf:

log_min_error_statement = error
log_statement = 'none'
log_min_duration_statement = -1

I tried these settings on newly created users and databases to no avail. I restarted postgres server repeatedly.

I also tried these commands in psql:

alter database mydb reset log_statement;
alter database mydb set log statement = 'none';
alter user myuser reset log_statement;
alter user myuser set log_statement = 'none';

alter database mydb reset log_min_duration_statement;
alter database mydb set log log_min_duration_statement = -1;
alter user myuser reset log_min_duration_statement;
alter user myuser set log_min_duration_statement = -1;

Some relevant commands and their outputs:

postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

postgres=# show log_min_duration_statement;
log_min_duration_statement
----------------------------
-1
(1 row)


postgres=# show log_statement;
log_statement
---------------
none
(1 row)

There are no applications connected to the database. Just to make sure:

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname='mydb';

pg_terminate_backend
----------------------
(0 rows)

edit:

I wasn't aware that client messages and logs are not the same thing. Settings above get the job done for setting log_min_error_statement to "ERROR".

Changing client_min_messages doesn't work:

image_search=> show client_min_messages;
client_min_messages
---------------------
notice
(1 row)

image_search=> set client_min_messages to 'ERROR';
SET
image_search=> show client_min_messages;
client_min_messages
---------------------
error
(1 row)

image_search=> insert into ones_counts(key, ones) values (5,5);
INSERT 0 1

That solution https://stackoverflow.com/a/11411109/1508077 does not work either.

I inadvertently duplicated the question. It should really be about suppressing INFO messages when running psql scripts. Not about logging.

I'm on OS X Mavericks. Postgres 9.3.0 installed via homebrew.

Community
  • 1
  • 1
andrew_vvv
  • 75
  • 1
  • 1
  • 5
  • `log_statement = 'all'` is going to log (nearly) every single statement. Did you look at `log_min_messages (enum)`? – Joe Nov 25 '13 at 16:58
  • log_statement = 'none'; log_min_duration_statement = -1; (http://www.postgresql.org/docs/current/static/runtime-config-logging.html). After making those changes, reload your `postgresql.conf`. Eg. as the "postgres" user, issue `SELECT pg_reload_conf();` – bma Nov 25 '13 at 17:01
  • Thanks Joe and bma. I meant to write 'none' instead off 'all'. – andrew_vvv Nov 25 '13 at 17:02
  • `select rolname,rolconfig from pg_roles` -- does that show any role explicitly still having those settings enabled? – bma Nov 25 '13 at 17:07
  • `select rolname, rolconfig from pg_roles` gives two users. One of them is the role I created for the database and the second is the admin. Both `rolname`s have empty `rolconfig` column. – andrew_vvv Nov 25 '13 at 17:13
  • `select pg_reload_conf();` as the admin didn't work either. – andrew_vvv Nov 25 '13 at 17:18
  • "didn't work"? What do you mean? It errored out? Also, what is the output of the following: `show log_min_duration_statement` and `show log_statement`? – bma Nov 25 '13 at 17:30
  • output of `select pg_reload_conf();`: ` pg_reload_conf ---------------- t (1 row)` Logs still show up. outputs of `show log_min_duration_statement;` and `show log_statement;` show `-1` and `none`. – andrew_vvv Nov 25 '13 at 17:36
  • Do you have an application connected, eg JDBC or something? If so, try restarting your app's connections, or restart the db cluster to ensure all connections are broken. I've these changes not have any affect when our Java apps are connected, so a connection restart is necessary. – bma Nov 25 '13 at 17:42
  • I try simple insert commands in postgres command line. There are no applications connected to the database. Just to make sure I ran this command: `select pg_terminate_backend(pid) from pg_stat_activity where datname='mydb';` There are 0 rows. Logs still show up. – andrew_vvv Nov 25 '13 at 17:48
  • Can you clarify: "Logs still show up" means new entries in the db log are still (newly) appearing if you `tail` the log? – bma Nov 25 '13 at 17:51
  • Ha, that's it. By saying "Logs still show up" I didn't mean server.log file which turns out behaves like it should. Postgres logs only errors. What I meant by "Logs still show up" is showing messages like this when I execute insert statement: `INSERT 0 1`. Thanks bma. Is there a way to turn these messages off? I have a big number of huge files containing millions of insert statements to various tables. These messages slow things down considerably. – andrew_vvv Nov 25 '13 at 18:01
  • Are you able to try issuing the following before you connect? `set client_min_messages to 'WARNING';` (or to ERROR) – bma Nov 25 '13 at 18:05
  • bma, please look at the last edit to the question. It's more readable that a comment would be. – andrew_vvv Nov 25 '13 at 18:30
  • I'm running out of ideas. Have you tried executing `psql` with either `psql -d -U -qtAc "select ..."` or just `psql -d -U -qc "select ..."` ? – bma Nov 26 '13 at 15:45
  • I gave up on the damn thing and wrote a python script that executes those statements. No info messages are displayed. Thanks bma! I wish I could transfer you some points for the help. – andrew_vvv Nov 28 '13 at 19:25

1 Answers1

3

Looking at your example, it looks like you are seeing the return message of the insert statement displayed. This is not a logging message but just provides information (OID if any, number of rows affected) of the inserted row.

You are right, that INSERT 0 1 message can't be disabled on the server side. It is psql displaying status info given back by PostgreSQL as a routine part of the protocol. You can disable it by redirecting STDERR from psql to /dev/null but that is about the only way.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182