How to Set Up PostgreSQL Logical Replication

What You’ll Need

• Hetzner VPS or Contabo VPS for hosting primary and subscriber database nodes
• DigitalOcean as an alternative cloud infrastructure provider
• n8n Cloud or self-hosted n8n for triggering downstream event processing pipelines

Table of Contents

• Understanding PostgreSQL Logical Replication Architecture
• Step 1: Preparing and Provisioning the Primary Database Server
• Step 2: Creating Publications on the Publisher Instance
• Step 3: Provisioning Schema and Creating Subscriptions on the Target Server
• Step 4: Monitoring, Syncing New Tables, and Resolving Conflicts
• Getting Started

Understanding PostgreSQL Logical Replication Architecture

PostgreSQL logical replication uses a publish and subscribe model to stream database changes from a primary instance (the publisher) to one or more destination instances (the subscribers). Unlike physical replication, which operates on write ahead log (WAL) blocks byte for byte, logical replication streams change events based on row data changes: inserts, updates, and deletes.

This architecture offers key benefits:

1. Selective replication: You can replicate specific tables or schemas rather than the entire cluster.
2. Cross-version migration: You can stream data seamlessly between different major versions of PostgreSQL (for instance, PostgreSQL 14 to PostgreSQL 16).
3. Analytics offloading: You can write to a production cluster while streaming data to a reporting replica where subscribers can have different indexes or trigger custom triggers.
4. Multi-master topology support: While standard setup is single publisher to single subscriber, logical replication allows complex routing where a single subscriber receives tables from multiple publishers.

Under the hood, PostgreSQL uses a logical decoding output plugin called pgoutput. The primary node creates a replication slot that retains necessary WAL data, parses row level changes, formats them into a protocol stream, and sends them to subscriber background workers. On the subscriber side, the apply worker process reads incoming transactions and executes them locally inside identical table schemas.

If you are deploying database instances across lightweight cloud servers, read my guide on Self-hosting open source tools on budget VPS to optimize your underlying Linux compute nodes before proceeding.

Step 1: Preparing and Provisioning the Primary Database Server

To start setting up logical replication, you must adjust configuration parameters on your publisher server hosted on Hetzner VPS or DigitalOcean. The primary database must write sufficient information to the write ahead log (WAL) so that logical output plugins can decode row operations.

Modifying postgresql.conf Settings

Locate your primary server configuration file (typically /etc/postgresql/16/main/postgresql.conf on Debian or Ubuntu systems) and update the following settings:

wal_level = logical
max_wal_senders = 10
max_replication_slots = 10
max_worker_processes = 8
listen_addresses = '*'

Let us review these options:

• wal_level = logical: Upgrades WAL logging from replica level to include decoding details necessary for logical replication.
• max_wal_senders: Specifies how many concurrent streaming process connections PostgreSQL will allow.
• max_replication_slots: Defines the maximum number of active logical replication slots available on the publisher.
• max_worker_processes: Sets the total process capacity for parallel workers, background operations, and logical workers.

After updating postgresql.conf, restart PostgreSQL using systemctl:

sudo systemctl restart postgresql

Configuring Network Access in pg_hba.conf

Next, configure network host permissions on the publisher node. Open /etc/postgresql/16/main/pg_hba.conf and add an entry permitting the subscriber server IP address to connect over TLS/SSL using a dedicated replication user:

host    all             replicator      198.51.100.45/32        scram-sha-256
host    replication     replicator      198.51.100.45/32        scram-sha-256

Reload PostgreSQL configuration without restarting the entire process:

SELECT pg_reload_conf();

Creating Database, Tables, and Replication Roles

Log into the primary database instance as the superuser (postgres) using psql. Run the following script to create a production database, a dedicated schema with explicit primary keys, and an isolated user role with replication privileges:

CREATE DATABASE primary_ecom_db;
\c primary_ecom_db;

