Fairdb Health Check

Perform a comprehensive health check of the FairDB PostgreSQL infrastructure including server resources, database status, backup integrity, and customer databases.

System Health Overview

#!/bin/bash
# FairDB Comprehensive Health Check

echo "================================================"
echo "        FairDB System Health Check"
echo "        $(date '+%Y-%m-%d %H:%M:%S')"
echo "================================================"

Step 1: Server Resources Check

echo -e "\n[1/10] SERVER RESOURCES"
echo "------------------------"

# CPU Usage
CPU_USAGE=$(top -bn1 | grep "Cpu(s)" | awk '{print $2}' | cut -d'%' -f1)
echo "CPU Usage: ${CPU_USAGE}%"
if (( $(echo "$CPU_USAGE > 80" | bc -l) )); then
    echo "⚠️  WARNING: High CPU usage detected"
fi

# Memory Usage
MEM_INFO=$(free -m | awk 'NR==2{printf "Memory: %s/%sMB (%.2f%%)\n", $3,$2,$3*100/$2 }')
echo "$MEM_INFO"
MEM_PERCENT=$(free | grep Mem | awk '{print $3/$2 * 100.0}')
if (( $(echo "$MEM_PERCENT > 90" | bc -l) )); then
    echo "⚠️  WARNING: High memory usage detected"
fi
…

Step 2: PostgreSQL Service Status

echo -e "\n[2/10] POSTGRESQL SERVICE"
echo "-------------------------"

# Check if PostgreSQL is running
if systemctl is-active --quiet postgresql; then
    echo "✅ PostgreSQL service: RUNNING"

    # Get version and uptime
    sudo -u postgres psql -t -c "SELECT version();" | head -1

    UPTIME=$(sudo -u postgres psql -t -c "
        SELECT now() - pg_postmaster_start_time() as uptime;")
    echo "Uptime: $UPTIME"
else
    echo "❌ CRITICAL: PostgreSQL service is NOT running!"
    echo "Attempting to start..."
    sudo systemctl start postgresql
    sleep 5
…

Step 3: Database Connections

echo -e "\n[3/10] DATABASE CONNECTIONS"
echo "---------------------------"

# Connection statistics
sudo -u postgres psql -t << EOF
SELECT
    'Total Connections: ' || count(*) || '/' || setting AS connection_info
FROM pg_stat_activity, pg_settings
WHERE pg_settings.name = 'max_connections'
GROUP BY setting;
EOF

# Connections by database
echo -e "\nConnections by database:"
sudo -u postgres psql -t -c "
    SELECT datname, count(*) as connections
    FROM pg_stat_activity
    GROUP BY datname
…

Step 4: Database Performance Metrics

echo -e "\n[4/10] PERFORMANCE METRICS"
echo "--------------------------"

# Cache hit ratio
sudo -u postgres psql -t << 'EOF'
SELECT
    'Cache Hit Ratio: ' ||
    ROUND(100.0 * sum(heap_blks_hit) /
          NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) || '%'
FROM pg_statio_user_tables;
EOF

# Transaction statistics
sudo -u postgres psql -t -c "
    SELECT
        'Transactions: ' || xact_commit || ' commits, ' ||
        xact_rollback || ' rollbacks, ' ||
        ROUND(100.0 * xact_rollback / NULLIF(xact_commit + xact_rollback, 0), 2) || '% rollback rate'
…

Step 5: Backup Status

echo -e "\n[5/10] BACKUP STATUS"
echo "--------------------"

# Check pgBackRest status
if command -v pgbackrest &> /dev/null; then
    echo "pgBackRest Status:"

    # Get all stanzas
    STANZAS=$(sudo -u postgres pgbackrest info --output=json 2>/dev/null | jq -r '.[].name' 2>/dev/null)

    if [ -z "$STANZAS" ]; then
        echo "⚠️  WARNING: No backup stanzas configured"
    else
        for STANZA in $STANZAS; do
            echo -e "\nStanza: $STANZA"

            # Get last backup info
            LAST_BACKUP=$(sudo -u postgres pgbackrest --stanza=$STANZA info --output=json 2>/dev/null | \
…

Step 6: Replication Status

echo -e "\n[6/10] REPLICATION STATUS"
echo "-------------------------"

# Check if this is a primary or replica
IS_PRIMARY=$(sudo -u postgres psql -t -c "SELECT pg_is_in_recovery();")

if [ "$IS_PRIMARY" = " f" ]; then
    echo "Role: PRIMARY"

    # Check replication slots
    REP_SLOTS=$(sudo -u postgres psql -t -c "
        SELECT count(*) FROM pg_replication_slots WHERE active = true;")
    echo "Active replication slots: $REP_SLOTS"

    # Check connected replicas
    sudo -u postgres psql -t -c "
        SELECT client_addr, state, sync_state,
               pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) as lag
…

Step 7: Security Audit

echo -e "\n[7/10] SECURITY AUDIT"
echo "---------------------"

# Check for default passwords
echo "Checking for common issues..."

# SSL status
SSL_STATUS=$(sudo -u postgres psql -t -c "SHOW ssl;")
echo "SSL: $SSL_STATUS"
if [ "$SSL_STATUS" != " on" ]; then
    echo "⚠️  WARNING: SSL is not enabled"
fi

# Check for users without passwords
NO_PASS=$(sudo -u postgres psql -t -c "
    SELECT count(*) FROM pg_shadow WHERE passwd IS NULL;")
if [ $NO_PASS -gt 0 ]; then
    echo "⚠️  WARNING: $NO_PASS users without passwords"
…

Step 8: Customer Database Health

echo -e "\n[8/10] CUSTOMER DATABASES"
echo "-------------------------"

# Check each customer database
CUSTOMER_DBS=$(sudo -u postgres psql -t -c "
    SELECT datname FROM pg_database
    WHERE datname NOT IN ('postgres', 'template0', 'template1')
    ORDER BY datname;")

for DB in $CUSTOMER_DBS; do
    echo -e "\nDatabase: $DB"

    # Size
    SIZE=$(sudo -u postgres psql -t -c "
        SELECT pg_size_pretty(pg_database_size('$DB'));")
    echo "  Size: $SIZE"

    # Connection count
…