POSTGRESQL如何訪問同instance的庫

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

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

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

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

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

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

1  dblink

2  postgres_fdw  

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

1 dblink

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

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';

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

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

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

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

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

問題

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

SELECT dblink_get_connections();

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

2  解除dblink 

dblink_disconnect();

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

dblink_is_busy

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

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

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

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

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

1  在目的庫上創建 create extension

2  創建于目的庫的連接

3  創建本地用戶和遠程連接之間的賬號mapping

4  創建外部表

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');

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

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