保持PostgreSQL(FDW)外部架构同步

Question

使用带有postgres_fdw扩展的Postgres 9.6，有什么方法可以保持本地模式中的表与远程数据库中的表同步？我经常需要将新表添加到远程数据库上的封装模式中，并希望通过FDW本地访问它们，而不必在外来模式或单个表出现/消失时删除和重新导入它们。

我正在寻找像REFRESH FOREIGN SCHEMA schema_name这样的命令。

Answer 1

我不认为有刷新，但删除和导入应该不到一秒钟：

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

删除并重新创建确实有效，但我不喜欢它，因为我经常有依赖于本地表(引用外部模式(的视图，所以删除模式也会删除所有视图。为了解决这个问题，您可以重新导入外部模式，但仅限于您创建的新表：

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

Answer 3

对于最近的postgres(我使用的是13(，以下操作类似于psql的刷新。引用这些表是为了避免类似SQL关键字的表混淆解析器。

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

应该使用EXECUTE FORMAT而不是选择和字符串串联直接滚动到函数中。

Answer 4

如果没有任何依赖项，最简单的方法是再次DROP和CREATE一个外部表

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

如果您有依赖项(查看或其他(，您可以添加/更改未同步的列。

ADD COLUMN主要用于远程表添加一些新列，而本地表没有更新(默认情况下(。

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

例如：

ALTER FOREIGN TABLE library.book ADD COLUMN co_author text;

如果您想更改当前外部表的类型：

ALTER FOREIGN TABLE <schema_name>.<table_name> ALTER COLUMN <column_name> TYPE <column type>
ALTER FOREIGN TABLE library.book ALTER COLUMN pages TYPE int;

你可以在这里阅读更多Postgres Foreign Table Doc

Answer 5

我发现了一个让你保持远程模式同步的过程，它只解决了视图问题，但这个概念可以应用于其他对象(我想(

一旦导入了模式，并创建了几个使用远程表的本地视图，刷新模式的过程如下：

首先，创建一个新的模式other_schema(这只是临时的(

create schema IF NOT EXISTS other_schema;
IMPORT FOREIGN SCHEMA public
FROM SERVER remote_server INTO other_schema;

现在，我们应该更改所有视图代码，以使用新模式other_schema中的表，将对原始remote_schema的引用从psql替换为我们可以执行的：

SELECT 'CREATE OR REPLACE view '|| viewname || ' AS ' ||
replace(definition, 'remote_schema.', 'other_schema.') view_stmt
from pg_views where definition like '%remote_schema.%' gexec

现在我们已经更新了模式和本地视图，因此我们可以删除过时的模式并重命名新的模式，以保持所有系统在刷新过程之前的状态。

DROP schema remote_schema cascade;
ALTER SCHEMA other_schema RENAME TO remote_schema;

仅此而已。现在，远程模式中的所有内容都已更新，您的视图正在使用它

这种方法有一些注意事项，在短时间内，您将2个类似的模式存储在本地(您需要足够的磁盘来支持这一点(，模式的名称应该与任何现有的表或视图不同，最后这只解决了视图的问题，要应用于其他对象，您需要读取正确的目录表/视图并生成SQL命令来更新它。