pgbadger session - 2 : PostgreSQL configuration for pgbadger

 

PostgreSQL config to bring forward much benefits out of pgbadger:

A. pg_setting - we need to have at minimum for pgbadger to work flawlessly or effectively:

1. log_min_duration_statement = 0 [increase the value if needed, this value creates additional stress to a loaded machine]

2. for stderr (default)

log_line_prefix = '%t [%p]: db=%d,user=%u,app=%a,client=%h '

3. Other options to enable:

        log_checkpoints = on

        log_connections = on

        log_disconnections = on

        log_lock_waits = on

        log_temp_files = 0

        log_autovacuum_min_duration = 0

        log_error_verbosity = default

4. Ensure english is the language for log messages

        lc_messages='en_US.UTF-8'

        lc_messages='C'

5. Recommendation: Do not enable both log_min_duration_statement, log_duration and log_statement all together

log_min_duration_statement --- number of ms over which the queries shouldbe reported

log_duration --- set to log_min_duration_statement set to -1 and log_duration set

or

log_statement =all (no setting for other 2)

B. Verify pg_setting shows the new values were set properly

select name,setting

from pg_settings

where name in ('log_destination'

, 'log_min_duration_statement'

, 'log_line_prefix'

, 'log_checkpoints'

, 'log_connections'

, 'log_disconnections'

, 'log_lock_waits'

, 'log_temp_files'

, 'log_autovacuum_min_duration'

, 'log_error_verbosity'

, 'lc_messages'

)

order by 1;

alter system set log_min_duration_statement = 0;

alter system set log_line_prefix = '%t [%p]: db=%d,user=%u,app=%a,client=%h ';

alter system set log_checkpoints = on;

alter system set log_connections = on;

alter system set log_disconnections = on;

alter system set log_lock_waits = on;

alter system set log_temp_files = 0;

alter system set log_autovacuum_min_duration = 0;

select pg_reload_conf();

select name,setting

from pg_settings

where name in ('log_destination'

, 'log_min_duration_statement'

, 'log_line_prefix'

, 'log_checkpoints'

, 'log_connections'

, 'log_disconnections'

, 'log_lock_waits'

, 'log_temp_files'

, 'log_autovacuum_min_duration'

, 'log_error_verbosity'

, 'lc_messages'

)

order by 1;

-- We will need to take a postgresql bounce, since the log_connections and log_disconnections wont reflect with pg_reload_conf().

Target value:

            name             |                 setting

-----------------------------+------------------------------------------

 lc_messages                 | en_US.UTF-8

 log_autovacuum_min_duration | 0

 log_checkpoints             | on

 log_connections             | on

 log_destination             | stderr

 log_disconnections          | on

 log_error_verbosity         | default

 log_line_prefix             | %t [%p]: db=%d,user=%u,app=%a,client=%h

 log_lock_waits              | on

 log_min_duration_statement  | 0

 log_temp_files              | 0

(11 rows)

postgres=#

YouTube Video: