可以使用PostgreSQL执行跨数据库查询吗? [英] Possible to perform cross-database queries with PostgreSQL?

问题描述

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

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?

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

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

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 users table in the other database). I have no idea why these are two separate databases instead of schema, but c'est la vie...

推荐答案

注意:正如原始要求者所暗示的，如果要在同一台计算机上设置两个数据库，则可能要创建两个

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

使用 postgres_fdw (外部数据包装器)进行连接到任何Postgres数据库(本地或远程)中的表.

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

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

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

不再支持旧版本，但是如果您需要在2013年以前的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.

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

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执行跨数据库查询吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！