Kaushik's Notes

Replication Postgres Example

2 min read

Note: This is a basic demonstration of PostgreSQL replication concepts, not production-grade configuration.

Setup Overview

  • Master: PostgreSQL primary accepting writes
  • Standby: PostgreSQL replica replicating from master
  • Method: Streaming replication with synchronous mode
  • Tool: Docker containers for isolation

Step 1: Run Master Container

docker run --name pmaster -p 5432:5432 \
  -v /Users/kaushik/Projects/Database/rep/pmaster_data:/var/lib/postgresql/18/docker \
  -e POSTGRES_PASSWORD=postgres -d postgres

Step 2: Run Standby Container

docker run --name pstandby -p 5433:5432 \
  -v /Users/kaushik/Projects/Database/rep/pstandby_data:/var/lib/postgresql/18/docker \
  -e POSTGRES_PASSWORD=postgres -d postgres

Step 3: Prepare Standby for Replication

Stop Both Servers

docker stop pmaster pstandby

Backup and Replace Standby Data

# Backup existing standby data
mv pstandby_data pstandby_data_bk
 
# Copy master data to standby (base backup)
cp -R pmaster_data pstandby_data

Step 4: Configure Master for Replication

Edit pmaster_data/pg_hba.conf

Add at end of file:

host replication postgres all md5

Warning: Using postgres superuser for replication is not recommended in production. Create dedicated replication user.

Edit pmaster_data/postgresql.conf

Uncomment and set:

synchronous_standby_names = 'first 1 (standby_1)'

Step 5: Configure Standby for Replication

Edit pstandby_data/postgresql.conf

Uncomment and set:

primary_conninfo = 'application_name=standby_1 host=host.docker.internal port=5432 user=postgres password=postgres'

Create Standby Signal File

touch pstandby_data/standby.signal

Note: standby.signal file tells PostgreSQL to start in standby mode.

Step 6: Start Servers

docker start pmaster
docker start pstandby

Verification

Check Replication Status on Master

SELECT * FROM pg_stat_replication;

Test Replication

-- On master (port 5432)
CREATE TABLE test (id INT, name TEXT);
INSERT INTO test VALUES (1, 'Replication Test');
 
-- On standby (port 5433)
SELECT * FROM test;  -- Should see the data

Key Configuration Points

Master Configuration

  • pg_hba.conf: Allow replication connections
  • synchronous_standby_names: Define synchronous standby servers

Standby Configuration

  • primary_conninfo: Connection string to master
  • standby.signal: File indicating standby mode

Important Notes

  • Port Mapping: Master (5432), Standby (5433)
  • Host Reference: Use host.docker.internal for container-to-host communication
  • Synchronous Mode: Master waits for standby confirmation before commit
  • Data Directory: Standby starts with copy of master’s data directory
  • Production: Use proper replication user, SSL, and monitoring

Summary

  • Setup: Copy master data to standby, configure replication settings
  • Replication Type: Streaming replication (WAL-based)
  • Mode: Synchronous — with the default synchronous_commit = on, commits are flushed on the standby (no data loss), but not necessarily applied, so standby reads can still be stale. Use remote_apply for read-your-writes on the standby
  • Key Files: pg_hba.conf, postgresql.conf, standby.signal

Graph View

Backlinks