Friday, January 21, 2022

Postgres parameters to check performance

Need to check/Set below parameters: 

show shared_preload_libraries;

show debug_print_plan;

show debug_print_parse;

show logging_collector;

show log_statement;

show log_min_duration_statement;

show log_temp_files;

show log_connections;

show log_disconnections;

show log_lock_waits;

show log_line_prefix;

show log_checkpoints;

show log_error_verbosity;


ALTER DATABASE DATABASE_NAME set shared_preload_libraries = pg_stat_statements;

ALTER DATABASE DATABASE_NAME set log_statement = 'all';

ALTER DATABASE DATABASE_NAME set log_min_duration_statement = 0;

ALTER DATABASE DATABASE_NAME set logging_collector = on;

ALTER DATABASE DATABASE_NAME set debug_print_plan = on;

ALTER DATABASE DATABASE_NAME set debug_print_parse = on;

ALTER DATABASE DATABASE_NAME set log_connections = on;

ALTER DATABASE DATABASE_NAME set log_disconnections = on;

ALTER DATABASE DATABASE_NAME set log_lock_waits = on;

ALTER DATABASE DATABASE_NAME set log_temp_files = 0;

ALTER DATABASE DATABASE_NAME set log_line_prefix = '%t:%r:%u@%d:[%p]: db=%d,user=%u,app=%a,client=%h';

ALTER DATABASE DATABASE_NAME set log_checkpoints = on;

ALTER DATABASE DATABASE_NAME set log_error_verbosity = default;



select tc.table_schema, tc.table_name, kc.column_name

from information_schema.table_constraints tc

  join information_schema.key_column_usage kc 

    on kc.table_name = tc.table_name and kc.table_schema = tc.table_schema and kc.constraint_name = tc.constraint_name

where tc.constraint_type = 'PRIMARY KEY'and kc.ordinal_position is not nullorder by tc.table_schema,

         tc.table_name,

         kc.position_in_unique_constraint;

No comments:

Post a Comment