PostgreSQL 中的postgres_fdw擴展詳解
通過postgres_fdw 擴展，訪問遠程數(shù)據(jù)庫表
一、環(huán)境準備
虛擬機(node107)：centos7、PostgreSQL10
遠程服務器(百度云服務BBC)： centos7、PostgreSQL10
在本地虛擬機上訪問遠程服務器的數(shù)據(jù)表。
二、配置連接
（1）創(chuàng)建擴展: 在本地107這個節(jié)點上創(chuàng)建擴展。
[root@107 ~]# su postgre su: user postgre does not exist [root@107 ~]# su postgres bash-4.2$ psql mydb postgres could not change directory to "/root": 權(quán)限不夠 psql (10.7) Type "help" for help. mydb=# CREATE EXTENSION postgres_fdw; CREATE EXTENSION
如果是普通用戶使用 ·postgres_fdw 需要單獨授權(quán)
grant usage on foreign data wrapper postgres_fdw to 用戶名
（2） 創(chuàng)建 foreign server 外部服務器，外部服務是指連接外部數(shù)據(jù)源的連接信息
mydb=# create server fs_postgres_bbc foreign data wrapper postgres_fdw options(host '182.61.136.109',port '5432',dbname 'technology'); mydb=#
定義名稱為 fs_postgres_bbc的外部服務，options 設置遠程PostgreSQL數(shù)據(jù)源連接選項，通常設置主機名、端口、數(shù)據(jù)庫名稱。
（3）需要給外部服務創(chuàng)建映射用戶
mydb=# create user mapping for postgres server fs_postgres_bbc options(user 'postgres',password 'password'); CREATE USER MAPPING mydb=#
for 后面接的是 node107 的數(shù)據(jù)庫用戶，options 里接的是遠程PostgreSQL數(shù)據(jù)庫的用戶和密碼。password 注意修改成自己的
其實想訪問遠程數(shù)據(jù)庫，無非就是知道連接信息。包括host、port、dbname、user、password
（4）BBC上準備數(shù)據(jù)。
technology=# select * from public.customers where id < 5; id | name ----+------- 1 | name1 2 | name2 3 | name3 4 | name4 (4 rows) technology=# -- schemaname = public
(5) 在node107上創(chuàng)建外部表：
mydb=# create foreign table ft_customers
(
id int4 primary key ,
name varchar(200)
) server fs_postgres_bbc options (schema_name 'public',table_name 'customers');
錯誤: 外部表上不支持主鍵約束
第1行create foreign table ft_customers (id int4 primary key , nam...
^
mydb=#
可以看見，外部表不支持主鍵約束。想想也是合理
mydb=# create foreign table ft_customers ( id int4 , name varchar(200) ) server fs_postgres_bbc options (schema_name 'public',table_name 'customers'); CREATE FOREIGN TABLE mydb=#
options 選項中： 需要指定外部表的schema和表名
（6）在node107上去訪問遠程BBC的數(shù)據(jù)
mydb=# select * from ft_customers where id < 5; id | name ----+------- 1 | name1 2 | name2 3 | name3 4 | name4 (4 rows) mydb=#
可以看見在mydb上能夠訪問遠程數(shù)據(jù)庫上 的數(shù)據(jù)了。
如果出現(xiàn)報錯，如報pg_hba.conf 文件沒有訪問策略，在需要在對修改配置文件。
（7）本地數(shù)據(jù)庫表與遠程數(shù)據(jù)庫表進行進行關(guān)聯(lián)查詢
create table orders ( id int PRIMARY KEY, customerid int ); INSERT INTO orders(id,customerid) VALUES(1,1),(2,2); SELECT * FROM orders; -- 和外部表關(guān)聯(lián)查詢。 mydb=# SELECT o.*,c.* mydb-# FROM orders o mydb-# INNER JOIN ft_customers c ON o.customerid = c.id mydb-# WHERE c.id < 10000; id | customerid | id | name ----+------------+----+------- 1 | 1 | 1 | name1 2 | 2 | 2 | name2 (2 rows) mydb=#
三、postgres_fdw 外部表支持寫操作
postgres_fdw 外部表一開始只支持讀，PostgreSQL9.3 版本開始支持可寫。
寫操作需要保證：1. 映射的用戶對有寫權(quán)限；2. 版本需要9.3 以上。
在node107結(jié)點上線刪除數(shù)據(jù)，后再插入數(shù)據(jù)、最后更新。并查看遠程BBC數(shù)據(jù)庫表情況
mydb=# select count(*) from ft_customers; count ---------- 10000000 (1 row) mydb=# delete from ft_customers where id = 9999999; DELETE 1 mydb=# select count(*) from ft_customers; count --------- 9999999 (1 row) mydb=# insert into ft_customers values(9999999,'name1'); INSERT 0 1 mydb=# select count(*) from ft_customers; count ---------- 10000000 (1 row) mydb=# select * from ft_customers where id = 9999999; id | name ---------+------- 9999999 | name1 (1 row) mydb=# update ft_customers set name = 'name999' where id = 9999999; UPDATE 1 mydb=# select * from ft_customers where id = 9999999; id | name ---------+--------- 9999999 | name999 (1 row) mydb=#
可以看見對ft_customers 進行增刪改查。
四、postgres_fdw支持聚合函數(shù)下推
PostgreSQL10 增強了postgres_fdw 擴展模塊的特性，可以將聚合、關(guān)聯(lián)操作下推到遠程PostgreSQL數(shù)據(jù)庫進行，而之前的版本是將外部表相應的遠程數(shù)據(jù)全部取到本地再做聚合，10版本這個心特性大幅度減少了從遠程傳輸?shù)奖镜貛斓臄?shù)據(jù)量。提升了postgres_fdw外部表上聚合查詢的性能。
mydb=# EXPLAIN(ANALYZE on,VERBOSE on) select id,count(*) from ft_customers where id < 100 group by id;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Foreign Scan (cost=104.88..157.41 rows=199 width=12) (actual time=16.725..16.735 rows=99 loops=1)
Output: id, (count(*))
Relations: Aggregate on (public.ft_customers)
Remote SQL: SELECT id, count(*) FROM public.customers WHERE ((id < 100)) GROUP BY 1
Planning time: 0.247 ms
Execution time: 249.410 ms
(6 rows)
mydb=#
remote sql: 遠程庫上執(zhí)行的SQL，此SQL為聚合查詢的SQL。聚合是在遠程上執(zhí)行的。
如果在PostgreSQL9.6 測試，則需要從遠程傳輸?shù)奖镜夭趴梢浴?/p>
小結(jié)
物理表和外部表不能同名，因為pg_class的對象名稱唯一鍵的緣故
外部表不會存儲數(shù)據(jù)。
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
postgresql使用filter進行多維度聚合的解決方法
這篇文章給大家介紹postgresql使用filter進行多維度聚合的解決方法，本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友參考下吧2021-07-07
這篇文章主要介紹了postgresql 替換空格 換行和回車的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了解決PostgreSQL 執(zhí)行超時的情況，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
在數(shù)據(jù)庫系統(tǒng)中,死鎖是一個常見但棘手的問題,PostgreSQL 也不例外,如果不及時診斷和解決,死鎖可能會導致系統(tǒng)性能嚴重下降,甚至應用程序的崩潰,本文將詳細探討如何診斷和解決 PostgreSQL 中的死鎖問題,需要的朋友可以參考下2024-07-07
postgresql 刪除重復數(shù)據(jù)案例詳解
這篇文章主要介紹了postgresql 刪除重復數(shù)據(jù)案例詳解,本篇文章通過簡要的案例,講解了該項技術(shù)的了解與使用,以下就是詳細內(nèi)容,需要的朋友可以參考下2021-08-08
這篇文章主要介紹了PostgreSQL 正則表達式替換-使用變量方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
pg中的長事務會影響表中垃圾回收，導致表的年齡增長無法freeze。能消耗事務的只有當執(zhí)行了一些DML或者DDL操作后才能算是我們通常說的長事務。否則只能算是我們常說的長連接，當然長連接也有很多弊端，例如占用內(nèi)存、cpu等資源2022-09-09
PostgreSQL利用遞歸優(yōu)化求稀疏列唯一值的方法
這篇文章主要介紹了PostgreSQL利用遞歸優(yōu)化求稀疏列唯一值的方法,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-01-01
PostgreSQL ROW_NUMBER() OVER()的用法說明
這篇文章主要介紹了PostgreSQL ROW_NUMBER() OVER()的用法說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
這篇文章主要介紹了PostgreSQL 自增語法的用法說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02