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 ste 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