Sunday, November 22, 2026
TR
Linux

PostgreSQL HA Cluster Test Script: A Comprehensive Analysis and Usage Guide

PostgreSQL HA Cluster Test Script: A Comprehensive Analysis and Usage Guide
Contents
  1. System Architecture
  2. Test Script Analysis
  3. Script Header and General Structure
  4. Configuration Parameters
  5. Helper Functions
  6. Main Test Function
  7. Test Sections
  8. 1. Cluster Status Check
  9. 2. Connection Tests
  10. 3. Master Node Detection
  11. 4. Data Operations Test
  12. 5. Performance Test
  13. 6. Failover Test
  14. 7. HAProxy Status Check
  15. 8. System Resource Check
  16. Summary and Report
  17. Main Function and Script Startup
  18. Advantages of the Script
  19. 1. Comprehensive Test Coverage
  20. 2. User-Friendly Interface
  21. 3. Safety Measures
  22. 4. Modular Structure
  23. Usage Recommendations
  24. 1. Running Tests Regularly
  25. 2. Monitoring Integration
  26. 3. Integration into a CI/CD Pipeline
  27. Customization Options
  28. 1. Additional Test Sections
  29. 2. Multi-Environment Support
  30. 3. JSON/XML Output
  31. Conclusion

Introduction

PostgreSQL High Availability (HA) clusters are designed to provide uninterrupted database service for critical applications. Testing whether such systems work correctly is vital for system administrators. In this article, a professional bash script that comprehensively tests a PostgreSQL HA cluster will be analyzed and its usage details explained.

System Architecture

The test script targets an HA cluster made up of the following components:

  • 3 PostgreSQL Nodes: postgresql0, postgresql1, postgresql2
  • Patroni: For PostgreSQL cluster management
  • etcd: For cluster coordination
  • HAProxy: Load balancer and connection pooling

Test Script Analysis

Script Header and General Structure

#!/bin/bash

# PostgreSQL HA Cluster Test Script
# This script tests whether the PostgreSQL HA cluster is working properly

set -e

# Color codes
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
PURPLE='\033[0;35m'
NC='\033[0m' # No Color

The script runs in bash strict mode (set -e) and stops if any command fails. ANSI color codes are defined for colored output.

Configuration Parameters

# Connection details
HAPROXY_IP="192.168.99.52"
HAPROXY_PORT="5000"
POSTGRES_USER="postgres"
POSTGRES_PASSWORD="postgres123"
DATABASE="postgres"

# Node IP'leri
NODE1_IP="192.168.99.50"
NODE2_IP="192.168.99.51"
NODE3_IP="192.168.99.52"

This section defines the basic connection details of the cluster. HAProxy can be seen running on the 3rd node.

Helper Functions

# Test counters
TEST_COUNT=0
PASS_COUNT=0
FAIL_COUNT=0

# Log functions
log() {
    echo -e "${GREEN}[$(date +'%H:%M:%S')] ✓ $1${NC}"
}

warn() {
    echo -e "${YELLOW}[$(date +'%H:%M:%S')] ⚠ $1${NC}"
}

error() {
    echo -e "${RED}[$(date +'%H:%M:%S')] ✗ $1${NC}"
}

info() {
    echo -e "${BLUE}[$(date +'%H:%M:%S')] ℹ $1${NC}"
}

header() {
    echo
    echo -e "${PURPLE}================================================${NC}"
    echo -e "${PURPLE} $1${NC}"
    echo -e "${PURPLE}================================================${NC}"
}

These functions display test results in color and with timestamps. Test statistics are also tracked in this section.

Main Test Function

# Test fonksiyonu
run_test() {
    local test_name="$1"
    local test_command="$2"
    
    TEST_COUNT=$((TEST_COUNT + 1))
    info "Test $TEST_COUNT: $test_name"
    
    if eval "$test_command" >/dev/null 2>&1; then
        log "PASS: $test_name"
        PASS_COUNT=$((PASS_COUNT + 1))
        return 0
    else
        error "FAIL: $test_name"
        FAIL_COUNT=$((FAIL_COUNT + 1))
        return 1
    fi
}

The run_test function ensures that all tests are run in a standard way and that their results are recorded.

Test Sections

