Logical Replication in Postgres

Logical replication in PostgreSQL offers a sophisticated mechanism for data synchronization, allowing users to replicate data changes at a logical level. This capability enables the replication of specific tables or entire databases across different PostgreSQL instances, providing a more granular control compared to traditional physical replication, which duplicates the entire database cluster. Such flexibility is particularly advantageous in scenarios involving data migration, high availability, or multi-master replication configurations.

Prerequisites

• Two PostgreSQL instances running on different servers or the same server for simplicity.
• Basic familiarity with SQL commands and PostgreSQL configuration.

In the following example, we will configure logical replication between two PostgreSQL databases: db1 (the primary database) and db2 (the subscriber database). The objective is to replicate a simple table from db1 to db2.

1. Preparing the Primary Database (db1)

To begin, we need to set up the primary database to publish changes to a table. Follow these steps:

Create the Table and Insert Initial Data

Connect to db1 and execute the following commands to create a table and insert some initial data:

CREATE TABLE table1 (ad VARCHAR(15), soyad VARCHAR(15));
INSERT INTO table1 (ad, soyad) VALUES ('Kemal', 'oz');

Create a Publication

A publication is a named collection of changes (inserts, updates, and deletes) that you want to replicate. Create a publication for all tables:

CREATE PUBLICATION alltables FOR ALL TABLES;

2. Preparing the Subscriber Database (db2)

Next, configure the subscriber database to receive data from the publication created on db1.

Create the Table

Ensure that db2 has the same table structure as db1:

CREATE TABLE table1 (ad VARCHAR(15), soyad VARCHAR(15));

Create a Subscription

A subscription connects to a publication and applies changes from it. Execute the following command to create a subscription in db2:

CREATE SUBSCRIPTION mysub1 CONNECTION 'host=localhost port=5432 user=postgres dbname=test9' PUBLICATION alltables;

Be sure to replace the connection parameters (host, port, user, and dbname) with those appropriate for your setup.

3. Testing the Replication

To verify that replication is functioning correctly, insert new data into the table on db1 and check if it appears in db2.

Insert Data into db1

Execute the following command on db1:

INSERT INTO table1 (ad, soyad) VALUES ('alı', 'oz');

Verify Data in db2

Connect to db2 and check the table contents:

SELECT * FROM table1;

You should see both the initial data and the new data inserted into db1:

ad   | soyad
-------+-------
Kemal | oz
alı   | oz