PostgreSQL Branch-Specific Data Replication Demo

This repository demonstrates how to implement branch-specific data isolation and synchronization using PostgreSQL 18's logical replication features. The setup creates a system where different branches can handle specific customer segments while maintaining bidirectional synchronization with a central database.

Architecture Overview

                      ┌─────────────────┐
                      │  Central DB     │
                      │    (HQ)         │
                      │ All Customers   │
                      └─────┬─────┬─────┘
                            │     │
              pub_hq_to_a ┌─┘     └─┐ pub_hq_to_b
                         │          │
                    type A     type B
                         │          │
              ┌─────────┘          └──────────┐
              │                               │
    ┌─────────┴───────┐             ┌────────┴──────┐
    │   Branch A      │             │   Branch B     │
    │ Type A Customers│             │Type B Customers│
    └─────────┬───────┘             └────────┬──────┘
              │                              │
              └──────────┐        ┌─────────┘
                        │        │
                 pub_a_to_hq  pub_b_to_hq

Replication Flow:
─────────────────
→ HQ to Branch A: Only type 'A' customers
→ HQ to Branch B: Only type 'B' customers
← Branch A to HQ: Changes to type 'A' customers
← Branch B to HQ: Changes to type 'B' customers

The system consists of:

• Central Database (HQ): Maintains the complete customer dataset
• Branch A: Handles customers of type 'A'
• Branch B: Handles customers of type 'B'

Prerequisites

• Docker and Docker Compose
• Basic understanding of PostgreSQL
• Terminal/Command Line access

Quick Start

1. Clone the repository:

git clone https://github.com/dwickyfp/postgres-branch-isolation.git
cd postgres-branch-isolation

2. Start the PostgreSQL instances:

docker-compose up -d

3. Execute the setup scripts in order:

# For Central Database (using port 5433)
psql -h localhost -p 5433 -U admin -d analytics -f sql/sql_central.sql

# For Branch A (using port 5434)
psql -h localhost -p 5434 -U admin -d analytics -f sql/sql_branch_a.sql

# For Branch B (using port 5435)
psql -h localhost -p 5435 -U admin -d analytics -f sql/sql_branch_b.sql

Repository Structure

├── docker-compose.yml      # Docker configuration for PostgreSQL instances
├── sql/
│   ├── sql_central.sql    # Setup script for central database
│   ├── sql_branch_a.sql   # Setup script for branch A
│   └── sql_branch_b.sql   # Setup script for branch B
├── article.md             # Detailed explanation article
└── README.md             # This file

Configuration Details

Docker Compose Configuration

The docker-compose.yml file sets up three PostgreSQL 18 instances with:

• Logical replication enabled (wal_level=logical)
• Sufficient replication slots (max_replication_slots=16)
• Optimized performance settings

Database Ports

• Central Database: 5433
• Branch A: 5434
• Branch B: 5435

Connection Details

All databases use the following credentials:

• Username: admin
• Password: pubpass
• Database: analytics

Testing the Setup

1. Insert test data in the central database:

INSERT INTO retail.customers (id, name, tipe, email)
VALUES
  (gen_random_uuid(),'Alice','A','alice@acme.test'),
  (gen_random_uuid(),'Bob','B','bob@acme.test'),
  (gen_random_uuid(),'Cici','A','cici@acme.test');

2. Verify data distribution:

-- In each database:
SELECT * FROM retail.customers;

Expected results:

• Central DB: All records
• Branch A: Only type 'A' customers
• Branch B: Only type 'B' customers

Monitoring

Check replication status:

-- List publications
SELECT * FROM pg_publication;

-- List subscriptions
SELECT * FROM pg_subscription;

-- Check replication slots
SELECT * FROM pg_replication_slots;

Common Issues and Troubleshooting

1. Connection Issues:

# Check if containers are running
docker ps

# View container logs
docker logs pg18_central
docker logs pg18_branch_a
docker logs pg18_branch_b

2. Replication Issues:

-- Check subscription status
SELECT * FROM pg_stat_subscription;

-- Check replication slot status
SELECT * FROM pg_replication_slots;

Further Reading

• PostgreSQL Logical Replication Documentation
• Docker PostgreSQL Image Documentation

Contributing

Feel free to submit issues and enhancement requests!

License

This project is licensed under the MIT License - see the LICENSE file for details.