PostgreSQL

Monitor PostgreSQL database logs including query logs, error logs, slow query analysis, and connection monitoring using the File Stream plugin

PostgreSQL Integration

Monitor and analyze PostgreSQL database logs in real-time using LogFlux Agent’s File Stream plugin. This configuration-based approach provides comprehensive database monitoring, query performance analysis, and security auditing for PostgreSQL deployments.

Overview

The PostgreSQL integration leverages LogFlux Agent’s File Stream plugin to:

Real-time monitoring of query logs, error logs, and connection logs
Query performance analysis with slow query identification and execution time tracking
Security auditing with authentication attempts and privilege escalation monitoring
Connection monitoring with client connection tracking and session analysis
Replication monitoring for master-slave and streaming replication setups
Vacuum and maintenance operation tracking for database health

Installation

The File Stream plugin is included with LogFlux Agent. Enable it for PostgreSQL log monitoring:

1
2
3
4
5


# Enable File Stream plugin
sudo systemctl enable --now logflux-filestream

# Verify plugin status
sudo systemctl status logflux-filestream

PostgreSQL Configuration

Configure PostgreSQL logging in postgresql.conf:

Basic Logging Configuration

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


-- Enable logging
logging_collector = on
log_destination = 'stderr,csvlog'
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_rotation_age = 1d
log_rotation_size = 100MB

-- Log levels
log_min_messages = info
log_min_error_statement = error
log_min_duration_statement = 1000  # Log queries taking >1 second

-- Query logging
log_statement = 'all'  # or 'ddl', 'mod', 'none'
log_duration = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

-- Connection logging
log_connections = on
log_disconnections = on
log_hostname = on

-- Lock monitoring
log_lock_waits = on
deadlock_timeout = 1s

CSV Log Format Configuration (Recommended)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


-- CSV logging for structured parsing
log_destination = 'csvlog'
log_filename = 'postgresql-%Y-%m-%d.csv'
log_csv_header = on
log_csv_separator = ','
log_csv_quote = '"'

-- Detailed CSV logging
log_line_prefix = '%m [%p] %q%u@%d '
log_statement = 'all'
log_duration = on
log_min_duration_statement = 0

Basic Configuration

Configure the File Stream plugin to monitor PostgreSQL logs by creating /etc/logflux-agent/plugins/filestream-postgresql.toml:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33


[filestream.postgresql_main]
paths = ["/var/log/postgresql/postgresql-*.log"]
format = "postgresql_log"
tags = ["postgresql", "database", "sql"]
fields = {
  service = "postgresql",
  log_type = "main"
}

[filestream.postgresql_csv]
paths = ["/var/log/postgresql/postgresql-*.csv"]
format = "csv"
tags = ["postgresql", "database", "csv"]
fields = {
  service = "postgresql",
  log_type = "csv"
}

[filestream.postgresql_slow]
paths = ["/var/log/postgresql/postgresql-*.log"]
format = "postgresql_log"
tags = ["postgresql", "slow", "performance"]
fields = {
  service = "postgresql",
  log_type = "slow_query"
}

# Filter for slow queries only
[filestream.postgresql_slow.processors.grep]
patterns = [
  "duration:",
  "LOG:.*duration"
]

PostgreSQL Log Formats

Standard PostgreSQL Log Format

1
2
3
4
5
6
7


[filestream.postgresql_standard]
paths = ["/var/log/postgresql/postgresql-*.log"]
format = "regex"
regex = '^(?P<timestamp>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{3} \w+) \[(?P<pid>\d+)\] (?P<level>\w+): (?P<message>.*)$'
parse_timestamp = true
timestamp_field = "timestamp"
timestamp_format = "2006-01-02 15:04:05.000 MST"

Detailed PostgreSQL Log with User/DB Context

1
2
3
4
5
6
7
8


[filestream.postgresql_detailed]
paths = ["/var/log/postgresql/postgresql-*.log"]
format = "regex"
regex = '^(?P<timestamp>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{3} \w+) \[(?P<pid>\d+)\]: \[(?P<line_num>\d+)-(?P<session_line>\d+)\] user=(?P<user>[^,]+),db=(?P<database>[^,]+),app=(?P<application>[^,]+),client=(?P<client_host>[^ ]+) (?P<level>\w+): (?P<message>.*)$'
parse_timestamp = true
timestamp_field = "timestamp"
timestamp_format = "2006-01-02 15:04:05.000 MST"
tags = ["postgresql", "detailed"]

