Logical replication basics in PostgreSQL

Introduction

Logical replication lets you copy data changes from one database to another in real time. It helps keep data in sync across different places.

You want to keep a backup database updated without stopping the main one.

You need to share data between different applications or locations.

You want to migrate data from one server to another with minimal downtime.

You want to replicate only some tables, not the whole database.

You want to analyze data on a separate server without affecting the main database.

Syntax

PostgreSQL

CREATE PUBLICATION publication_name FOR TABLE table_name;

-- On subscriber server:
CREATE SUBSCRIPTION subscription_name CONNECTION 'connection_string' PUBLICATION publication_name;

CREATE PUBLICATION defines what data changes to send.

CREATE SUBSCRIPTION connects to the publisher and receives changes.

Examples

This creates a publication named my_pub that sends changes from the customers table.

PostgreSQL

CREATE PUBLICATION my_pub FOR TABLE customers;

This creates a subscription named my_sub that connects to the publisher and receives changes from my_pub.

PostgreSQL

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

Sample Program

This example sets up logical replication for the products table. The publisher creates the table, inserts some data, and creates a publication. The subscriber creates the same table and subscribes to the publication to receive data changes.

PostgreSQL

-- On publisher server:
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price NUMERIC);
INSERT INTO products (name, price) VALUES ('Pen', 1.20), ('Notebook', 2.50);
CREATE PUBLICATION product_pub FOR TABLE products;

-- On subscriber server:
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price NUMERIC);
CREATE SUBSCRIPTION product_sub CONNECTION 'host=publisher_host dbname=pubdb user=replicator password=secret' PUBLICATION product_pub;

Important Notes

Both publisher and subscriber must have the same table structure for replication to work correctly.

Logical replication sends only data changes, not schema changes like adding columns.

Use a replication user with proper permissions for security.

Summary

Logical replication copies data changes between databases in real time.

Use CREATE PUBLICATION on the source and CREATE SUBSCRIPTION on the target.

It is useful for backups, migrations, and sharing data selectively.