Setting Up Logical Replication in PostgreSQL for Data Synchronization

PostgreSQL Logical Replication: A Guide to Data Sync

PostgreSQL's logical replication allows you to selectively replicate data across PostgreSQL databases. Unlike physical replication, which replicates the entire data cluster, logical replication lets you replicate specific tables or changes to rows, offering flexibility in data synchronization across distributed systems. It’s ideal for scaling databases, synchronizing data in real-time, and implementing high-availability systems.

Syntax and Setup of Logical Replication

Logical replication requires setting up a publisher and subscriber. The publisher is the database where changes originate, and the subscriber is where changes are applied.

1. Create a Publication: The publication is set up on the source database (publisher).

2. Create a Subscription: The subscription is created on the target database (subscriber).

Step 1: Creating a Publication on the Publisher

Code:

-- Create a publication for specific tables
CREATE PUBLICATION my_publication FOR TABLE table_name;

-- Or create a publication for all tables
CREATE PUBLICATION my_publication FOR ALL TABLES;

Explanation:

• CREATE PUBLICATION initializes a publication named my_publication.
• Using FOR TABLE table_name, you specify which table(s) to replicate.
• The FOR ALL TABLES option replicates all tables in the database.

Step 2: Creating a Subscription on the Subscriber

Code:

-- Create a subscription to the publication from the publisher
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=publisher_host dbname=publisher_db user=username password=your_password'
    PUBLICATION my_publication;

Explanation:

• CREATE SUBSCRIPTION sets up a subscription named my_subscription.
• CONNECTION specifies the connection string to the publisher database.
• PUBLICATION my_publication connects the subscription to the publication created on the publisher.

Example: Setting up Logical Replication for Specific Tables

Suppose we have two databases, source_db (publisher) and target_db (subscriber), and we want to replicate changes in a table called employees.

1. On source_db (Publisher):

Code:

CREATE PUBLICATION employee_pub FOR TABLE employees;

2. On target_db (Subscriber):

Code:

CREATE SUBSCRIPTION employee_sub
    CONNECTION 'host=source_host dbname=source_db user=replica_user password=secure_pass'
    PUBLICATION employee_pub;

With this setup, any data changes in the employees table in source_db will automatically replicate to the employees table in target_db.

Key Considerations and Explanation:

• Selective Data Replication: Logical replication allows specific tables or rows to be replicated, useful for cases where full replication is unnecessary.
• Real-Time Data Synchronization: Suitable for scenarios needing real-time or near-real-time data availability, such as high-availability applications.
• Schema Changes: Logical replication does not automatically replicate schema changes. Structural modifications (e.g., adding or dropping columns) must be manually synchronized.
• Network and Security: Logical replication relies on network connectivity and PostgreSQL roles with replication permissions, so secure configurations are critical.

All PostgreSQL Questions, Answers, and Code Snippets Collection.