CSV Log Format (Structured)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


[filestream.postgresql_csv_structured]
paths = ["/var/log/postgresql/postgresql-*.csv"]
format = "csv"
csv_headers = [
  "log_time", "user_name", "database_name", "process_id", "connection_from", 
  "session_id", "session_line_num", "command_tag", "session_start_time", 
  "virtual_transaction_id", "transaction_id", "error_severity", "sql_state_code",
  "message", "detail", "hint", "internal_query", "internal_query_pos", 
  "context", "query", "query_pos", "location", "application_name"
]
parse_timestamp = true
timestamp_field = "log_time"
timestamp_format = "2006-01-02 15:04:05.000 MST"

Advanced Configuration

Query Performance Monitoring

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


[filestream.postgresql_performance]
paths = ["/var/log/postgresql/postgresql-*.log"]
format = "postgresql_log"
tags = ["postgresql", "performance"]
fields = {
  service = "postgresql",
  log_type = "performance"
}

# Extract query duration and add performance metrics
[filestream.postgresql_performance.processors.extract]
source_field = "message"
pattern = 'duration: (?P<duration_ms>\d+\.\d+) ms'

[filestream.postgresql_performance.processors.add_fields]
fields = {
  duration_seconds = "{{ div .duration_ms 1000 }}",
  slow_query = "{{ if gt .duration_ms 1000.0 }}true{{ else }}false{{ end }}"
}

Connection Monitoring

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


[filestream.postgresql_connections]
paths = ["/var/log/postgresql/postgresql-*.log"]
format = "postgresql_log"
tags = ["postgresql", "connections"]
fields = {
  service = "postgresql",
  log_type = "connections"
}

# Filter for connection events
[filestream.postgresql_connections.processors.grep]
patterns = [
  "connection authorized",
  "connection received",
  "disconnection:",
  "authentication failed",
  "connection rejected"
]

Security and Authentication Monitoring

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


[filestream.postgresql_security]
paths = ["/var/log/postgresql/postgresql-*.log"]
format = "postgresql_log"
tags = ["postgresql", "security", "auth"]
fields = {
  service = "postgresql",
  log_type = "security"
}

# Filter for security-related events
[filestream.postgresql_security.processors.grep]
patterns = [
  "FATAL.*authentication",
  "FATAL.*password",
  "FATAL.*role",
  "ERROR.*permission denied",
  "LOG.*connection authorized",
  "LOG.*connection rejected"
]

Replication Monitoring

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


[filestream.postgresql_replication]
paths = ["/var/log/postgresql/postgresql-*.log"]
format = "postgresql_log"
tags = ["postgresql", "replication"]
fields = {
  service = "postgresql",
  log_type = "replication"
}

# Filter for replication events
[filestream.postgresql_replication.processors.grep]
patterns = [
  "replication connection",
  "standby",
  "streaming",
  "wal receiver",
  "archive recovery",
  "checkpoint"
]

Lock and Deadlock Monitoring

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


[filestream.postgresql_locks]
paths = ["/var/log/postgresql/postgresql-*.log"]
format = "postgresql_log"
tags = ["postgresql", "locks", "deadlocks"]
fields = {
  service = "postgresql",
  log_type = "locks"
}

# Filter for lock-related events
[filestream.postgresql_locks.processors.grep]
patterns = [
  "deadlock detected",
  "process.*still waiting for",
  "lock timeout",
  "acquired.*lock",
  "cancelled on conflict"
]

Usage Examples

Monitor PostgreSQL Database

1
2
3
4
5
6
7
8


# Stream all PostgreSQL logs
logflux-cli stream --filter 'service:postgresql'

# Monitor specific database
logflux-cli stream --filter 'service:postgresql AND database:myapp'

# Track query performance
logflux-cli stream --filter 'service:postgresql AND log_type:performance'

Performance Analysis

