postgresql 连接来自两个独立数据库的结果

Joining Results from Two Separate Databases

提问by sennin

Is it possible to JOINrows from two separate postgres databases?

是否可以JOIN从两个单独的 postgres 数据库中行？

I am working with system with couple databases in one server and sometimes I really need such a feature.

我正在使用一台服务器上有几个数据库的系统，有时我真的需要这样的功能。

采纳答案by ndtreviv

According to http://wiki.postgresql.org/wiki/FAQ

根据http://wiki.postgresql.org/wiki/FAQ

There is no way to query a database other than the current one. Because PostgreSQL loads database-specific system catalogs, it is uncertain how a cross-database query should even behave. contrib/dblink allows cross-database queries using function calls. Of course, a client can also make simultaneous connections to different databases and merge the results on the client side.

除了当前数据库之外，没有其他方法可以查询数据库。由于 PostgreSQL 加载特定于数据库的系统目录，因此跨数据库查询应该如何表现还不确定。contrib/dblink 允许使用函数调用进行跨数据库查询。当然，客户端也可以同时连接不同的数据库，并在客户端合并结果。

EDIT: 3 years later (march 2014), this FAQ entry has been revised and is more helpful:

编辑：3 年后（2014 年 3 月），这个 FAQ 条目已经过修订并且更有帮助：

How do I perform queries using multiple databases?

There is no way to directly query a database other than the current one. Because PostgreSQL loads database-specific system catalogs, it is uncertain how a cross-database query should even behave.

The SQL/MED support in PostgreSQL allows a "foreign data wrapper" to be created, linking tables in a remote database to the local database. The remote database might be another database on the same PostgreSQL instance, or a database half way around the world, it doesn't matter. postgres_fdw is built-in to PostgreSQL 9.3 and includes read/write support; a read-only version for 9.2 can be compiled and installed as a contrib module.

contrib/dblink allows cross-database queries using function calls and is available for much older PostgreSQL versions. Unlike postgres_fdw it can't "push down" conditions to the remote server, so it'll often land up fetching a lot more data than you need.

Of course, a client can also make simultaneous connections to different databases and merge the results on the client side.

如何使用多个数据库执行查询？

没有办法直接查询当前数据库以外的数据库。由于 PostgreSQL 加载特定于数据库的系统目录，因此跨数据库查询应该如何表现还不确定。

PostgreSQL 中的 SQL/MED 支持允许创建“外部数据包装器”，将远程数据库中的表链接到本地​​数据库。远程数据库可能是同一个 PostgreSQL 实例上的另一个数据库，或者是地球另一端的数据库，这无关紧要。postgres_fdw 内置于 PostgreSQL 9.3 并包括读/写支持；9.2 的只读版本可以作为 contrib 模块编译和安装。

contrib/dblink 允许使用函数调用进行跨数据库查询，并且可用于更旧的 PostgreSQL 版本。与 postgres_fdw 不同，它不能将条件“下推”到远程服务器，因此它通常会获取比您需要的更多的数据。

当然，客户端也可以同时连接不同的数据库，并在客户端合并结果。

回答by Luiz Vaz

Forget about dblink!

忘记 dblink！

Say hello to Postgres_FDW:

向Postgres_FDW问好：

To prepare for remote access using postgres_fdw:

1. Install the postgres_fdwextension using CREATE EXTENSION.

2. Create a foreign server object, using CREATE SERVER, to represent each remote database you want to connect to. Specify connection information, except user, and password, as options of the server object.

3. Create a user mapping, using CREATE USER MAPPING, for each database user you want to allow to access each foreign server. Specify the remote user name and password to use as user and password options of the user mapping.

4. Create a foreign table, using CREATE FOREIGN TABLEor IMPORT FOREIGN SCHEMA, for each remote table you want to access. The columns of the foreign table must match the referenced remote table. You can, however, use table and/or column names different from the remote table's, if you specify the correct remote names as options of the foreign table object.

Now you need only SELECTfrom a foreign table to access the data stored in its underlying remote table.

使用postgres_fdw以下方法准备远程访问：

1. 安装postgres_fdw使用延长CREATE EXTENSION。

2. 创建一个外部服务器对象，使用CREATE SERVER，来表示您想要连接的每个远程数据库。将连接信息（用户和密码除外）指定为服务器对象的选项。

3. 使用CREATE USER MAPPING为要允许访问每个外部服务器的每个数据库用户创建用户映射。指定远程用户名和密码以用作用户映射的用户和密码选项。

4. 使用CREATE FOREIGN TABLE或IMPORT FOREIGN SCHEMA为要访问的每个远程表创建一个外部表。外部表的列必须与引用的远程表匹配。但是，如果您指定正确的远程名称作为外部表对象的选项，则可以使用与远程表不同的表和/或列名称。

现在您只需SELECT要从一个外部表访问存储在其底层远程表中的数据。

It's really useful even on large data.

即使在大数据上它也非常有用。

回答by Elliot B.

