# Yemaya PostgreSQL High Availability

This directory contains configuration and scripts for PostgreSQL streaming
replication and high availability setup.

## Overview

The high availability setup provides:

- **Streaming Replication**: Real-time data replication from primary to standby
- **Hot Standby**: Read-only queries on standby servers
- **Automatic Failover Scripts**: Promote standby to primary when needed
- **Replication Monitoring**: Health checks and alerting

## Architecture

```
                    ┌─────────────────┐
                    │   Application   │
                    └────────┬────────┘
                             │
                    ┌────────▼────────┐
                    │    PgBouncer    │
                    │  (connection    │
                    │    pooler)      │
                    └───┬─────────┬───┘
                        │         │
             ┌──────────▼──┐  ┌──▼──────────┐
             │   Primary   │  │   Standby   │
             │ PostgreSQL  │──│ PostgreSQL  │
             │   (R/W)     │  │   (R/O)     │
             └─────────────┘  └─────────────┘
                    │
             ┌──────▼──────┐
             │ WAL Archive │
             └─────────────┘
```

## Quick Start

### Using Docker Compose

```bash
cd infrastructure/postgres-ha

# Start HA cluster
docker compose -f docker-compose.ha.yml up -d

# Check status
docker compose -f docker-compose.ha.yml ps

# View logs
docker compose -f docker-compose.ha.yml logs -f postgres-primary postgres-standby

# Enable monitoring (optional)
docker compose -f docker-compose.ha.yml --profile monitoring up -d
```

### Verify Replication

```bash
# On primary - check connected standbys
docker exec yemaya-postgres-primary psql -U yemaya -c "SELECT * FROM pg_stat_replication;"

# On standby - verify recovery mode
docker exec yemaya-postgres-standby psql -U yemaya -c "SELECT pg_is_in_recovery();"

# Check replication lag
docker exec yemaya-postgres-standby psql -U yemaya -c "
  SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::int AS lag_seconds;
"
```

## Configuration Files

### primary.conf

Primary server configuration with:

- `wal_level = replica` - Enable WAL for replication
- `max_wal_senders = 5` - Maximum concurrent standby connections
- `wal_keep_size = 512MB` - WAL retention for standbys
- `archive_mode = on` - Enable WAL archiving for PITR

### standby.conf

Standby server configuration with:

- `hot_standby = on` - Allow read-only queries
- `hot_standby_feedback = on` - Prevent vacuum conflicts

### pg_hba.conf

Authentication configuration allowing:

- Local connections
- Docker network connections
- Replication connections from replicator user
- Application connections

## Scripts

### setup-primary.sh

Configures a PostgreSQL server as the replication primary:

- Creates replication user
- Creates replication slot
- Sets up WAL archiving

```bash
REPLICATOR_PASSWORD=secure_password ./setup-primary.sh
```

### setup-standby.sh

Configures a PostgreSQL server as a streaming replica:

- Takes base backup from primary
- Configures recovery settings
- Starts streaming replication

```bash
PRIMARY_HOST=postgres-primary \
REPLICATOR_PASSWORD=secure_password \
./setup-standby.sh
```

### failover.sh

Manages failover from primary to standby:

```bash
# Check current status
./failover.sh status

# Check if primary is healthy
./failover.sh check-primary --primary-host postgres-primary

# Promote standby to primary
./failover.sh promote

# Full failover procedure
./failover.sh failover --primary-host postgres-primary
```

### check-replication.sh

Monitors replication health:

```bash
# Single health check (returns exit codes for monitoring)
./check-replication.sh check

# Continuous monitoring
./check-replication.sh monitor

# Get current replication lag
./check-replication.sh lag
```

## Failover Procedures

### Planned Failover (Switchover)

Use for maintenance or upgrades:

1. **Stop application traffic**

   ```bash
   # Pause PgBouncer
   docker exec yemaya-pgbouncer pgbouncer -R
   ```

2. **Wait for replication to catch up**

   ```bash
   ./check-replication.sh lag  # Should be 0
   ```

