Contents
- System Architecture
- Test Script Analysis
- Script Header and General Structure
- Configuration Parameters
- Helper Functions
- Main Test Function
- Test Sections
- 1. Cluster Status Check
- 2. Connection Tests
- 3. Master Node Detection
- 4. Data Operations Test
- 5. Performance Test
- 6. Failover Test
- 7. HAProxy Status Check
- 8. System Resource Check
- Summary and Report
- Main Function and Script Startup
- Advantages of the Script
- 1. Comprehensive Test Coverage
- 2. User-Friendly Interface
- 3. Safety Measures
- 4. Modular Structure
- Usage Recommendations
- 1. Running Tests Regularly
- 2. Monitoring Integration
- 3. Integration into a CI/CD Pipeline
- Customization Options
- 1. Additional Test Sections
- 2. Multi-Environment Support
- 3. JSON/XML Output
- 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.