Fix Pgpool II Backend Error

What's Actually Happening

Pgpool-II returns backend errors when connecting to PostgreSQL servers. Connection pooling or load balancing fails.

The Error You'll See

```bash $ psql -h pgpool -p 9999 -U user -d mydb

ERROR: failed to create a backend connection ```

Backend down error:

ERROR: all backends are down

Connection refused:

ERROR: connection to backend failed: Connection refused

Authentication error:

ERROR: failed to authenticate user "pgpool"

Why This Happens

1. 1.PostgreSQL down - Backend server not running
2. 2.Wrong connection settings - Host/port mismatch
3. 3.Authentication failure - pg_hba.conf not configured
4. 4.Health check failed - Backend marked as down
5. 5.Pool exhausted - All connections in use
6. 6.Network issues - Cannot reach PostgreSQL

Step 1: Check Pgpool-II Status

```bash # Check Pgpool-II process: pcp_node_info -h localhost -p 9898 -U pgpool -n 0

# Check all nodes: pcp_pool_status -h localhost -p 9898 -U pgpool | grep backend

# View node status: pcp_watchdog_info -h localhost -p 9898 -U pgpool

# Check Pgpool process: ps aux | grep pgpool

# Check logs: tail -f /var/log/pgpool/pgpool.log

# Connect to Pgpool admin: psql -h localhost -p 9999 -U pgpool -d postgres # Then run: SHOW POOL_NODES;

# Check configuration: cat /etc/pgpool-II/pgpool.conf | grep -E "backend|host|port" ```

Step 2: Check PostgreSQL Connectivity

```bash # Test direct connection to PostgreSQL: psql -h postgres-primary -p 5432 -U user -d mydb -c "SELECT 1"

# Check PostgreSQL is running: systemctl status postgresql # Or: docker ps | grep postgres

# Test each backend: psql -h backend0 -p 5432 -U user -d mydb -c "SELECT pg_is_in_recovery()"

# Check PostgreSQL logs: tail -f /var/log/postgresql/postgresql.log

# Check PostgreSQL port: netstat -tlnp | grep 5432

# Check listen addresses: cat /etc/postgresql/*/main/postgresql.conf | grep listen

# Verify pg_hba.conf allows Pgpool: cat /etc/postgresql/*/main/pg_hba.conf | grep -E "host|local"

# Required entry: # host all pgpool <pgpool-ip>/32 trust # host all all <pgpool-ip>/32 md5 ```

Step 3: Fix Backend Configuration

```bash # Check backend settings in pgpool.conf:

# Backend 0 (primary): backend_hostname0 = 'postgres-primary' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/var/lib/postgresql/data' backend_flag0 = 'ALLOW_TO_FAILOVER'

# Backend 1 (standby): backend_hostname1 = 'postgres-standby' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/postgresql/data' backend_flag1 = 'ALLOW_TO_FAILOVER'

# Verify settings: grep backend_hostname /etc/pgpool-II/pgpool.conf grep backend_port /etc/pgpool-II/pgpool.conf

# Check backend count: num_init_children = 32 max_pool = 4

# Apply changes: systemctl reload pgpool2 # Or: pgpool reload

# Test backend: pcp_node_info -h localhost -p 9898 -U pgpool -n 0 ```

Step 4: Fix Health Check Settings

```bash # Health check configuration:

# Enable health check: health_check_period = 30 health_check_timeout = 20 health_check_user = 'pgpool' health_check_password = '' health_check_database = 'postgres'

# Check if health check user exists: psql -h localhost -U postgres -c "\du pgpool"

# Create health check user: psql -h localhost -U postgres -c "CREATE USER pgpool WITH PASSWORD 'password';"

# Grant permissions: psql -h localhost -U postgres -c "GRANT pg_read_all_stats TO pgpool;"

# Check health check logs: grep -i "health" /var/log/pgpool/pgpool.log

# Test health check query: psql -h postgres-primary -U pgpool -d postgres -c "SELECT 1"

# Adjust timeouts if needed: health_check_timeout = 60 health_check_period = 60

# Special query for health check: health_check_max_retries = 3 health_check_retry_delay = 1 ```

