Add Email Delivery for PostgreSQL HTML Index Report

With email delivery:

• DBAs get reports without logging into servers
• Managers can review performance easily
• Issues are noticed early
• Reports become part of operational workflow

We’ll use Linux mail / sendmail / mailx, which is the most common, reliable approach.

---

What This Will Do

✅ Generate HTML report
✅ Email it as HTML body (not just attachment)
✅ Optional attachment support
✅ Fully automated via cron
✅ Safe for production

---

Prerequisites (One‑Time)

1️⃣ Ensure Mail Utility Is Installed

Debian / Ubuntu

Shell

sudo apt install mailutils

Show more lines

RHEL / CentOS / Rocky

Shell

sudo yum install mailx

Show more lines

✅ Assumes server can send email (SMTP configured or relay allowed).

---

Step 1: Decide Report Recipients

Edit once in script:

Shell

EMAIL_TO="dba@company.com,team@company.com"

EMAIL_FROM="postgres-monitor@company.com"

Show more lines

---

Step 2: Update HTML Report Generator Script

Below is the FULL updated script with HTML generation + email delivery.

📄 scripts/generate_index_report.sh

Shell

#!/bin/bash

 

DB="mydb"

BASE="/opt/pg_monitoring"

DATE=$(date +%F)

PSQL="/usr/bin/psql"

 

EMAIL_TO="dba@company.com"

EMAIL_FROM="postgres-monitor@company.com"

SUBJECT="PostgreSQL Index Report - $DATE"

 

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

echo "<style>" >> $HTML_OUT

cat $STYLE >> $HTML_OUT

echo "</style>" >> $HTML_OUT

 

add_section () {

TITLE=$1

QUERY=$2

 

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

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

}

 

# ---------- HTML CONTENT ----------

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

 

# ---------- EMAIL DELIVERY ----------

mail -a "Content-Type: text/html" \

-s "$SUBJECT" \

-r "$EMAIL_FROM" \

"$EMAIL_TO" < $HTML_OUT

Show more lines

Make executable (if not already):

Shell

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

Show more lines

---

Step 3: Cron Job (No Change)

Your existing cron job now automatically emails the report.

Shell

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

Show more lines

✅ HTML is generated
✅ Email is sent immediately
✅ Zero manual steps

---

Step 4: What the Email Looks Like

📧 Email Subject

PostgreSQL Index Report - 2026-04-18

📄 Email Body

• Full HTML report
• Styled tables
• Index risk information
• Easy to read on desktop & mobile

✅ No attachment required
✅ Opens instantly
✅ Manager‑friendly

---

Optional: Send as HTML Attachment Instead

If your mail server blocks HTML bodies, use attachment mode:

Shell

mail -s "$SUBJECT" \

-a "$HTML_OUT" \

"$EMAIL_TO" < /dev/null

Show more lines

---

Optional: Send Only If Issues Found

Add logic before sending:

Shell

ISSUES=$(grep -c "<tr>" $HTML_OUT)

 

if [ "$ISSUES" -gt 5 ]; then

mail -a "Content-Type: text/html" -s "$SUBJECT" "$EMAIL_TO" < $HTML_OUT

fi

Show more lines

✅ Avoids alert fatigue
✅ Sends only meaningful reports

---

Best Practices for Email Reports

✅ Send once per day
✅ Use clear subject with date
✅ Group recipients wisely
✅ Use HTML body, not just attachments
✅ Keep reports under control (don’t spam)

---

Summary

• HTML index reports now emailed automatically
• Works with cron
• Uses standard Linux mail tools
• Production‑safe and readable
• Zero downtime impact

You now have end‑to‑end PostgreSQL index monitoring:

✅ Collect
✅ Analyze
✅ Report
✅ Email