Postgres: How can we preserve data of foreign table which is creating using foreign data wrapper

Question

I am trying to migrate Oracle database to Postgres using foreign_database_wrapper by creating foreign tables.

But since the foreign tables acts like a view of Oracle so at the time of executing any query it fetches data on fly from the Original source and hence it increases the processing time.

As of now, in order to maintain physical data at Postgres end, I am creating table and inserting those data in it.

eg: create table employee_details as select * from emp_det;

where employee_details is a physical table and emp_det is a foreign table

But I felt this process is kind of redundant and time to time we need to manipulate this table(new insertion, updation or deletion)

So if anyone could share some related way where I can preserve these data with some other mode.

Regards,

Laurenz Albe · Accepted Answer · 2017-07-28 10:42:07Z

1

See the identical Github issue.

oracle_fdw does not store the Oracle data on the PostgreSQL side. Each access to a foreign table directly accesses the Oracle database.

If you want a copy of the data physically located in the PostgreSQL database, you can either do it like you described, or you could use a materialized view:

CREATE MATERIALIZED VIEW emp_det_mv AS SELECT * FROM emp_det;

That will do the same thing, but simpler. To refresh the data, you can run

REFRESH MATERIALIZED VIEW emp_det_mv;

answered Jul 28, 2017 at 10:42

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pooja Over a year ago

Laurenz Albe , in order to refresh these created MATERIALIZED VIEW I have created job using pgAgent. Job sucessfully got created but while I am running that job it doesn't process the given SQL code. Kindly help. For the same I have created ticket 45670622.

Collectives™ on Stack Overflow

Postgres: How can we preserve data of foreign table which is creating using foreign data wrapper

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related