We need to replicate an oracle table into a postgresql database, now we are doing that with etl jobs and programatically but the frequence of the updates are to high for this approach. I was thinking to change the strategy doing:

1) build a fdw connecting this external oracle table to the postgresql db target server

2) setup a postgresql native ( streaming or logical, whichever works ) replication pointing to this fdw oracle connection.

Is that possible?

  • 1
    With "native replication" are you referring to streaming replication or logical replication? But I don't think either of them will replicate foreign tables as they only replicate data stored in Postgres and the data of a foreign table is stored in the foreign (remote) server. But why do you think you need to replicate the table? Is the access through the FDW too slow? – a_horse_with_no_name Sep 12 '18 at 6:37
  • the goal of this project is to remove part of the job load form oracle to postgresql, to allow people to access to this pg replica as read only for reportings, etc. I am assuming with just a fdw we not reduce the work load of oracle because is just an abstraction connection layer to access to REAL oracle connection from pg.. – jacktrade Sep 12 '18 at 7:15
  • 1
    Replication is used to replicate data from on Postgres server to another Postgres Server. It is not intended to "replicate" data inside one Postgres server. You could create a materialized view based on the foreign table - but it depends on your requirement how up-to-date the data in that table has to be and how quick a refresh of the mview would be. – a_horse_with_no_name Sep 12 '18 at 7:19

Your Answer

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

Browse other questions tagged or ask your own question.