1
2
3
4
5
6
7
8


# Monitor slow queries (>5 seconds)
logflux-cli stream --filter 'service:postgresql AND duration_seconds:>5'

# Track connection issues
logflux-cli stream --filter 'service:postgresql AND log_type:connections'

# Monitor lock waits
logflux-cli stream --filter 'service:postgresql AND message:waiting'

Security Monitoring

1
2
3
4
5
6
7
8


# Track failed authentication attempts
logflux-cli stream --filter 'service:postgresql AND message:authentication AND level:FATAL'

# Monitor privilege escalation attempts
logflux-cli stream --filter 'service:postgresql AND message:permission AND level:ERROR'

# Track administrative operations
logflux-cli stream --filter 'service:postgresql AND (message:CREATE OR message:DROP OR message:ALTER)'

Database-Specific Monitoring

pg_stat_statements Integration

Monitor query statistics with pg_stat_statements:

1
2
3
4
5
6
7
8


-- Enable pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Configure in postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
pg_stat_statements.save = on

Create monitoring script:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


#!/bin/bash
# pg_stat_statements logger
while true; do
    psql -d postgres -c "
    SELECT 
        now() as timestamp,
        query,
        calls,
        total_time,
        mean_time,
        rows
    FROM pg_stat_statements 
    WHERE calls > 10 AND mean_time > 100
    ORDER BY mean_time DESC 
    LIMIT 10;" 2>&1 | logger -t postgresql-stats
    sleep 60
done

Table and Index Monitoring

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- Log table statistics
SELECT 
    schemaname,
    tablename,
    n_tup_ins + n_tup_upd + n_tup_del as modifications,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000;

Connection Pool Monitoring (pgBouncer)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


[filestream.pgbouncer]
paths = ["/var/log/pgbouncer/pgbouncer.log"]
format = "regex"
regex = '^(?P<timestamp>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{3} \w+) (?P<pid>\d+) (?P<level>\w+) (?P<message>.*)$'
parse_timestamp = true
timestamp_field = "timestamp"
timestamp_format = "2006-01-02 15:04:05.000 MST"
tags = ["pgbouncer", "connection-pool"]
fields = {
  service = "postgresql",
  component = "pgbouncer",
  log_type = "connection_pool"
}

Monitoring and Alerting

Key Metrics to Monitor

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34


# Slow query alert
[alerts.postgresql_slow_queries]
query = "service:postgresql AND duration_seconds:>10"
threshold = 5
window = "2m"
message = "PostgreSQL slow queries detected on {{ .database }}"

# Connection limit alert
[alerts.postgresql_connection_limit]
query = "service:postgresql AND message:too AND message:connections"
threshold = 1
window = "30s"
message = "PostgreSQL connection limit reached"

# Replication lag alert
[alerts.postgresql_replication_lag]
query = "service:postgresql AND message:replication AND message:lag"
threshold = 1
window = "1m"
message = "PostgreSQL replication lag detected"

# Authentication failure alert
[alerts.postgresql_auth_failures]
query = "service:postgresql AND level:FATAL AND message:authentication"
threshold = 5
window = "1m"
message = "High authentication failure rate in PostgreSQL"

# Deadlock alert
[alerts.postgresql_deadlocks]
query = "service:postgresql AND message:deadlock"
threshold = 1
window = "30s"
message = "PostgreSQL deadlock detected"

Dashboard Metrics

Monitor these key PostgreSQL metrics:

Query performance (execution time, slow queries count)
Connection metrics (active connections, connection rate)
Database size (table sizes, index usage)
Replication status (lag, streaming status)
Lock monitoring (lock waits, deadlocks)
Vacuum operations (autovacuum frequency, duration)
Error rates (by error type and severity)
Authentication (success/failure rates, user activity)

Troubleshooting

Common Issues

PostgreSQL logs not appearing:

1
2
3
4
5
6
7
8
9


# Check PostgreSQL is running
sudo systemctl status postgresql

# Verify log configuration
sudo -u postgres psql -c "SHOW log_destination;"
sudo -u postgres psql -c "SHOW logging_collector;"

# Check log file permissions
sudo ls -la /var/log/postgresql/

Log parsing errors:

