PostgreSQL Log Analysis Tutorial: Complete Database Performance Troubleshooting and Query Optimization Guide for Linux VPS in 2026

Understanding PostgreSQL Logging Architecture

PostgreSQL generates detailed logs that reveal everything happening inside your database server. These logs contain query execution times, error messages, connection patterns, and performance bottlenecks.

By default, PostgreSQL logs minimal information to /var/log/postgresql/postgresql-15-main.log on Ubuntu systems. You can configure extensive logging to capture slow queries, lock waits, and checkpoint activities.

The logging system uses several key parameters in postgresql.conf. These settings control what gets logged and where. Understanding them helps you balance diagnostic detail with disk space usage on your VPS.

Configuring PostgreSQL Logging for Performance Analysis

Start by editing your PostgreSQL configuration file. On Ubuntu 24.04, this file is located at /etc/postgresql/15/main/postgresql.conf:

sudo nano /etc/postgresql/15/main/postgresql.conf

Configure these essential logging parameters:

# Enable logging
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_size = 100MB
log_rotation_age = 1d

# Log slow queries
log_min_duration_statement = 1000  # Log queries taking >1 second
log_statement = 'mod'  # Log all DDL and DML
log_duration = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# Log connections and disconnections
log_connections = on
log_disconnections = on

# Log checkpoints and locks
log_checkpoints = on
log_lock_waits = on
deadlock_timeout = 1s

Restart PostgreSQL to apply changes:

sudo systemctl restart postgresql

This configuration captures slow queries, connection events, and system operations. The log rotation prevents your VPS disk space from filling up.

PostgreSQL Log Analysis for Query Performance

Once logging is enabled, you can identify performance issues by examining query patterns. Use grep to find slow queries:

sudo grep "duration:" /var/log/postgresql/postgresql-15-main.log | grep -v "duration: 0"

This command shows all queries that took measurable time to execute. Look for patterns like:

• Queries consistently taking over 5 seconds
• Frequent execution of identical slow queries
• Lock wait messages indicating contention
• Temporary file usage suggesting insufficient memory

Here's what a typical slow query log entry looks like:

2026-01-15 10:23:45 UTC [1234]: [3-1] user=webapp,db=production,app=rails,client=192.168.1.100 LOG: duration: 2847.123 ms statement: SELECT * FROM orders WHERE customer_id = 12345 ORDER BY created_at DESC;

This query took nearly 3 seconds. This suggests a missing index on customer_id or created_at columns.

Using pgBadger for Advanced Log Analysis

pgBadger generates comprehensive PostgreSQL performance reports from your log files. Install it on your Ubuntu VPS:

sudo apt update
sudo apt install pgbadger

Generate a performance report from your logs:

sudo pgbadger /var/log/postgresql/postgresql-15-main.log -o /tmp/pgbadger_report.html

The report includes query frequency charts, slowest queries, error analysis, and connection statistics. Access the report through your web browser or download it locally.

For continuous monitoring, create a daily report generation script:

#!/bin/bash
# /usr/local/bin/daily-pgbadger.sh

DATE=$(date +%Y-%m-%d)
LOG_FILE="/var/log/postgresql/postgresql-${DATE}_*.log"
REPORT_DIR="/var/www/html/pgbadger"

if [ -f $LOG_FILE ]; then
    pgbadger $LOG_FILE -o "$REPORT_DIR/report-$DATE.html"
    # Keep only last 30 days of reports
    find $REPORT_DIR -name "report-*.html" -mtime +30 -delete
fi

Make it executable and add to crontab:

sudo chmod +x /usr/local/bin/daily-pgbadger.sh
echo "0 6 * * * /usr/local/bin/daily-pgbadger.sh" | sudo crontab -

Identifying and Resolving Common PostgreSQL Issues

PostgreSQL logs reveal specific performance patterns. Here's how to identify and fix common problems:

Missing Indexes: Look for sequential scan warnings in logs. Queries showing "Seq Scan" in execution plans need proper indexing:

sudo grep -A 5 "Seq Scan" /var/log/postgresql/postgresql-15-main.log

Lock Contention: Search for lock wait messages that indicate concurrent transaction conflicts:

sudo grep "lock" /var/log/postgresql/postgresql-15-main.log

High lock contention often requires application-level changes. Try shorter transactions or different isolation levels.

Memory Issues: Find temporary file usage indicating insufficient work_mem:

sudo grep "temporary file" /var/log/postgresql/postgresql-15-main.log

If you see frequent temporary file usage, increase work_mem in postgresql.conf. You can also optimize your queries to reduce memory requirements.

Setting Up Real-Time PostgreSQL Monitoring

For immediate issue detection, monitor PostgreSQL logs in real-time using tail:

sudo tail -f /var/log/postgresql/postgresql-15-main.log | grep -E "(ERROR|WARNING|duration|lock)"

This command shows errors, warnings, slow queries, and lock events as they happen. You can also create custom monitoring scripts that send alerts when specific patterns appear.

Many VPS administrators combine PostgreSQL log analysis with system monitoring. This provides comprehensive database health tracking.

PostgreSQL Log Analysis FAQ

How often should I analyze PostgreSQL logs on my VPS?

Review PostgreSQL logs daily for production systems, weekly for development. Set up automated alerts for critical errors. Monitor slow query trends weekly to catch performance degradation early.

What PostgreSQL log retention policy should I use?

Keep 7 days of detailed logs for immediate troubleshooting. Store compressed weekly summaries for 3 months. This balance provides sufficient diagnostic history without consuming excessive VPS disk space.

How can I reduce PostgreSQL log file sizes without losing important data?

Adjust log_min_duration_statement to capture only queries slower than your performance threshold (typically 100-500ms). Use log_statement = 'ddl' instead of 'mod' to reduce volume while maintaining schema change tracking.

Which PostgreSQL log analysis tools work best for VPS environments?

pgBadger provides excellent offline analysis with minimal resource usage. For real-time monitoring, combine tail commands with grep filters. Avoid resource-heavy monitoring tools on smaller VPS instances to prevent performance impact.

How do I correlate PostgreSQL logs with application performance issues?

Use log_line_prefix to include application names and client IPs. Match timestamps in PostgreSQL logs with application logs to identify which code paths generate slow queries. Monitor connection patterns to detect application connection pool issues.