0Pricing
SQL Academy · Lesson

pgBadger for Log Analysis

Run pgBadger over Postgres logs to produce HTML reports of slow queries, lock waits, and connection patterns.

pgBadger for Log Analysis is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What pgBadger Is

A Perl-based PostgreSQL log analyser that produces beautiful HTML reports of slow queries, lock waits, errors, connection patterns. Run it periodically against your logs.

Configure Logging

postgresql.conf:

log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_min_duration_statement = '500ms'
log_lock_waits = on
log_temp_files = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Run pgBadger

Generate a report:

pgbadger /var/log/postgresql/postgresql-2024-03-15.csv \
  -o report.html

# Multiple files:
pgbadger /var/log/postgresql/*.csv -o report.html

What's In the Report

  • Slowest queries with examples
  • Most frequent queries
  • Most time-consuming queries
  • Lock waits
  • Errors
  • Connection patterns over time
  • Temporary file usage
  • Checkpoint and vacuum activity

Incremental Mode

For continuous log analysis, generate daily reports:

pgbadger -I -O /var/www/pgbadger/ /var/log/postgresql/*.csv
# Updates an HTML dashboard incrementally.

Auto Run via Cron

Nightly job:

0 1 * * * pgbadger -I -O /var/www/pgbadger/ /var/log/postgresql/postgresql-`date +%F`.csv

vs pg_stat_statements

pg_stat_statements: aggregated metrics, in-database.
pgBadger: parses raw logs, finds patterns that aggregates miss (lock waits, errors, connection storms).

Pull Lock Wait Patterns

pgBadger summarises lock waits — which queries blocked which, for how long. Great for diagnosing deadlock and contention issues.

Errors Section

Counts of every error code. "23505 unique_violation: 12,000 times" is a flag — application is retrying duplicate inserts.

Temporary Files

Queries that spill to disk (sort/hash too big for work_mem):

log_temp_files = 0     -- log all temp file usage
-- pgBadger summarises queries that created temp files.

Cloud Alternatives

RDS Performance Insights, Cloud SQL Query Insights, etc. do similar analysis in managed environments — usually built on the same stats sources.

Recap

pgBadger turns Postgres logs into a dashboard.

  • Set log_min_duration_statement
  • Generate periodic HTML reports
  • Find lock waits, temp files, error patterns
  • Complements pg_stat_statements

Quick Check

You see your app retrying many INSERTs. Which Postgres log setting helps you confirm it?

Frequently asked questions

Is the “pgBadger for Log Analysis” lesson free?

Yes — the full text of “pgBadger for Log Analysis” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “pgBadger for Log Analysis”?

Run pgBadger over Postgres logs to produce HTML reports of slow queries, lock waits, and connection patterns. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “pgBadger for Log Analysis” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. pg_stat_statements: Top Queries
  2. pgBadger for Log Analysis
  3. Connection Pooling: PgBouncer
  4. Capacity Planning and Bloat Audits
← Back to SQL Academy