How to re-create or update schema on Postgresql Foreign Data Wrappers?

Question

I had two postgresql database server, each running on separate physical server. One is running version 13.3 (say server A) and the other 9.6 (say server B).

I have installed postgresql's foreign data wrapper on server A connected to sever B, and imported schema from server B (say b_adempiere).

As I work with server B (add/delete field inside view), Today I noticed that the view inside imported schema on server A, doesn't get updated with schema on server B. I have to delete imported schema on server A, and re-import schema from server B.

DROP SCHEMA b_adempiere CASCADE;
CREATE SCHEMA b_adempiere;
IMPORT FOREIGN SCHEMA adempiere FROM SERVER b_server INTO b_adempiere;

Question: How to automatically re-create/update imported schema on server A, each time we make structure database update on server B ?

Thank you.

Answer 1

There is no way to do this automatically. Either make sure you also ALTER the foreign table whenever you ALTER the underlying table or view, or regularly drop and re-import the foreign tables.

Answer 2

Partial solution which might work for your case (it works for mine): https://stackoverflow.com/a/72746177/1216939.

I made it into a function for future use: https://gist.github.com/KazimirPodolski/37a417f9fbf526b0acd11b027545daae

CREATE OR REPLACE PROCEDURE fleet.refresh_fdw_schema(
 server name,
 foreign_schema name,
 target_schema name,
 temp_schema name
)
 LANGUAGE plpgsql AS
$$
DECLARE
 view_definition text;
 view_name text;
 view_schema text;
BEGIN
 EXECUTE format($sub$
 CREATE SCHEMA IF NOT EXISTS %2$I;
 IMPORT FOREIGN SCHEMA %1$I
 FROM SERVER %3$I
 INTO %2$I
 $sub,ドル foreign_schema, temp_schema, server);
 FOR view_definition, view_name, view_schema IN
 SELECT 'CREATE OR REPLACE VIEW ' || schemaname || '.' || viewname || ' AS ' ||
 replace(definition, target_schema || '.', temp_schema || '.'),
 viewname,
 schemaname
 FROM pg_views
 WHERE definition LIKE '%' || target_schema || '.%'
 LOOP
 RAISE NOTICE 'Transferring view %.%', view_schema, view_name;
 EXECUTE (view_definition);
 END LOOP;
 EXECUTE format($sub$
 DROP SCHEMA %1$I CASCADE;
 ALTER SCHEMA %2$I RENAME TO %1$I;
 $sub,ドル target_schema, temp_schema);
END;
$$;