# Database Replicas

Database replicas provide high availability, improved read performance, and disaster recovery capabilities for your managed databases. DanubeData makes it easy to create and manage read replicas for PostgreSQL, MySQL, and MariaDB databases.

## Overview

A database replica is a read-only copy of your primary database that automatically synchronizes data from the primary. Replicas can be used to:

- **Scale Read Operations**: Distribute read queries across multiple replicas
- **High Availability**: Automatic failover to replica if primary fails
- **Disaster Recovery**: Maintain a standby database in case of a primary host failure
- **Load Balancing**: Distribute reads across replicas via the reader endpoint
- **Reporting and Analytics**: Run heavy queries without impacting production

## How Replicas Work

### Replication Architecture

```
┌─────────────┐        Async Replication        ┌─────────────┐
│   Primary   │ ──────────────────────────────> │   Replica   │
│  (Read/Write)│                                  │ (Read-Only) │
└─────────────┘                                  └─────────────┘
       │                                                  │
       │                                                  │
   Write Traffic                                    Read Traffic
```

### Replication Process

1. **Write to Primary**: All write operations (INSERT, UPDATE, DELETE) go to primary
2. **Binary Log**: Primary records changes in binary log (binlog)
3. **Replication Stream**: Changes are streamed to replica
4. **Apply Changes**: Replica applies changes to maintain synchronization
5. **Read from Replica**: Applications can read from replica

### Replication Lag

Replicas are updated asynchronously, which means there can be a delay between primary and replica:

- **Typical Lag**: < 1 second under normal load
- **High Load**: Lag can increase during heavy write operations
- **Monitoring**: Replication lag is monitored and displayed in dashboard
- **Automatic Catch-Up**: Replicas automatically catch up when lag occurs

## Creating a Replica

### Prerequisites

- An existing managed database (PostgreSQL, MySQL, or MariaDB)
- Database must be in a healthy state
- Sufficient account resources for additional instance

### Via Dashboard

1. Navigate to your database instance
2. Click the **Replicas** tab
3. Click **Create Replica**
4. Configure replica settings:
   - **Replica Name**: Descriptive name for the replica
   - **Node**: Same or different host within the datacenter
   - **Resource Profile**: Can match or differ from primary
5. Click **Create Replica**

The replica will be created within 5-10 minutes. Initial synchronization time depends on database size.

### Replica Configuration

#### Replica Placement

All replicas run on a separate host within the same datacenter as the primary. Best for:
- Load balancing read traffic
- High availability via hardware isolation from the primary
- Minimal replication lag

## Connecting to Replicas

### Connection Details

Each replica has its own connection endpoint:

```
Primary: db-primary-123456.danubedata.ro:5432
Replica 1: db-replica-123456-01.danubedata.ro:5432
Replica 2: db-replica-123456-02.danubedata.ro:5432
```

### Read-Only Access

Replicas are read-only. Write operations will fail:

```sql
-- This works on replicas
SELECT * FROM users WHERE status = 'active';

-- This will fail on replicas
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
-- ERROR: cannot execute INSERT in a read-only transaction
```

### Application Configuration

#### Direct Connection

Configure separate read and write connections:

```php
// PHP Example with Laravel
return [
    'database' => [
        'connections' => [
            'mysql' => [
                'read' => [
                    'host' => [
                        'db-replica-123456-01.danubedata.ro',
                        'db-replica-123456-02.danubedata.ro',
                    ],
                ],
                'write' => [
                    'host' => [
                        'db-primary-123456.danubedata.ro',
                    ],
                ],
                'port' => 3306,
                'database' => 'myapp',
                'username' => 'dbuser',
                'password' => 'password',
            ],
        ],
    ],
];
```

#### Python Example

```python
from sqlalchemy import create_engine

# Primary connection for writes
primary_engine = create_engine(
    'postgresql://user:pass@db-primary-123456.danubedata.ro:5432/mydb'
)

# Replica connection for reads
replica_engine = create_engine(
    'postgresql://user:pass@db-replica-123456-01.danubedata.ro:5432/mydb'
)

# Use primary for writes
with primary_engine.connect() as conn:
    conn.execute("INSERT INTO users (name) VALUES ('John')")

# Use replica for reads
with replica_engine.connect() as conn:
    result = conn.execute("SELECT * FROM users")
```

#### Node.js Example

