Message-ID: <1517914520.2849.4.camel@cybertec.at>
Subject: Re: advice about logging
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Thomas Poty <thomas.poty@gmail.com>, pgsql-admin@postgresql.org
Date: Tue, 06 Feb 2018 11:55:20 +0100
In-Reply-To: 
 <CAN_ctniTni+p9Y4y=g8xzUy2z_Pfb_YsjZk0AzcceObGjFphMA@mail.gmail.com>
References: 
	<CAN_ctniTni+p9Y4y=g8xzUy2z_Pfb_YsjZk0AzcceObGjFphMA@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

Thomas Poty wrote:
> This morning, when I arrived to my desk. PostgreSQL had crashed.
> 
> I try to inspect the log file but its size about 27G and i cannot check it.

Why?
The interesting entries are probably towards the end of the file.

> So, in order to try to reduce log i wonder which configuration about logging is a good compromise between quantity and quality.
> 
> Here is my configuration,: 
> 
> select name,boot_val,reset_val from pg_settings where name ~ 'log';
>             name             |            boot_val            |        reset_val        
> -----------------------------+--------------------------------+-------------------------
>  log_autovacuum_min_duration | -1                             | -1
>  log_checkpoints             | off                            | off
>  log_connections             | off                            | on
>  log_destination             | stderr                         | stderr
>  log_directory               | pg_log                         | pg_log
>  log_disconnections          | off                            | on
>  log_duration                | off                            | off
>  log_error_verbosity         | default                        | default
>  log_executor_stats          | off                            | off
>  log_file_mode               | 384                            | 384
>  log_filename                | postgresql-%Y-%m-%d_%H%M%S.log | postgresql-%Y-%m-%d.log
>  log_hostname                | off                            | off
>  log_line_prefix             |                                | %t [%p]: [%l] %u@%d 
>  log_lock_waits              | off                            | off

You want "on". Locks that last more tham a second are a problem.

>  log_min_duration_statement  | -1                             | 1000
>  log_min_error_statement     | error                          | error
>  log_min_messages            | warning                        | warning

These two are what you want to change.

Set log_min_messages and log_min_error_statement to 'log'.

>  log_parser_stats            | off                            | off
>  log_planner_stats           | off                            | off
>  log_replication_commands    | off                            | off
>  log_rotation_age            | 1440                           | 1440
>  log_rotation_size           | 10240                          | 10240
>  log_statement               | none                           | none
>  log_statement_stats         | off                            | off
>  log_temp_files              | -1                             | -1

You should set this to some positive value to see if many of your statements
need to write temporary files and increasing work_mem would be a good idea.

>  log_timezone                | GMT                            | Europe/Luxembourg
>  log_truncate_on_rotation    | off                            | off
>  logging_collector           | off                            | on
>  syslog_facility             | local0                         | local0
>  syslog_ident                | postgres                       | postgres
>  wal_log_hints               | off                            | off
> (31 rows)

Yours,
Laurenz Albe