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. Last vacuum time of tables
    8. Last analyze time of tables
    9. Tables with no statistics
    10. Table locks
    11. Transactions blocking each other
    12. Blocked and blocking activities
    13. Bloating percentage of the tables
    14. Bloating percentage of the indexes
    15. Monitoring Disk I/O performance
    16. Foreign keys with no indexes
    17. Needed indexes
    18. Unused indexes
    19. Duplicated indexes
    20. 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