Yes, it is possible to do this using dblinkalbeit with significant performance considerations.

是的，dblink尽管有重要的性能考虑，但可以使用它来做到这一点。

The following example will require the current SQL user to have permissions on both databases. If db2is not located on the same cluster, then you will need to replace dbname=db2with the full connection string defined in the dblink documentation.

以下示例将要求当前 SQL 用户对两个数据库都具有权限。如果db2不在同一个集群上，那么您将需要替换dbname=db2为dblink 文档中定义的完整连接字符串。

SELECT * 
FROM   table1 tb1 
LEFT   JOIN (
   SELECT *
   FROM   dblink('dbname=db2','SELECT id, code FROM table2')
   AS     tb2(id int, code text);
) AS tb2 ON tb2.column = tb1.column;

If table2is very large, you could have performance issues because the sub-query loads up the entire table2before performing the join.

如果table2非常大，您可能会遇到性能问题，因为子查询table2在执行联接之前加载了整个查询。

回答by Frank Heikens

No you can't. You could use dblinkto connect from one database to another database, but that won't help if you're looking for JOIN's.

不，你不能。您可以使用dblink从一个数据库连接到另一个数据库，但如果您正在寻找 JOIN，这将无济于事。

You can't use different SCHEMA's within a single database to store all you data?

您不能在单个数据库中使用不同的 SCHEMA 来存储所有数据吗？

回答by kenyee

You need to use dblink...as araqnid mentioned above, something like this works fine:

您需要使用 dblink...作为 araqnid 上面提到的，这样的工作正常：

select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, * from information_schema.Columns ST full outer join dblink('dbname=otherdatabase','select Table_Name, Column_Name from information_schema.Columns') DV(Table_Name text, Column_Name text) on ST.Table_Name = DV.Table_name and ST.Column_Name = DV.Column_Name where ST.Column_Name is null or DV.Column_Name is NULL

select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, * from information_schema.Columns ST 全外连接 dblink('dbname=otherdatabase','select Table_Name, Column_Name from information_schema.Columns') DV(Table_Name text, ST.Table_Name = DV.Table_name 和 ST.Column_Name = DV.Column_Name 上的 Column_Name 文本），其中 ST.Column_Name 为 null 或 DV.Column_Name 为 NULL

回答by Ipsita Upadhyay

Just a few steps and You can reach the goal: follow this reference step by step

只需几步，您就可以达到目标： 按照这个参考一步一步来

WE HAVE BEEN CONNECTED TO DB2 WITH TABLE TBL2 AND COLUMN COL2
ALSO THERE IS DB1 WITH TBL1 AND COLUMN COL1

 *** connecting to second db ie db2
    Now just **copy paste the 1-7 processes** (make sure u use correct username and password and ofcourse db name)

    1.**CREATE EXTENSION dblink;**

    2.**SELECT pg_namespace.nspname, pg_proc.proname 
    FROM pg_proc, pg_namespace 
    WHERE pg_proc.pronamespace=pg_namespace.oid 
       AND pg_proc.proname LIKE '%dblink%';**

    3.**SELECT dblink_connect('host=localhost user=postgres password=postgres dbname=db1');**

    4.**CREATE FOREIGN DATA WRAPPER postgres VALIDATOR postgresql_fdw_validator;**

    5.**CREATE SERVER postgres2 FOREIGN DATA WRAPPER postgres OPTIONS (hostaddr '127.0.0.1', dbname 'db1');**

    6.**CREATE USER MAPPING FOR postgres SERVER postgres2 OPTIONS (user 'postgres', password 'postgres');**

    7.**SELECT dblink_connect('postgres2');**

    ---Now, you can SELECT the data of Database_One from Database_Two and even join both db results:

    **SELECT * FROM public.dblink
    ('postgres2','SELECT col1,um_name FROM public.tbl1 ') 
    AS DATA(um_userid INTEGER),tbl2 where DATA.col1=tbl2.col2;**


You can also Check this :[How to join two tables of different databases together in postgresql [\[working finely in version 9.4\]][1]

回答by Anvesh

You have use dblink extension of postgresql.

您已经使用了 postgresql 的 dblink 扩展。

Reference take from this Article:

参考本文：

DbLink extension of PostgreSQL which is used to connect one database to another database.

PostgreSQL 的 DbLink 扩展，用于将一个数据库连接到另一个数据库。

Install DbLink extension.

安装 DbLink 扩展。

CREATE EXTENSION dblink;

Verify DbLink:

验证 DbLink：

SELECT pg_namespace.nspname, pg_proc.proname 
FROM pg_proc, pg_namespace 
WHERE pg_proc.pronamespace=pg_namespace.oid 
   AND pg_proc.proname LIKE '%dblink%';

I have already prepared full demonstration on this. Please visit my post to learn step by step for executing cross database query in Postgresql.

我已经准备好了完整的演示。请访问我的帖子以逐步了解在 Postgresql 中执行跨数据库查询的步骤。