Question: What is PostgreSQL logical replication?

Answer

Logical replication in PostgreSQL allows for selective data replication from one database to another. This method of replication is based on the publication and subscription model, where a source database can publish one or more sets of changes, and one or more target databases can subscribe to these changes. Logical replication can replicate data across different major versions of PostgreSQL and supports replicating data between PostgreSQL and other systems.

Key Features

• Selective Replication: Unlike physical replication that replicates the entire database, logical replication allows for the selection of specific tables to be replicated.
• Publisher/Subscriber Model: The publication (on the source database) defines which data changes are to be replicated. The subscription (on the target database) specifies from which publication it wants to receive data.
• Cross-Version Replication: Enables replication between different PostgreSQL versions, facilitating easier upgrades.
• DDL Replication: While logical replication primarily focuses on data changes (DML), DDL changes must be applied manually or through separate tools/scripts.

Configuration Steps

1. Enable Logical Replication on Both Servers Set wal_level to 'logical' and adjust max_replication_slots and max_wal_senders as needed in postgresql.conf.

   wal_level = logical
   max_replication_slots = 4 # Ensure this is at least the number of subscriptions
   max_wal_senders = 4 # Ensure this is sufficient for your setup

2. Create Publication on Source Database

   CREATE PUBLICATION my_publication FOR TABLE my_table;

3. Create Subscription on Target Database

   CREATE SUBSCRIPTION my_subscription
   CONNECTION 'host=source_host port=5432 dbname=source_db user=replicator password=secret'
   PUBLICATION my_publication;

Considerations

• Conflict Handling: Logical replication does not have built-in conflict resolution. Conflicts should be handled at the application level or through careful design.
• Performance Impact: While logical replication can be more flexible than physical replication, it may incur a higher performance overhead due to the need to transform WAL records into logical change records.
• Use Cases: Ideal for use cases requiring selective table replication, cross-version upgrades, or integrating with external systems.