0

Is there any technology to replicate a table from public schema on the master server to a table on the slave server but not in the public schema.

MASTER SERVER                              SLAVE SERVER
pubic.table_name  ----- replicate ----->   schema1.table_name

I tried slony-I but the problem that the table on the slave must in the public schema too.

2
  • Use a foreign data wrapper
    – user1822
    Commented Mar 7, 2017 at 15:35
  • i'm reading about it but i think you gave me what i needed you can put it as an answer and some link for usefull reading.
    – Charif DZ
    Commented Mar 8, 2017 at 7:20

1 Answer 1

1

At least exists three way to do this:

1. REPLICATION + VIEW / FDW

You can create ON MASTER VIEW or FDW for table and VIEW / FDW will be replicate to slave.

Something like :

CREATE VIEW schema1.table_name AS SELECT * FROM public.table_name;

Or

CREATE SERVER srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'db', host 'localhost');
CREATE USER MAPPING FOR postgres SERVER dc OPTIONS (password 'pwd', "user" 'postgres');
CREATE FOREIGN TABLE schema1.table_name (..table description..) SERVER srv OPTIONS (schema_name 'public', table_name 'table_name');

2. TRIGGER + SAVE TO REMOTE

You can create trigger on public.table_name on master to write changes to remote db via dblink or FDW.

3. LOGICAL REPLICATION

The most hard path as for me. You can write your own decoder and/or add trigger to write also/instead data to schema1.table_name

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.