如何在PostgreSQL中使用（安装）dblink？

Question

我习惯使用Oracle，在我的模式中创建一个dblink，然后像这样访问远程数据库：mytable@myremotedb，是否有办法在PostgreSQL中做同样的事情？

现在我正在使用以下方式使用dblink：

SELECT logindate FROM dblink('host=dev.toto.com
                              user=toto
                              password=isok
                              dbname=totofamily', 'SELECT logindate FROM loginlog');

当我执行此命令时，我收到以下错误：

提示：没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换。

有人有什么想法吗？我们需要在使用它们之前“激活”dblinks或者做些其他事情吗？

在我们查询远程数据库时，需要在远程数据库上做些什么吗？我们也需要激活dblink吗？我一直收到“无法建立连接”的错误。这是我键入的行：

SELECT dblink_connect_u('host=x.x.x.x dbname=mydb user=root port=5432');

IP地址正确，Postgres在远程服务器上运行。有任何建议吗？

- Spredzy

Answer 1

自从PostgreSQL 9.1版本开始，安装附加模块变得更加简单。像dblink这样的已注册扩展可以使用CREATE EXTENSION命令进行安装。请参阅：https://www.postgresql.org/docs/current/contrib.html 和 https://www.postgresql.org/docs/current/sql-createextension.html。

CREATE EXTENSION dblink;

安装到您的默认模式中，默认情况下是 public。在运行命令之前，请确保您的 search_path 已正确设置。此模式必须对所有需要使用它的角色可见。请参见：

• 搜索路径如何影响标识符解析和“当前模式”

或者，您可以通过以下方式将其安装到任何所需的模式中:

CREATE EXTENSION dblink SCHEMA extensions;

请见：

• 在Postgres数据库中为多个模式安装hstore的最佳方法是什么？

每个数据库只需运行一次。或者在标准系统数据库template1中运行它，以便自动将其添加到每个新创建的数据库中。详细信息请参见手册。

您需要先在服务器上安装提供该模块的文件。对于Debian和派生产品，这将是包postgresql-contrib-9.1 - 显然是针对PostgreSQL 9.1的。自Postgres 10以来，只有一个postgresql-contrib元包。

- Erwin Brandstetter

---

PostgreSQL并不是那么简单...错误信息"/usr/share/postgresql/9.1/extension/dblink.control"：文件或目录未找到...有没有一个简单的命令可以解决它？ - Peter Krauss

在另一台安装有现代pg9.3（在Debian上）的服务器上也出现了相同的问题。SQL消息为“ERROR: could not open extension control file '/usr/share/postgresql/9.3/extension/dblink.control': No such file or directory”。 - Peter Krauss

@PeterKrauss：您是否按建议安装了 contrib 包？Pg 9.3 不在当前稳定版本（wheezy）中。您可以从 PostgreSQL Apt 存储库 中获取它。 - Erwin Brandstetter

感谢您提供的所有提示和线索，我会成功的...但是，我再次确认PostgreSQL永远不会是即插即用的...如果某些东西“已经简化”，那么它肯定会变得更慢并需要更多的东西，而且其他地方的技术发展速度更快。 - Peter Krauss

为什么Postgres默认没有启用此功能？从SQL Server转过来，我经常进行跨数据库查询，这似乎是一个常见的需求？ - Zach Smith

Answer 2

我正在使用DBLINK连接内部数据库进行跨数据库查询。

参考此文章。

安装DbLink扩展程序。

CREATE EXTENSION 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%';

测试数据库连接：

SELECT dblink_connect('host=localhost user=postgres password=enjoy dbname=postgres');

- Anvesh

Answer 3

在Linux上，找到dblink.sql文件，然后在PostgreSQL控制台中执行以下类似命令以创建所有必需的函数：

\i /usr/share/postgresql/8.4/contrib/dblink.sql 

你可能需要安装contrib包：sudo apt-get install postgresql-contrib

- evgeni

---

使用postgres-contrib-9.3软件包，dblink.sql现在已更名为dblink--1.1.sql。 - Peter Becich

我的文件被发现在/usr/share/pgsql/contrib/目录下。PostgreSQL 9.5运行于CentOS 6系统。 - Davidson Lima

Answer 4

安装模块通常需要运行随数据库安装包提供的SQL脚本。

假设您使用类似Linux的操作系统。

find / -name dblink.sql

验证位置并运行它

- Unreason

Answer 5

可以通过以下方式添加：

$psql -d databaseName -c "CREATE EXTENSION dblink"

- angelous

Answer 6

# or even faster copy paste answer if you have sudo on the host 
sudo su - postgres  -c "psql template1 -c 'CREATE EXTENSION IF NOT EXISTS \"dblink\";'"

- Yordan Georgiev

Answer 7

你可能还会问，dblink 应该安装在哪里？

大多数情况下，推荐将常见的 Postgers 扩展安装在 pg_catalog 模式中。如果你在 pg_catalog 中安装扩展，则所有相关函数都可以在任何模式中使用。如果你在 public 中安装扩展，则在另一个模式中，你也必须定义模式。

例如，如果 dblink 安装在 public 中，则在模式 myschema 中，你必须定义 public 模式。

SELECT public.dblink_get_connections();

例如，如果在 pg_catalog 中安装了 dblink，则在模式 myschema 中，您不需要定义 public 模式：

SELECT dblink_get_connections();

pg_catalog模式中的所有函数都会自动附加到其他模式中。

要在pg_catalog中安装dblink：

CREATE EXTENSION IF NOT EXISTS dblink WITH SCHEMA pg_catalog;

- D.A.H