Generating AWR-Like Reports in PostgreSQL: A DBA’s Guide

By Mohsin Ali | Data & Database Architect | Multi-Cloud & Migration Specialist

Introduction

As database administrators, we often rely on AWR (Automatic Workload Repository) reports in Oracle to analyze system performance and optimize queries. However, PostgreSQL lacks a built-in AWR equivalent.

So, how do we generate an AWR-like report in PostgreSQL?

In this article, I'll walk you through multiple approaches to achieving AWR-like workload analysis in PostgreSQL, including pg_stat_statements, pgBadger, pg_profile, and EDB pg_awr.

1. Using pg_stat_statements for Query Performance Analysis

One of PostgreSQL’s built-in extensions, pg_stat_statements, helps track query execution statistics.

Setup

Enable pg_stat_statements by modifying postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
        

Restart PostgreSQL:

systemctl restart postgresql
        

Generate a Query Performance Report

To identify the top queries consuming the most execution time:

SELECT
  userid::regrole AS user,
  dbid::regdatabase AS database,
  query,
  calls,
  total_time,
  mean_time,
  min_time,
  max_time,
  rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;        

This provides insight into which queries need optimization.

2. Using pgBadger for Advanced Log-Based Analysis

pgBadger is a log analyzer that generates AWR-like HTML reports.

Setup

Install pgBadger:

sudo apt install pgbadger  # Debian/Ubuntu
sudo yum install pgbadger  # RHEL/CentOS
        

Modify postgresql.conf:

logging_collector = on
log_min_duration_statement = 500  # Log queries taking >500ms
log_statement = 'all'
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'        

Restart PostgreSQL.

Generate AWR-Like Reports

Run pgBadger to analyze logs and generate reports:

pgbadger -o pg_awr_report.html /var/log/postgresql/postgresql-*.log        

Open pg_awr_report.html in a browser for a detailed performance report.

3. Using pg_profile for Snapshot-Based Workload Analysis

pg_profile provides historical workload snapshots.

Setup

git clone https://github.com/dataegret/pg_profile.git
cd pg_profile
make install        

Capture Snapshots & Generate Report

Take a snapshot:

SELECT profile.capture_snapshot();        

Later, compare snapshots:

SELECT profile.create_report(last_snapshot_id, current_snapshot_id);        

This produces a detailed AWR-like workload report.

4. Using EDB Postgres Advanced Server (pg_awr)

For EnterpriseDB (EDB) Postgres Advanced Server users, there’s a built-in pg_awr module.

Enable AWR Data Collection

ALTER SYSTEM SET edb_enable_awr = on;
SELECT edb_awr.snapshot();        

Generate an AWR Report

SELECT * FROM edb_awr.get_report_html();        

This generates a true AWR-like report, similar to Oracle.

Choosing the Right Method

Method Description Best Use Case pg_stat_statements Built-in SQL query tracking Quick query performance insights pgBadger Log-based analysis (Oracle AWR-like) Full workload analysis & visualization pg_profile Snapshot-based historical analysis Comparing workload changes over time EDB pg_awr True AWR-like reports (EDB only) EnterpriseDB users

For community PostgreSQL, pgBadger is the closest alternative to Oracle AWR reports.

Conclusion

While PostgreSQL lacks a built-in AWR report, these four methods help DBAs monitor and optimize database performance effectively.

#PostgreSQL #DatabasePerformance #AWR #pgBadger #DBA #PostgreSQLMonitoring #PerformanceTuning #DatabasePerformance #PerformanceTuning #SQLPerformance #QueryOptimization #DBATools #DatabaseOptimization #DatabaseTuning #DatabaseMonitoring #SQLTuning #SQLOptimization #CloudDBA #CloudComputing #MultiCloud #AWS #Azure #GoogleCloud #CloudDatabases #DevOps #SRE #DatabaseAutomation