3. **Promote standby**

   ```bash
   ./failover.sh promote
   ```

4. **Update PgBouncer configuration**

   ```bash
   # Edit pgbouncer.ini to point to new primary
   docker restart yemaya-pgbouncer
   ```

5. **Resume traffic**

### Unplanned Failover (Emergency)

When primary fails unexpectedly:

1. **Verify primary is unavailable**

   ```bash
   ./failover.sh check-primary --primary-host postgres-primary
   ```

2. **Run failover procedure**

   ```bash
   ./failover.sh failover --primary-host postgres-primary
   ```

3. **Update DNS/PgBouncer**

4. **Investigate failed primary**

### Post-Failover Tasks

After any failover:

1. **Update application configuration** if using direct connections
2. **Set up old primary as new standby** (if recovered)
3. **Review monitoring and alerting**
4. **Document the incident**

## Monitoring

### Key Metrics

| Metric             | Warning | Critical | Description                |
| ------------------ | ------- | -------- | -------------------------- |
| Replication Lag    | > 30s   | > 120s   | Delay in data replication  |
| WAL Senders        | < 1     | = 0      | Active standby connections |
| Replay Lag (bytes) | > 16MB  | > 64MB   | Data not yet applied       |

### Prometheus Metrics

Add to your monitoring:

```yaml
# Example Prometheus queries
pg_replication_lag_seconds > 30  # Warning
pg_replication_lag_seconds > 120  # Critical
pg_stat_replication_count == 0   # No standbys connected
```

### Alerting Example

```yaml
groups:
  - name: postgres-ha
    rules:
      - alert: PostgresReplicationLag
        expr: pg_replication_lag_seconds > 30
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: 'PostgreSQL replication lag is high'

      - alert: PostgresNoStandby
        expr: pg_stat_replication_count == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: 'No PostgreSQL standby connected'
```

## Best Practices

### Production Recommendations

1. **Synchronous Replication** for critical data:

   ```
   synchronous_commit = on
   synchronous_standby_names = 'standby1'
   ```

2. **Multiple Standbys**: Run 2+ standbys in different availability zones

3. **WAL Archiving**: Enable for point-in-time recovery

   ```
   archive_mode = on
   archive_command = 'aws s3 cp %p s3://bucket/wal/%f'
   ```

4. **Connection Pooling**: Always use PgBouncer in production

5. **Automated Failover**: Consider Patroni or pg_auto_failover for automatic
   failover

### Security

- Use SSL for replication connections
- Restrict replication user permissions
- Use strong passwords
- Network segmentation between primary and standbys

## Troubleshooting

### Replication Not Starting

1. Check network connectivity
2. Verify pg_hba.conf allows replication
3. Check replication slot exists
4. Review PostgreSQL logs

### High Replication Lag

1. Check network bandwidth
2. Verify standby has sufficient resources
3. Look for long-running queries on standby
4. Check for heavy write load on primary

### Standby Not Accepting Connections

1. Verify `hot_standby = on`
2. Check if recovery is in progress
3. Review standby logs

### WAL Segments Accumulating

1. Check if standbys are connected
2. Verify replication slots are active
3. Consider dropping inactive slots:
   ```sql
   SELECT pg_drop_replication_slot('inactive_slot');
   ```

## Files

| File                    | Description                             |
| ----------------------- | --------------------------------------- |
| `primary.conf`          | Primary server PostgreSQL configuration |
| `standby.conf`          | Standby server PostgreSQL configuration |
| `pg_hba.conf`           | Client authentication configuration     |
| `setup-primary.sh`      | Initialize primary for replication      |
| `setup-standby.sh`      | Initialize standby from primary         |
| `failover.sh`           | Failover management script              |
| `check-replication.sh`  | Replication health monitoring           |
| `init-primary.sh`       | Docker init script for primary          |
| `init-standby.sh`       | Docker init script for standby          |
| `docker-compose.ha.yml` | Docker Compose for HA setup             |
