Keep PostgreSQL (FDW) Foreign Schema In-Sync

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>;

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 .

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 ;