POSTGRESQL如何訪問同instance的庫

這篇文章主要介紹POSTGRESQL如何訪問同instance的庫，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

其實說到這個問題,有些同學(xué)會有疑問,訪問同instance 的有那么難嗎? 估計用過SQL SERVER ,MYSQL的同學(xué)會提出這樣的疑問, 而ORACLE的同學(xué)則會提出什么同一個instance 多個庫, really ? 

實際上SQL SERVER 本身是多個數(shù)據(jù)庫和schema 并存的數(shù)據(jù)庫, POSTGRESQL 也是. 這時估計SQL SERVER 的同學(xué)會嘴角上揚,我們的功能是最全的,哪有訪問同一個INSTANCE 的多個數(shù)據(jù)庫還這么麻煩.

其實我到是有不同的意見,原因如下

同一個數(shù)據(jù)庫的INSTANCE 下多個數(shù)據(jù)庫可以無障礙的訪問,本身是弊大于利還是利大于弊,這不好說, 尤其現(xiàn)在開發(fā)中使用MYSQL時,都已經(jīng)分庫分表了,同一個INSTANCE 下多個數(shù)據(jù)庫能互訪的方便,當然不是壞事,但你見過一個INSTANCE 下掛了N 個數(shù)據(jù)庫,然后庫和庫互相夸庫查詢, 等到拆庫的時候有多麻煩, 另外從現(xiàn)在軟件開發(fā)的角度來說,如果還算是一個項目的話, 這樣的跨庫訪問的方式應(yīng)該被減少,甚至是禁止.

當然不是還有那么多不是項目的項目,PG 也是可以進行跨庫訪問,常用的2中方式

1  dblink

2  postgres_fdw  

以下操作基于PG 11版本及以上

1 dblink

dblink 熟悉這個名詞是ORACLE SQL SERVER 的同學(xué),一般都是訪問另一個物理庫的數(shù)據(jù)表, POSTGRESQL 可以通過這樣方式,訪問本實例中的另外的數(shù)據(jù)庫或遠程的數(shù)據(jù)庫.

1  建立一個連接

select dblink_connect('test','host=192.168.198.100 port=5432 user=test password=test dbname=test');

2  直接進行查詢

select * from dblink('test','select * from actor limit 10') as t ( actor_id int,first_name varchar(45),last_name varchar(45), last_update timestamp);

3  增加條件怎么辦,是寫在里面還是寫在外面

select * from dblink('test','select * from actor limit 10') as t ( actor_id int,first_name varchar(45),last_name varchar(45), last_update timestamp) where first_name = 'Penelope';

上面的例子是寫在外面, 但是有條件的,應(yīng)該是本地庫的跨庫查詢

那為什么不寫到里面, 其實涉及到轉(zhuǎn)移符的問題,撰寫起來比較麻煩,所以寫到了外面.

大部分文章到底就為止了,實際上這就完了,怎么查詢我都有多少了dblink

怎么清理dblink ,即使 G 類似的知識也沒找到說的明白的.

實際上DBLINK是一套功能集合,下面的看看常用的 DBLINK的功能有哪些

問題

1  建立dblink , 是通過 dblink_connect 來建立的上面是有的,但如果你建立的dblink太多了,怎么查看當前建立了多少dblink 

SELECT dblink_get_connections();

通過上面的方式就可以查看到具體本地庫已經(jīng)啟用了db_link,的名字和數(shù)量2 

2  解除dblink 

dblink_disconnect();

3 判斷當前BDLINK 是否在使用中 

dblink_is_busy

另外DBLINK 還支持異步調(diào)用,將語句發(fā)送給remote  的數(shù)據(jù)庫再等待后將信息取回. 這點實際上在其他數(shù)據(jù)庫上如果找到類比, 可能類似 SQL SERVER  SERVICE BROKE 但也僅僅是類似.

使用上應(yīng)該是類似于句柄,將信息發(fā)送給遠程的數(shù)據(jù)庫服務(wù)器,然后,在通過判斷句柄將信息取回. 但返回的信息會在本地機的內(nèi)存中保存,所以不建議獲取數(shù)據(jù)量較大的信息. 這里面建議使用標準的方式來獲取數(shù)據(jù),也就是最上面的方式.

同時建議提高work_mem 的大小和臨時表的大小.

上面的訪問的方式應(yīng)該會有一部分人吐槽,很麻煩,PG 還提供另一種方式 POSTGRES_FDW

其實POSTGRES_FDW 做起來并不麻煩 4 步就OK

1  在目的庫上創(chuàng)建 create extension

2  創(chuàng)建于目的庫的連接

3  創(chuàng)建本地用戶和遠程連接之間的賬號mapping

4  創(chuàng)建外部表

create extension postgres_fdw;

create server foreign_server_t foreign data wrapper postgres_fdw options (host '192.168.198.100',port '5432',dbname 'test');

create user mapping for postgres server foreign_server_t options (user 'test',password 'test');

CREATE FOREIGN TABLE foreign_table (

actor_id integer NOT NULL,

 first_name varchar(45),

last_name varchar(45),

last_update  timestamp

SERVER foreign_server_t       

OPTIONS (schema_name 'public', table_name 'actor');

查詢和在本地庫是沒有區(qū)別的,這是第二種解決本地庫中兩個數(shù)據(jù)庫中的表進行跨庫訪問的方式.

以上是“POSTGRESQL如何訪問同instance的庫”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道！