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.

  • Use a foreign data wrapper – a_horse_with_no_name Mar 7 '17 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 Mar 8 '17 at 7:20
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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