SQL 可以使用 PostgreSQL 执行跨数据库查询吗？

Possible to perform cross-database queries with PostgreSQL?

提问by matt b

I'm going to guess that the answer is "no" based on the below error message (and this Google result), but is there anyway to perform a cross-database query using PostgreSQL?

根据以下错误消息（以及此 Google 结果），我将猜测答案是否定的，但是是否可以使用 PostgreSQL 执行跨数据库查询？

databaseA=# select * from databaseB.public.someTableName;
ERROR:  cross-database references are not implemented:
 "databaseB.public.someTableName"

I'm working with some data that is partitioned across two databases although data is really shared between the two (userid columns in one database come from the userstable in the other database). I have no idea why these are two separate databases instead of schema, but c'est la vie...

我正在处理一些跨两个数据库分区的数据，尽管数据确实在两者之间共享（一个数据库中的用户 ID 列来自另一个数据库中的users表）。我不知道为什么这些是两个单独的数据库而不是模式，但是 c'est la vie ...

采纳答案by Neall

Note: As the original asker implied, if you are setting up two databases on the same machine you probably want to make two schemasinstead - in that case you don't need anything special to query across them.

注意：正如最初的提问者暗示的那样，如果您在同一台机器上设置两个数据库，您可能想要创建两个模式- 在这种情况下，您不需要任何特殊的东西来查询它们。

postgres_fdw

postgres_fdw

Use postgres_fdw(foreign data wrapper) to connect to tables in any Postgres database - local or remote.

使用postgres_fdw（外部数据包装器）连接到任何 Postgres 数据库中的表 - 本地或远程。

Note that there are foreign data wrappers for other popular data sources. At this time, only postgres_fdwand file_fdware part of the official Postgres distribution.

请注意，有其他流行数据源的外部数据包装器。目前，只有postgres_fdw和file_fdw是官方 Postgres 发行版的一部分。

For Postgres versions before 9.3

对于 9.3 之前的 Postgres 版本

Versions this old are no longer supported, but if you need to do this in a pre-2013 Postgres installation, there is a function called dblink.

不再支持这么旧的版本，但如果您需要在 2013 年之前的 Postgres 安装中执行此操作，则有一个名为dblink.

I've never used it, but it is maintained and distributed with the rest of PostgreSQL. If you're using the version of PostgreSQL that came with your Linux distro, you might need to install a package called postgresql-contrib.

我从未使用过它，但它与 PostgreSQL 的其余部分一起维护和分发。如果您使用的是 Linux 发行版附带的 PostgreSQL 版本，则可能需要安装一个名为 postgresql-contrib 的包。

回答by Manwal

dblink()-- executes a query in a remote database

dblink()——在远程数据库中执行查询

dblink executes a query (usually a SELECT, but it can be any SQL statement that returns rows) in a remote database.

When two text arguments are given, the first one is first looked up as a persistent connection's name; if found, the command is executed on that connection. If not found, the first argument is treated as a connection info string as for dblink_connect, and the indicated connection is made just for the duration of this command.

dblink 在远程数据库中执行查询（通常是 SELECT，但它可以是任何返回行的 SQL 语句）。

当给出两个文本参数时，第一个作为持久连接的名称首先被查找；如果找到，则在该连接上执行该命令。如果未找到，则第一个参数将被视为与 dblink_connect 一样的连接信息字符串，并且仅在此命令的持续时间内建立所指示的连接。

one of the good example:

一个很好的例子：

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;

Note: I am giving this information for future reference. Refrence

注意：我提供此信息以供将来参考。参考

回答by stimms

I have run into this before an came to the same conclusion about cross database queries as you. What I ended up doing was using schemas to divide the table space that way I could keep the tables grouped but still query them all.

在得出与您相同的关于跨数据库查询的结论之前，我已经遇到过这个问题。我最终做的是使用模式来划分表空间，这样我就可以将表分组但仍然查询它们。

回答by Esteban Küber

_{Just to add a bit more information.}

_{只是为了添加更多信息。}

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 允许使用函数调用进行跨数据库查询。当然，客户端也可以同时连接不同的数据库，并在客户端合并结果。

PostgreSQL FAQ

PostgreSQL 常见问题

回答by Esteban Küber

Yes, you can by using DBlink (postgresql only) and DBI-Link (allows foreign cross database queriers) and TDS_LInk which allows queries to be run against MS SQL server.

是的，您可以使用 DBlink（仅限 postgresql）和 DBI-Link（允许外部跨数据库查询器）和 TDS_LInk，它允许对 MS SQL 服务器运行查询。

I have used DB-Link and TDS-link before with great success.

我之前使用过 DB-Link 和 TDS-link 并取得了巨大成功。

回答by Haroldo_OK

In case someone needs a more involved example on how to do cross-database queries, here's an example that cleans up the databasechangeloglocktable on every database that has it:

如果有人需要有关如何进行跨数据库查询的更复杂的示例，这里有一个示例，用于清理databasechangeloglock每个拥有它的数据库上的表：

CREATE EXTENSION IF NOT EXISTS dblink;

DO 
$$
DECLARE database_name TEXT;
DECLARE conn_template TEXT;
DECLARE conn_string TEXT;
DECLARE table_exists Boolean;
BEGIN
    conn_template = 'user=myuser password=mypass dbname=';

    FOR database_name IN
        SELECT datname FROM pg_database
        WHERE datistemplate = false
    LOOP
        conn_string = conn_template || database_name;

        table_exists = (select table_exists_ from dblink(conn_string, '(select Count(*) > 0 from information_schema.tables where table_name = ''databasechangeloglock'')') as (table_exists_ Boolean));
        IF table_exists THEN
            perform dblink_exec(conn_string, 'delete from databasechangeloglock');
        END IF;     
    END LOOP;

END
$$

回答by dpavlin

If performance is important and most queries are read-only, I would suggest to replicate data over to another database. While this seems like unneeded duplication of data, it might help if indexes are required.

如果性能很重要并且大多数查询都是只读的，我建议将数据复制到另一个数据库。虽然这看起来像是不必要的重复数据，但如果需要索引，它可能会有所帮助。

This can be done with simple on insert triggers which in turn call dblink to update another copy. There are also full-blown replication options (like Slony) but that's off-topic.

这可以通过简单的插入触发器来完成，然后调用 dblink 来更新另一个副本。还有成熟的复制选项（如 Slony），但这是题外话。

回答by Rocckk

I have checked and tried to create a foreign key relationships between 2 tables in 2 different databases using both dblinkand postgres_fdwbut with no result.

我已经检查并尝试使用dblink和postgres_fdw在 2 个不同数据库中的 2 个表之间创建外键关系，但没有结果。

Having read the other peoples feedback on this, for example hereand hereand in some other sources it looks like there is no way to do that currently:

阅读了其他人对此的反馈，例如这里和这里以及其他一些来源，目前似乎没有办法做到这一点：

The dblinkand postgres_fdwindeed enable one to connect to and query tables in other databases, which is not possible with the standard Postgres, but they do not allow to establish foreign key relationships between tables in different databases.

该DBLINK和postgres_fdw确实使人们能够连接其他数据库中，这是不可能与标准的Postgres和查询表，但他们不允许建立在不同的数据库表之间的外键关系。