1. Cluster Status Check

# Cluster durumu kontrol
check_cluster_status() {
    header "1. CLUSTER STATUS CHECK"
    
    info "Patroni cluster durumu kontrol ediliyor..."
    echo
    patronictl -c /etc/patroni.yml list
    echo
    
    # Her node'un patroni durumunu kontrol et
    run_test "Node1 Patroni Servisi" "systemctl is-active patroni >/dev/null"
    
    # etcd durumu (Node1'de)
    run_test "etcd Servisi (Node1)" "systemctl is-active etcd >/dev/null"
    
    # HAProxy durumu kontrol et
    run_test "HAProxy Servisi" "curl -s http://$NODE3_IP:7000 >/dev/null"
}

This section checks whether the basic components of the cluster are up and running:

  • Status of the Patroni services
  • Status of the etcd cluster coordinator
  • Reachability of HAProxy

2. Connection Tests

# PostgreSQL connection test
test_connection() {
    local host="$1"
    local port="$2"
    local desc="$3"
    
    PGPASSWORD="$POSTGRES_PASSWORD" psql -h "$host" -p "$port" -U "$POSTGRES_USER" -d "$DATABASE" -c "SELECT 1;" >/dev/null 2>&1
}

# Connection tests
test_connections() {
    header "2. CONNECTION TESTS"
    
    run_test "PostgreSQL connection via HAProxy" "test_connection $HAPROXY_IP $HAPROXY_PORT 'HAProxy'"
    run_test "Direct PostgreSQL connection to Node1" "test_connection $NODE1_IP 5432 'Node1'"
    run_test "Direct PostgreSQL connection to Node2" "test_connection $NODE2_IP 5432 'Node2'"
    run_test "Direct PostgreSQL connection to Node3" "test_connection $NODE3_IP 5432 'Node3'"
}

The connection tests check whether a PostgreSQL connection can be established both through HAProxy and directly to each node.

3. Master Node Detection

# Master node tespiti
identify_master() {
    header "3. MASTER NODE DETECTION"
    
    info "Master node tespit ediliyor..."
    
    # Get the master from Patroni
    MASTER_NODE=$(patronictl -c /etc/patroni.yml list | grep "Leader" | awk '{print $2}')
    
    if [[ -n "$MASTER_NODE" ]]; then
        log "Master Node: $MASTER_NODE"
        
        # Master IP'sini tespit et
        case $MASTER_NODE in
            "postgresql0") MASTER_IP="$NODE1_IP" ;;
            "postgresql1") MASTER_IP="$NODE2_IP" ;;
            "postgresql2") MASTER_IP="$NODE3_IP" ;;
        esac
        
        info "Master IP: $MASTER_IP"
        echo
        return 0
    else
        error "Master node tespit edilemedi!"
        return 1
    fi
}

This function uses Patroni to detect which node in the cluster holds the master role.

4. Data Operations Test

# Veri yazma/okuma testi
test_data_operations() {
    header "4. DATA WRITE/READ TESTS"
    
    local test_table="ha_test_$(date +%s)"
    
    info "Creating test table: $test_table"
    
    # Create the test table via HAProxy
    run_test "Create test table" "
        PGPASSWORD='$POSTGRES_PASSWORD' psql -h $HAPROXY_IP -p $HAPROXY_PORT -U $POSTGRES_USER -d $DATABASE -c \"
        CREATE TABLE $test_table (
            id SERIAL PRIMARY KEY,
            test_data VARCHAR(100),
            created_at TIMESTAMP DEFAULT NOW()
        );\"
    "
    
    # Veri ekleme
    run_test "Veri ekleme testi" "
        PGPASSWORD='$POSTGRES_PASSWORD' psql -h $HAPROXY_IP -p $HAPROXY_PORT -U $POSTGRES_USER -d $DATABASE -c \"
        INSERT INTO $test_table (test_data) VALUES 
        ('Test data 1'), 
        ('Test data 2'), 
        ('Test data 3');\"
    "
    
    # Veri okuma
    run_test "Veri okuma testi" "
        PGPASSWORD='$POSTGRES_PASSWORD' psql -h $HAPROXY_IP -p $HAPROXY_PORT -U $POSTGRES_USER -d $DATABASE -c \"
        SELECT COUNT(*) FROM $test_table;\" | grep -q '3'
    "
    
    # Replication check - verify the data on the slave nodes
    info "Checking replication..."
    sleep 2
    
    # Her node'da veriyi kontrol et
    for ip in $NODE1_IP $NODE2_IP $NODE3_IP; do
        if PGPASSWORD="$POSTGRES_PASSWORD" psql -h "$ip" -p 5432 -U "$POSTGRES_USER" -d "$DATABASE" -c "SELECT COUNT(*) FROM $test_table;" 2>/dev/null | grep -q '3'; then
            log "Replication OK: $ip"
        else
            error "Replication FAIL: $ip"
        fi
    done
    
    # Test tablosunu temizle
    PGPASSWORD="$POSTGRES_PASSWORD" psql -h $HAPROXY_IP -p $HAPROXY_PORT -U $POSTGRES_USER -d $DATABASE -c "DROP TABLE IF EXISTS $test_table;" >/dev/null 2>&1
    
    echo
}

