【PostgreSQL】Postgres数据库安装、配置、使用DBLink详解

目录

• • • 一、技术背景
    • • 1.1 背景
      • 1.2 什么是 DBLink
    • 二、安装配置 DBLink
    • • 2.1 安装 DBLink
      • 2.2 配置 DBLink
      • • 1. 修改 `postgresql.conf`
        • 2. 修改 `pg_hba.conf`
    • 三、DBLink 使用
    • • 3.1 数据准备
      • 3.2 DBLink 使用
      • • 1. 创建 DBLink 连接
        • 2. 使用 DBLink 进行查询
        • 3. 使用 DBLink 进行增删改
        • 4. 使用 DBLink 进行异步查询
        • 5. 关闭DBLink 连接
    • 四、DBLink 可能存在的问题

一、技术背景

1.1 背景

随着业务复杂程度的提高、数据规模的增长，越来越多的公司选择对其在线业务数据库进行垂直或水平拆分，甚至根据业务场景选择不同的数据库类型以满足其业务需求。与此同时，业务的数据被“散落”在各个数据库实例中。如何方便地对这些数据进行汇总查询，已经成为困扰使用者的一大问题。

针对这类问题，我们可以使用基于DBLink的解决方案，使用者通过在一个数据库实例中就可以查询到多个数据库实例中的数据。

1.2 什么是 DBLink

DBLink技术源于Oracle，我们可以登录上一个Oracle数据库实例，建立一个DBLink指向另一个远程的网络联通的Oracle数据库实例。

现在，基本上主流的数据库都支持DBLink 操作。

• DBLink和数据库实例一一对应，对于PostgreSQL来说，对应的就是PostgreSQL数据库所在的ip+port+database
• DBLink可以指向PostgreSQL、SQLServer、MySQL、Oracle等；
• 使用者可通过创建dblink时获取到的连接名，对远端的数据库实例进行操作；

二、安装配置 DBLink

我在本地的一台 Windows 电脑上装了postgres14数据库，另一个数据库实例在一台Linux服务器的Docker容器中，使用这2个PG 数据库实例进行下面所有的演示。

2.1 安装 DBLink

一般我们在安装 PostgreSQL 数据库的时候，不管是二进制包还是源码包，都已经有了DBLink插件，只是这个插件没有嵌入到PostgreSQL数据库中，我们通过以下命令查看PostgreSQL数据库中可用的插件：

select * from pg_available_extensions; 

执行后发现可用的插件非常多，需要分页显示。

通过 SQL select * from pg_available_extensions where name like '%dblink%'; 发现 PostgreSQL数据库中提供了 DBLink 可供安装：

如果你的PG数据库是通过源码编译安装的，那么可能通过这个SQL查到的结果发现并没有DBLink，这时你需要切换到PG的源码包目录，执行如下命令：

cd contrib/dblink make make install 

这时再通过上面的SQL就可以查询到DBLink插件了。

也可通过以下 SQL 查询已安装的PG 插件：

select * from pg_extension; 

我们可以通过以下 SQL 在 PG 数据库中安装 DBLink 插件：

 create extension dblink; 

我们再查询PG 数据库中已经安装的插件，就已经有了：

2.2 配置 DBLink

找到 PG 数据库的 postgresql.conf 和 pg_hba.conf 这2个配置文件，对他们进行如下更改，否则创建的 DBLink 可能无法访问远端数据库。

1. 修改 postgresql.conf

找到 listen_address 配置项，修改成如下效果，保持对所有的 IP 进行监听。

2. 修改 pg_hba.conf

修改 IPv4 local connections 处的配置项，修改成如下效果。

配置项修改完成后，需要重启 PG 数据库，否者修改的配置不生效。

三、DBLink 使用

3.1 数据准备

首先，我在远端的Linux服务器上的PG数据库中创建一个名为 tsdb 的数据库，并登入该数据库：

在该数据库中创建一个名为project的表，该表中包含id, name 这2个字段，字段id 为主键。

在这张数据表中插入3条数据：

3.2 DBLink 使用

1. 创建 DBLink 连接

在本地的postgres数据库执行如下命令，创建一个 dblink 连接：

SELECT dblink_connect('dblink_test', 'dbname=tsdb host=10.xxx.xxx.xxx port=5433 user=postgres password=xxxxxx');

PG 数据库中的 dblink_connect() 函数的功能就是用来创建 DBLink 连接的。

