PostgreSQL中的dblink扩展模块的使用

什么是dblink

    dblink(Database Link)是一个PostgreSQL扩展模块，它允许在数据库之间建立连接并执行远程数据库的操作。该扩展模块提供了一组函数和存储过程，可以方便地在一个数据库会话中连接到其他数据库，并执行查询、插入、更新等操作。

dblink扩展模块的基本使用

1、安装dblink扩展模块

    从 PostgreSQL 9.1 版本开始，dblink 插件已经作为 PostgreSQL 的核心模块之一自带了，所以无需单独下载，只需要执行创建扩展命令即可

CREATE EXTENSION dblink;

    创建扩展成功后，可以去查看数据库中是否安装了该扩展插件，可以执行下面的命令查看，如果查询结果为空，表示当前数据库没有安装dblink插件，如果非空表示成功安装

SELECT * FROM pg_extension WHERE extname = 'dblink';

2、连接到远程数据库

**函数:**dblink_connect — 打开与远程数据库的持久连接

**语法:**dblink_connect(connname text, connstr text) returns text

    该函数接受两个参数:conname和connstr,并返回一个文本值

**介绍:**创建一个新的连接并将其命名为conname,并使用指定的连接字符串connstr连接到一个远程数据库,连接字符串包含了连接到远程数据库所需的信息,如主机名、端口号、数据库名、用户名和密码等。

3、查询远程数据库

**函数:**dblink — 在远程数据库中执行查询

**语法:**dblink(connname text, sql text [, fail_on_error bool]) returns setof record

    该函数主要接受两个参数:connname和sql,用于执行数据库中的SQL查询,并返回结果集

介绍:

• conname表示要使用的连接的名称,即使用dblink_connect函数建立的远程连接中的连接名称;
• sql为要执行的SQL查询语句;
• fail_on_error:默认情况下为True,即未提供参数的情况下,远程连接发生错误会导致本地也抛出错误,如果设置为False,远程错误会以NOTICE形式本地报告,并且函数不返回任何行;
• 在使用dblink扩展执行远程查询时,必须使用AS关键字,因为dblink函数返回的结果是record类型,它可以包含任意数量和类型的列,子句的"别名"部分必须指定函数将返回的列名和类型.

4、关闭远程连接

**函数:**dblink_disconnect — 关闭与远程数据库的持久连接

**语法:**dblink_disconnect([connname text]]) returns text

**介绍:**该函数用来显示的关闭与远程数据库的连接,可以指定connname,也可以不指定，如果不指定参数，则会断开当前数据库会话中所有活动的dblink连接

**NOTE:**该函数用来显示的关闭,如果应用程序结束或数据库会话被中止，则会自动关闭所有连接

dblink扩展模块的实际应用

本地主机中的跨库查询

--查询是否安装了dblink模块
SELECT * FROM pg_extension WHERE extname = 'dblink';
--安装dblink模块
CREATE EXTENSION dblink;
--建立远程连接
SELECT dblink_connect('local_connect','hostaddr=127.0.0.1 port=5432 dbname=DW_model user=postgres password=888888'); 
--解释：'local_connect'是我自定义的连接的名称,hostaddr=127.0.0.1表示是本机地址,port=5432表示使用5432端口,pgsql默认的端口就是5432,自行设置,dbname=DW_model:表示要访问的数据库的名称,user=postgres password=888888 分别表示用户名和密码,根据自己配置的用户名密码更改
--执行完后显示OK即连接建立成功
--跨库查询
SELECT * 
FROM dblink('local_connect','SELECT "姓名" FROM "信息表";') AS
t1("姓名" varchar)
--解释：'local_connect'是远程连接的名称，'SELECT "姓名" FROM "信息表";'里面封装了SQL查询语句,最后使用了AS关键字以及别名来指定返回结果的列名和类型
--关闭连接(两种方法都可以)
SELECT dblink_disconnect('local_connect');
SELECT dblink_disconnect();

远程主机的跨库查询

在进行远程主机的跨库查询之前，需要做以下几个准备工作：

• 测试与远程主机的连通性
• 修改远程主机的pgsql配置文件并重启pgsql服务（如果本地和远程主机都需要对彼此的数据库进行跨库查询则都进行设置）
• 配置本地主机和远程主机的防火墙出站入站规则

代码就不举例了，只需要改一下连接中的IP即可，照上面的本机跨库代码修改即可

1、连通性测试

    打开小黑窗使用ping命令测试与远程主机是否连通

2、修改配置文件

• 找到postgresql的安装目录，进入data文件夹，找到文件postgresql.conf，以记事本的形式打开，设置listen_addresses = ''或者listen_addresses = 'localhost,192.168.7.101'（此处添加IP为允许远程连接本机的IP）。默认情况下为localhost表示只接受来自本地主机的连接；设置为，表示 PostgreSQL 服务器将接受来自任何网络地址的连接请求，为了安全起见也可以新增指定的地址。
• 找到postgresql的安装目录，进入data文件夹，找到文件pg_hba.conf（该文件定义了客户端身份验证规则）以记事本打开，找到#IPv4 local connections:这一条注释，在其下添加远程主机的IP地址，见下图。

3、重启pgsql服务

    win+R打开运行，输入"services.msc"打开服务，在里面找到postgresql的服务重启即可

4、配置防火墙

    打开控制面板，找到系统与安全，打开防火墙，进入高级设置，本地主机需要配置出站规则，远程主机需要配置入站规则，步骤都一样，规则类型选择端口，选择TCP以及特定本地端口5432，起一个名字即可。