PostgreSQL: Replication

Replication is the process of copying data from one PostgreSQL server (the primary) to one or more servers (replicas). It ensures high availability and load distribution.

Types of Replication in PostgreSQL:

• Streaming Replication – Real-time log-based replication.

  • Synchronous Replication – Ensures data is written to both primary and replica before acknowledging.

  • Asynchronous Replication – Data is sent to the replica but does not wait for confirmation.

• Logical Replication – Table-level replication for selective data sharing.

Streaming Replication

Streaming replication uses WAL (Write-Ahead Log) to send real-time changes from the primary database to one or more standby servers. It provides near-instantaneous replication for all changes. Streaming replication can be either synchronous or asynchronous.

Synchronous Replication: The primary waits for confirmation from the replica before committing transactions. This guarantees no data is lost if the primary fails.

synchronous_standby_names = 'standby1'
synchronous_commit = 'on'

Asynchronous Replication: The primary database does not wait for the replica to confirm. It offers faster performance but may cause data lag on replicas.

synchronous_standby_names = 'standby1'
synchronous_commit = 'off'

Use Case: High Availability and Disaster Recovery

A fintech company needs to ensure zero downtime for critical customer transactions. If the primary database fails, a standby replica can quickly take over. This enable quick recovery and minimal data loss.

Example Scenario:

• Primary Database: Handles live customer transactions.

• Standby Database: Fails over automatically in case of hardware failure.

Logical Replication

Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key).

Logical replication uses a publish and subscribe model with one or more subscribers subscribing to one or more publications on a publisher node. Subscribers pull data from the publications they subscribe to and may subsequently re-publish data to allow cascading replication or more complex configurations.

Use Case: Data Sharing Across Systems

A retail company wants to share specific tables (e.g., orders, products) with its BI (Business Intelligence) platform for reporting, while keeping customer data private.

Example Scenario:

• Primary Database: Handles e-commerce transactions.

• Replica Database: Only replicates the orders table for analytics.

• Benefit: Selective replication for cross-system data sharing.

Example Setup:

Set up a logical publication on the primary:

CREATE PUBLICATION my_pub FOR TABLE orders;

On the replica, subscribe to the publication:

CREATE SUBSCRIPTION my_sub
CONNECTION 'host=primary_db user=replicator dbname=ecommerce'

References

• https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION
• https://www.postgresql.org/docs/current/logical-replication.html