This critical test section:

  • Tests table creation
  • Tests data insertion
  • Tests data reading
  • Performs a replication check – the most important feature
  • Cleans up the test data

5. Performance Test

# Performans testi
test_performance() {
    header "5. PERFORMANCE TEST"
    
    info "Running a simple performance test..."
    
    # 1000 insert testi
    local start_time=$(date +%s.%N)
    
    PGPASSWORD="$POSTGRES_PASSWORD" psql -h $HAPROXY_IP -p $HAPROXY_PORT -U $POSTGRES_USER -d $DATABASE -c "
    CREATE TEMP TABLE perf_test AS 
    SELECT generate_series(1,1000) as id, 
           'test_data_' || generate_series(1,1000) as data;
    " >/dev/null 2>&1
    
    local end_time=$(date +%s.%N)
    local duration=$(echo "$end_time - $start_time" | bc -l)
    
    log "Time to process 1000 records: ${duration} seconds"
    
    # Temizle
    PGPASSWORD="$POSTGRES_PASSWORD" psql -h $HAPROXY_IP -p $HAPROXY_PORT -U $POSTGRES_USER -d $DATABASE -c "DROP TABLE IF EXISTS perf_test;" >/dev/null 2>&1
    
    echo
}

This section performs a simple performance measurement by generating 1000 records.

6. Failover Test

# Failover testi
test_failover() {
    header "6. FAILOVER TEST"
    
    warn "This test will temporarily shut down the master node!"
    read -p "Devam etmek istiyor musunuz? (y/N): " -n 1 -r
    echo
    
    if [[ ! $REPLY =~ ^[Yy]$ ]]; then
        warn "Failover test skipped."
        return
    fi
    
    # Detect the current master
    identify_master
    local original_master="$MASTER_NODE"
    local original_master_ip="$MASTER_IP"
    
    info "Original Master: $original_master ($original_master_ip)"
    
    # Test verisi ekle
    local test_value="failover_test_$(date +%s)"
    PGPASSWORD="$POSTGRES_PASSWORD" psql -h $HAPROXY_IP -p $HAPROXY_PORT -U $POSTGRES_USER -d $DATABASE -c "
    CREATE TABLE IF NOT EXISTS failover_test (id SERIAL, data TEXT);
    INSERT INTO failover_test (data) VALUES ('$test_value');
    " >/dev/null 2>&1
    
    # Master node'u durdur (sadece bu sunucudaysak)
    if [[ "$(hostname -I | awk '{print $1}')" == "$original_master_ip" ]]; then
        warn "Master node ($original_master) durduruluyor..."
        systemctl stop patroni
        
        # Wait for the failover to complete
        info "Failover bekleniyor (30 saniye)..."
        sleep 30
        
        # Check the new master
        NEW_MASTER=$(ssh root@$NODE2_IP "patronictl -c /etc/patroni.yml list | grep 'Leader' | awk '{print \$2}'" 2>/dev/null || 
                    ssh root@$NODE3_IP "patronictl -c /etc/patroni.yml list | grep 'Leader' | awk '{print \$2}'" 2>/dev/null)
        
        if [[ "$NEW_MASTER" != "$original_master" && -n "$NEW_MASTER" ]]; then
            log "Failover successful! New Master: $NEW_MASTER"
            
            # Test the connection
            sleep 5
            if test_connection $HAPROXY_IP $HAPROXY_PORT "HAProxy after failover"; then
                log "HAProxy is successfully routing to the new master"
                
                # Test verisini kontrol et
                if PGPASSWORD="$POSTGRES_PASSWORD" psql -h $HAPROXY_IP -p $HAPROXY_PORT -U $POSTGRES_USER -d $DATABASE -c "SELECT data FROM failover_test WHERE data='$test_value';" 2>/dev/null | grep -q "$test_value"; then
                    log "No data loss - Failover successful!"
                else
                    error "Data loss detected!"
                fi
            else
                error "HAProxy cannot connect after failover"
            fi
        else
            error "Failover failed! Master did not change."
        fi
        
        # Restart the original master
        warn "Restarting the original master ($original_master)..."
        systemctl start patroni
        sleep 15
        
        # Wait for the cluster to recover
        info "Cluster stabilizasyonu bekleniyor..."
        sleep 10
        
    else
        warn "This server is not the master, the failover test must be run from another server"
        return
    fi
    
    # Temizle
    PGPASSWORD="$POSTGRES_PASSWORD" psql -h $HAPROXY_IP -p $HAPROXY_PORT -U $POSTGRES_USER -d $DATABASE -c "DROP TABLE IF EXISTS failover_test;" >/dev/null 2>&1
    
    echo
}

