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 data center failure
Geographic Distribution: Place replicas closer to users for lower latency
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

Write to Primary: All write operations (INSERT, UPDATE, DELETE) go to primary
Binary Log: Primary records changes in binary log (binlog)
Replication Stream: Changes are streamed to replica
Apply Changes: Replica applies changes to maintain synchronization
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

Navigate to your database instance
Click the Replicas tab
Click Create Replica
Configure replica settings:
- Replica Name: Descriptive name for the replica
- Region: Same or different data center
- Resource Profile: Can match or differ from primary
Click Create Replica

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

Replica Configuration

Same-Region Replicas

Best for:

Load balancing read traffic
High availability within a region
Minimal replication lag

Cross-Region Replicas

Best for:

Geographic distribution
Disaster recovery
Compliance requirements
Serving users in different regions

Note: Cross-region replicas may have higher replication lag due to network latency.

Connecting to Replicas

Connection Details

Each replica has its own connection endpoint:

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

Read-Only Access

Replicas are read-only. Write operations will fail:

-- 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 Example with Laravel
return [
    'database' => [
        'connections' => [
            'mysql' => [
                'read' => [
                    'host' => [
                        'db-replica-123456-01.danubedata.com',
                        'db-replica-123456-02.danubedata.com',
                    ],
                ],
                'write' => [
                    'host' => [
                        'db-primary-123456.danubedata.com',
                    ],
                ],
                'port' => 3306,
                'database' => 'myapp',
                'username' => 'dbuser',
                'password' => 'password',
            ],
        ],
    ],
];

Python Example

from sqlalchemy import create_engine

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

# Replica connection for reads
replica_engine = create_engine(
    'postgresql://user:pass@db-replica-123456-01.danubedata.com: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

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

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

// Replica pool for reads
const replicaPool = new Pool({
  host: 'db-replica-123456-01.danubedata.com',
  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:

# 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):

// 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:

# 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):

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):

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:

import random

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

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:

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

-- 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

-- 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:

// 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:

// 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:

# 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:

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

Manual Promotion

Promote a replica to primary manually:

Navigate to database in dashboard
Click Replicas tab
Select replica to promote
Click Promote to Primary
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:

Create additional replicas as needed
Update application configuration
Distribute read traffic across all replicas

Resize Replicas

Change replica resource profile:

Navigate to replica in dashboard
Click Resize
Select new profile
Confirm resize

Replicas can have different profiles than primary.

Removing Replicas

Delete replicas when no longer needed:

Navigate to replica in dashboard
Click Delete
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:

Delete the problematic replica
Create a new replica
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:

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:

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

Cross-Region Disaster Recovery

Use cross-region replicas for disaster recovery:

Create replica in different region
Monitor replication health
In case of regional failure, promote replica to primary
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 cross-region replicas for global applications
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

Database Replicas

Overview

How Replicas Work

Replication Architecture

Replication Process

Replication Lag

Creating a Replica

Prerequisites

Via Dashboard

Replica Configuration

Same-Region Replicas

Cross-Region Replicas

Connecting to Replicas

Connection Details

Read-Only Access

Application Configuration

Direct Connection

Python Example

Node.js Example

SSL/TLS Connections to Replicas

MySQL/MariaDB with SSL

PostgreSQL with SSL

Load Balancing Across Replicas

Round-Robin

Health-Based Routing

Connection Pooling

Managing Replication Lag

Monitoring Lag

PostgreSQL

MySQL/MariaDB

Handling Lag in Applications

Read-After-Write Consistency

Eventual Consistency

Wait for Replication

Failover and High Availability

Automatic Failover

Manual Promotion

Failover Best Practices

Replica Management

Scaling Replicas

Add More Replicas

Resize Replicas

Removing Replicas

Rebuilding Replicas

Performance Optimization

Replica Resource Sizing

Query Routing

Caching

Monitoring and Alerts

Key Metrics

Setting Up Alerts

Backup and Disaster Recovery

Backups

Cross-Region Disaster Recovery

Recovery Time Objective (RTO)

Recovery Point Objective (RPO)

Best Practices

Application Design

Scaling Strategy

Cost Optimization

Troubleshooting

High Replication Lag

Replica Connection Errors

Replica Out of Sync

Related Documentation