PostgreSQL - Log Analysis Using pgBadger

Overview

This document explains how to install pgBadger and generate PostgreSQL performance reports from log files. All database, schema, and table names are kept generic so the guide can be reused across environments.

 

pgBadger is a powerful PostgreSQL log analyzer that converts database logs into detailed HTML reports, helping identify slow queries, errors, connections, locks, and workload patterns.

Prerequisites

• Linux server with PostgreSQL logs enabled

• Access to PostgreSQL log directory

• Perl installed (pgBadger dependency)

• sudo/root privileges for installation

Step 1: Download pgBadger

Navigate to a working directory and download the pgBadger package.

 

cd /opt

curl -L -o pgbadger.tar.gz https://github.com/darold/pgbadger/archive/refs/tags/v12.4.tar.gz

 

Explanation:

This command downloads a specific pgBadger version from GitHub and saves it locally.

Step 2: Extract the Package

tar xzf pgbadger.tar.gz

 

Explanation:

This extracts the downloaded archive into a directory such as pgbadger-12.4.

Step 3: Install pgBadger Binary

Copy the executable to a system-wide path.

 

sudo cp /opt/pgbadger-12.4/pgbadger /usr/local/bin/

sudo chmod +x /usr/local/bin/pgbadger

 

Explanation:

This makes pgBadger available globally so it can be executed from anywhere.

Step 4: Verify Installation

pgbadger --version

 

Expected Output:

pgBadger version X.X

PostgreSQL Logging Requirements

Ensure PostgreSQL logging is configured properly for meaningful reports.

 

Recommended parameters in postgresql.conf:

logging_collector = on

log_min_duration_statement = 0

log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h'

log_checkpoints = on

log_connections = on

log_disconnections = on

log_lock_waits = on

 

Explanation:

These settings capture detailed query and connection information required for analysis.

Generate Report — Last 24 Hours

pgbadger /path/to/logfile   --begin "$(date -d '1 day ago' '+%Y-%m-%d %H:%M:%S')"   --end   "$(date '+%Y-%m-%d %H:%M:%S')"   --jobs 8   --sample 10   -o /path/to/output/report_last24hrs.html

Explanation of Parameters

--begin / --end:

Defines the time window for analysis.

 

--jobs:

Number of parallel workers to speed up processing.

 

--sample:

Samples queries for performance statistics instead of processing every occurrence.

 

-o:

Output HTML report file.

Generate Report for Specific Time Window

/usr/local/bin/pgbadger   -p '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h'   /path/to/logfile*   -b 'YYYY-MM-DD HH:MI:SS'   -e 'YYYY-MM-DD HH:MI:SS'   -o report_specific_window.html

Explanation

-p:

Specifies log_line_prefix format so pgBadger can correctly parse logs.

 

-b / -e:

Defines begin and end timestamps for the report.

 

Wildcard (*):

Allows processing multiple rotated log files.

Example — Analytical Environment

pgbadger /path/to/logfile-YYYY-MM-DD*   --begin "YYYY-MM-DD HH:MI:SS"   --end   "YYYY-MM-DD HH:MI:SS"   --jobs 8   --sample 10   -p '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h'   -o /path/to/output/report.html

Best Practices

• Run pgBadger during low system load when processing large logs

• Archive old logs before analysis

• Use parallel jobs for faster report generation

• Monitor disk space for HTML output

• Schedule periodic reports using cron jobs

Common Use Cases

• Identify slow queries

• Analyze peak load periods

• Detect locking and contention

• Monitor connection spikes

• Investigate production incidents

Conclusion

pgBadger provides deep insights into PostgreSQL workload behavior through log analysis. Regular report generation helps database administrators proactively detect performance issues and optimize database operations.