Database Replica Configuration

This project supports PostgreSQL read replicas for improved performance and scalability.

Architecture

  • Primary Database: Handles all write operations
  • Replica Database: Handles read-only operations for queries that don’t require real-time data

Configuration Files

The replica setup is configured across multiple Docker Compose files:

  • docker-compose.local.db.replica.yaml: Local development with enhanced replica configuration

Environment Variables

Configure the following variables:

# Primary Database
POSTGRES_HOST=postgres
POSTGRES_DATABASE=chatwoot_dev
POSTGRES_USERNAME=postgres
POSTGRES_PASSWORD=your_password

# Replica Database  
POSTGRES_REPLICA_HOST=postgres-replica
POSTGRES_REPLICA_PORT=5433
POSTGRES_REPLICA_DATABASE=chatwoot_dev
POSTGRES_REPLICA_USERNAME=postgres
POSTGRES_REPLICA_PASSWORD=your_password

# Replication User
POSTGRES_REPLICATION_USER=replicator
POSTGRES_REPLICATION_PASSWORD=replicator_password

Starting the Services

Local Development

docker-compose -f docker-compose.local.db.replica.yaml up -d

Database Connections

The Rails application is configured to automatically route:

  • Write queries → Primary database (port 5432)
  • Read queries → Replica database (port 5433)

This is handled through the database.yml configuration with replica: true setting.

Monitoring Replication

To check replication status:

# Connect to primary database
docker-compose exec postgres psql -U postgres -d chatwoot_dev

# Check replication status
SELECT client_addr, state, sync_state FROM pg_stat_replication;

# Connect to replica database  
docker-compose exec postgres-replica psql -U postgres -d chatwoot_dev

# Check if replica is in recovery mode
SELECT pg_is_in_recovery();

Troubleshooting

Replica Not Starting

  1. Check if primary database is running and accessible
  2. Verify replication user credentials
  3. Check Docker logs: docker-compose logs postgres-replica

Replication Lag

  1. Monitor replication lag with: SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()));
  2. Check network connectivity between primary and replica
  3. Monitor disk I/O on both databases

Replica Out of Sync

  1. Stop replica container
  2. Remove replica data volume
  3. Restart replica container (it will automatically resync from primary)
docker-compose stop postgres-replica
docker volume rm titan-default_postgres_replica
docker-compose up -d postgres-replica