PostgreSQL跨库查询-dblink 模块应用

概述

dblink 是Postgresql的一个模块，支持从数据库会话中连接到其他数据库。

安装 dblink

alert user postgres with superuser;
\c template1
复制

CREATE EXTENSION dblink; 
select * from pg_available_extensions;
复制

\c postgres   *abc为当前表

复制

CREATE EXTENSION dblink; 

复制

登录数据库，在Postgres=#下

CREATE EXTENSION dblink; 
复制

本地跨库访问

首先在 Postgresql中建立 userinfo 表，随后在本地新建一个 localdb 数据库，并在其中建立一个 local_test 数据表。

*建表
CREATE TABLE userinfo (id int primary key, name text);    
*插值
INSERT INTO userinfo VALUES (1, 'Eric'), (2, 'Tom');
*查询一下
SELECT * FROM userinfo;
*建localdb库
CREATE DATABASE localdb;

*连接库
\c localdb
*建表
CREATE TABLE local_test (id serial primary key, ival int default 0, create_time timestamptz not null default now());
*插值
INSERT INTO local_test(ival) VALUES (1), (2), (3), (4);
*查询一下
SELECT * FROM local_test;
复制

4条记录，OK,我们继续

在 Postgresql数据库中查询 local_test 表，就需要使用到 dblink 来访问了。首先，我们通过 dblink_connect 创建一个连接。

SELECT dblink_connect('local_dblink_test','dbname=localdb hostaddr=127.0.0.1 port=5432 user=postgres password=mypassword');
复制

再通过 dblink 执行查询。

SELECT * FROM dblink('local_dblink_test', 'SELECT * FROM local_test;') AS lt(id int, ival int, create_time timestamptz);
复制

再把返回结果与本库中的表进行联合查询。

SELECT u.id, name, create_time FROM userinfo u JOIN dblink('local_dblink_test', 'SELECT * FROM local_test;') AS lt(id int, ival int, create_time timestamptz) on u.id = lt.id;
复制

可以为dblink创建一个视图。

CREATE VIEW v_localdb_test AS SELECT * FROM dblink('local_dblink_test', 'SELECT * FROM local_test;') AS lt(id int, ival int, create_time timestamptz);

SELECT * FROM v_localdb_test;

SELECT u.id, name, create_time FROM userinfo u JOIN v_localdb_test v ON u.id = v.id;
复制

备注： 在 local_test 表中 id 字段类型为 serial，但是在通过 dblink 查询时返回的结果类型不能使用 serial 类型。

远端跨库访问

远端跨库访问本质也是类似的，在配置 dblink_connect 连接参数时指明远端数据库的地址、端口、用户名和密码等信息。如：

SELECT dblink_connect('remote_dblink_test', 'dbname=remotedb hostaddr=192.168.10.24 port=5432 user=postgres password=mypassword');
复制

操作参考上面的<本地跨库访问>

关闭 dblink

当不需要在使用 dblink 访问外部数据库时，我们需要使用 dblink_disconnect 来关闭连接。首先，我们通过 dblink_get_connections 来查看现有的 dblink 连接，随后将其关闭。

SELECT dblink_get_connections();
SELECT dblink_disconnect('remote_dblink_test');
SELECT dblink_disconnect('local_dblink_test');
复制