This is the most critical test, as it tests the actual purpose of an HA cluster:

  • Intentionally stops the master node
  • Monitors the failover process
  • Verifies that a new master has been elected
  • Tests that HAProxy routes to the new master
  • Verifies that no data was lost
  • Brings the old master back online

7. HAProxy Status Check

# HAProxy status check
test_haproxy() {
    header "7. HAPROXY STATUS CHECK"
    
    info "HAProxy backend'leri kontrol ediliyor..."
    
    # Check the HAProxy stats page
    if curl -s "http://$NODE3_IP:7000" | grep -q "Statistics Report"; then
        log "HAProxy dashboard is accessible"
        info "Dashboard: http://$NODE3_IP:7000"
        
        # Show backend states
        echo
        info "Backend States:"
        curl -s "http://$NODE3_IP:7000/;csv" | grep "postgres" | while IFS=',' read -r line; do
            server_name=$(echo "$line" | cut -d',' -f2)
            status=$(echo "$line" | cut -d',' -f18)
            echo "  - $server_name: $status"
        done
    else
        error "HAProxy dashboard is not accessible"
    fi
    
    echo
}

Checks the backend statuses from HAProxy's statistics page.

8. System Resource Check

# System resource usage
check_resources() {
    header "8. SYSTEM RESOURCE USAGE"
    
    info "Checking system resources..."
    
    echo "CPU Usage:"
    top -bn1 | grep "Cpu(s)" | awk '{print "  " $0}'
    
    echo
    echo "Memory Usage:"
    free -h | grep -E "(Mem|Swap)" | awk '{print "  " $0}'
    
    echo
    echo "Disk Usage (/data/patroni):"
    df -h /data/patroni | tail -1 | awk '{print "  " $0}'
    
    echo
    echo "PostgreSQL Process'leri:"
    ps aux | grep postgres | grep -v grep | wc -l | awk '{print "  Toplam PostgreSQL process: " $0}'
    
    echo
}

Reports on the usage of system resources.

Summary and Report

# Summary report
show_summary() {
    header "TEST RESULT SUMMARY"
    
    echo -e "${BLUE}Toplam Test: $TEST_COUNT${NC}"
    echo -e "${GREEN}Passed: $PASS_COUNT${NC}"
    echo -e "${RED}Failed: $FAIL_COUNT${NC}"
    
    local success_rate=$((PASS_COUNT * 100 / TEST_COUNT))
    echo -e "${PURPLE}Success Rate: %$success_rate${NC}"
    
    echo
    if [[ $FAIL_COUNT -eq 0 ]]; then
        log "ALL TESTS PASSED! The HA Cluster is working properly."
    else
        warn "Some tests failed. Check the logs."
    fi
    
    echo
    info "Useful Commands:"
    echo "  - Cluster durumu: patronictl -c /etc/patroni.yml list"
    echo "  - HAProxy dashboard: http://$NODE3_IP:7000"
    echo "  - PostgreSQL connection: psql -h $HAPROXY_IP -p $HAPROXY_PORT -U $POSTGRES_USER"
    echo "  - Patroni logs: journalctl -u patroni -f"
    echo "  - HAProxy logs: journalctl -u haproxy -f"
}

