如何在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.

有人有想法吗?在使用它们之前，我们是否必须激活"数据库链接或执行某些操作?

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

我们要查询的远程数据库上有什么事情要做吗?我们也必须激活dblink吗?我一直在使用could not establish connection.这是该行的类型:

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 9.1的软件包 postgresql-contrib-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屋！