使用dblink插件

简介

dblink是PostgreSQL数据库中的一个扩展模块，它允许在不同的数据库之间建立连接，使得这些数据库之间可以互相访问和查询数据。dblink的主要作用是实现分布式查询，即在不同的数据库之间查询数据。它可以将不同的数据库看作一个整体，方便用户进行跨数据库的查询和操作。

此外，dblink还可以用于数据的备份和恢复，数据的同步等操作。它可以大大提高数据库的灵活性和可扩展性，使得数据库应用更加高效和可靠。更多使用说明详见dblink官方文档。

支持的版本说明

该插件支持从9.5到15的版本。

可通过以下SQL语句查询当前实例是否支持该插件：

SELECT * FROM pg_available_extension_versions WHERE name = 'dblink';

如果不支持，可通过升级内核小版本或者大版本升级使用该插件。

RDS for PostgreSQL实例支持的插件，具体请参见支持的插件列表。

插件安装与卸载

• 安装插件

  SELECT control_extension ('create', 'dblink');

• 卸载插件

  SELECT control_extension ('drop', 'dblink');

更多信息，请参见通过界面安装和卸载插件和通过SQL命令安装和卸载插件。

使用dblink插件跨库操作时，要求两个数据库实例的服务端IP必须在同一个VPC内。

基本使用

场景一：

在一个实例里，存在多个数据库，不同数据库承载不同的业务，有些操作需要在多个数据库中执行，此时就可以使用dblink进行跨库操作。

-- 1、创建数据库
create database homedb;
create database db1;

-- 2、切换到homedb，创建dblink连接
select dblink_connect('connect_db1', 'dbname=db1 port=5432 user=root password=******** host=127.0.0.1');
dblink_connect
----------------
OK
(1 row)

--3、执行sql命令
--执行查询
SELECT * FROM dblink('connect_db1', 'select * from test') as test(id integer, info varchar(8));
id | info
----+------
1 | a
2 | b
(2 rows)

--执行插入
SELECT dblink_exec('connect_db1', 'insert into test values(3,'c')');
dblink_exec
-------------
INSERT 0 1
(1 row)

-- 3、切换到db1查看结果
select * from test;
id | info
----+------
1 | a
2 | b
3 | c
(3 rows)

-- 4、关闭远程连接
SELECT dblink_disconnect('connect_db1');
dblink_disconnect
----------------
OK
(1 row)

场景二：

同一VPC下有两个RDS for PostgreSQL数据库实例：生产环境（数据库db1）、测试环境（数据库db2）。需要将测试环境的数据同步至生产环境，此时在生产环境中可通过dblink插件实现数据同步。

-- 1、登录生产环境，创建数据库
create database db1;

-- 2、切换到生产库db1，连接测试环境库db2
select dblink_connect('connect_db2', 'dbname=db2 port=5432 user=root password=******** host=10.29.182.247');
dblink_connect
----------------
OK
(1 row)

-- 3、查询测试库db2中test1表的值
SELECT * FROM dblink('host=10.29.182.247 port=5432 user=root password=******** dbname=db2', 'select * from test1') as test1(id int, name text);
id | name
----+------
1 | a
2 | b
(2 rows)

-- 4、将测试库db2中的test1表同步到生产库db1中的backup1表
insert into backup1 SELECT * FROM dblink('dbname=db2 port=5432 user=root password=******** host=10.29.182.247', 'select * from test1') as backup1(id int, name text);
dblink
-------------
INSERT 0 2
(2 row)

--5、查询生产库db1库backup1表
select * from backup1;
id | name
----+------
1 | a
2 | b
(2 rows)

--6、关闭连接
SELECT dblink_disconnect('connect_db2');
dblink_disconnect
----------------
OK
(1 row)