What types of replication exist in PostgreSQL? What is the difference between physical and logical replication?

In PostgreSQL, there are two main types of replication: physical and logical.

Physical replication copies data at the filesystem block level, creating an exact copy of the database on the replica. It operates at the WAL (Write-Ahead Logging) level and provides synchronous or asynchronous replication. This approach is suitable for creating hot backups and scaling read operations, but does not allow data modification on replicas.

Logical replication works at the data change level (DML), transmitting individual INSERT, UPDATE, DELETE operations. It is more flexible: it allows replicating specific tables, filtering data, and even changing the structure of the replica. Logical replication is implemented through publications and subscriptions.

Key differences:

• Physical replication copies the entire cluster, while logical replication copies individual objects.
• Physical replication does not allow data modification on the replica, whereas logical replication can support independent changes.
• Logical replication is more flexible but more complex to set up and may have higher load.

Example of setting up logical replication:

1. Create a publication on the primary server:

CREATE PUBLICATION my_pub FOR TABLE my_table;

2. Create a subscription on the replica:

CREATE SUBSCRIPTION my_sub CONNECTION 'host=master_host dbname=mydb user=replicator password=secret' PUBLICATION my_pub;

Thus, the choice between physical and logical replication depends on the system's flexibility requirements and architecture.

Physical and logical replication in PostgreSQL differ in the method of data transfer and capabilities:

Physical replication:

• Copies binary data (WAL — Write-Ahead Log) from the primary server to the replica.
• The replica is an exact copy of the master at the filesystem level.
• Used for creating hot standby backups.
• The replica is usually read-only.
• Does not allow selective replication of individual tables or data.

Logical replication:

• Replicates changes at the SQL (DML) level — inserts, updates, deletes.
• Allows replication of individual tables or sets of data.
• The replica may not be an exact copy of the master; data transformation is permitted.
• Used for migrations, integrations, distributed systems.
• Allows having replicas with different structures or data filtering.

Example: if you need to replicate only part of the data or specific tables, logical replication is used. For high availability and quick synchronization of the entire database — physical replication.