Bash Script for PostgreSQL Health Check

To ensure the health and performance of your PostgreSQL database, you can set up a daily health check script. The script will help monitor key database metrics and alert you to any potential issues.

Bash Script for PostgreSQL Health Check:

Click here to download script.

What This Script Does:

1. Check PostgreSQL Connection
2. Check disk space on the PostgreSQL data directory
3. Check CPU usage
4. Check memory usage
5. Check for uptime
6. Check for any errors in PostgreSQL logs
7. Check for long-running queries
8. Check for replication lag (if replication is configured)
9. Check existing replication slots details
10. Check database connections and transactions
11. Check for orphan or outdated prepared transactions
12. Monitoring transaction ID exhaustion (wraparound)
13. Checklist for every databases in the cluster
    1. Create extension pgstattuple (if not exist)
    2. Database size
    3. How many tables having more than $TUP_THRESHOLD dead tuples particular database
    4. Sessions that are blocking other sessions
    5. Number of live tuples and dead tuples in tables
    6. Queries running more than $RUNTIME_THRESHOLD minutes
    7. Most frequently executed queries
    8. Slow queries
    9. Queries that return many rows
    10. Most time-consuming queries
    11. Last vacuum time of tables
    12. Last analyze time of tables
    13. Tables with no statistics
    14. Table locks
    15. Transactions blocking each other
    16. Blocked and blocking activities
    17. Bloating percentage of the tables
    18. Bloating percentage of the indexes
    19. Monitoring Disk I/O performance
    20. Foreign keys with no indexes
    21. Needed indexes
    22. Unused indexes
    23. Duplicated indexes
    24. Current transaction age of tables

How to Use:

1. Save this script to a file, e.g., postgresql_health_check.sh.
2. Make the script executable:
   chmod +x postgresql_health_check.sh

3. You can schedule this script to run daily using cron. Open your crontab configuration:
   crontab -e

4. Add an entry to run the script at a specific time every day (e.g., at 2:00 AM):
   0 2 * * * /path/to/postgresql_health_check.sh > /dev/null 2>&1

This will execute the health check script at 2:00 AM daily and log the output to /var/log/pg_health_check.log.

You may want to expand or modify the script to suit your specific monitoring requirements.

If you have any issue when running script , you should convert DOS text file to Unix format using below command :

dos2unix postgresql_health_check.sh