PostgreSQL Replication with Spring Boot

Complete implementation of PostgreSQL Primary-Standby streaming replication with automatic read/write routing in Spring Boot.

Overview

This project demonstrates a production-ready database replication setup with:

• PostgreSQL Streaming Replication: Primary-Standby setup with real-time data synchronization
• Smart Datasource Routing: Automatic routing of write operations to primary and read operations to standby
• Spring Boot Integration: Custom annotations for seamless read/write separation
• Database Administration: pgAdmin4 for monitoring and management
• Containerized Setup: Docker Compose with health checks
• Schema Management: Liquibase for database migrations

Architecture

Spring Boot App (8080) → Routes operations based on annotations
    ↓ @WriteOperation        ↓ @ReadOnly
Primary DB (5435)  →  Standby DB (5436)
  Read + Write           Read Only
                    ↑
              Streaming Replication

PostgreSQL Replication Configuration

Primary Database

• wal_level=replica - Enables Write-Ahead Logging for replication
• max_wal_senders=10 - Supports up to 10 standby connections
• wal_keep_size=64MB - Retains WAL files for standby servers
• synchronous_commit=on - Ensures transaction consistency
• synchronous_standby_names='standby_db' - Synchronous replication mode

Standby Database

• Initialized using pg_basebackup from primary
• Contains standby.signal file for standby mode
• Configured with primary_conninfo to connect to primary
• Operates in hot standby mode (read-only with live queries)

Spring Boot Routing

The application uses custom annotations for database routing:

@ReadOnly        // Routes to standby database for read operations
@WriteOperation  // Routes to primary database for write operations

Key components:

• DataSourceRouter: Determines which database to use
• DataSourceContextHolder: Thread-local context for routing decisions
• Custom annotations for method-level routing

Setup Instructions

Prerequisites

• Docker and Docker Compose
• Java 17+
• Maven

Installation

1. Clone the repository:

git clone https://github.com/MahmoudZain1/springboot-postgres-ha-routing
cd postgres-replication-springboot

2. Start all services:

docker-compose up -d --build

3. Verify all services are running:

docker-compose ps

All services should show "healthy" status.

Access Points

• Spring Boot Application: http://localhost:8080
• pgAdmin: http://localhost:5050 (admin@admin.com / admin)
• Primary Database: localhost:5435
• Standby Database: localhost:5436

Testing the Setup

Create User (Write Operation - Primary)

curl -X POST http://localhost:8080/api/users \
  -H "Content-Type: application/json" \
  -d '{"name":"John Doe","email":"john@example.com"}'

Get Users (Read Operation - Standby)

curl http://localhost:8080/api/users

Verify Replication

1. Access pgAdmin at http://localhost:5050
2. Add Primary Server: Host=primary_db, Port=5432, DB=primarydb
3. Add Standby Server: Host=standby_db, Port=5432, DB=primarydb
4. Compare data between both servers to confirm replication

Health Monitoring

Check application health:

curl http://localhost:8080/actuator/health

Monitor database logs:

# Primary database logs
docker-compose logs primary_db

# Standby database logs
docker-compose logs standby_db

# Application logs
docker-compose logs spring-app