pg_stat_log

pg_stat_log : Track cumulative PostgreSQL log message statistics by backend, database, user, level, and SQLSTATE.

Overview

Packages

Source

Install

Make sure PGDG repo available:

pig repo add pgdg -u    # add pgdg repo and update cache

Install this extension with pig:

pig install pg_stat_log;		# install via package name, for the active PG version

pig install pg_stat_log -v 18;   # install for PG 18

Config this extension to shared_preload_libraries:

shared_preload_libraries = '$libdir/pg_stat_log';

Create this extension with:

CREATE EXTENSION pg_stat_log;

Usage

Sources: README, SQL objects, control file

pg_stat_log collects cumulative statistics about PostgreSQL log messages. It hooks into emit_log_hook and counts messages by backend type, database, user, severity, SQLSTATE, and SQLSTATE condition name.

Enable

pg_stat_log requires PostgreSQL 18 or newer and must be preloaded:

shared_preload_libraries = 'pg_stat_log'

Restart PostgreSQL, then create the extension:

CREATE EXTENSION pg_stat_log;

View Statistics

SELECT *
FROM pg_stat_log
ORDER BY count DESC;

The view exposes backend_type, database_oid, database_name, user_oid, user_name, elevel, sqlerrcode, sqlerrcode_name, and count.

Common Queries

SELECT elevel, sqlerrcode, sqlerrcode_name, sum(count) AS total
FROM pg_stat_log
GROUP BY elevel, sqlerrcode, sqlerrcode_name
ORDER BY total DESC
LIMIT 10;

SELECT backend_type, elevel, sqlerrcode_name, count
FROM pg_stat_log
WHERE backend_type <> 'client backend'
ORDER BY count DESC;

Reset And Capacity

SELECT pg_stat_log_reset();
SELECT * FROM pg_stat_log_info();

pg_stat_log_info() reports max_entries, num_entries, n_dropped, and stats_reset. Increase pg_stat_log.max_entries if n_dropped grows.

Configuration

Settings include pg_stat_log.enabled, pg_stat_log.min_error_level, and pg_stat_log.max_entries.

emit_log_hook only sees messages that reach the server log. log_min_messages therefore acts as a floor for what can be counted.