如何在 PostgreSQL 中使用(安装)dblink? [英] How to use (install) dblink in PostgreSQL?

问题描述

我习惯于使用 Oracle 并在我的架构中创建一个 dblink，然后像这样访问远程数据库:mytable@myremotedb，无论如何对 PostgreSQL 做同样的事情吗?

I am used to Oracle and to create a dblink in my schema and then access to a remote database like this: mytable@myremotedb, is there anyway do to the same with PostgreSQL?

现在我正在使用这样的 dblink:

Right now I am using dblink like this:

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

执行此命令时出现以下错误:

When I execute this command I get the following error:

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

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

有人有想法吗?我们是否必须激活"dblinks 或在使用它们之前做些什么?

Does anybody have an idea ? Do we have to "activate" dblinks or do something before using them?

我们要查询的远程数据库有什么事情要做吗?我们也必须激活dblink吗?我一直遇到无法建立连接.这是类型的行:

Is there something to do on the remote database we are going to query? Do we have to activate dblink too? I keep having a could not establish connection. This is the line is type:

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

IP 地址正确并且 Postgres 正在远程服务器上运行.有什么想法吗?

IP Address is correct and Postgres is running on the remote server. Any idea?

推荐答案

自 PostgreSQL 9.1 起，附加模块的安装很简单.注册的扩展，如 dblink 可以用 创建扩展:

Since PostgreSQL 9.1, installation of additional modules is simple. Registered extensions like dblink can be installed with CREATE EXTENSION:

CREATE EXTENSION dblink;

安装到您的默认架构中，默认为 public.在运行命令之前，请确保您的 search_path 设置正确.架构必须对所有必须使用它的角色可见.见:

Installs into your default schema, which is public by default. Make sure your search_path is set properly before you run the command. The schema must be visible to all roles who have to work with it. See:

• 如何search_path 是否影响标识符解析和当前模式"

或者，您可以通过以下方式安装到您选择的任何架构:

Alternatively, you can install to any schema of your choice with:

CREATE EXTENSION dblink SCHEMA extensions;

见:

• 最佳如何在 Postgres 数据库中的多个模式上安装 hstore?

每个数据库运行一次.或者在标准系统数据库template1中运行，自动将其添加到每个新创建的数据库中.手册中的详细信息.

Run once per database. Or run it in the standard system database template1 to add it to every newly created DB automatically. Details in the manual.

您需要先在服务器上安装提供模块的文件.对于 Debian 和衍生产品，这将是包 postgresql-contrib-9.1 - 对于 PostgreSQL 9.1，明显地.从 Postgres 10 开始，只有一个 postgresql-contrib 元包.

You need to have the files providing the module installed on the server first. For Debian and derivatives this would be the package postgresql-contrib-9.1 - for PostgreSQL 9.1, obviously. Since Postgres 10, there is just a postgresql-contrib metapackage.

这篇关于如何在 PostgreSQL 中使用(安装)dblink?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！