具有第二个数据库的PostgreSQL FOREIGN KEY [英] PostgreSQL FOREIGN KEY with second database

问题描述

我正在PostgreSQL 9.3上运行以下查询：

I'm running the following queries on PostgreSQL 9.3:

CREATE TABLE "app_item" 
  ( 
     "id"          SERIAL NOT NULL PRIMARY KEY, 
     "location_id" UUID NOT NULL 
  ); 

CREATE INDEX app_item_e274a5da 
  ON "app_item" ("location_id"); 

ALTER TABLE "app_item" 
  ADD CONSTRAINT app_item_location_id_5cecc1c0b46e12e2_fk_fias_addrobj_aoguid 
  FOREIGN KEY ("location_id") REFERENCES "fias_addrobj" ("aoguid") deferrable 
  initially deferred;

第三次查询返回：

错误：不存在关系 fias_addrobj

ERROR: relation "fias_addrobj" does not exist

• app_item -第一个数据库中的表


• fias_addrobj -第二个数据库中的表


• app_item - table in first database
• fias_addrobj - table in second database

如何对此数据库进行正确查询？

How to do correct query with this databases?

推荐答案

我没有机会亲自使用此功能，但您可能想研究外国数据包装器，它实际上是 dblink 的后继产品。尤其是 postgres-fdw 。

I've not had occasion to use this myself, but you might want to look into Foreign Data Wrappers, which are essentially the successor to dblink. In particular, postgres-fdw.

一旦完成了 fdw 的常规设置（上面链接中的第1-3步），您就可以创建外部表通过 CREATE FOREIGN TABLE （类似于远程数据库中的表进行定义），然后将该表用作外键 CONSTRAINT ，并查看其是否有效。

Once the general setup of the fdw is in place (steps 1-3 in the link above), you could create a foreign table via CREATE FOREIGN TABLE, defined like the table in your remote DB, and then use that table as part of the foreign key CONSTRAINT, and see if it works.

如果这不起作用，另一种选择是让一个过程使用ETL作为数据（例如，通过 Python 脚本）从远程服务器到本地服务器（例如，每小时或每天一次，具体取决于大小），然后您将拥有一个真正的本地表在外键 CONSTRAINT 中使用。它不是实时的，但是根据您的需要，可能就足够了。

If that doesn't work, another option would be to have a process which ETL's the data (say, via a Python script) from the remote server over to the local server (say, on an hourly or daily basis, depending on the size), and then you would have a true local table to use in the foreign key CONSTRAINT. It wouldn't be real-time, but depending on your needs, may suffice.

这篇关于具有第二个数据库的PostgreSQL FOREIGN KEY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！