I understand that using "postgres_fdw" we will be able to fetch the DB 'A' from another DB 'B', but this is applicable to tables available before creating fdw and for tables created after fdw in DB 'A' are not reflected in 'B' unless manually importing the foreign schema to table. Is there any way whenever a new tables/functions/sequences are created they should be automatically imported or something like 'REFERESH SERVER A/B'?
WhatIf the app database changes - if you add a table, or drop a column. Your foreign tables will need updating too, What is the way to do it?
Well, There is no way apart from running IMPORT FOREIGN SCHEMA.
However, for a workaround, You can create a migration script or function to do it.
Rebuilding the server config and foreign tables every time you migrate keeps your server up-to-date.
Note: Wrapping the whole thing [Function] in a transaction ensures queries against the foreign tables that are run while you’re running the function's task will be blocked until the task finishes, but won’t fail.