CREATE TABLE public.customers (
    customer_id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE public.orders (
    order_id UUID PRIMARY KEY,
    customer_id UUID NOT NULL REFERENCES public.customers(customer_id),
    total_amount NUMERIC(12, 2) NOT NULL,
    status VARCHAR(50) NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'SecureSuperSecretPassword2026!';

GRANT CONNECT ON DATABASE primary_ecom_db TO replicator;
GRANT USAGE ON SCHEMA public TO replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replicator;

Notice that each table must have a primary key or a valid replica identity configured. Logical replication uses primary keys to execute UPDATE and DELETE queries on the target subscriber.

💡 Fast-Track Your Project: Don’t want to configure this yourself? I build custom n8n pipelines and bots. Message me with code SYS3-HUGO.

Step 2: Creating Publications on the Publisher Instance

A publication is a group of tables whose changes are bundled together for streaming replication. You can publish all tables in a database or selectively select specific tables and explicit operations (such as streaming only INSERT and UPDATE commands while dropping DELETE events).

Creating a Global vs Specific Table Publication

Execute the following commands on the primary database (primary_ecom_db):

\c primary_ecom_db;

CREATE PUBLICATION ecom_full_pub FOR ALL TABLES;

If you prefer to stream only specific tables and ignore deletion statements to preserve historical record logs, you can specify individual table options:

CREATE PUBLICATION ecom_orders_pub FOR TABLE public.orders WITH (publish = 'insert, update');

To verify the active publications and their operational parameters, run:

SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete, pubtruncate FROM pg_publication;

Inserting Test Data on the Publisher

Populate initial records into the primary instance so that we can evaluate snapshot creation during subscription initialization:

INSERT INTO public.customers (customer_id, email, full_name) VALUES
('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'alice@example.com', 'Alice Smith'),
('b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a22', 'bob@example.com', 'Bob Jones');

INSERT INTO public.orders (order_id, customer_id, total_amount, status) VALUES
('c0eebc99-9c0b-4ef8-bb6d-6bb9bd380a33', 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 149.99, 'completed'),
('d0eebc99-9c0b-4ef8-bb6d-6bb9bd380a44', 'b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a22', 89.50, 'processing');

Step 3: Provisioning Schema and Creating Subscriptions on the Target Server

PostgreSQL logical replication does not replicate Data Definition Language (DDL) commands such as CREATE TABLE, ALTER TABLE, or DROP TABLE. The destination table schemas must exist on the subscriber node before replication can begin streaming physical changes.

Pre-Creating Destination Schema on Subscriber

Connect to your target node hosted on Contabo VPS or Hetzner VPS and open psql. Execute this script to provision the target database and destination schemas:

CREATE DATABASE replica_ecom_db;
\c replica_ecom_db;

CREATE TABLE public.customers (
    customer_id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE public.orders (
    order_id UUID PRIMARY KEY,
    customer_id UUID NOT NULL REFERENCES public.customers(customer_id),
    total_amount NUMERIC(12, 2) NOT NULL,
    status VARCHAR(50) NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Creating the Subscription

Now create the subscription on the subscriber database (replica_ecom_db). Specify connection credentials pointing back to the publisher server:

CREATE SUBSCRIPTION ecom_full_sub
CONNECTION 'host=203.0.113.10 port=5432 dbname=primary_ecom_db user=replicator password=SecureSuperSecretPassword2026!'
PUBLICATION ecom_full_pub
WITH (copy_data = true, create_slot = true, enabled = true);

Parameters breakdown:

• copy_data = true: Performs an initial snapshot load of existing publisher row data into subscriber tables before applying live WAL updates.
• create_slot = true: Asks the publisher instance to create a new logical replication slot automatically.
• enabled = true: Immediately starts streaming WAL data post creation.

Verifying Initial Data Copy

Check if existing rows were copied successfully over to the target database:

SELECT * FROM public.customers;
SELECT * FROM public.orders;

Both queries will return the rows created on the primary server in Step 2.

If you process replicated events in real time through webhooks or external API calls, check out our operational guide on How To Implement Idempotent Webhook Payload Processing to prevent duplicate message execution when handling streaming state changes.

Step 4: Monitoring, Syncing New Tables, and Resolving Conflicts

Maintaining logical replication requires monitoring replication health, managing slot sizes, adding new tables over time, and resolving primary key collisions.

Checking Replication Lag and Slot Status

Run this diagnostic query on the primary database server to evaluate active logical connections, lag, and physical replication slots:

SELECT
    sub.client_addr,
    sub.application_name,
    slot.slot_name,
    slot.active,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sub.sent_lsn) AS sent_lag_bytes,
    pg_wal_lsn_diff(sub.sent_lsn, sub.write_lsn) AS write_lag_bytes,
    pg_wal_lsn_diff(sub.write_lag_bytes, sub.flush_lsn) AS flush_lag_bytes,
    pg_wal_lsn_diff(sub.flush_lsn, sub.replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication sub
JOIN pg_replication_slots slot ON sub.pid = slot.active_pid;

On the subscriber database, check state status using pg_stat_subscription:

SELECT
    subid,
    subname,
    pid,
    relid::regclass AS table_name,
    received_lsn,
    last_msg_send_time,
    last_msg_receipt_time,
    latest_end_lsn
FROM pg_stat_subscription;

Dynamically Adding New Tables to Replication

When you create a new table on the primary server, remember to mirror the schema on the target server manually and update the publication and subscription metadata.

On Primary:

CREATE TABLE public.products (
    product_id UUID PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

ALTER PUBLICATION ecom_full_pub ADD TABLE public.products;

On Subscriber:

CREATE TABLE public.products (
    product_id UUID PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

ALTER SUBSCRIPTION ecom_full_sub REFRESH PUBLICATION;

Running REFRESH PUBLICATION missing tables synchronization worker fetches the existing records from the newly added primary table without stopping current subscription streams.

Resolving Replication Conflicts

Logical replication halts apply operations when unique constraint violations or duplicate key conflicts occur on the subscriber.

To skip a blocking conflict transaction on the subscriber, locate the log output finding the conflicting LSN point, disable the subscription temporarily, set a skip LSN position, and re-enable it:

ALTER SUBSCRIPTION ecom_full_sub DISABLE;

ALTER SUBSCRIPTION ecom_full_sub SET (skip_lsn = '0/030000D0');

ALTER SUBSCRIPTION ecom_full_sub ENABLE;

Replace '0/030000D0' with the exact LSN logged in PostgreSQL system logs (/var/log/postgresql/postgresql-16-main.log).

Getting Started

Setting up PostgreSQL logical replication provides complete control over row-level data migration, analytics pipelines, and cross-region backups. Provision your primary node on Hetzner VPS or Contabo VPS, configure explicit primary keys, create your publications, and subscribe your target nodes cleanly.

If you build secondary notification layers or automated bot monitors for database event pipelines, check out my step-by-step guide on How to Build a Telegram Bot with n8n (No Code Required) to connect your database states to real-time communication channels.

Outsource Your Automation

Don’t have time? I build production n8n workflows, WhatsApp bots, and fully automated YouTube Shorts pipelines. Hire me on Fiverr, mention SYS3-HUGO for priority. Or DM at chasebot.online.