Full Cron Automation Example for PostgreSQL Index Monitoring

This guide shows end‑to‑end automation, including:

• SQL metric collection
• Shell scripts
• Cron scheduling
• Log management
• Basic alert logic

✅ Production‑ready and simple

---

Directory Structure (Recommended)

Create a clean structure first:

Shell

/opt/pg_monitoring/

├── sql/

│ ├── collect_index_size.sql

│ ├── collect_index_usage.sql

│ └── detect_index_growth.sql

├── scripts/

│ └── run_index_monitoring.sh

└── logs/

└── index_monitoring.log

Show more lines

---

Step 1: Create Monitoring Tables (Run Once)

SQL

CREATE SCHEMA IF NOT EXISTS monitoring;

 

CREATE TABLE monitoring.index_size_history (

captured_at TIMESTAMP DEFAULT now(),

schemaname TEXT,

table_name TEXT,

index_name TEXT,

index_size_bytes BIGINT

);

 

CREATE TABLE monitoring.index_usage_history (

captured_at TIMESTAMP DEFAULT now(),

schemaname TEXT,

table_name TEXT,

index_name TEXT,

idx_scan BIGINT

);

Show more lines

---

Step 2: SQL Files for Metric Collection

1️⃣ Collect Index Size

/opt/pg_monitoring/sql/collect_index_size.sql

SQL

INSERT INTO monitoring.index_size_history (

schemaname,

table_name,

index_name,

index_size_bytes

)

SELECT

schemaname,

relname,

indexrelname,

pg_relation_size(indexrelid)

FROM pg_stat_user_indexes;

Show more lines

---

2️⃣ Collect Index Usage

/opt/pg_monitoring/sql/collect_index_usage.sql

SQL

INSERT INTO monitoring.index_usage_history (

schemaname,

table_name,

index_name,

idx_scan

)

SELECT

schemaname,

relname,

indexrelname,

idx_scan

FROM pg_stat_user_indexes;

Show more lines

---

3️⃣ Detect Dangerous Index Growth

/opt/pg_monitoring/sql/detect_index_growth.sql

SQL

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

ORDER BY growth DESC;

Show more lines

✅ Flags indexes growing >100 MB in 7 days

---

Step 3: Monitoring Shell Script

/opt/pg_monitoring/scripts/run_index_monitoring.sh

Shell

#!/bin/bash

 

DB_NAME="mydb"

PSQL="/usr/bin/psql"

BASE_DIR="/opt/pg_monitoring"

LOG_FILE="$BASE_DIR/logs/index_monitoring.log"

 

echo "===== Index Monitoring Started: $(date) =====" >> $LOG_FILE

 

$PSQL -d $DB_NAME -f $BASE_DIR/sql/collect_index_size.sql >> $LOG_FILE 2>&1

$PSQL -d $DB_NAME -f $BASE_DIR/sql/collect_index_usage.sql >> $LOG_FILE 2>&1

 

echo "----- Index Growth Report (Last 7 Days) -----" >> $LOG_FILE

$PSQL -d $DB_NAME -f $BASE_DIR/sql/detect_index_growth.sql >> $LOG_FILE 2>&1

 

echo "===== Index Monitoring Finished: $(date) =====" >> $LOG_FILE

echo "" >> $LOG_FILE

Show more lines

✅ Logs everything
✅ Safe (no destructive actions)
✅ Easy to extend

Make it executable:

Shell

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

Show more lines

---

Step 4: Cron Job Configuration

Edit crontab:

Shell

crontab -e

Show more lines

Daily Execution (Recommended)

Run every day at 2:30 AM:

Shell

30 2 * * * /opt/pg_monitoring/scripts/run_index_monitoring.sh

Show more lines

This is a best practice time window (low traffic).

---

Step 5: Log Inspection Example

Check logs anytime:

Shell

tail -n 50 /opt/pg_monitoring/logs/index_monitoring.log

Show more lines

Example output:

Plain Text

===== Index Monitoring Started: Sat Apr 18 02:30:01 =====

index_name | growth

-------------------+---------

idx_users_profile | 145 MB

idx_orders_json | 312 MB

===== Index Monitoring Finished =====

 

Show more lines

🚨 These indexes need review or REINDEX.

---

Step 6: Optional Email Alert (Simple Example)

Add this to shell script:

Shell

if grep -q "MB" "$LOG_FILE"; then

mail -s "PostgreSQL Index Growth Alert" dba@company.com < $LOG_FILE

fi

Show more lines

✅ Sends alert only when growth detected.

---

Step 7: Weekly & Monthly Enhancements (Optional)

Weekly Unused Index Detection

Shell

0 3 * * 0 psql -d mydb -c "

SELECT indexrelname

FROM pg_stat_user_indexes

WHERE idx_scan = 0;"

Show more lines

Monthly REINDEX Recommendation Report

• Review largest indexes manually
• Schedule REINDEX CONCURRENTLY

---

Safety Rules (Very Important)

✅ Cron should NOT:

• Drop indexes
• Reindex automatically
• Modify schema

Cron should:

• Collect
• Detect
• Alert

Humans decide corrective actions.

---

Minimal Production Strategy (Recommended)

Task	Frequency
Index size collection	Daily
Index usage collection	Daily
Growth analysis	Weekly
REINDEX planning	Monthly
Cleanup unused indexes	Quarterly

---

Summary

• Cron automation makes index monitoring reliable
• SQL collects size and usage metrics
• Shell script centralizes execution
• Logs provide auditability
• Alerts prevent surprises
• Safe by default for production

You now have a complete, production‑ready cron automation setup for PostgreSQL index monitoring ✅