Step-by-Step : PostgreSQL Logical Replication Setup

From prod-1, I need to replicate only two tables — employees and departments — to prod-2 using PostgreSQL logical replication.

Prerequisites:

• PostgreSQL version 10 or higher on both servers
• Network connectivity between prod-1 and prod-2
• wal_level must be set to logical on prod-1
• Superuser or replication privileges are available

1. Configure Source Server (prod-1)

1.1. Ensure wal_level is logical

Edit postgresql.conf on prod-1:

        wal_level = logical

        max_replication_slots = 10

        max_wal_senders = 10

        Reload the cluster -

        SELECT pg_reload_conf();

1.2. Allow prod-2 to connect to connect prod-1

In prod-1 pg_hba.conf:

    host    replication    replicator    <PROD-2 IP>/32    md5

    host    all            replicator    <PROD-2 IP>/32    md5

    Reload the cluster:

    SELECT pg_reload_conf();

1.3. Create a replication user

On prod-1:

    CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'StrongPassword';

1.4. Grant SELECT on tables to replication user

    GRANT SELECT ON TABLE public.employees TO replicator;

    GRANT SELECT ON TABLE public.departments TO replicator;

    Also grant usage on schema:

    GRANT USAGE ON SCHEMA public TO replicator;

2. Create Publication (prod-1)

Note - Replicate only employees and departments:

CREATE PUBLICATION pub_hr FOR TABLE employees, departments;

Verify:

    \dRp+ pub_hr

3. Ensure Tables Have Primary Keys

Logical replication requires PK or unique index.

On prod-1:

\d employees

\d departments

If missing:

ALTER TABLE employees ADD PRIMARY KEY (id);

ALTER TABLE departments ADD PRIMARY KEY (dept_id);

4. Prepare Target Server (PROD-2)

4.1. Create same tables on analytics-prod

Tables must exist with same structure (except constraints/triggers).

Either:

• create manually, or

• use pg_dump -s:

pg_dump -s -t employees -t departments prod1_db > hr_schema.sql

psql analytics_db < hr_schema.sql

5. Create Subscription on PROD-2

CREATE SUBSCRIPTION sub_hr CONNECTION 'host=<PROD-1-IP> port=5432 dbname=prod1_db user=replicator password=StrongPassword' PUBLICATION pub_hr;

This will:

• copy initial data

• begin replication

6. Check Replication Status

On prod-2:

SELECT * FROM pg_subscription;

SELECT * FROM pg_stat_subscription;

On prod-1:

SELECT * FROM pg_stat_replication;

7. What Is Replicated Automatically?

Object Replicated?

INSERT Yes

UPDATE Yes

DELETE Yes

8. To Add More Tables Later

On prod-1:

ALTER PUBLICATION pub_hr ADD TABLE some_table;

On prod-2:

ALTER SUBSCRIPTION sub_hr REFRESH PUBLICATION;

9. To Remove the Subscription

On analytics-prod:

DROP SUBSCRIPTION sub_hr;

On prod-1:

DROP PUBLICATION pub_hr;