229°

PG-跨库操作-dblink

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

  • dblink
  • postgres_fdw

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

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

lottu01=# create extension dblink;
CREATE EXTENSION

调用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)

使用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)

若我们跨库操作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)

本文转载自博客园,原文链接:https://www.cnblogs.com/lottu/p/13331387.html

全部评论: 0

    我有话说: