PostgreSQL的dblink擴(kuò)展模塊使用

更新時(shí)間：2025年06月16日 10:08:59 作者：老蘇暢談運(yùn)維

PostgreSQL dblink擴(kuò)展支持跨庫查詢,需定義返回列名和類型,可以實(shí)現(xiàn)在不同的數(shù)據(jù)庫之間進(jìn)行通信和交互,下面就一起來了解一下

PostgreSQL想要在A庫下查詢B庫的表，可以使用dblink插件。PostgreSQL的dblink是一個(gè)支持在一個(gè)數(shù)據(jù)庫會(huì)話中連接到其他PostgreSQL數(shù)據(jù)庫的擴(kuò)展模塊，可以實(shí)現(xiàn)在不同的數(shù)據(jù)庫之間進(jìn)行通信和交互。

它可以讓你在一個(gè)數(shù)據(jù)庫中訪問另一個(gè)數(shù)據(jù)庫的表和函數(shù)，甚至可以在不同的服務(wù)器之間進(jìn)行數(shù)據(jù)交互。

pgsql9.6版本以后自帶，不需要手動(dòng)安裝，另外PG使用dblink執(zhí)行一個(gè)遠(yuǎn)程查詢時(shí)，必須在調(diào)用時(shí)定義返回的列名和類型。

dblink用法

創(chuàng)建 pg dblink擴(kuò)展

CREATE EXTENSION IF NOT EXISTS dblink;
###如果已經(jīng)有，可以在 pg 擴(kuò)展表查到
SELECT * FROM pg_extension WHERE extname = 'dblink'; 
或使用\dx 
postgres=# \dx
                                                 已安裝擴(kuò)展列表
        名稱        | 版本 |  架構(gòu)模式  |                                  描述
--------------------+------+------------+------------------------------------------------------------------------
 adminpack          | 2.1  | pg_catalog | administrative functions for PostgreSQL
 dblink             | 1.2  | postgres   | connect to other PostgreSQL databases from within a database
 oracle_fdw         | 1.2  | postgres   | foreign data wrapper for Oracle access
 pg_stat_statements | 1.9  | postgres   | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0  | pg_catalog | PL/pgSQL procedural language

建立遠(yuǎn)程連接

SELECT dblink_connect('local_connect','hostaddr=127.0.0.1 port=5432 dbname=xxxx user=xxxx password=xxxx') as dev;
解釋：
'local_connect'  是我自定義的連接的名稱
hostaddr=127.0.0.1  表示是本機(jī)地址
port=5432  表示使用5432端口，自行設(shè)置
dbname  表示要訪問的數(shù)據(jù)庫的名稱
user，password分別表示用戶名和密碼，根據(jù)自己配置的用戶名密碼更改

如：
postgres=# SELECT dblink_connect('local_connect','hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr') as dev;

 dev
-----

 OK
(1 行記錄)

-- 查詢所有已鏈接的dblink
select dblink_get_connections();

PS:

當(dāng)dblink連接的是同一個(gè)PG實(shí)例下的不同數(shù)據(jù)庫時(shí)，hostaddr就寫 127.0.0.1，不用寫實(shí)際的實(shí)例地址。
當(dāng)是不同實(shí)例時(shí)，需要寫正確的實(shí)例，且這兩個(gè)實(shí)例地址間網(wǎng)絡(luò)是通的。

查詢所有已鏈接的dblink

postgres=# select dblink_get_connections();
 dblink_get_connections
------------------------
 {local_connect}
(1 行記錄)

執(zhí)行查詢

--跨庫查詢
SELECT num,id FROM dblink('local_connect','select num,id from hr.demotable') as t(num numeric,id integer);
SELECT * FROM dblink('local_connect','select num,id from hr.demotable') as t(num numeric,id integer);
SELECT * FROM dblink('local_connect','select * from hr.demotable') as t(num numeric,id integer);

--跨庫查詢寫入
insert into t_dblink
select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable where id<1000') as t(num numeric,id integer);

