PG-跨库操作-dblink

在PostgreSQL数据库之间进行跨库操作的方式

• dblink
• postgres_fdw

本文先说说dblink；dblink是一个支持从数据库会话中连接到其他PostgreSQL数据库的插件。在其他数据库跨库操作也是采用dblink的方式

一、安装dblink

PostgreSQL插件dblink是PostgreSQL安装包自带的；若安装数据库时没有安装dblink插件；可以自行去编译安装。用使用dblink；先要按照dblink扩展；安装完成之后；我们调用dblink函数来实现跨库操作。我们常用的操作是跨库查询。

lottu01=# create extension dblink;
CREATE EXTENSION

二、使用dblink

2.1、创建dblink连接(dblink_connect)

调用dblink_connect函数；创建dblink连接（连接名为lottu）；这样我们可以在这个会话中访问lottu数据库中下面的对象。

lottu01=> select dblink_connect('lottu', 'host=192.168.1.221 port=6000 user=lottu password=li0924 dbname=lottu');
 dblink_connect 
----------------
 OK
(1 row)

2.2、跨库查询表(dblink)

使用dblink；我们更多是跨库查询操作；由于上面创建dblink_connect连接；所以我们可以拿来使用。

lottu01=> SELECT * FROM dblink('lottu', 'select id, info from public.t1') as lottu_t1(id int, info text);
  id  | info  
------+-------
 1001 | lottu
(1 row)

我们也可以直接使用；无需先创建dblink_connect。这是常用的方式。

lottu01=> SELECT * FROM dblink('host=192.168.1.221 port=6000 user=lottu password=li0924 dbname=lottu', 'select id, info from public.t1') as lottu_t1(id int, info text);
  id  | info  
------+-------
 1001 | lottu
(1 row)

2.3、跨库执行ddl，dcl操作(dblink_exec)

若我们跨库操作create、insert、update、delete语句；其实这种行为一般不跨库操作。慎用！下面演示下insert操作

lottu01=> SELECT dblink_exec('lottu', 'insert into public.t1 values (1002,''hello'')');
 dblink_exec 
-------------
 INSERT 0 1
(1 row)
# OR
lottu01=> SELECT dblink('lottu', format('insert into public.t1 select %L, %L', 1003, 'lottu'));
     dblink     
----------------
 ("INSERT 0 1")
(1 row)

2.4、关闭dblink连接

在PostgreSQL中dblink是会话级别；会话断开即dblink也关闭。当然也可以在会话中手动关闭

lottu01=> SELECT dblink_disconnect('lottu');
 dblink_disconnect 
-------------------
 OK
(1 row)

三、扩展

使用dblink查询；都要带有conn_str；非常不简洁；在会话使用临时表/视图来保存。两种效果不同

到底选择视图/临时表；看你需求；在PostgreSQL中临时表在会话结束后是不会保持的。这样的好处；不使用的话无需去删除对应的临时表

lottu01=> create temp table lottu_t1 as SELECT * FROM dblink('host=192.168.1.221 port=6000 user=lottu password=li0924 dbname=lottu', 'select id, info from public.t1') as lottu_t1(id int, info text);
SELECT 2
lottu01=> select * from lottu_t1;
  id  | info  
------+-------
 1001 | lottu
 1002 | hello
(2 rows)

四、应用场景

PostgreSQL使用dblink；存在优势是；即取即用；无须在创建其他对象；下一章节会讲解PostgreSQL_fdw;相比PostgreSQL_fdw优势在此；不足之处；后续补充讲解。

4.1、同步远端库表

在开发工作中；测试数据库需要同步某个（新建/旧的）表的数据；使用dblink非常便捷。

lottu01=> create table public.t1(id int, info text);
CREATE TABLE
lottu01=> insert into public.t1 SELECT * FROM dblink('host=192.168.1.221 port=6000 user=lottu password=li0924 dbname=lottu', 'select id, info from public.t1') as lottu_t1(id int, info text);
INSERT 0 5
lottu01=> select * from public.t1;
  id  | info  
------+-------
 1001 | lottu
 1002 | hello
 1003 | rax
 1004 | rax
 1005 | lottu
(5 rows)

更多详情请参见dblink。