1
2
3
4
5
6
7
8


# Check log format in PostgreSQL
sudo -u postgres psql -c "SHOW log_line_prefix;"

# Test regex pattern
tail -n 10 /var/log/postgresql/postgresql-*.log

# Validate CSV format
head -n 5 /var/log/postgresql/postgresql-*.csv

Performance monitoring issues:

1
2
3
4
5
6
7
8


# Check pg_stat_statements
sudo -u postgres psql -c "SELECT * FROM pg_stat_statements LIMIT 1;"

# Verify slow query logging
sudo -u postgres psql -c "SHOW log_min_duration_statement;"

# Check current connections
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

Replication monitoring problems:

1
2
3
4
5
6
7
8


# Check replication status
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

# Verify standby status
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

# Check WAL status
sudo -u postgres psql -c "SELECT pg_current_wal_lsn();"

Best Practices

Performance

Enable pg_stat_statements for comprehensive query analysis
Set appropriate log_min_duration_statement to avoid log flooding
Use CSV format for better parsing performance
Regular VACUUM and ANALYZE operations

Security

Enable connection logging for security auditing
Monitor authentication failures and implement rate limiting
Log DDL operations for change tracking
Use SSL connections and log SSL status

High Availability

Monitor replication lag and failover scenarios
Track connection pool health (pgBouncer, pgPool)
Monitor backup operations and recovery procedures
Implement proper alerting for critical database events

Log Management

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


# Optimize log rotation for PostgreSQL
/var/log/postgresql/*.log /var/log/postgresql/*.csv {
    daily
    rotate 30
    missingok
    notifempty
    compress
    delaycompress
    postrotate
        systemctl reload postgresql.service > /dev/null 2>&1 || true
        systemctl reload logflux-filestream.service > /dev/null 2>&1 || true
    endpostrotate
}

Query Optimization

1
2
3
4
5
6


-- Enable auto_explain for query plan logging
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1000;
SET auto_explain.log_analyze = true;
SET auto_explain.log_verbose = true;
SET auto_explain.log_timing = true;

Integration Examples

Docker Deployment

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


version: '3.8'
services:
  postgresql:
    image: postgres:15
    environment:
      POSTGRES_DB: myapp
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - postgres_logs:/var/log/postgresql
      - ./postgresql.conf:/etc/postgresql/postgresql.conf
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
    
  logflux-agent:
    image: logflux/agent:latest
    volumes:
      - postgres_logs:/var/log/postgresql:ro
      - ./logflux-config:/etc/logflux-agent/plugins
    depends_on:
      - postgresql

volumes:
  postgres_data:
  postgres_logs:

Kubernetes Deployment

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50


apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgresql
spec:
  serviceName: postgresql
  replicas: 1
  selector:
    matchLabels:
      app: postgresql
  template:
    metadata:
      labels:
        app: postgresql
    spec:
      containers:
      - name: postgresql
        image: postgres:15
        env:
        - name: POSTGRES_DB
          value: "myapp"
        - name: POSTGRES_USER
          value: "myuser"
        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              name: postgres-secret
              key: password
        volumeMounts:
        - name: postgres-data
          mountPath: /var/lib/postgresql/data
        - name: postgres-logs
          mountPath: /var/log/postgresql
        - name: postgres-config
          mountPath: /etc/postgresql/postgresql.conf
          subPath: postgresql.conf
      volumes:
      - name: postgres-config
        configMap:
          name: postgres-config
      - name: postgres-logs
        emptyDir: {}
  volumeClaimTemplates:
  - metadata:
      name: postgres-data
    spec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 100Gi

This comprehensive PostgreSQL integration provides real-time database monitoring, query performance analysis, and security auditing using LogFlux Agent’s File Stream plugin. The configuration-based approach offers detailed insights into database operations, connection patterns, and performance characteristics across different PostgreSQL deployment scenarios.

Disclaimer

The PostgreSQL logo and trademarks are the property of the PostgreSQL Global Development Group. LogFlux is not affiliated with, endorsed by, or sponsored by the PostgreSQL Global Development Group. The PostgreSQL logo is used solely for identification purposes to indicate compatibility and integration capabilities.