```javascript
const { Pool } = require('pg');

// Primary pool for writes
const primaryPool = new Pool({
  host: 'db-primary-123456.danubedata.ro',
  port: 5432,
  database: 'mydb',
  user: 'dbuser',
  password: 'password',
  max: 20,
});

// Replica pool for reads
const replicaPool = new Pool({
  host: 'db-replica-123456-01.danubedata.ro',
  port: 5432,
  database: 'mydb',
  user: 'dbuser',
  password: 'password',
  max: 20,
});

// Write to primary
async function createUser(name) {
  const result = await primaryPool.query(
    'INSERT INTO users (name) VALUES ($1) RETURNING *',
    [name]
  );
  return result.rows[0];
}

// Read from replica
async function getUsers() {
  const result = await replicaPool.query('SELECT * FROM users');
  return result.rows;
}
```

### SSL/TLS Connections to Replicas

**Important:** All database replicas support SSL/TLS connections using the same certificates as the master database. SSL certificates are automatically provisioned to your VPS instances in `/etc/database-certs/`.

#### MySQL/MariaDB with SSL

**Command Line:**
```bash
# Connect to specific replica with SSL
mysql -h mysql-db-replica-1.namespace.svc.cluster.local \
  -u root -p \
  --ssl-ca=/etc/database-certs/mysql-db-ca.pem \
  --ssl-cert=/etc/database-certs/mysql-db-client-cert.pem \
  --ssl-key=/etc/database-certs/mysql-db-client-key.pem

# Connect to reader endpoint (load-balanced across all replicas)
mysql -h mysql-db-read.namespace.svc.cluster.local \
  -u root -p \
  --ssl-ca=/etc/database-certs/mysql-db-ca.pem \
  --ssl-cert=/etc/database-certs/mysql-db-client-cert.pem \
  --ssl-key=/etc/database-certs/mysql-db-client-key.pem
```

**PHP (PDO):**
```php
// Connect to reader endpoint with SSL
$dsn = "mysql:host=mysql-db-read.namespace.svc.cluster.local;port=3306;dbname=mydb";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_SSL_CA => '/etc/database-certs/mysql-db-ca.pem',
    PDO::MYSQL_ATTR_SSL_CERT => '/etc/database-certs/mysql-db-client-cert.pem',
    PDO::MYSQL_ATTR_SSL_KEY => '/etc/database-certs/mysql-db-client-key.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
];
$pdo = new PDO($dsn, 'root', $password, $options);
```

#### PostgreSQL with SSL

**Command Line:**
```bash
# Connect to specific replica with SSL
psql "postgresql://postgres:password@postgresql-db-replica-1.namespace.svc.cluster.local:5432/mydb?sslmode=verify-ca&sslrootcert=/etc/database-certs/postgresql-db-ca.pem&sslcert=/etc/database-certs/postgresql-db-client-cert.pem&sslkey=/etc/database-certs/postgresql-db-client-key.pem"

# Connect to reader endpoint (load-balanced across all replicas)
psql "postgresql://postgres:password@postgresql-db-read.namespace.svc.cluster.local:5432/mydb?sslmode=verify-ca&sslrootcert=/etc/database-certs/postgresql-db-ca.pem"
```

**Python (psycopg2):**
```python
import psycopg2

# Connect to reader endpoint with SSL
conn = psycopg2.connect(
    host='postgresql-db-read.namespace.svc.cluster.local',
    port=5432,
    user='postgres',
    password='password',
    database='mydb',
    sslmode='verify-ca',
    sslrootcert='/etc/database-certs/postgresql-db-ca.pem',
    sslcert='/etc/database-certs/postgresql-db-client-cert.pem',
    sslkey='/etc/database-certs/postgresql-db-client-key.pem'
)
```

**Node.js (pg):**
```javascript
const { Pool } = require('pg');
const fs = require('fs');

// Replica pool with SSL
const replicaPool = new Pool({
  host: 'postgresql-db-read.namespace.svc.cluster.local',
  port: 5432,
  user: 'postgres',
  password: 'password',
  database: 'mydb',
  ssl: {
    ca: fs.readFileSync('/etc/database-certs/postgresql-db-ca.pem').toString(),
    cert: fs.readFileSync('/etc/database-certs/postgresql-db-client-cert.pem').toString(),
    key: fs.readFileSync('/etc/database-certs/postgresql-db-client-key.pem').toString()
  }
});
```

