Log Insights in Minutes: A Simpler pgBadger Workflow

Sometimes the fastest way to understand a PostgreSQL workload is not another dashboard. It is a good log report.

pgBadger is a PostgreSQL log analysis tool that turns raw PostgreSQL logs into an interactive HTML report. It helps summarize query activity, connection patterns, errors, temporary files, lock waits, autovacuum activity, and more.

Earlier guidance for generating pgBadger reports from Azure Database for PostgreSQL Flexible Server focused on exporting logs through Diagnostic Settings, storing them in a storage account, and then using tools such as BlobFuse and jq to extract PostgreSQL log lines from JSON files.

That workflow is still useful when customers centralize logs across multiple servers. However, if you are already using the Server logs feature in Azure Database for PostgreSQL Flexible Server, there is a much simpler path.

Why use this workflow?

With Server logs, you can download native PostgreSQL .log files directly from the Azure portal and run pgBadger locally.

When should you use this workflow?

Before you start

• A machine where you can install or run pgBadger.
• A working Perl runtime.
• Git Bash on Windows, so the multi-line shell commands work as shown.
• Portal access to your Azure Database for PostgreSQL Flexible Server.
• Permission to update server parameters and enable Server logs.

Workflow overview

1. Install or prepare pgBadger on the machine where you will analyze logs.
2. Configure log_line_prefix so pgBadger can parse each log line.
3. Enable Server logs, so PostgreSQL logs are available for download.
4. Download the logs and run pgBadger locally.

Step 1: Install pgBadger

Before generating a report, you need pgBadger available on the machine where you plan to analyze the downloaded PostgreSQL log files. Run this on a Linux VM, WSL, or another Linux-based environment where you can install packages.

Copy and run

sudo apt-get update && sudo apt-get install -y git perl make gcc && \
git clone https://github.com/darold/pgbadger.git && \
cd pgbadger && \
perl Makefile.PL && \
make && \
sudo make install && \
pgbadger -V

Step 2: Configure log_line_prefix

This is a one-time server configuration step.

The log_line_prefix parameter controls the beginning of each PostgreSQL log line. pgBadger uses this prefix to extract useful fields such as timestamp, user, database, and process ID.

In the Azure portal, open your Flexible Server and go to Server parameters. Search for:

Parameter

log_line_prefix

Set this value

%m user=%u db=%d pid=%p:

Then select Save.

In Server parameters, confirm that the custom value is saved for log_line_prefix.

Figure 1: Set log_line_prefix so pgBadger can correctly parse timestamp, user, database, and process ID from each log line.

Prefix tokens

After this change, log lines should look like this:

Example log line

2026-06-22 19:00:00.070 UTC user=pgadmin db=highcpu pid=3805603: LOG: statement: SELECT 1 FROM pg_extension WHERE extname='pg_stat_statements'

The matching pgBadger prefix for this log format is:

Matching pgBadger prefix

%m user=%u db=%d pid=%p:

You will use this same value later in the pgBadger command.

Step 3: Enable Server logs for download

This is also a one-time setup step.

In the Azure portal, open your Flexible Server and go to Server logs. Enable:

Portal setting

Capture logs for download

Set the retention period based on how long you want logs to remain available for download. For example, a 7-day retention period keeps logs available for download for 7 days.

In Server logs, enable Capture logs for download and choose the retention window.

Figure 2: Enable Capture logs for download and set a retention period long enough to cover the analysis window you want to inspect.

Once enabled, hourly log files appear in the Server logs blade. The files are named by date and hour, for example:

Example log files

postgresql_2026_06_22_19_00_00.log
postgresql_2026_06_22_20_00_00.log

Step 4: Download and organize the logs locally

From the Server logs page, select the .log files for the time window you want to analyze and download them.

For example, to analyze activity between 19:00 and 21:00 UTC, download:

Example files to download

postgresql_2026_06_22_19_00_00.log
postgresql_2026_06_22_20_00_00.log

On your local machine, create a folder for that analysis window. A simple convention is to use the Mon-DD format.

Folder name

Jun-22

Place the downloaded .log files inside that folder. Your local folder structure should look like this:

Folder structure

pgbadger-13.1/
  pgbadger
  Jun-22/
    postgresql_2026_06_22_19_00_00.log
    postgresql_2026_06_22_20_00_00.log

Step 5: Generate the pgBadger report

Open Git Bash from the folder where pgBadger is located. For example, if pgBadger is inside the pgbadger-13.1 folder, open Git Bash from that folder.