Step 5: Fix Authentication Configuration

```bash # Pgpool authentication settings:

# In pgpool.conf: enable_pool_hba = on pool_passwd = 'pool_passwd'

# Create password file: pg_md5 --md5auth --username=user password

# Or create manually: echo "user:md5$(echo -n 'passworduser' | md5sum | cut -d' ' -f1)" >> /etc/pgpool-II/pool_passwd

# Check pool_passwd file: cat /etc/pgpool-II/pool_passwd

# Allow pool_hba.conf: cat /etc/pgpool-II/pool_hba.conf

# Required entries: # host all all 0.0.0.0/0 md5 # local all all trust

# In PostgreSQL pg_hba.conf: # Allow connections from Pgpool: host all all <pgpool-ip>/32 md5 host replication replication <pgpool-ip>/32 trust

# Reload after changes: systemctl reload postgresql systemctl reload pgpool2 ```

Step 6: Check Connection Pooling

```bash # Pool configuration:

# Connection pool settings: num_init_children = 32 max_pool = 4 child_life_time = 300 child_max_connections = 0 connection_life_time = 0

# Check current connections: pcp_proc_info -h localhost -p 9898 -U pgpool

# View pool status: psql -h localhost -p 9999 -U pgpool -d postgres -c "SHOW POOL_POOLS;"

# Check connection count: SELECT count(*) FROM pg_stat_activity WHERE client_addr = '<pgpool-ip>';

# If pool exhausted: # Increase num_init_children: num_init_children = 64

# Or increase max_pool: max_pool = 8

# Watch connections: watch -n 1 'pcp_proc_count -h localhost -p 9898 -U pgpool'

# Reset connections: pcp_detach_node -h localhost -p 9898 -U pgpool -n 0 pcp_attach_node -h localhost -p 9898 -U pgpool -n 0 ```

Step 7: Fix Load Balancing Issues

```bash # Load balancing configuration:

# Enable load balancing: load_balance_mode = on

# Check load balance status: psql -h localhost -p 9999 -U pgpool -c "SHOW POOL_NODES;"

# Backend weights control load distribution: backend_weight0 = 1 # Primary backend_weight1 = 1 # Standby

# Read queries go to standbys: # Only if statement_level_load_balance = on

# Check query routing: # In pgpool.log: log_per_node_statement = on

# View routed queries: tail -f /var/log/pgpool/pgpool.log | grep "DB node id"

# For write queries, all go to primary # For read queries, distributed by weight

# Disable load balancing temporarily: load_balance_mode = off

# Read from specific node: /*REPLICATE*/SELECT ... # Force to primary ```

Step 8: Handle Failover Issues

```bash # Check failover configuration:

# Failover settings: failover_command = '/etc/pgpool-II/failover.sh %d %P %H %m' follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %H'

# Check failover script: cat /etc/pgpool-II/failover.sh

# Manual failover: pcp_promote_node -h localhost -p 9898 -U pgpool -n 1

# Check backend status after failover: pcp_node_info -h localhost -p 9898 -U pgpool -n 0

# Recovery after failover: # On new standby: pg_basebackup -h new-primary -D /var/lib/postgresql/data -U replication

# Attach node back: pcp_attach_node -h localhost -p 9898 -U pgpool -n 1

# Online recovery: pcp_recovery_node -h localhost -p 9898 -U pgpool -n 1 ```

Step 9: Check Watchdog Configuration

