Keep PostgreSQL (FDW) Foreign Schema In-Sync

Question

Using Postgres 9.6 with postgres_fdw extension, there any means to keep the tables present within the local schema in-sync with the tables on the remote database? I often need to add new tables to the wrapped schema on the remote database and would like to access them locally via FDW without having to drop and re-import my foreign schema or individual tables as they come / go.

I'm looking for a command such as REFRESH FOREIGN SCHEMA schema_name.

Answer 1

I don't think there is a refresh, 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 ;

Answer 2

Dropping and recreating definitely works, but I don't like it as I often have views that are dependent on my local tables (which reference the foreign schema), so dropping the schema will also remove all views. To get around this, you can reimport the foreign schema, but limit it only to the new tables you've created:

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

Answer 3

With a recent postgres (I'm using 13) the following works like a refresh from psql. The tables are quoted to avoid tables that resemble SQL keywords to confuse the parser.

SELECT 'IMPORT FOREIGN SCHEMA <foreign_schema> EXCEPT ('|| 
   (SELECT string_agg('"'||table_name||'"',',') 
   FROM information_schema.tables 
   WHERE table_schema='<local_schema>') ||') 
FROM SERVER <foreign_server> INTO <local_schema>'\gexec

Should be straight forward to roll into a function using EXECUTE FORMAT instead of select and and string concatenation .