PostgreSQL Replication Explained: How to Ensure Your Data Stays in Sync

Introduction

In today’s digital world, data availability and consistency are critical. Businesses can’t afford downtime or data loss, which is why database replication plays such an important role. Replication is the process of maintaining copies of your database across different servers so that if one fails, another can take over.

PostgreSQL, one of the most popular open-source relational databases, offers powerful replication features out-of-the-box. From traditional streaming replication to more flexible logical replication and advanced bi-directional replication (BDR), PostgreSQL makes it easier to build reliable and scalable systems.

In this blog, we’ll break down how PostgreSQL replication works, the different replication methods, and best practices to ensure your data stays safe, synchronized, and highly available.

How PostgreSQL Replication Works

At the heart of PostgreSQL replication lies Write-Ahead Logging (WAL). WAL is a mechanism where every database change is first written to a log file before being applied to the actual data files.

Here’s how it works in replication:

1. The Primary server generates WAL records whenever data changes occur.
2. These WAL records are sent to one or more Replica servers.
3. Replicas apply these changes to stay synchronized with the Primary.

This approach ensures consistency and durability,even if the primary crashes, the replica can quickly catch up.

Types of Replication in PostgreSQL

PostgreSQL provides replication based primarily on how changes are delivered from the Primary to the Replica. The two main replication methods are,

Streaming Replication

Streaming replication ensures your PostgreSQL database stays synchronized across multiple servers in near real-time. Instead of copying entire tables, it streams Write-Ahead Log (WAL) changes to replicas, keeping them up-to-date without impacting performance.

Workflow
Here’s how data flows from the Primary to the Replicas in real time. Understanding this workflow helps you plan replication architecture and anticipate how the system behaves during operations.

1. Every change in the Primary database (INSERT, UPDATE, DELETE) is first written to the WAL.
2. The WAL sender process on the Primary streams these changes over TCP to the Replicas.
3. The WAL receiver on each Replica picks up the changes and replays them to keep data synchronized.
4. Replicas can serve read-only queries while applying WAL changes.
5. If the Primary fails, one of the Replicas can be promoted to become the new Primary, minimizing downtime.

Behavior / Modes
Replication can operate in different modes depending on your business needs: balancing speed versus data safety. Choosing the right mode is critical for performance and reliability.

• Asynchronous Mode: The Primary does not wait for confirmation from the Replica before committing transactions.
  • Pros: Faster performance on the Primary.
  • Cons: Slight risk of losing recent transactions if the Primary crashes.
• Synchronous Mode: The Primary waits until at least one Replica confirms it has received the WAL changes before committing.
  • Pros: Guarantees zero data loss.
  • Cons: Slightly slower transactions due to waiting for acknowledgment.

When to Use
Knowing the right scenarios to implement streaming replication ensures high availability and scalability for your PostgreSQL environment.

• High Availability: Keep your database online even if the Primary fails.
• Read Scalability: Offload read-only queries to Replicas to reduce load on the Primary.
• Disaster Recovery: Standby Replicas can quickly take over in case of failure.

Streaming Replication Configuration Guide

Step 1: Configure Primary Server (postgresql.conf)

wal_level = replica      # Ensures enough WAL data is available for streaming
max_wal_senders = 10     # Allows multiple Replicas to connect simultaneously
wal_keep_size = 64MB     # Retains enough WAL files to prevent replication lag
synchronous_standby_names = 'standby1'  # Optional for synchronous

Step 2: Configure Client Authentication (pg_hba.conf)

host replication replicator 192.168.1.101/32 md5

• Allows the replication user from the standby server to connect to the Primary for streaming WAL.

Step 3: Create Replication User on Primary

CREATE ROLE replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'StrongPass123';
CREATE ROLE

• Replication requires a dedicated user with REPLICATION privileges to securely stream WAL.

Step 4: Reload Primary Configuration

pg_ctl reload
server signaled

• Apply changes made in postgresql.conf and pg_hba.conf without restarting the database.

Step 5: Take Base Backup on Standby

pg_basebackup -h 192.168.1.100 -D /var/lib/postgresql/16/main -U replicator -v -P --wal-method=stream
pg_basebackup: starting background WAL streaming
pg_basebackup: base backup completed

• Initializes the standby with a consistent snapshot of the Primary database.
• --wal-method=stream ensures WAL files are streamed during the backup for real-time sync.

Step 6: Configure Standby Server (postgresql.conf)

hot_standby = on
primary_conninfo = 'host=192.168.1.100 port=5432 user=replicator password=StrongPass123 sslmode=prefer'

touch /var/lib/postgresql/16/main/standby.signal

• hot_standby = on → Allows read-only queries on the standby.
• primary_conninfo → Connection details to the Primary for streaming.
• standby.signal → Activates standby mode in PostgreSQL 12+.

Step 7: Start Standby Server

pg_ctl start
waiting for server to start... done
server started
2025-09-21 22:50:00 LOG: started streaming WAL from primary at 0/3000140 on timeline 1
2025-09-21 22:50:00 LOG: consistent recovery state reached at 0/3000140

• Begins streaming WAL from the Primary, keeping the standby synchronized in real-time.

Step 8: Verify Replication on Primary

