SQL 如何在 PostgreSQL 中使用（安装）dblink？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3862648/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use (install) dblink in PostgreSQL?
提问by Spredzy
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?
我习惯了Oracle和在我的模式中创建一个DBLINK然后进入这样一个远程数据库：mytable@myremotedb，反正是有做与PostgreSQL的一样吗？
Right now I am using dblink like this:
现在我正在使用这样的 dblink：
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?
有人有想法吗？我们是否必须“激活”dblinks 或在使用它们之前做些什么？
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:
我们要查询的远程数据库有什么事情要做吗？我们是否也必须激活 dblink？我一直有一个could not establish connection. 这是行类型：
SELECT dblink_connect_u('host=x.x.x.x dbname=mydb user=root port=5432');
IP Address is correct and Postgres is running on the remote server. Any idea?
IP 地址正确并且 Postgres 正在远程服务器上运行。任何的想法？
回答by Erwin Brandstetter
Since PostgreSQL 9.1, installation of additional modules is simple. Registered extensions like dblinkcan be installed with CREATE EXTENSION:
从PostgreSQL 9.1 开始，附加模块的安装很简单。注册的扩展像dblink可以安装CREATE EXTENSION：
CREATE EXTENSION dblink;
Installs into your default schema, which is publicby default. Make sure your search_pathis set properly before you run the command. The schema must be visible to all roles who have to work with it. See:
安装到您的默认架构中，public默认情况下。search_path在运行命令之前，请确保您的设置正确。架构必须对所有必须使用它的角色可见。看：
Alternatively, you can install to any schema of your choice with:
或者，您可以安装到您选择的任何架构：
CREATE EXTENSION dblink SCHEMA extensions;
See:
看：
Run once per database. Or run it in the standard system database template1to add it to everynewly created DB automatically. Details in the manual.
每个数据库运行一次。或者在标准系统数据库中运行它template1以将其自动添加到每个新创建的数据库中。手册中的详细信息。
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-contribmetapackage.
您需要先在服务器上安装提供模块的文件。对于 Debian 和衍生产品，这将是包postgresql-contrib-9.1- 显然，对于 PostgreSQL 9.1。从 Postgres 10 开始，只有一个postgresql-contrib元包。
回答by Anvesh
I am using DBLINK to connect internal database for cross database queries.
我正在使用 DBLINK 连接内部数据库以进行跨数据库查询。
Reference taken from this article.
Install DbLink extension.
安装 DbLink 扩展。
CREATE EXTENSION dblink;
Verify 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%';
Test connection of database:
测试连接数据库：
SELECT dblink_connect('host=localhost user=postgres password=enjoy dbname=postgres');
回答by evgeni
On linux, find dblink.sql, then execute in the postgresql console something like this to create all required functions:
在 linux 上，找到 dblink.sql，然后在 postgresql 控制台中执行这样的操作来创建所有必需的函数：
\i /usr/share/postgresql/8.4/contrib/dblink.sql
you might need?to install the contrib packages: sudo apt-get install postgresql-contrib
你可能需要？安装 contrib 包： sudo apt-get install postgresql-contrib
回答by Unreason
Installing modules usually requires you to run an sql script that is included with the database installation.
安装模块通常需要您运行包含在数据库安装中的 sql 脚本。
Assuming linux-like OS
假设类似 linux 的操作系统
find / -name dblink.sql
Verify the location and run it
验证位置并运行它
回答by angelous
It can be added by using:
可以使用以下方法添加：
$psql -d databaseName -c "CREATE EXTENSION dblink"
回答by Yordan Georgiev
# 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\";'"
