pg_sqlog

pig build pkg pg_sqlog         # build RPM / DEB packages

pig repo add pgsql -u          # Add repo and update cache

CREATE EXTENSION pg_sqlog CASCADE;  -- requires: file_fdw

-- Current day's log
SELECT * FROM sqlog.log();

-- Specific day's log
SELECT * FROM sqlog.log('yesterday');
SELECT * FROM sqlog.log('2024-01-15');

-- Error summary
SELECT error_severity, COUNT(*) FROM sqlog.log() GROUP BY 1;

-- Top 3 slowest query patterns
SELECT
  AVG(sqlog.duration(message)) AS avg_duration,
  COUNT(*) AS count,
  sqlog.preparable_query(message) AS query_pattern
FROM sqlog.log()
WHERE message ~ '^duration'
GROUP BY 3
ORDER BY 1 DESC
LIMIT 3;

-- Query summary with timing
SELECT
  log_time::time,
  sqlog.duration(message),
  sqlog.summary(message)
FROM sqlog.log('yesterday')
WHERE message ~ '^duration';

SELECT * FROM sqlog.autovacuum() LIMIT 5;
SELECT * FROM sqlog.autoanalyze() LIMIT 5;

log_destination = 'syslog,csvlog'
log_filename = 'postgresql.%F'
logging_collector = 'on'
log_rotation_age = '1d'
log_rotation_size = 0
log_truncate_on_rotation = 'on'
log_min_duration_statement = 1000