Copy and run

FOLDER=Jun-22

ls -lh ./$FOLDER

perl -X ./pgbadger -f stderr \
  --prefix '%m user=%u db=%d pid=%p:' \
  ./$FOLDER/*.log \
  -o ./$FOLDER/pgbadger-report.html

Command breakdown

When the command completes successfully, you should see output like this:

Expected output

Parsed 12134249 bytes of 12134249 (100.00%), queries: 26684, events: 83
LOG: Ok, generating html report...

Step 6: Open the report

Open the generated report:

Copy and run

start ./$FOLDER/pgbadger-report.html

The report opens in your default browser. The final report is created here:

Generated report path

Jun-22/pgbadger-report.html

What the report can show

The pgBadger report gives you a quick view into the workload shape for the selected log window. For example, in a sample run across two hourly log files, pgBadger summarized:

• Total number of queries.
• Number of unique normalized queries.
• Query traffic over time.
• Events such as errors and fatal messages.
• Session and connection patterns.

Once the report opens, start with Global Stats to confirm the time range, total queries, normalized queries, and query peak.

Figure 3: Start with Global Stats to validate the selected time range, total query count, normalized query count, and query peak.

Query volume and normalized queries

Many raw queries can often reduce to a smaller number of normalized query patterns. This helps identify whether the workload is spread across many different query shapes or dominated by a smaller set of repeated statements.

Traffic patterns

The SQL Traffic section helps identify spikes, quiet periods, and workload changes over time.

Figure 4: Use SQL Traffic to identify query spikes, quiet periods, and workload changes during the selected log window.

Figure 5: Review the query breakdown to compare read vs. write volume and query-type distribution for the selected Server logs window.

For example, if the report shows a steady baseline followed by a sharp spike, that spike can be correlated with application activity, batch jobs, synthetic tests, or operational events during the same time window.

Query duration

If query duration shows 0 ms or the slow query sections are empty, it usually means duration logging was not enabled when the logs were collected.

In that case, pgBadger can still show query counts and events, but it cannot calculate the slowest queries, total execution time, average duration, or maximum duration.

To unlock those timing sections, enable log_min_duration_statement, collect fresh logs, and rerun pgBadger.

What pgBadger cannot infer from missing logs

pgBadger reports are only as complete as the log data you provide. If PostgreSQL did not log duration, lock waits, temporary files, or autovacuum activity during the selected time window, pgBadger cannot reconstruct those details later.

Repeatable copy/paste block

Copy and run

FOLDER=Jun-22

ls -lh ./$FOLDER

perl -X ./pgbadger -f stderr \
  --prefix '%m user=%u db=%d pid=%p:' \
  ./$FOLDER/*.log \
  -o ./$FOLDER/pgbadger-report.html

start ./$FOLDER/pgbadger-report.html

For another date, change only this line:

Update this value

FOLDER=Jun-22

Examples:

Example folder values

FOLDER=Jun-23
FOLDER=Jul-01
FOLDER=Aug-15

Optional: Improve report quality

pgBadger can only analyze the information captured in PostgreSQL logs.

The default logs may be enough for query frequency, connection activity, and errors. For deeper performance troubleshooting, consider enabling additional logging parameters based on your scenario.

Useful parameters include:

Recommended logging parameters

log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0

To capture query durations, configure:

Duration logging

log_min_duration_statement = 1000

This logs statements that run longer than 1000 milliseconds.

For short test runs, you can temporarily use:

Short test run only

log_min_duration_statement = 0

Common mistakes and quick fixes

Common troubleshooting FAQs

head -5 ./$FOLDER/*.log

log_min_duration_statement = 1000

# or temporarily for testing
log_min_duration_statement = 0

Capture logs for download

pgbadger -V

perl -X ./pgbadger

Summary

For customers already using Azure Database for PostgreSQL Flexible Server logs, the pgBadger workflow is straightforward:

1. Install pgBadger.
2. Configure log_line_prefix.
3. Enable Server logs for download.
4. Download the .log files.
5. Place them in a local date-based folder.
6. Run pgBadger with the matching prefix.
7. Open pgbadger-report.html.

References

• pgBadger - source and documentation GitHub
• pgBadger - project site
• Azure - Download server logs from the portal Flexible Server
• Azure - Logging concepts Flexible Server
• Azure - Configure server parameters via the portal
• PostgreSQL - log_line_prefix and logging parameters