```bash # Watchdog for high availability:

# Enable watchdog: use_watchdog = on

# Watchdog settings: wd_hostname = 'pgpool1' wd_port = 9000 wd_priority = 1

# Other Pgpool nodes: other_pgpool_hostname0 = 'pgpool2' other_pgpool_port0 = 9999 other_wd_port0 = 9000

# Check watchdog status: pcp_watchdog_info -h localhost -p 9898 -U pgpool

# Virtual IP configuration: delegate_IP = '192.168.1.100' if_cmd_path = '/sbin' if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0' if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'

# Test VIP: ip addr show | grep 192.168.1.100

# Check which node is leader: pcp_watchdog_info -h localhost -p 9898 -U pgpool | grep "MASTER|LEADER" ```

Step 10: Pgpool-II Verification Script

```bash # Create verification script: cat << 'EOF' > /usr/local/bin/check-pgpool.sh #!/bin/bash

echo "=== Pgpool-II Process ===" ps aux | grep pgpool | grep -v grep

echo "" echo "=== Node Status ===" pcp_node_info -h localhost -p 9898 -U pgpool -n 0 2>/dev/null || echo "Cannot get node 0 info" pcp_node_info -h localhost -p 9898 -U pgpool -n 1 2>/dev/null || echo "Cannot get node 1 info"

echo "" echo "=== Pool Status ===" pcp_pool_status -h localhost -p 9898 -U pgpool 2>/dev/null | grep -E "backend|load_balance" | head -20

echo "" echo "=== Connection Pool ===" psql -h localhost -p 9999 -U pgpool -d postgres -c "SHOW POOL_POOLS;" 2>/dev/null | head -20

echo "" echo "=== PostgreSQL Connectivity ===" psql -h postgres-primary -p 5432 -U postgres -c "SELECT version();" 2>/dev/null || echo "Cannot connect to primary" psql -h postgres-standby -p 5432 -U postgres -c "SELECT version();" 2>/dev/null || echo "Cannot connect to standby"

echo "" echo "=== Health Check ===" grep -i "health" /var/log/pgpool/pgpool.log 2>/dev/null | tail -5

echo "" echo "=== Recent Errors ===" grep -i "error|fail" /var/log/pgpool/pgpool.log 2>/dev/null | tail -10

echo "" echo "=== Configuration ===" grep -E "backend_hostname|backend_port|load_balance" /etc/pgpool-II/pgpool.conf 2>/dev/null | head -20

echo "" echo "=== Watchdog Status ===" pcp_watchdog_info -h localhost -p 9898 -U pgpool 2>/dev/null | head -10

echo "" echo "=== Recommendations ===" echo "1. Verify PostgreSQL backends are running" echo "2. Check pg_hba.conf allows Pgpool connections" echo "3. Verify health check user exists" echo "4. Check network connectivity to backends" echo "5. Review connection pool settings" echo "6. Ensure failover scripts are executable" echo "7. Check watchdog configuration for HA" EOF

chmod +x /usr/local/bin/check-pgpool.sh

# Usage: /usr/local/bin/check-pgpool.sh ```

Pgpool-II Backend Checklist

Verify the Fix

```bash # After fixing Pgpool-II backend issues

# 1. Check node status pcp_node_info -h localhost -p 9898 -U pgpool -n 0 // Status: up

# 2. Test connection psql -h localhost -p 9999 -U user -d mydb -c "SELECT 1" // Returns result

# 3. Check pool psql -h localhost -p 9999 -U pgpool -c "SHOW POOL_NODES;" // All nodes up

# 4. Test load balance psql -h localhost -p 9999 -U pgpool -c "SELECT pg_is_in_recovery();" // Returns correct node status

# 5. Monitor logs tail -f /var/log/pgpool/pgpool.log // No errors

# 6. Verify failover # Stop primary, check failover occurs ```

Related Issues

• [Fix PostgreSQL Connection Refused](/articles/fix-postgresql-connection-refused)
• [Fix PgBouncer Connection Pool Exhausted](/articles/fix-pgbouncer-connection-pool-exhausted)
• [Fix MySQL Connection Refused](/articles/fix-mysql-connection-refused)