PostgreSQL trace session and log analyzer pgbadger

Инструкция_'PostgreSQL trace session and log analyzer pgbadger'.txt

-- Инструкция_'PostgreSQL trace session and log analyzer pgbadger'

-- save default PosrgreSQL setting: 

select 'alter system set '|| name ||' = '''||setting||''';' , name,setting,context from pg_settings
where name in
('client_min_messages'           
,'log_autovacuum_min_duration'   
,'log_checkpoints'               
,'log_connections'               
,'log_destination'               
,'log_directory'                 
,'log_disconnections'            
,'log_duration'                  
,'log_error_verbosity'           
,'log_filename'                  
,'log_line_prefix'               
,'log_lock_waits'                
,'log_min_duration_statement'    
,'log_min_error_statement'       
,'log_min_messages'              
,'log_rotation_age'              
,'log_rotation_size'             
,'log_statement'                 
,'log_temp_files'                
,'log_truncate_on_rotation'      
,'logging_collector'             
,'shared_preload_libraries'      
,'auto_explain.log_min_duration'); 


-- Enable pg_stat_statements, auto_explain PosrgreSQL extension:  

-- For PostgreSQL version < 9.4 manual edit postgresql.conf^

shared_preload_libraries = pg_stat_statements, auto_explain;
logging_collector = on;

-- For PostgreSQL Version >= 9.4 use command ALTER SYSTEM
-- http://www.postgresql.org/docs/9.4/static/sql-altersystem.html:

alter system set shared_preload_libraries = pg_stat_statements, auto_explain;
-- For 1C apps 
-- alter system set shared_preload_libraries = pg_stat_statements, auto_explain, online_analyze, plantuner;
alter system set logging_collector = on;

-- restart instance

pg_ctl -D $PGDATA stop
pg_ctl -D $PGDATA start

psql -d <you name database>

# create extension pg_stat_statements;
CREATE EXTENSION

# LOAD 'auto_explain';
LOAD

-- You must enable and set some configuration directives in your postgresql.conf before starting: 

-- For PostgreSQL version < 9.4:

-- Manual edit postgresql.conf
auto_explain.log_min_duration = 0 -- increase this value to only log queries with a longer duration
client_min_messages = notice
log_autovacuum_min_duration = 0
log_checkpoints             = on
log_connections             = on
log_destination             = stderr
log_directory               = pg_log
log_disconnections          = on
log_duration                = on
log_error_verbosity = verbose
log_filename                = 'postgresql-%Y%m%d-%H.log'
log_line_prefix             = '%t [%p]: [%l-1] db=%d,user=%u '
log_lock_waits              = on
log_min_duration_statement  = 0 -- increase this value to only log queries with a longer duration
log_min_error_statement     = error
log_min_messages            = info
log_rotation_age            = 1h
log_rotation_size           = 0
log_statement               = 'all'
log_temp_files              = 0
log_truncate_on_rotation    = on
logging_collector           = on

-- For PostgreSQL Version >= 9.4 use command ALTER SYSTEM:

alter system set auto_explain.log_min_duration = 0; -- increase this value to only log queries with a longer duration
alter system set log_checkpoints               = on;
alter system set log_connections               = on;
alter system set log_disconnections            = on;
alter system set log_duration                  = on;
alter system set log_error_verbosity           = verbose;
alter system set log_filename                  = 'postgresql-%Y%m%d-%H.log';
alter system set log_line_prefix               = '%t [%p]: [%l-1] db=%d,user=%u ';
alter system set log_lock_waits                = on;
alter system set log_min_duration_statement    = 0; -- increase this value to only log queries with a longer duration
alter system set log_min_messages              = info;
alter system set log_rotation_age              = '1h';
alter system set log_statement                 = 'all';
alter system set log_temp_files                = 0;
alter system set log_truncate_on_rotation      = on;

-- Additional parameters:
alter system set track_activities = on;
alter system set track_counts = on;
alter system set track_io_timing = on;

-- Reolad PosrgreSQL setting:

select pg_reload_conf();

-- start user procedure 

-- end user proc

-- Undo Defaults PosrgreSQL setting:  
alter system set auto_explain.log_min_duration = -1 ;
alter system set log_checkpoints               = off;
alter system set log_connections               = off;
alter system set log_disconnections            = off;
alter system set log_duration                  = off;
alter system set log_error_verbosity           = default;
alter system set log_filename                  = 'postgresql-%a.log';
alter system set log_line_prefix               = '< %m >';
alter system set log_lock_waits                = off;
alter system set log_min_duration_statement    = -1;
alter system set log_rotation_age              = 1440;
alter system set log_statement                 = 'none';
alter system set log_temp_files                = -1;
alter system set log_min_messages              = info;
select pg_reload_conf();


-- Example run pgbadger:

pgbadger -j 4 postgresql-20160414-15.log -o postgresql-20160414-15.log.html --log-duration --prefix '%t [%p]: [%l-1] db=%d,user=%u ' --timezone +3  

alter system set log_line_prefix               = '%t:%r:%u@%d:[%p]:';
pgbadger -j 4 postgresql-20160414-16.log -o postgresql-20160414-16.log.html --log-duration --prefix '%t:%r:%u@%d:[%p]:' --timezone +3