PostgreSql

Add HTML Report Generation for PostgreSQL Index Monitoring

HTML reports are useful because they:

Are easy to read in browsers
Can be shared with managers and teams
Look professional
Can be emailed or hosted internally
Combine multiple reports into one dashboard

We will generate a single self‑contained HTML report automatically.

Final Architecture (After This Step)

Shell

/opt/pg_monitoring/

├── reports/

│ ├── sql/

│ │ ├── index_health_report.sql

│ │ ├── largest_indexes.sql

│ │ ├── growing_indexes.sql

│ │ ├── unused_indexes.sql

│ │ └── gin_indexes.sql

│ └── html/

│ ├── header.html

│ ├── footer.html

│ └── style.css

├── output/

│ ├── txt/

│ ├── csv/

│ └── html/

│ └── index_report_YYYY-MM-DD.html

└── scripts/

└── generate_index_report.sh

Show more lines

1️⃣ Create HTML Template Files

📄 reports/html/header.html

HTML

<!DOCTYPE html>

<html>

<head>

<title>PostgreSQL Index Report</title>

</head>

<body>

<h1>PostgreSQL Index Monitoring Report</h1>

<p>Generated at: {{DATE}}</p>

<hr>

Show more lines

📄 reports/html/footer.html

HTML

<hr>

Generated automatically by PostgreSQL Index Monitoring

</p>

</body>

</html>

Show more lines

📄 reports/html/style.css

CSS

body {

font-family: Arial, sans-serif;

margin: 40px;

background-color: #f9f9f9;

}

h1, h2 {

color: #2c3e50;

}

table {

border-collapse: collapse;

width: 100%;

margin-bottom: 30px;

background: #ffffff;

}

th, td {

border: 1px solid #dddddd;

padding: 8px;

}

th {

background-color: #34495e;

color: white;

}

tr:nth-child(even) {

background-color: #f2f2f2;

}

.warning {

color: red;

font-weight: bold;

}

Show more lines

2️⃣ Generate HTML Tables Using psql

psql has a built‑in HTML output mode.

We’ll use:

Shell

psql -H -c "SELECT ..."

Show more lines

3️⃣ Update the Report Generator Script (HTML Included)

📄 scripts/generate_index_report.sh

Shell

#!/bin/bash

DB="mydb"

BASE="/opt/pg_monitoring"

DATE=$(date +%F)

PSQL="/usr/bin/psql"

HTML_OUT="$BASE/output/html/index_report_$DATE.html"

HEADER="$BASE/reports/html/header.html"

FOOTER="$BASE/reports/html/footer.html"

STYLE="$BASE/reports/html/style.css"

# ---------- HTML HEADER ----------

sed "s/{{DATE}}/$DATE/" $HEADER > $HTML_OUT

# embed CSS directly

echo "<style>" >> $HTML_OUT

cat $STYLE >> $HTML_OUT

echo "</style>" >> $HTML_OUT

# ---------- SECTION FUNCTION ----------

add_section () {

TITLE=$1

QUERY=$2

echo "<h2>$TITLE</h2>" >> $HTML_OUT

$PSQL -d $DB -H -c "$QUERY" >> $HTML_OUT

}

# ---------- HTML SECTIONS ----------

add_section "Index Health Summary" "

SELECT

COUNT(*) AS total_indexes,

SUM(CASE WHEN idx_scan = 0 THEN 1 ELSE 0 END) AS unused_indexes,

pg_size_pretty(SUM(pg_relation_size(indexrelid))) AS total_index_size

FROM pg_stat_user_indexes;

add_section "Top 10 Largest Indexes" "

SELECT

indexrelname AS index_name,

relname AS table_name,

pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,

idx_scan

FROM pg_stat_user_indexes

ORDER BY pg_relation_size(indexrelid) DESC

LIMIT 10;

add_section "Fast Growing Indexes (Last 7 Days)" "

SELECT

index_name,

pg_size_pretty(MAX(index_size_bytes) - MIN(index_size_bytes)) AS growth

FROM monitoring.index_size_history

WHERE captured_at >= now() - INTERVAL '7 days'

GROUP BY index_name

HAVING MAX(index_size_bytes) - MIN(index_size_bytes) > 100000000;

add_section "Unused Indexes" "

SELECT

indexrelname AS index_name,

relname AS table_name,

pg_size_pretty(pg_relation_size(indexrelid)) AS index_size

FROM pg_stat_user_indexes

WHERE idx_scan = 0

ORDER BY pg_relation_size(indexrelid) DESC;

add_section "GIN Indexes (High Risk)" "

SELECT

indexrelname AS gin_index,

relname AS table_name,

pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,

idx_scan

FROM pg_stat_user_indexes

WHERE indexrelname ILIKE '%gin%'

ORDER BY pg_relation_size(indexrelid) DESC;

# ---------- HTML FOOTER ----------

cat $FOOTER >> $HTML_OUT

Show more lines

Make executable (if not already):

Shell

chmod +x /opt/pg_monitoring/scripts/generate_index_report.sh

Show more lines

4️⃣ Cron Job (Already Works)

No change required — HTML is now generated automatically:

Shell

0 3 * * * /opt/pg_monitoring/scripts/generate_index_report.sh

Show more lines

5️⃣ Sample HTML Output (What You’ll See)

✅ Clear tables
✅ Color‑coded headers
✅ Scrollable and shareable
✅ Manager‑friendly

Example sections:

Index Health Summary
Top Largest Indexes
Fast‑Growing Indexes
Unused Indexes
GIN Index Risk Table

Open it with:

Shell

xdg-open index_report_2026-04-18.html

Show more lines

or serve via Nginx/Apache.

6️⃣ Best Practices for HTML Reports

✅ One HTML file per day
✅ Retain 30–90 days
✅ Embed CSS for portability
✅ Do NOT auto‑fix based on HTML
✅ Use HTML for decision review

Summary

HTML reports added cleanly
Uses built‑in psql HTML mode
Integrates with existing cron/job setup
Produces professional, readable reports
Safe for production systems

You now have TXT + CSV + HTML auto‑generated PostgreSQL index reports ✅

PostgreSql

Saturday, 9 May 2026

No comments:

Post a Comment