Log Insights on Self-Managed PostgreSQL

Two paths for self-managed PostgreSQL: collector tails the log file directly (agent mode only), or the monitor role gets pg_read_server_files permission and we read through PG itself.

Path A: collector-tailed file (agent mode only)

If MonPG's agent runs on the same host as PostgreSQL — or on a host that NFS-mounts the log directory — point the collector at the log file:

MONPG_LOG_FILE=/var/log/postgresql/postgresql-15-main.log
MONPG_LOG_TAIL=true

The collector tails the file and follows rotation with the same cadence as PostgreSQL. No extra setup, no pipeline. This is the easiest path when it's available.

Path B: monitor-role grant (hosted or agent)

For setups where the collector runs elsewhere — different host, different container, different cloud — grant the monitor role permission to read log files through PostgreSQL itself. Uses pg_read_file() with the server-side path from log_directory + log_filename.

-- Run as superuser:
GRANT pg_read_server_files TO monpg_monitor_<12hex>;

Then we query:

SELECT * FROM pg_read_file(current_setting('log_directory') || '/' || current_setting('log_filename')) LIMIT 1000;

This isn't the default because pg_read_server_files is a powerful role — it grants read access to any server-local file. Grant only after confirming your security policy is OK with that scope. Most internal teams are; some compliance regimes are not.

Recommended logging settings

For the best Log Insights experience, set these in postgresql.conf:

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d.log'

log_line_prefix = '%m [%p] %q%u@%d '
log_min_duration_statement = 1000
log_lock_waits = on
log_checkpoints = on
log_autovacuum_min_duration = 1000
log_connections = on
log_disconnections = on

Reload with SELECT pg_reload_conf(); — none of these need a full restart.

Log rotation

If you use an external rotator (logrotate) instead of PostgreSQL's built-in rotation, keep rotated files in log_directory for at least the retention window we read (default 7 days, configurable). Or switch to PostgreSQL's native rotation by setting log_rotation_age + log_rotation_size. Either works; PG's native rotation is fewer moving parts.