**Key Points:**
- Replicas use the same SSL certificates as the master database
- Certificate files are automatically provisioned to VPS instances at `/etc/database-certs/{database-name}-*.pem`
- Both individual replica endpoints and the reader endpoint (load-balanced) support SSL
- Full certificate verification is enabled by default for maximum security
- Master-replica replication traffic is unencrypted (internal cluster communication only)

## Load Balancing Across Replicas

### Round-Robin

Distribute reads evenly across multiple replicas:

```python
import random

replicas = [
    'db-replica-123456-01.danubedata.ro',
    'db-replica-123456-02.danubedata.ro',
    'db-replica-123456-03.danubedata.ro',
]

def get_replica_host():
    return random.choice(replicas)

# Use for read queries
replica_host = get_replica_host()
```

### Health-Based Routing

Route to healthy replicas only:

```python
healthy_replicas = []

for replica in replicas:
    if check_replica_health(replica):
        healthy_replicas.append(replica)

if healthy_replicas:
    replica_host = random.choice(healthy_replicas)
else:
    # Fallback to primary
    replica_host = primary_host
```

### Connection Pooling

Use connection pooling to manage multiple replica connections efficiently:

- Maintain separate pools for primary and replicas
- Set appropriate pool sizes based on workload
- Configure connection timeouts and retry logic
- Monitor pool utilization

## Managing Replication Lag

### Monitoring Lag

Check replication lag in the dashboard or via SQL:

#### PostgreSQL

```sql
-- On primary
SELECT 
    client_addr as replica_ip,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    sync_state,
    EXTRACT(EPOCH FROM (NOW() - pg_last_xact_replay_timestamp())) as lag_seconds
FROM pg_stat_replication;
```

#### MySQL/MariaDB

```sql
-- On replica
SHOW SLAVE STATUS\G

-- Look for:
-- Seconds_Behind_Master: 0 (or higher if lagging)
```

### Handling Lag in Applications

#### Read-After-Write Consistency

For critical reads after writes, read from primary:

```php
// Write to primary
DB::connection('mysql')->table('users')->insert([
    'name' => 'John Doe',
    'email' => 'john@example.com',
]);

// Read from primary immediately after write
$user = DB::connection('mysql')->table('users')
    ->where('email', 'john@example.com')
    ->first();

// Later reads can use replica
$users = DB::connection('mysql_replica')->table('users')->get();
```

#### Eventual Consistency

For non-critical reads, accept eventual consistency:

```php
// Write to primary
createUser($userData);

// Read from replica (may not include just-created user yet)
$users = getUsersFromReplica();
```

#### Wait for Replication

Some frameworks support waiting for replication:

```python
# Django example
from django.db import transaction

with transaction.atomic():
    user = User.objects.create(name='John')
    
# Wait for replication before reading
time.sleep(0.1)  # Small delay to allow replication
users = User.objects.using('replica').all()
```

## Failover and High Availability

### Automatic Failover

When enabled, automatic failover provides high availability:

1. **Health Monitoring**: System continuously monitors primary health
2. **Failure Detection**: Primary failure detected within 45 seconds
3. **Promotion**: Replica automatically promoted to primary
4. **DNS Update**: Connection endpoint updated to point to new primary
5. **Application Reconnect**: Applications reconnect to new primary

### Manual Promotion

Promote a replica to primary manually:

1. Navigate to database in dashboard
2. Click **Replicas** tab
3. Select replica to promote
4. Click **Promote to Primary**
5. Confirm promotion

> **Warning**: Promoting a replica to primary will make the old primary a replica (if still healthy) or detach it if unhealthy.

### Failover Best Practices

- **Enable automatic failover** for production databases
- **Configure application retries** to handle temporary connection failures
- **Monitor replication lag** to ensure replicas are up-to-date
- **Test failover** in staging environment before production
- **Set up alerts** for failover events

## Replica Management

### Scaling Replicas

#### Add More Replicas

Scale read capacity by adding more replicas:

1. Create additional replicas as needed
2. Update application configuration
3. Distribute read traffic across all replicas

#### Resize Replicas

Change replica resource profile:

1. Navigate to replica in dashboard
2. Click **Resize**
3. Select new profile
4. Confirm resize

Replicas can have different profiles than primary.

### Removing Replicas

Delete replicas when no longer needed:

1. Navigate to replica in dashboard
2. Click **Delete**
3. Confirm deletion

> **Note**: Deleting a replica does not affect the primary or other replicas.

