PG operation and maintenance -- error log and slow log

Error log

Related parameters

      
      

       
       log_destination  Log format ,stderr
       
       ,csvlog
       
       ,syslog
       
       

       
       
logging_collector 
       
       = 
       
       on              Start log collection 
       
       

       
       
log_directory 
       
       = 
       
       'log'               Log directory 
       
       

       
       
log_filename 
       
       = 
       
       'postgresql-%Y-%m-%d_%H%M%S.log' Log file name 
       
       
  
       
       
log_truncate_on_rotation 
       
       = off      Is it covered? 
       
       
   
       
       
log_rotation_age 
       
       = 
       
       1d               Generate every day 
       
       

       
       
log_rotation_size 
       
       = 
       
       10MB            The size of each log 
      
      
      
      

       
       
1.
       
       
2.
       
       
3.
       
       
4.
       
       
5.
       
       
6.
       
       
7.
       
       
8.
       
       
9.
       
       
10.
       
       
11.
       
       
12.
       
       
13.
      
      

Only time-based file switching will overwrite , File switching during server restart will not overwrite .

log The level of logging

log Level by parameter log_min_messages control , Respectively debug5 debug4 ..debug1、INFO、NOTICE、WARNING、ERROR、LOG、FATAL、PANIC The lower the level , The less logs you print .

Slow query log

log_min_duration_statement

By the parameter log_min_duration_statement control , If a sql The running time of the statement is greater than or equal to the set number of milliseconds , Then the SQL The statement and its running time will be recorded in the log , When set to 0 when , All the running SQL Statements will be recorded .

SQL Statement log

log_min_error_statement

Record a sql Generated a certain level of logs , Usually error log . By the parameter log_min_error_statement To control , Respectively debug5 debug4 ..debug1、INFO、NOTICE、WARNING、ERROR、LOG、FATAL、PANIC Level control .

log_statement

PG You can also control whether to record DDL、DML Or all SQL sentence , This parameter is determined by log_statement Controlled ,

（1） When set to none when , Don't record sql sentence ;

（2） When set to ddl when , Record all DDL sentence ;

（3） When set to mod when , Record DML,truncate,copy from etc. ,PREPARE、EXECUTE or EXPLAIN ANALYZE The statement produces an update , These statements will also be recorded

（4） When set to all when , all SQL Statements will be recorded .

notes ： If used log_min_duration_statement, More than a certain time SQL The statement has been printed in the log , And these SQL Statement conforms to log_statement Parameter configuration and output to log , be SQL The statement will not be printed twice in the log , Only print once .

log_temp_files

When SQL With sorting 、 Temporary query results or Hash Temporary files will be generated , These temporary files are sometimes large , Need to be monitored , Parameters can be set “log_temp_files” Is an integer value , When the generated temporary file is greater than this value , Then print the information of the temporary file into the log file .

Execution plan log

PostgreSQL There are also the following debugging SQL Log parameters of execution plan and process .

·debug_print_parse： Set to “on” when , hold SQL The parse tree of is printed into the log .

·debug_print_rewritten： Set to “on” when , hold SQL Rewrite the query and print it to the log .

·debug_print_plan： Set to “on” when , hold SQL Print the execution plan of to the log .

·debug_pretty_print： Whether to indent above 3 Kind of log to make it more readable .

Other logs

PostgreSQL There are also many log switches , It is up to the user to decide what information to record in the log .

·log_checkpoints： Whether to record checkpoint.

·log_connections： Whether to record the connection of the client .

·log_disconnections： Whether to record the information of client disconnection .

·log_duration： Whether to record the duration of each completed statement , For clients using the extended query protocol , Syntax analysis 、 binding 、 perform , The time taken for each step is recorded separately .

·log_hostname： Whether to record the host name of the client .

·log_lock_waits： When a session waits longer than deadlock_timeout when , Whether to record a log message .