在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。
