在PostgreSQL資料庫之間進行跨庫操作的方式 dblink postgres_fdw 本文先說說dblink；dblink是一個支持從資料庫會話中連接到其他PostgreSQL資料庫的插件。在其他資料庫跨庫操作也是採用dblink的方式 一、安裝dblink PostgreSQL插件dblin ...
在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。
