csvlog & JSON Logs

Structured PostgreSQL logs (csvlog or jsonlog) feed centralized SIEM, Loki, or CloudWatch Logs Insights. Parse once, query by user_name, application_name, and session_id, and retain off-host so a compromised DB VM does not erase audit evidence.

Recipe

# postgresql.conf
log_destination = 'csvlog'
logging_collector = on
log_line_prefix = '%m [%p] %u@%d %a %h '

# filebeat.yml excerpt (ship csvlog)
filebeat.inputs:
  - type: log
    paths:
      - /var/lib/postgresql/18/main/log/postgresql-*.log
    fields:
      service: postgresql
      env: prod
output.elasticsearch:
  hosts: ["https://es.internal:9200"]

When to reach for this:

• SOC2/HIPAA centralized log retention
• Cross-service correlation with app and IdP logs
• Replacing grep on prod disks for incident search

Working Example

csvlog to Elasticsearch with JSON alternative on PostgreSQL 15+.

# Option A: csvlog (widely used)
log_destination = 'csvlog'
logging_collector = on
log_filename = 'postgresql-%Y-%m-%d.log'

# Option B: jsonlog (PG15+)
log_destination = 'jsonlog'
logging_collector = on

# Vector.dev remap csv to JSON (conceptual)
transforms:
  parse_pg:
    type: remap
    inputs: [postgresql_logs]
    source: |
      . = parse_csv!(string!(.message))
      .service = "postgresql"

-- Force a test log line
\set appname test_shipper
SET application_name TO :'appname';
SELECT pg_sleep(0.1);

What this demonstrates:

• csvlog produces consistent columns for parsers
• jsonlog native one-json-object-per-line on supported versions
• application_name appears in shipper queries for canary validation

Deep Dive

csvlog Key Columns

Cloud Managed Shipping

Retention Tiers

• Hot (7-30d): active incident search
• Warm (90-365d): compliance
• Cold/archive: object storage with Object Lock

Gotchas

• Shipping without rotation - local disk still fills if collector stalls. Fix: monitor shipper lag and disk free alerts.
• Parsing stderr as CSV - broken fields. Fix: one log_destination, not both mixed on same file glob.
• Clock skew - ES timestamps disagree with app logs. Fix: UTC NTP on DB and collectors.
• Multiline stack traces - some errors span lines in text logs; csvlog keeps one row per event. Fix: prefer csvlog/jsonlog over plain text for errors.
• Sensitive statement logging - SIEM becomes PII store. Fix: redact literals in ingest; limit log_statement.
• High-cardinality indexes on message - expensive ES clusters. Fix: index user, db, app, error code fields only.

Alternatives

FAQs

Related

• Logging Basics - prefix and volume
• pgaudit & Compliance Logging - audit fields
• Forensic Queries - SIEM + SQL investigation
• Monitoring Best Practices - metrics complement
• Logging Best Practices - policy checklist

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.