log_min_messages=debug5, despite an explicit setting to warning on postgresql.conf

[Kong Man]

We ran into a production issue during our maintenance window when I switched over LifeKeeper/PostgreSQL nodes from one to another. We noticed that the database was 1000 times slower than it usually is, then realized that the log_min_messages setting was set to 'debug5' for no reason.

Facts:
The postgresql.conf file has always been using the default value, which is 'warning'.An attempt to explicitly set log_min_messages=warning and reload could not override the setting of 'debug5'.The heavy debug5 logging on our production system drastically slowed down our database, and eventually filled up the root file system (due to the huge /var/log/postgresql)The LifeKeeper failover has been done in a recent past with the same unmodified postgresql.log file without such an excessive logging behavior.Changing log_destination from syslog to stderr appeared to have done the trick, after hours of troubleshooting. The log_min_messages value took the explicit setting from postgresql.log ('warning') and restored our database performance.
I could not find any relevant issue reported and want to see if anyone has a clue about this issue.

Thank you,
-Kong

[Kevin Grittner]

Post by Kong Man
The postgresql.conf file has always been using the default value, which is 'warning'.

Maybe it's not using the postgresql.conf file you think it is. Does
this show the file you've been looking at?:

SHOW config_file;

-Kevin

[Walter Hurry]

Post by Kong Man
The postgresql.conf file has always been using the default value, which is 'warning'.

Maybe it's not using the postgresql.conf file you think it is. Does this
SHOW config_file;

Incidentally, I thought the default level was notice.

[Tom Lane]

Post by Kong Man
We ran into a production issue during our maintenance window when I switched over LifeKeeper/PostgreSQL nodes from one to another. We noticed that the database was 1000 times slower than it usually is, then realized that the log_min_messages setting was set to 'debug5' for no reason.

How was the postmaster started? From memory, "-d5" on its command line
would do that, and would not have been overridable from postgresql.conf.

regards, tom lane

[Kong Man]

Hi Tom,

Thank you for some clue in this. So, I assume changing log_destination (from syslog to stderr) was a way around it to override log_min_messages when the -d option is specified?

I am checking with the vender, SIOS, on what scenarios LifeKeeper would start up PostgreSQL with the -d5 option.

Best regards,
-Kong

Subject: Re: [ADMIN] log_min_messages=debug5, despite an explicit setting to warning on postgresql.conf
Date: Sun, 23 Dec 2012 12:21:12 -0500

Post by Kong Man
We ran into a production issue during our maintenance window when I switched over LifeKeeper/PostgreSQL nodes from one to another. We noticed that the database was 1000 times slower than it usually is, then realized that the log_min_messages setting was set to 'debug5' for no reason.

How was the postmaster started? From memory, "-d5" on its command line
would do that, and would not have been overridable from postgresql.conf.
regards, tom lane

[Kong Man]

So, LifeKeeper default setting of LKPGSQLDEBUG=5 on
/etc/default/LifeKeeper was the culprit for this issue, probably by
starting postmaster with -d5 option.

Thank you for pointing us to the right direction.

-Kong

From: ***@hotmail.com
To: ***@sss.pgh.pa.us
CC: pgsql-***@postgresql.org
Subject: RE: [ADMIN] log_min_messages=debug5, despite an explicit setting to warning on postgresql.conf
Date: Sun, 23 Dec 2012 21:59:35 -0800





Hi Tom,

Thank you for some clue in this. So, I assume changing log_destination (from syslog to stderr) was a way around it to override log_min_messages when the -d option is specified?

I am checking with the vender, SIOS, on what scenarios LifeKeeper would start up PostgreSQL with the -d5 option.

Best regards,
-Kong

Subject: Re: [ADMIN] log_min_messages=debug5, despite an explicit setting to warning on postgresql.conf
Date: Sun, 23 Dec 2012 12:21:12 -0500

Post by Kong Man
We ran into a production issue during our maintenance window when I switched over LifeKeeper/PostgreSQL nodes from one to another. We noticed that the database was 1000 times slower than it usually is, then realized that the log_min_messages setting was set to 'debug5' for no reason.

How was the postmaster started? From memory, "-d5" on its command line
would do that, and would not have been overridable from postgresql.conf.
regards, tom lane