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;