####使用 dblink 函數(shù)從遠(yuǎn)程數(shù)據(jù)庫獲取數(shù)據(jù)。 local_connect是預(yù)先配置好的遠(yuǎn)程數(shù)據(jù)庫連接名
####dblink 中查詢語句被引號(hào)括起來，如果查詢語句本身有引號(hào)，需要多寫一個(gè)引號(hào)做轉(zhuǎn)義
####AS t()表示dblink返回的結(jié)果集定義了一個(gè)別名't'，并指定了每個(gè)列的數(shù)據(jù)類型

關(guān)閉連接

-- 關(guān)閉遠(yuǎn)程連接
###在PostgreSQL中dblink是會(huì)話級(jí)別；會(huì)話斷開即dblink也關(guān)閉。當(dāng)然也可以在會(huì)話中手動(dòng)關(guān)閉
SELECT dblink_disconnect('local_connect');

-- 查詢所有已鏈接的dblink
select dblink_get_connections();

dblink 擴(kuò)展

簡(jiǎn)便寫法

上面使用方法比較繁瑣，要先創(chuàng)建 dblink連接才能使用，也可以寫成下面這種方式，在一個(gè)語句中完成:

--直接寫 dblink 方式，預(yù)先配置好的到遠(yuǎn)程數(shù)據(jù)庫的連接名 
SELECT * FROM dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable') as t(num numeric,id integer);


create table t_dblink as select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable where 1=2') as t(num numeric,id integer);

insert into t_dblink
select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable where id<1000') as t(num numeric,id integer);

explain analyze with t_temp as (select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable where id<1000') as t(num numeric,id integer)) 
select a.num,a.id from t_dblink a,t_temp b where a.id=b.id;

使用dblink查詢要帶有conn_str，非常不簡(jiǎn)潔，可以考慮在會(huì)話使用臨時(shí)表/視圖來保存。

臨時(shí)表調(diào)用方式

postgres=# create temp table t_dblink as SELECT * FROM dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable') as t(num numeric,id integer);
SELECT 1000000
postgres=# select * from t_dblink;
...........

--退出后重新進(jìn)去臨時(shí)表不存在
postgres=# select * from t_dblink;
錯(cuò)誤:  關(guān)系 "t_dblink" 不存在
第1行select * from t_dblink;

視圖調(diào)用方式

如果認(rèn)為每次查詢都要寫dblink的一堆信息很麻煩的話，可以在db中建一個(gè)view來解決

postgres=# create view  v_dblink as SELECT * FROM dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable') as t(num numeric,id integer);
CREATE VIEW
postgres=# select * from v_dblink;
................
--退出后，重新執(zhí)行
postgres=# select * from v_dblink;

到底選擇視圖/臨時(shí)表，看你需求。在PostgreSQL中臨時(shí)表在會(huì)話結(jié)束后是不會(huì)保持的，這樣的好處：不使用的話無需去刪除對(duì)應(yīng)的臨時(shí)表。

跨庫執(zhí)行ddl/dml操作

–如果需要跨庫執(zhí)行ddl、dml操作，使用dblink_exec

SELECT dblink_connect('local_connect','hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr') as dev;
SELECT dblink_exec('local_connect', 'create table aa(id int,name varchar(50))');
SELECT dblink_exec('local_connect', 'drop table aa');
SELECT dblink_exec('local_connect', 'insert into hr.t values (1011102,8999,''hello'',''2048-10-09''::date)');
SELECT dblink_exec('local_connect', 'delete from  hr.t values where id=1011102');

總結(jié)

PostgreSQL使用這種dblink，存在優(yōu)勢(shì)是即取即用，無須在創(chuàng)建其他對(duì)象；劣勢(shì)是只能連通posrgresql的不同數(shù)據(jù)庫，不能進(jìn)行異構(gòu)數(shù)據(jù)庫的連通。當(dāng)然如果需要連接異構(gòu)的數(shù)據(jù)庫，可以使用Foreign Data Wrapper（FDW）插件，后面再來說說這個(gè)的使用方法。

到此這篇關(guān)于PostgreSQL的dblink擴(kuò)展模塊使用的文章就介紹到這了,更多相關(guān)PostgreSQL dblink擴(kuò)展內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: