All geek questions in one place

Hold PostgreSQL (FDW) in the external circuit in sync

Using Postgres 9.6 with the extension postgres_fdw , are there any means of storing tables present in the local schema in synchronization with tables in the remote database? I often need to add new tables to a wrapped schema in a remote database and access them locally through FDW without having to drop and re-import my external schema or individual tables when they come / go.

I am looking for a command like REFRESH FOREIGN SCHEMA schema_name .

+9
source share
2 answers

I do not think there is an update, but the drop and import should take less than a second:

 DROP SCHEMA IF EXISTS local_schema_name CASCADE; CREATE SCHEMA local_schema_name ; IMPORT FOREIGN SCHEMA foreign_schema_name FROM SERVER foreign_server_name INTO local_schema_name ;
+2
source

Deleting and re-creating definitely works, but I don’t like it, since I often have views that depend on my local tables (which refer to an external schema), so deleting the schema will also delete all the views. To get around this, you can re-import the external schema, but restrict it to only the new tables you created:

 IMPORT FOREIGN SCHEMA <foreign_schema> LIMIT TO (<new_table1>, <new_table2>) FROM SERVER <foreign_server> INTO <local_schema>;
0
source

Source: https://habr.com/ru/post/1271330/

More articles:


All Articles