上面SQL 的用法中 dblink_connect() 函数有2个参数：

• 第一个参数：用于指定所创建的 DBLink 的名称。该参数可忽略，如果忽略的话，将创建一个未命名的 DBLink 连接。因为一个session 中只能有一个未命名的数据库连接，如果原来已经存在其它的未命名DBLink连接，新创建的将会挤掉老的。
• 第二个参数：是创建 DBLink的连接参数串。在该连接串中，你需要指定要进行远程连接的数据库所在节点的IP，数据库端口号port，所用的数据库库名dbname，登陆该数据库所需的账号和密码。

我们可以使用如下 SQL 查询当前该数据库有哪些已经创建好的 DBLink 连接：

 select dblink_get_connections(); 

2. 使用 DBLink 进行查询

在上一步操作中，我已经在本地的Windows电脑的 PG 数据库上已经创建了一个与指定IP的Linux服务器PG数据库（以下简称为远端）的一个DBLink连接，接下来，我在 Windows电脑的PG数据库（以下简称为本地）操作窗口中，通过DBLink连接对远端数据库进行查询。

select * from dblink('dblink_test', 'select * from project') as t(id int, name varchar(32)); 

可以看到，在我本地的PG 数据库中是可以查到远端PG 数据库中的数据的。

除此之外，使用DBLink也可以进行一些较为复杂的查询操作，例如表连接。

我先在本地创建了一个表，并插入3条数据，本地表结构和数据如下：

然后演示下通过 DBLink 远端PG数据库与本地PG数据库进行表连接的操作：

SQL： select t.*, e.name from dblink('dblink_test', 'select * from project') as t(id int, name varchar(32)) left join employee e on t.id=e.id;

3. 使用 DBLink 进行增删改

在本地执行如下 SQL 在远端的PG 数据库中插入数据：

select dblink_exec('dblink_test', 'insert into project (id, name) values (4, ''China Mobile'')'); 

此时，我们到远端PG数据库上查看数据验证下，发现确实新增了一条数据。

在本地执行如下SQL，利用DBLink删除远端PG 数据库数据：

select dblink_exec('dblink_test', 'delete from project where id=4'); 

这是我们再去远端PG数据库确认下，相应的数据确实没了。

更新数据同样是使用 dblink_exec() 函数，SQL如下：

select dblink_exec('dblink_test', 'update project set name=''China Mobile''where id=3'); 

远端的PG 数据库也已经更新。

4. 使用 DBLink 进行异步查询

如果要进行操作的远端PG 数据库为生产数据库，读写的压力较大，我们可以使用DBLink提供的异步查询功能，一定程度上可以缓解远端数据库的查询压力。

在使用DBLink进行一个异步查询之前，我们可以通过如下SQL 判断当前DBLink 连接是否正在忙于一个还未结束的异步查询：

select dblink_is_busy('dblink_test'); 

可以看到返回的值为0，表示当前没有异步查询任务正在进行。

运行如下SQL，使用DBLink在远端数据库执行一个异步查询：

select dblink_send_query('dblink_test', 'select * from project;'); 

执行完该SQL后并不会直接返回结果，异步查询会在系统压力不大时才开始执行。

我们可以通过 dblink_get_result() 函数获取异步查询的结果，示例SQL 如下：

select * from dblink_get_result('dblink_test') as t(id int, name varchar(32)); 

5. 关闭DBLink 连接

使用如下SQL 关闭一个有命名的打开的DBLink连接：

select dblink_disconnect(<dblink_name>); 

例如，我们来关闭上面创建的连接，执行如下SQL：

 select dblink_disconnect('dblink_test'); 

可以看到，此处我们再执行 SQL select dblink_get_connections(); ，可用的DBLink 连接已经为空了。但是，下面仍然显示有一行记录，感觉应该是PG的BUG，因为我下面再次执行断开dblink_test 这个连接时报错了。

四、DBLink 可能存在的问题

不可否认用 DBLINK 在某些方面能带来很多方便，如跨库查询、临时迁移数据、少部分基础表的数据同步等，但是还存在以下三个方面的问题：

• 不支持断点续传功能，如果源端数据库出问题（UNDO 不足、TEMP 不足等）、网络问题，需要重新同步数据，牵扯到效率问题；
• 不支持 DDL，如果通过大量的自定义触发器来实现，在效率和准确性方面需要长时间验证，得不偿失；
• 几年前爆发过 dblink 导致 SCN Headroom 过低问题；