关于sql:可以使用PostgreSQL执行跨数据库查询吗?

关于sql:可以使用PostgreSQL执行跨数据库查询吗?

Possible to perform cross-database queries with PostgreSQL?

我将基于以下错误消息(以及此Google结果)猜测答案为"否",但是是否仍然可以使用PostgreSQL执行跨数据库查询?

1
2
3
databaseA=# SELECT * FROM databaseB.public.someTableName;
ERROR:  cross-DATABASE REFERENCES are NOT implemented:
"databaseB.public.someTableName"

我正在处理一些跨两个数据库分区的数据,尽管数据实际上是在两个数据库之间共享的(一个数据库中的userid列来自另一个数据库中的users表)。 我不知道为什么这些是两个独立的数据库而不是模式,但是请放心...


注意:就像原始的问询者所暗示的那样,如果您要在同一台计算机上设置两个数据库,则可能要创建两个模式-在这种情况下,您不需要任何特殊的查询即可在它们之间进行查询。

从9.3开始更新

现在,您可以使用新的postgres_fdw(外部数据包装器)连接到任何Postgres数据库(本地或远程)中的表。

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

9.3版之前的原始答案

该功能不是默认PostgreSQL安装的一部分,但是您可以添加它。它称为dblink

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


在得出关于跨数据库查询的相同结论之前,我已经碰到了这一点。我最终要做的是使用模式划分表空间,这样我可以将表分组,但仍然可以查询所有表。


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.

很好的例子之一:

1
2
3
4
5
6
7
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;

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


只需添加更多信息。

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常见问题


是的,您可以使用DBlink(仅适用于postgresql)和DBI-Link(允许使用外部跨数据库查询器)和TDS_LInk,从而允许针对MS SQL Server运行查询。

我曾经使用DB-Link和TDS-link取得了巨大的成功。


如果性能很重要,并且大多数查询都是只读的,则建议将数据复制到另一个数据库。尽管这似乎是不必要的数据重复,但如果需要索引可能会有所帮助。

这可以通过简单的插入触发器来完成,该触发器依次调用dblink以更新另一个副本。也有成熟的复制选项(例如Slony),但这是不合时宜的。


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

阅读了其他人对此的反馈,例如在这里和这里以及在其他一些来源中,看来目前尚无办法:

dblink和postgres_fdw实际上使人们能够连接和查询其他数据库中的表,这是标准Postgres所无法实现的,但是它们不允许在不同数据库中的表之间建立外键关系。


如果有人需要更多有关如何进行跨数据库查询的示例,下面的示例将清理每个具有该数据库的数据库上的databasechangeloglock表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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
$$

推荐阅读