Logical vs Physical Replication in PostgreSQL: Key Differences and Usage

In PostgreSQL, physical replication copies the exact database files at the storage level, keeping a standby server in sync with the primary. Logical replication sends data changes as SQL statements, allowing selective replication and more flexibility like replicating specific tables or filtering data.

Quick Comparison

This table summarizes the main differences between logical and physical replication in PostgreSQL.

Aspect	Physical Replication	Logical Replication
Replication Level	Block-level (storage files)	Row-level (SQL changes)
Flexibility	Replicates entire database cluster	Replicates selected tables or data subsets
Use Cases	High availability, failover	Data integration, selective sync
Setup Complexity	Simpler, uses streaming WAL	More complex, requires publications/subscriptions
Data Format	Binary WAL segments	Logical changes as SQL or JSON
Supports Schema Changes	No, must be identical	Yes, can handle some schema changes

Key Differences

Physical replication works by copying the Write-Ahead Log (WAL) files at the binary level from the primary server to the standby. This means the standby is an exact byte-for-byte copy of the primary, including all databases and tables. It is mainly used for high availability and disaster recovery because it keeps the standby ready to take over instantly.

In contrast, logical replication sends data changes as logical SQL statements or messages. It allows replicating only specific tables or even filtering rows, which physical replication cannot do. Logical replication uses a publish-subscribe model where the primary publishes changes and the subscriber applies them. This makes it more flexible for data integration and selective replication.

Physical replication requires the primary and standby to have the same database cluster structure, while logical replication can handle some schema changes and different versions. However, logical replication is more complex to set up and may have slightly higher latency due to processing changes at a higher level.

Code Comparison

Here is how you set up physical replication in PostgreSQL by configuring the primary server to allow streaming WAL to a standby.

bash

## On primary server (postgresql.conf)
wal_level = replica
max_wal_senders = 3
wal_keep_size = 16MB

## pg_hba.conf
host replication replicator 192.168.1.100/32 md5

## Create replication user
CREATE ROLE replicator WITH REPLICATION PASSWORD 'password' LOGIN;

## On standby server
pg_basebackup -h primary_host -D /var/lib/postgresql/data -U replicator -P --wal-method=stream

## Start standby with standby.signal file

Output

Base backup copied and standby server starts streaming WAL from primary.

Logical Replication Equivalent

Logical replication setup involves creating a publication on the primary and a subscription on the standby for selected tables.

sql

## On primary server
CREATE PUBLICATION mypub FOR TABLE customers, orders;

## On subscriber server
CREATE SUBSCRIPTION mysub CONNECTION 'host=primary_host dbname=mydb user=replicator password=password' PUBLICATION mypub;

Output

Subscription "mysub" created, replicating specified tables from primary.

When to Use Which

Choose physical replication when you need a full standby server for high availability or disaster recovery with minimal delay and exact copy of the entire database cluster. It is simpler to set up for this purpose.

Choose logical replication when you want to replicate only certain tables, filter data, or integrate data across different PostgreSQL versions or schemas. It is ideal for data distribution, reporting, or migrating data selectively.

Key Takeaways

Physical replication copies the entire database cluster at the storage level for high availability.

Logical replication sends SQL changes and allows selective table or row replication.

Use physical replication for failover and disaster recovery setups.

Use logical replication for flexible data integration and selective syncing.

Logical replication supports some schema changes; physical replication requires identical clusters.