如何使用PostgreSQL外部数据包装器连接两个不同的PostgreSQL数据库。

Question

能否提供一个使用PostgreSQL中的外部数据包装器（FDW）的示例（涉及各种SQL语句），以启用从PostgreSQL数据库A中的表与来自PostgreSQL数据库B的表进行连接？

从文档中不清楚FDW功能在pgsql 9.0与9.1中可用的程度。文档也没有展示如何使用FDW在两个不同的PostgreSQL数据库之间进行连接（带WHERE限定符下推）的任何示例。

http://www.postgresql.org/docs/9.0/static/sql-createforeigndatawrapper.html

http://www.postgresql.org/docs/9.1/static/ddl-foreign-data.html

http://www.depesz.com/index.php/2011/03/14/waiting-for-9-1-foreign-data-wrapper/

- archmeta

Answer 1

你可以像操作任何表格一样操作它。根据Depesz的帖子：

CREATE FOREIGN TABLE passwd (
    username text,
    pass text,
    uid int4,
    gid int4,
    gecos text,
    home text,
    shell text
) SERVER file_server
OPTIONS (format 'text', filename '/etc/passwd', delimiter ':', null '');

select * from passwd;

这些文档没有给出关于表连接的例子是有原因的：它只是普通的SQL...

目前，连接下推是GSOC的主题：

• http://wiki.postgresql.org/wiki/Enhancing_FDW_functionality_for_PostgreSQL_GSoC2011
• http://nine-chapters.com/?p=5

- Denis de Bernardy

---

您提供的示例是针对“file_server”的。您能否编辑示例以连接到外部postgresql服务器？谢谢。 - archmeta

然后，它将是相同类型的语法，只是使用不同的服务器。请参阅相关文档：http://www.postgresql.org/docs/current/static/sql-createserver.html / CREATE SERVER myserver FOREIGN DATA WRAPPER pgsql OPTIONS (host 'foo', dbname 'foodb', port '5432'); - Denis de Bernardy

我在我的9.1版本中遇到了“ERROR: foreign-data wrapper "pgsql" does not exist”错误，有什么想法吗？谢谢。 - alfonx

Answer 2

我找到的最简单的解决方案是使用dblink扩展程序。我已经在PostgreSQL 9.1上进行了测试:

create extension dblink.
select * from dblink('port=5452 host=localhost dbname=mydb user=myuser password=xxx', 
                     'select id,spaltenname from variablen') as v (a int, b varchar(20));

http://www.postgresql.org/docs/9.1/static/dblink.html

一个简单的联接可以如下实现：

with a as (select * from dblink('port=5452 host=localhost dbname=mydb user=myuser password=xxx', 'select id,spaltenname from variablen') as v (a int, b varchar(20)))
select a join (select 1)b on (true);

上面的示例让您能够连接到另一个postgresql服务器上的表，但它只是复制然后连接。没有自动的“WHERE限定符下推”，正如您所称。当然，在第一条语句中选择需要的行...

- alfonx

Answer 3

如果您想连接两个不同的PostgreSQL数据库，我建议您使用dblink:

select datos.* 
     FROM dblink('hostaddr=192.168.0.10 port=5432 dbname=my_dbname user=my_user password=my_pass'::text, ' 
               select field_1, field_2 
               from my_table  order by field_1
               '
               ::text) 
      datos(field_1, integer, field_2 character varying(10));

我在 PostgreSQL 9.1.3 上测试过了。 http://www.postgresql.org/docs/9.2/static/contrib-dblink-function.html

- jgoge