### Rebuilding Replicas

If a replica falls too far behind or has issues:

1. Delete the problematic replica
2. Create a new replica
3. Initial sync will catch up to primary

## Performance Optimization

### Replica Resource Sizing

Size replicas based on read workload:

- **Light reads**: Smaller profile than primary
- **Heavy reads**: Same or larger profile than primary
- **Analytics**: Larger profile with more RAM for complex queries

### Query Routing

Route queries intelligently:

```python
class DatabaseRouter:
    def db_for_read(self, model, **hints):
        """Route reads to replica"""
        return 'replica'
    
    def db_for_write(self, model, **hints):
        """Route writes to primary"""
        return 'default'
```

### Caching

Combine replicas with caching:

```python
def get_user(user_id):
    # Check cache first
    user = cache.get(f'user:{user_id}')
    if user:
        return user
    
    # Read from replica
    user = User.objects.using('replica').get(id=user_id)
    
    # Cache for future reads
    cache.set(f'user:{user_id}', user, timeout=300)
    
    return user
```

## Monitoring and Alerts

### Key Metrics

Monitor these metrics for replicas:

- **Replication Lag**: Time difference between primary and replica
- **Replica Health**: Overall replica status
- **Connection Count**: Active connections to replica
- **Query Performance**: Slow queries on replica
- **Resource Usage**: CPU, RAM, disk I/O

### Setting Up Alerts

Configure alerts for critical events:

- Replication lag > 5 seconds
- Replica disconnected
- Replica storage > 80% full
- High CPU usage (> 80%)
- Failover events

## Backup and Disaster Recovery

### Backups

Backups are taken from the primary database:

- Replicas do not create separate backups
- Restoring from backup creates a new primary
- Replicas must be recreated after restoration

### Disaster Recovery via Replicas

Use replicas for host-level failover:

1. Create a replica on a separate host
2. Monitor replication health
3. In case of primary host failure, promote replica to primary
4. Update application DNS/configuration

### Recovery Time Objective (RTO)

- **Automatic failover**: ~1-2 minutes
- **Manual promotion**: ~5 minutes
- **Restore from backup**: 15-60 minutes (depending on size)

### Recovery Point Objective (RPO)

- **Synchronous replication**: 0 data loss (not supported)
- **Asynchronous replication**: Minimal data loss (typically < 1 second)
- **Backup restore**: Up to 24 hours (depending on backup schedule)

## Best Practices

### Application Design

- **Separate read/write connections** in application configuration
- **Handle replication lag** in application logic
- **Implement connection retry logic** for failover scenarios
- **Use read replicas for reporting** and analytics
- **Monitor both primary and replicas** for performance issues

### Scaling Strategy

- **Start with one replica** for high availability
- **Add more replicas** as read traffic grows
- **Use the reader endpoint** to load-balance across replicas automatically
- **Size replicas appropriately** based on workload
- **Monitor and adjust** as traffic patterns change

### Cost Optimization

- **Delete unused replicas** to save costs
- **Right-size replica profiles** to match workload
- **Use smaller profiles** for replicas with light read traffic
- **Consider replica placement** (same-region is cheaper)

## Troubleshooting

### High Replication Lag

**Symptoms**: Replica falling behind primary

**Causes**:
- Heavy write load on primary
- Slow network between primary and replica
- Undersized replica resources
- Long-running transactions

**Solutions**:
- Upgrade replica to larger profile
- Optimize write queries on primary
- Check network connectivity
- Split large transactions into smaller ones

### Replica Connection Errors

**Symptoms**: Cannot connect to replica

**Solutions**:
- Check replica status in dashboard
- Verify connection details
- Check firewall rules
- Ensure SSL/TLS configuration is correct
- Test with mysql/psql CLI client

### Replica Out of Sync

**Symptoms**: Data inconsistencies between primary and replica

**Solutions**:
- Check replication status
- Review replication logs
- Rebuild replica if necessary
- Contact support if issue persists

## Related Documentation

- [PostgreSQL](https://docs.danubedata.ro/databases-postgresql)
- [MySQL](https://docs.danubedata.ro/databases-mysql)
- [MariaDB](https://docs.danubedata.ro/databases-mariadb)
- [Database Overview](https://docs.danubedata.ro/databases-overview)
- [Database Backups](https://docs.danubedata.ro/databases-backups)
- [Monitoring Overview](https://docs.danubedata.ro/monitoring-overview)