SELECT pid, state, client_addr, sent_location, write_location, flush_location, replay_location 
FROM pg_stat_replication;
pid  |  state   | client_addr  | sent_location | write_location | flush_location | replay_location 
------+----------+--------------+---------------+----------------+----------------+-----------------
 7890 | streaming| 192.168.1.101| 0/3002500     | 0/3002500      | 0/3002500      | 0/3002500

• Confirms that replication is active and the standby is receiving WAL changes.

Logical Replication

Logical replication in PostgreSQL allows you to replicate specific tables or subsets of data between databases, rather than the entire cluster. Instead of streaming raw WAL files like streaming replication, it replicates data changes at the row level, giving you more flexibility for selective replication.

Workflow

Here’s how data flows from the Publisher to the Subscriber in logical replication:

1. The Publisher database captures INSERT, UPDATE, and DELETE changes on the specified tables.
2. These changes are stored in logical decoding messages instead of raw WAL.
3. The Subscriber connects to the Publisher using a subscription and applies these changes to its tables.
4. Subscribers can be in the same or a different PostgreSQL version or even in a different schema.
5. If the Publisher goes down, the Subscriber can continue operating independently once the connection is restored.

Behavior / Modes

Logical replication is asynchronous by design, meaning the Publisher does not wait for the Subscriber to confirm receipt before committing transactions.

Pros:

• Fine-grained replication (specific tables, columns, or rows).
• Supports upgrades and migrations between versions.
• Allows cross-database replication.

Cons:

• Slightly more setup complexity than streaming replication.
• Some DDL operations are not automatically replicated.

When to Use

Logical replication is ideal in scenarios where you need flexibility and selective replication:

• Partial Replication: Only replicate certain tables or columns.
• Upgrades / Migrations: Move data to a new PostgreSQL version with minimal downtime.
• Data Integration: Combine data from multiple sources or replicate to reporting/analytics databases.
• Heterogeneous Replication: Replicate between different PostgreSQL versions or schemas.

Logical Replication Configuration Guide

Step 1: Configure Primary (postgresql.conf)

wal_level = logical         # Enables logical decoding so changes can be sent to subscribers

max_replication_slots = 10  # Ensures enough replication slots for all subscriptions, preventing WAL from being removed before subscribers receive it

max_wal_senders = 10        # Allows multiple subscribers to connect and receive changes simultaneously   

Step 2: Set Up Publication on Primary

CREATE PUBLICATION mypub FOR TABLE public.mytable;
CREATE PUBLICATION

• Defines which tables’ changes will be replicated to subscribers.
• Publications are necessary because logical replication is table-specific, unlike streaming replication which replicates the whole cluster.

Step 3: Set Up Subscription on Subscriber

CREATE SUBSCRIPTION mysub CONNECTION 'host=<primary_ip> dbname=mydb user=replicator password=StrongPass123' PUBLICATION mypub;
CREATE SUBSCRIPTION

• Connects the subscriber database to the primary and tells it which publication to follow.
• The subscriber will receive and apply only the changes from the published tables.

Step 4: Check Subscription Status

SELECT subname, pid, relid, received_lsn, last_msg_send_time, last_msg_receipt_time FROM pg_stat_subscription;
subname |  pid  | relid | received_lsn | last_msg_send_time     | last_msg_receipt_time  
---------+-------+-------+--------------+-----------------------+-----------------------
 mysub   | 12345 |     0 | 0/6007500    | 2025-09-21 23:15:10   | 2025-09-21 23:15:11

• Confirms the subscription is active and receiving changes from the publication.
• Helps monitor replication lag and troubleshoot connectivity or data flow issues.

Replication Topologies in PostgreSQL

PostgreSQL replication can be set up in different ways depending on your requirements for availability, scalability, and data flow. The following are some common replication topologies that illustrate how data moves between servers

Cascading Replication

Cascading replication is an extension of streaming replication where a standby server can act as a source for other standbys. Instead of all replicas connecting directly to the primary, changes flow through a chain of servers.

Why use it:

• Reduces load on the primary server.
• Useful for distributed or multi-level replication setups.

When to use:

• Large-scale deployments with multiple replicas.
• Geo-distributed architectures where replicas are spread across data centers.

Bi-Directional Replication (BDR)

BDR is a multi-master replication system that allows two or more PostgreSQL nodes to simultaneously accept writes while keeping the data synchronized across all nodes.

Why use it:

• Enables high availability with no single point of failure.
• Supports read/write scalability across multiple locations.

When to use:

• Multi-region deployments where applications need to write to multiple nodes.
• Scenarios requiring continuous availability even if one node fails.

Conclusion

Database replication is a critical component for building highly available, scalable, and resilient systems. PostgreSQL provides robust replication options to suit different needs:

• Streaming Replication: Ideal for real-time high availability and read scaling.
• Logical Replication: Perfect for selective, table-level replication, cross-version upgrades, and data integration.
• Cascading Replication: Reduces load on the primary in large or distributed setups.
• Bi-Directional Replication (BDR): Enables multi-master replication for geographically distributed, write-scalable systems.

By understanding the workflows, topologies, and configuration steps, you can choose the right replication strategy for your environment and ensure your data remains consistent, synchronized, and available even during failures.

Proper planning, monitoring, and following best practices will help you maximize the benefits of PostgreSQL replication while minimizing risks like replication lag or data loss.

With replication in place, your database is not just storing data. it’s actively protecting and distributing it to meet the demands of modern applications.