Setup relicate table Postgres

 

SHOW wal_level;

ALTER SYSTEM SET wal_level = logical;

On Publisher database:

--STEP 1: create publiccation named mypub, for table1,table2, table3
CREATE PUBLICATION mypub FOR TABLE newtable;

--check created publication
SELECT *
FROM pg_catalog.pg_publication

SELECT *
FROM pg_catalog.pg_publication_tables

--STEP 2: for each subscription, need to creating relication slot on publication node, 'pgoutput' is plugin
SELECT pg_create_logical_replication_slot('mysub', 'pgoutput');

--check replication slot
SELECT slot_name, slot_type, active FROM pg_replication_slots;

--in case to manual delete replication slot, run
SELECT * FROM pg_drop_replication_slot('slot_name');

 

On Subscriber database

 

--create subscription

CREATE SUBSCRIPTION mysub
CONNECTION 'host=192.168.56.20 port=5432 user=databaseuser password=passhere dbname=dbtest1'
PUBLICATION mypub
WITH (create_slot=false);

--check all subscription
SELECT *
FROM pg_subscription

-- drop subscription
ALTER SUBSCRIPTION mysub DISABLE;
ALTER SUBSCRIPTION mysub SET (slot_name=NONE);
DROP SUBSCRIPTION mysub