Eric Campbell 5 min read Databases

PostgreSQL Replication: Streaming and Logical

PostgreSQL offers two fundamentally different replication mechanisms, each suited for distinct operational requirements. Streaming replication creates exact physical copies of your entire database...

Key Insights

  • Streaming replication provides byte-for-byte physical copies ideal for high availability and read scaling, while logical replication enables selective data synchronization across different PostgreSQL versions and partial dataset replication.
  • Replication lag monitoring is critical—streaming replication lag appears in bytes of WAL difference, while logical replication lag manifests as transaction delays that require different monitoring approaches.
  • Replication slots prevent WAL deletion but can fill disks rapidly if subscribers disconnect; always monitor slot status and set max_slot_wal_keep_size to prevent catastrophic disk exhaustion.

Understanding PostgreSQL Replication Types

PostgreSQL offers two fundamentally different replication mechanisms, each suited for distinct operational requirements. Streaming replication creates exact physical copies of your entire database cluster, replicating every byte of data. Logical replication, conversely, replicates individual database changes at the logical level, allowing selective replication of specific tables or even columns.

The choice between these approaches determines your architecture’s capabilities. Streaming replication excels at disaster recovery, read scaling, and zero-downtime upgrades. Logical replication enables cross-version replication, partial dataset synchronization, and data consolidation from multiple sources.

Streaming Replication: Physical Copy Architecture

Streaming replication relies on PostgreSQL’s Write-Ahead Logging (WAL) mechanism. Every database change first writes to WAL files before modifying data pages. Replicas continuously stream these WAL records from the primary server and replay them locally, maintaining an exact copy.

Configure the primary server’s postgresql.conf:

# Enable WAL archiving and replication
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5
wal_keep_size = 1GB
hot_standby = on

# Synchronous replication (optional)
synchronous_commit = on
synchronous_standby_names = 'replica1,replica2'

The wal_level = replica setting ensures sufficient information in WAL for replication. The max_wal_senders parameter limits concurrent replica connections, while wal_keep_size prevents WAL deletion before replicas consume them.

Allow replication connections in pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replicator      192.168.1.0/24          scram-sha-256

Create a dedicated replication user with appropriate privileges:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

Initialize a replica using pg_basebackup:

# Stop PostgreSQL on replica server
systemctl stop postgresql

