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 .

Answer 4

If you don't have any dependency, the easiest way to do is to DROP and CREATE a foreign table again

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

In case you have dependency (View or something) you can Add/Alter columns that not have been synced.

ADD COLUMN is mostly used when your remote table adds some new column, and your local table is not updated (by default).

ALTER FOREIGN TABLE <schema_name>.<table_name> ADD COLUMN <column_name> <type of column>

For example:

ALTER FOREIGN TABLE library.book ADD COLUMN co_author text;

In case you want to change the type of current Foreign Table:

ALTER FOREIGN TABLE <schema_name>.<table_name> ALTER COLUMN <column_name> TYPE <column type>

ALTER FOREIGN TABLE library.book ALTER COLUMN pages TYPE int;

You can read more in here Postgres Foreign Table Doc