PostGreSQL 12 : Logical Replication - Specific Schemas

Question

0

I've two database servers : Host 1 & Host 2

On the Host 1, I've several schemas in the database :

SchemaA SchemaB SchemaC etc... I would like set in place a logical replication from Host 1 to Host 2 for only Schema A and Schema B

I've modified the postgresql.conf on the Host 1

############# REPLICATION #############

listen_addresses = '*'

max_worker_processes = 8 # (change requires restart)

wal_level = logical # minimal, replica, or logical

synchronous_commit = on # synchronization level;

max_wal_senders = 10 # max number of walsender processes

synchronous_standby_names = '*' # standby servers that provide sync rep

max_logical_replication_workers = 4 # taken from max_worker_processes

max_sync_workers_per_subscription = 2 # taken from max_logical_replication_workers

I've created the user/role replica_usr on Host 1 & Host 2

I've modified the pg_hba.conf on each host :

host replication replica_usr X.X.X.X/28 md5 
host replication replica_usr Y.Y.Y.Y/28 md5

I've created the publication on Host 1

CREATE PUBLICATION schemaA_publication;

I've add the table of the schema :

ALTER PUBLICATION SchemaA_publication ADD TABLE "SchemaA"."Table1";
ALTER PUBLICATION SchemaA_publication ADD TABLE "SchemaA"."Table2";
etc...

CREATE PUBLICATION schemaB_publication;

I've add the table of the schema :

ALTER PUBLICATION SchemaA_publication ADD TABLE "SchemaB"."Table1";
ALTER PUBLICATION SchemaA_publication ADD TABLE "SchemaB"."Table2";
etc...

An important point is the Host 2 contains local schema

So my questions are below :

• How can i initiate the replication ?

• Should I set a pgdump for the schema A & B from Host 1 to Host 2 ?

• At which moment should I create the subscription on Host 2 ?

CREATE SUBSCRIPTION subscription_schemaA CONNECTION 'host=X.X.X.X port=ZZZZ password=password user=replica_usr dbname=database' PUBLICATION schemaA_publication;

• Which rights needs the user/role on database/schema to prevent access error ?

Thank a lot for your help