# Remove existing data directory
rm -rf /var/lib/postgresql/14/main/*

# Create base backup from primary
pg_basebackup -h primary.example.com -D /var/lib/postgresql/14/main \
  -U replicator -P -v -R -X stream -C -S replica1_slot

# Start replica
systemctl start postgresql

The -R flag creates standby.signal and configures connection settings automatically. The -C -S replica1_slot options create a replication slot, ensuring the primary retains necessary WAL files.

Monitor replication status from the primary:

SELECT 
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    sync_state,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

This query reveals each replica’s connection state, LSN (Log Sequence Number) positions, and replication lag in bytes. Significant lag indicates network issues, replica resource constraints, or excessive primary write load.

Logical Replication: Selective Synchronization

Logical replication decodes WAL records into logical changes (INSERT, UPDATE, DELETE) and applies them to subscriber databases. This approach enables replicating specific tables, filtering data, and even replicating between different PostgreSQL versions.

Configure postgresql.conf on the publisher:

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Create a publication on the source database:

-- Publish all tables in the database
CREATE PUBLICATION my_publication FOR ALL TABLES;

-- Or publish specific tables
CREATE PUBLICATION orders_publication FOR TABLE orders, order_items;

-- Publish with row filtering (PostgreSQL 15+)
CREATE PUBLICATION active_users_pub FOR TABLE users WHERE (active = true);

On the subscriber database, create a subscription:

CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=publisher.example.com port=5432 dbname=sourcedb user=replicator password=secure_password'
    PUBLICATION my_publication;

The subscriber immediately begins replicating existing data and streaming changes. Monitor subscription status:

SELECT 
    subname,
    pid,
    received_lsn,
    latest_end_lsn,
    pg_wal_lsn_diff(latest_end_lsn, received_lsn) AS lag_bytes
FROM pg_stat_subscription;

Logical replication handles schema changes differently than streaming replication. DDL statements don’t replicate automatically—you must apply schema changes to both publisher and subscriber:

-- On publisher
ALTER TABLE orders ADD COLUMN shipped_date timestamp;

-- On subscriber (apply the same change)
ALTER TABLE orders ADD COLUMN shipped_date timestamp;

For selective column replication, define column lists in publications (PostgreSQL 15+):

CREATE PUBLICATION sensitive_data_pub 
    FOR TABLE users (user_id, username, email);
-- Excludes sensitive columns like password_hash

Performance Monitoring and Optimization

Replication lag represents the delay between primary writes and replica application. For streaming replication, query lag from the primary:

SELECT 
    application_name,
    client_addr,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag_size,
    EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds
FROM pg_stat_replication;

Monitor replication slots to prevent disk exhaustion:

SELECT 
    slot_name,
    slot_type,
    database,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Inactive slots accumulate WAL files indefinitely. Set a safety limit in postgresql.conf:

max_slot_wal_keep_size = 10GB

This prevents runaway disk usage but may cause replication failure if subscribers disconnect too long.

For logical replication, conflicts occur when subscribers modify replicated tables. PostgreSQL applies last-write-wins by default, but you can detect conflicts:

SELECT * FROM pg_stat_subscription_stats;

Optimize logical replication performance by creating appropriate indexes on subscriber tables and adjusting worker processes:

max_logical_replication_workers = 8
max_sync_workers_per_subscription = 4

Failover and High Availability

Promoting a streaming replica to primary involves stopping replication and enabling writes:

# On the replica to promote
pg_ctl promote -D /var/lib/postgresql/14/main

Or trigger promotion by creating a trigger file:

touch /var/lib/postgresql/14/main/promote.signal

Verify promotion success:

SELECT pg_is_in_recovery();
-- Returns false when promotion completes

After promotion, reconfigure the former primary as a replica. This requires a new base backup or using tools like pg_rewind:

# On former primary (now becoming replica)
systemctl stop postgresql

pg_rewind --target-pgdata=/var/lib/postgresql/14/main \
    --source-server='host=new-primary.example.com port=5432 user=replicator'

# Create standby.signal
touch /var/lib/postgresql/14/main/standby.signal

systemctl start postgresql

For production environments, use automated failover tools like Patroni or pg_auto_failover. These handle leader election, health checks, and automatic promotion without manual intervention.

Operational Best Practices

Always create replication slots for streaming replicas to prevent WAL deletion, but monitor them religiously:

-- Drop inactive slots after investigation
SELECT pg_drop_replication_slot('old_replica_slot');

Configure WAL retention conservatively:

wal_keep_size = 2GB
max_slot_wal_keep_size = 10GB
wal_compression = on

Implement monitoring alerts for replication lag exceeding acceptable thresholds. For most applications, 10 seconds of lag warrants investigation; 60 seconds demands immediate action.

Test failover procedures regularly. Untested disaster recovery plans fail when needed. Schedule quarterly failover drills to verify your procedures and tooling work correctly.

For logical replication, maintain schema consistency manually or use schema migration tools that apply changes to all databases. Document your schema change process explicitly.

Secure replication connections using SSL:

# In pg_hba.conf
hostssl replication replicator 0.0.0.0/0 scram-sha-256

Back up your primary database independently of replication. Replication protects against hardware failure but not logical corruption or accidental deletions, which replicate immediately to all subscribers.

PostgreSQL replication provides robust solutions for high availability and scalability. Choose streaming replication for complete database copies and disaster recovery. Select logical replication when you need version flexibility, partial replication, or data consolidation. Monitor both types vigilantly, test failover procedures, and maintain operational discipline around replication slot management.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.