域名預(yù)訂/競(jìng)價(jià)，好“米”不錯(cuò)過(guò)

這篇文章主要介紹了PostgreSQL 中的postgres_fdw擴(kuò)展詳解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧。

通過(guò)postgres_fdw 擴(kuò)展，訪問(wèn)遠(yuǎn)程數(shù)據(jù)庫(kù)表

一、環(huán)境準(zhǔn)備

虛擬機(jī)(node107)：centos7、PostgreSQL10

遠(yuǎn)程服務(wù)器(百度云服務(wù)BBC)： centos7、PostgreSQL10

在本地虛擬機(jī)*問(wèn)遠(yuǎn)程服務(wù)器的數(shù)據(jù)表。

二、配置連接

（1）創(chuàng)建擴(kuò)展: 在本地107這個(gè)節(jié)點(diǎn)上創(chuàng)建擴(kuò)展。

[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 需要單獨(dú)授權(quán)

grant usage on foreign data wrapper postgres_fdw to 用戶名

（2） 創(chuàng)建 foreign server 外部服務(wù)器，外部服務(wù)是指連接外部數(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的外部服務(wù)，options 設(shè)置遠(yuǎn)程PostgreSQL數(shù)據(jù)源連接選項(xiàng)，通常設(shè)置主機(jī)名、端口、數(shù)據(jù)庫(kù)名稱。

（3）需要給外部服務(wù)創(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ù)庫(kù)用戶，options 里接的是遠(yuǎn)程PostgreSQL數(shù)據(jù)庫(kù)的用戶和密碼。password 注意修改成自己的

其實(shí)想訪問(wèn)遠(yuǎn)程數(shù)據(jù)庫(kù)，無(wú)非就是知道連接信息。包括host、port、dbname、user、password

（4）BBC上準(zhǔn)備數(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');

錯(cuò)誤: 外部表上不支持主鍵約束

第1行create foreign table ft_customers (id int4 primary key , nam...
            ^
mydb=#

錯(cuò)誤: 外部表上不支持主鍵約束

第1行create foreign table ft_customers (id int4 primary key , nam... ^mydb=#

可以看見(jiàn)，外部表不支持主鍵約束。想想也是合理

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 選項(xiàng)中： 需要指定外部表的schema和表名

（6）在node107上去訪問(wèn)遠(yuǎn)程BBC的數(shù)據(jù)

mydb=# select * from ft_customers where id < 5;
 id | name
----+-------
 1 | name1
 2 | name2
 3 | name3
 4 | name4
(4 rows)

mydb=#

可以看見(jiàn)在mydb上能夠訪問(wèn)遠(yuǎn)程數(shù)據(jù)庫(kù)上 的數(shù)據(jù)了。

如果出現(xiàn)報(bào)錯(cuò)，如報(bào)pg_hba.conf 文件沒(méi)有訪問(wèn)策略，在需要在對(duì)修改配置文件。

（7）本地?cái)?shù)據(jù)庫(kù)表與遠(yuǎn)程數(shù)據(jù)庫(kù)表進(jìn)行進(jìn)行關(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 外部表支持寫(xiě)操作

postgres_fdw 外部表一開(kāi)始只支持讀，PostgreSQL9.3 版本開(kāi)始支持可寫(xiě)。

寫(xiě)操作需要保證：1. 映射的用戶對(duì)有寫(xiě)權(quán)限；2. 版本需要9.3 以上。

在node107結(jié)點(diǎn)上線刪除數(shù)據(jù)，后再插入數(shù)據(jù)、最后更新。并查看遠(yuǎn)程BBC數(shù)據(jù)庫(kù)表情況

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=#

四、postgres_fdw支持聚合函數(shù)下推

PostgreSQL10 增強(qiáng)了postgres_fdw 擴(kuò)展模塊的特性，可以將聚合、關(guān)聯(lián)操作下推到遠(yuǎn)程PostgreSQL數(shù)據(jù)庫(kù)進(jìn)行，而之前的版本是將外部表相應(yīng)的遠(yuǎn)程數(shù)據(jù)全部取到本地再做聚合，10版本這個(gè)心特性大幅度減少了從遠(yuǎn)程傳輸?shù)奖镜貛?kù)的數(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: 遠(yuǎn)程庫(kù)上執(zhí)行的SQL，此SQL為聚合查詢的SQL。聚合是在遠(yuǎn)程上執(zhí)行的。

如果在PostgreSQL9.6 測(cè)試，則需要從遠(yuǎn)程傳輸?shù)奖镜夭趴梢浴?/p>

小結(jié)

物理表和外部表不能同名，因?yàn)閜g_class的對(duì)象名稱唯一鍵的緣故

外部表不會(huì)存儲(chǔ)數(shù)據(jù)。

文章來(lái)源：腳本之家

來(lái)源地址：https://www.jb51.net/article/204328.htm

申請(qǐng)創(chuàng)業(yè)報(bào)道，分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處，共同探討創(chuàng)業(yè)新機(jī)遇！