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

Text

┌─────────────┐      Synchronous 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
Change Log: The primary records changes in its write-ahead log (WAL) for PostgreSQL, or binary log (binlog) for MySQL/MariaDB
Replication Stream: Changes are streamed to replica
Apply Changes: Replica applies changes to maintain synchronization
Read from Replica: Applications can read from replica

Replication Consistency

By default, a write is only considered committed once at least one standby confirms it (synchronous replication), which keeps replicas caught up under normal conditions:

Normal operation: Standbys stay caught up because the primary waits for acknowledgment on every write
Standby unhealthy: The primary stops waiting for it rather than block writes — replication briefly falls back to asynchronous for that standby until it recovers
Monitoring: Replication status is monitored and displayed in the dashboard

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 Nodes tab
Click Add Read Replica
Confirm the number of standbys to add — a replica always matches the primary's resource profile, and there's no separate name, node, or profile to choose
Click Add node

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

Replicas do not get their own public hostname — there's a single primary endpoint, and reads only reach a replica if you enable the connection proxy and use its any-instance endpoint (-proxy-r). That endpoint routes to any instance, primary or replica — once a replica exists, some connections land on it — rather than being an exclusive read-replica pool.

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:

Health Monitoring: System continuously monitors primary health
Failure Detection: The operator detects that the primary is unhealthy
Promotion: Replica automatically promoted to primary
Service Redirection: The primary connection endpoint is redirected to the newly promoted replica — no DNS change is needed
Application Reconnect: Applications reconnect using the same endpoint

Manual Promotion

Manual promotion isn't available from the dashboard today. If you need to force a switchover, contact support.

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

Replicas always run the same resource profile as the primary — resize the instance itself to change it for the whole cluster, replicas included.

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:

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:

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

Recovery Time Objective (RTO)

Automatic failover: Depends on how quickly the operator detects the primary is unhealthy and completes promotion — not covered by an SLA
Restore from backup: Depends on database size

Recovery Point Objective (RPO)

Normal operation: Near-zero data loss — the primary waits for a standby to confirm each write before committing (see Replication Consistency)
Standby unhealthy: A small window of possible data loss, since the primary stops waiting on an unhealthy standby rather than block writes
Backup restore: Depends on your 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

Database Replicas

Overview

How Replicas Work

Replication Architecture

Replication Process

Replication Consistency

Creating a Replica

Prerequisites

Via Dashboard

Replica Configuration

Replica Placement

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

Disaster Recovery via Replicas

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