Lists a summary of the test results and useful commands.

Main Function and Script Startup

# Ana fonksiyon
main() {
    header "PostgreSQL HA CLUSTER TEST STARTING"
    
    info "Test parametreleri:"
    echo "  - HAProxy Endpoint: $HAPROXY_IP:$HAPROXY_PORT"
    echo "  - PostgreSQL User: $POSTGRES_USER"
    echo "  - Node1: $NODE1_IP"
    echo "  - Node2: $NODE2_IP"
    echo "  - Node3: $NODE3_IP"
    
    # Run the tests
    check_cluster_status
    test_connections
    identify_master
    test_data_operations
    test_performance
    test_haproxy
    check_resources
    
    # Ask the user
    echo
    read -p "Failover testini de yapmak istiyor musunuz? (y/N): " -n 1 -r
    echo
    if [[ $REPLY =~ ^[Yy]$ ]]; then
        test_failover
    fi
    
    show_summary
}

# Check the required tools
if ! command -v patronictl &> /dev/null; then
    error "patronictl command not found. Is Patroni not installed?"
    exit 1
fi

if ! command -v psql &> /dev/null; then
    error "psql command not found. Is the PostgreSQL client not installed?"
    exit 1
fi

# Start the test
main "$@"

The main function runs all the tests in order and reports the results.

Advantages of the Script

1. Comprehensive Test Coverage

  • Tests all HA components
  • Simulates real data operations
  • Tests the failover scenario

2. User-Friendly Interface

  • Colored output makes results easy to see
  • Progress tracking lets you follow test progress
  • Detailed logging enables debugging

3. Safety Measures

  • Asks for user confirmation for the failover test
  • Automatically cleans up test data
  • Stops the script on error

4. Modular Structure

  • Each test section can run independently
  • New tests can be added easily
  • Configuration is managed from a central place

Usage Recommendations

1. Running Tests Regularly

You can monitor cluster health by running this script at regular intervals as a cron job:

# Run the test daily at 02:00
0 2 * * * /path/to/ha_test.sh > /var/log/ha_test_$(date +\%Y\%m\%d).log 2>&1

2. Monitoring Integration

The script output can be integrated into monitoring systems:

# Send an alert only for failed tests
if [[ $FAIL_COUNT -gt 0 ]]; then
    echo "HA Cluster test failures: $FAIL_COUNT" | mail -s "HA Alert" admin@company.com
fi

3. Integration into a CI/CD Pipeline

You can run the tests automatically after HA cluster changes.

Customization Options

1. Additional Test Sections

  • Backup/restore tests
  • Connection pooling tests
  • SSL/TLS connection tests
  • Monitoring endpoint tests

2. Multi-Environment Support

You can use configuration files for different environments:

# config/production.conf
HAPROXY_IP="10.0.1.100"
NODE1_IP="10.0.1.101"
# ...

# At the start of the script
source "config/${ENVIRONMENT:-development}.conf"

3. JSON/XML Output

You can output test results in a structured format:

# For JSON format
echo '{"total_tests": '$TEST_COUNT', "passed": '$PASS_COUNT', "failed": '$FAIL_COUNT'}' > test_results.json

Conclusion

This PostgreSQL HA Cluster test script is a professional tool designed to comprehensively test the health of HA clusters, which are critically important in production environments. The script's modular structure, detailed logging and ability to simulate real-world scenarios with failover tests make it an indispensable tool for system administrators and DevOps teams.

Given the complex nature of HA clusters, automated test tools like this both save time and minimize the risk of human error. When run regularly, they let you detect potential problems before you encounter them in production.

Using this script as a base, you can customize it to the specific needs of your own environment and keep the reliability of your HA cluster at the maximum level.

Kerim Koç

Systems and infrastructure engineer. I write about Linux, open source, high availability and security.