PostgreSQL 訪問外部數(shù)據(jù)庫之 postgres_fdw

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué),十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn),目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net

 

文章目錄

        postgres_fdw 簡介
        postgres_fdw 安裝
        創(chuàng)建外部服務(wù)器對象
        創(chuàng)建用戶映射
        創(chuàng)建外部表
        訪問遠(yuǎn)程數(shù)據(jù)表
        postgres_fdw 選項(xiàng)
            連接選項(xiàng)
            對象名稱選項(xiàng)
            成本評估選項(xiàng)
            遠(yuǎn)程執(zhí)行選項(xiàng)
            更新操作選項(xiàng)
            導(dǎo)入選項(xiàng)
        連接管理
        事務(wù)管理
        遠(yuǎn)程查詢優(yōu)化
        遠(yuǎn)程查詢執(zhí)行環(huán)境

大家好,我是只談技術(shù)不剪發(fā)的 Tony 老師。在某些情況下,例如數(shù)據(jù)倉庫的 ETL 流程中,我們可能需要從一個 PostgreSQL 服務(wù)器訪問另一個遠(yuǎn)程 PostgreSQL 服務(wù)器中的數(shù)據(jù)。為此,PostgreSQL 提供了一個擴(kuò)展的模塊:postgres_fdw。今天我們就來介紹一下這個模塊的使用方法和案例。
postgres_fdw 簡介

postgres_fdw 是基于 SQL/MED 標(biāo)準(zhǔn)開發(fā)的一個外部數(shù)據(jù)封裝器(Foreign Data Wrapper),可以用于訪問外部 PostgreSQL 服務(wù)器,對遠(yuǎn)程數(shù)據(jù)表執(zhí)行 SELECT、INSERT、UPDATE 以及 DELETE 操作。

與 postgres_fdw 類似的另一個擴(kuò)展模塊是 dblink。它們的功能基本相同,但是 postgres_fdw 提供了更透明且符合標(biāo)準(zhǔn)的語法來訪問遠(yuǎn)程表,并且在很多情況下可以提供更好的性能。postgres_fdw 可以支持的遠(yuǎn)程服務(wù)器最低版本為 PostgreSQL 8.3,如果是只讀訪問則可以支持到 PostgreSQL 8.1。

通過 postgres_fdw 訪問遠(yuǎn)程數(shù)據(jù)表的步驟如下:

    使用CREATE EXTENSION命令安裝 postgres_fdw 模塊;
    使用CREATE SERVER命令創(chuàng)建一個外部服務(wù)器對象,該對象代表了想要連接的遠(yuǎn)程數(shù)據(jù)庫;
    使用CREATE USER MAPPING命令為本地用戶創(chuàng)建一個遠(yuǎn)程用戶映射;
    使用CREATE FOREIGN TABLE或者IMPORT FOREIGN SCHEMA語句為每個遠(yuǎn)程數(shù)據(jù)庫中的表創(chuàng)建一個外部表。

然后,就可以通過查詢外部表訪問遠(yuǎn)程表中的數(shù)據(jù),包括 SELECT、INSERT、UPDATE 以及 DELETE 操作(當(dāng)然,用戶映射時指定的遠(yuǎn)程用戶必須擁有這些表上的相應(yīng)權(quán)限)。接下來我們就通過一個實(shí)際案例介紹如何實(shí)現(xiàn)以上步驟。

    ??除了 postgres_fdw,PostgreSQL 還支持訪問各種數(shù)據(jù)源的外部數(shù)據(jù)封裝器,包括 Oracle、MySQL、SQL Server、SQLite 等關(guān)系型數(shù)據(jù)庫,MongoDB、Redis、Neo4j、Cassandra 等 NoSQL 數(shù)據(jù)庫,CSV、XML、JSON 等文件,Elasticsearch、Hive、HBase 等大數(shù)據(jù)平臺等等,具體可以參考 PostgreSQL Wiki。

postgres_fdw 安裝

首先,我們需要安裝 postgres_fdw 模塊。對于 Linux 操作系統(tǒng),可以通過 postgresql-contrib 軟件包下載 PostgreSQL 擴(kuò)展模塊;對于 Windows 和 macOS,默認(rèn)安裝已經(jīng)包含了擴(kuò)展模塊。我們只需要執(zhí)行以下語句在當(dāng)前數(shù)據(jù)庫中安裝 postgres_fdw 模塊:

CREATE EXTENSION postgres_fdw;

創(chuàng)建外部服務(wù)器對象

然后,使用 CREATE SERVER 語句創(chuàng)建一個外部服務(wù)器(foreign server)。例如:

CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.56.104', port '5432', dbname 'pagila');

其中,foreign_server 是我們指定的外部服務(wù)器名稱;host 參數(shù)是遠(yuǎn)程服務(wù)器的地址,示例中為 192.168.56.104;port 參數(shù)是遠(yuǎn)程服務(wù)器的端口,示例中為 5432;dbname 參數(shù)是遠(yuǎn)程數(shù)據(jù)庫的名稱,示例中為 pagila。

    ??關(guān)于 CREATE SERVER 語句的詳細(xì)介紹,可以參考官方文檔。

創(chuàng)建用戶映射

下一步,使用 CREATE USER MAPPING 語句創(chuàng)建一個用戶映射(user mapping),為本地用戶指定一個訪問遠(yuǎn)程服務(wù)器時的角色。例如:

CREATE USER MAPPING FOR postgres
SERVER foreign_server
OPTIONS (user 'tony', password 'pswd123#');

以上語句為本地 postgres 用戶創(chuàng)建了一個訪問遠(yuǎn)程服務(wù)器 foreign_server 時的用戶映射,也就是使用用戶名 tony 和密碼 pswd123# 連接遠(yuǎn)程服務(wù)器。

    ??關(guān)于 CREATE USER MAPPING 語句的詳細(xì)介紹,可以參考官方文檔。

創(chuàng)建外部表

然后,使用 CREATE FOREIGN TABLE 語句創(chuàng)建一個外部表(foreign table)。例如:

CREATE FOREIGN TABLE country (
    country_id int NOT NULL,
    country varchar(50) NOT NULL,
    last_update timestamp NOT NULL
)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'country');

以上示例在本地?cái)?shù)據(jù)庫中創(chuàng)建了一個名為 country 的外部表,對應(yīng)的是遠(yuǎn)程服務(wù)器 foreign_server 中 public 模式下的 country 表。

通常來說,推薦創(chuàng)建外部表時使用和遠(yuǎn)程表一致的數(shù)據(jù)類型以及可能的排序規(guī)則。雖然目前 postgres_fdw 支持各種類型轉(zhuǎn)換,遠(yuǎn)程服務(wù)器和本地服務(wù)器解析 WHERE 子句的細(xì)微差別可能會導(dǎo)致意外的語義異常。

另外,外部表的字段個數(shù)可以少于遠(yuǎn)程表,字段順序也可以不同;因?yàn)樽侄蔚挠成涫峭ㄟ^名稱而不是字段位置實(shí)現(xiàn)。

    ??關(guān)于 CREATE FOREIGN TABLE 語句的詳細(xì)介紹,可以參考官方文檔。
    ??另一種創(chuàng)建外部表的方法是使用 IMPORT FOREIGN SCHEMA 語句實(shí)現(xiàn)批量導(dǎo)入,具體參考官方文檔。

訪問遠(yuǎn)程數(shù)據(jù)表

現(xiàn)在,我們可以在本地?cái)?shù)據(jù)庫中通過外部表訪問對應(yīng)的遠(yuǎn)程表。例如:

SELECT *
FROM country
LIMIT 5;
country_id|country       |last_update        |
----------|--------------|-------------------|
         1|Afghanistan   |2006-02-15 09:44:00|
         2|Algeria       |2006-02-15 09:44:00|
         3|American Samoa|2006-02-15 09:44:00|
         4|Angola        |2006-02-15 09:44:00|
         5|Anguilla      |2006-02-15 09:44:00|

除了查詢操作,postgres_fdw 同樣可以通過 DML 語句修改遠(yuǎn)程表中的數(shù)據(jù)。對于 INSERT 語句,目前還不支持 ON CONFLICT DO UPDATE 子句;但是可以使用省略了沖突條件的 ON CONFLICT DO NOTHING 子句。例如:

INSERT INTO country VALUES (109, 'Zambia', '2006-02-15 09:44:00')
ON CONFLICT DO NOTHING;

另外,雖然 postgres_fdw 支持對分區(qū)表執(zhí)行導(dǎo)致數(shù)據(jù)行移動的 UPDATE 語句;但是如果遠(yuǎn)程分區(qū)既是行移動的目標(biāo)分區(qū),又是更新操作的目標(biāo)分區(qū)時,UDATE 語句無法執(zhí)行。
postgres_fdw 選項(xiàng)

在使用 postgres_fdw 創(chuàng)建外部服務(wù)器、用戶映射以及外部表等對象時,可以通過 OPTIONS 指定一些選項(xiàng)設(shè)置不同的行為。
連接選項(xiàng)

通過 postgres_fdw 創(chuàng)建遠(yuǎn)程服務(wù)器時的選項(xiàng)和 libpq 連接字符串相同,但是不支持以下選項(xiàng):

    user 和 password(這兩個選項(xiàng)可以在創(chuàng)建用戶映射時指定);
    client_encoding(自動使用本地服務(wù)器編碼進(jìn)行設(shè)置);
    fallback_application_name(默認(rèn)設(shè)置為 postgres_fdw)。

只有超級永遠(yuǎn)可以不通過密碼認(rèn)證連接遠(yuǎn)程服務(wù)器,所以為普通用戶創(chuàng)建用戶映射時不要忘記指定 password 選項(xiàng)。
對象名稱選項(xiàng)

以下選項(xiàng)可以用于控制發(fā)送給遠(yuǎn)程服務(wù)器的 SQL 語句中使用的對象名稱,只有當(dāng)外部表定義中的名稱和遠(yuǎn)程表的名稱不一致時才需要指定:

    schema_name,該選項(xiàng)在創(chuàng)建外部表時指定,表示遠(yuǎn)程表所在的模式;如果省略,默認(rèn)使用外部表所在的模式名。
    table_name,該選項(xiàng)在創(chuàng)建外部表時指定,表示遠(yuǎn)程表的名稱;如果省略,默認(rèn)使用外部表的名稱。
    column_name,該選項(xiàng)在定義外部表的字段名時指定,表示對應(yīng)遠(yuǎn)程表中的字段名;如果省略,默認(rèn)使用外部表的字段名稱。

例如,以下語句重新創(chuàng)建了外部表 country:

DROP FOREIGN TABLE IF EXISTS country;

CREATE FOREIGN TABLE country (
    id int OPTIONS (column_name 'country_id') NOT NULL,
    country varchar(50) NOT NULL,
    last_update timestamp NOT NULL
)
SERVER foreign_server;

首先,外部表 country 中的 id 字段和遠(yuǎn)程表的 country_id 字段名稱不同,需要使用 OPTIONS 選項(xiàng)指定名稱映射;其次,外部表 country 定義中沒有指定 schema_name 和 table_name 選項(xiàng),表示使用該表在本地?cái)?shù)據(jù)庫中的模式名(public)和表名(country)訪問遠(yuǎn)程表。
成本評估選項(xiàng)

postgres_fdw 通過在遠(yuǎn)程服務(wù)器中執(zhí)行查詢語句返回?cái)?shù)據(jù),因此理想情況下掃描外部表的評估成本等于遠(yuǎn)程服務(wù)器執(zhí)行操作的成本加上網(wǎng)絡(luò)傳輸成本。獲取這個評估成本最可靠的方法就是詢問遠(yuǎn)程服務(wù)器并加上一些額外的消耗,但是對于簡單查詢語句,沒有必要為此執(zhí)行一次額外的遠(yuǎn)程查詢。所以 postgres_fdw 提供了以下用于成本評估的額選項(xiàng):

    use_remote_estimate,用于控制 postgres_fdw 是否提交遠(yuǎn)程 EXPLAIN 命令獲取評估成本。該選項(xiàng)支持外部表和外部服務(wù)器級別的設(shè)置,外部表的設(shè)置優(yōu)先級更高。默認(rèn)設(shè)置為 false。
    fdw_startup_cost,表示建立連接、遠(yuǎn)程服務(wù)器解析查詢和創(chuàng)建執(zhí)行計(jì)劃所需的額外成本。該選項(xiàng)支持外部服務(wù)器級別的設(shè)置,任何外部表的掃描都需要加上這個額外的啟動成本。默認(rèn)值為 100。
    fdw_tuple_cost,表示服務(wù)器之間傳輸一行數(shù)據(jù)所需的額外成本。該選項(xiàng)支持外部服務(wù)器級別的設(shè)置,可以根據(jù)網(wǎng)絡(luò)延遲的情況設(shè)置不同的參數(shù)。默認(rèn)值為 0.01。

如果 use_remote_estimate 設(shè)置為 true,postgres_fdw 從遠(yuǎn)程服務(wù)器獲取行數(shù)和成本評估,然后加上 fdw_startup_cost 和 fdw_tuple_cost;如果 use_remote_estimate 設(shè)置為 false,postgres_fdw 執(zhí)行本地行數(shù)和成本評估,然后加上 fdw_startup_cost 和 fdw_tuple_cost。

本地評估通常不太準(zhǔn)確,除非本地存儲了遠(yuǎn)程表的統(tǒng)計(jì)信息。診斷外部表執(zhí)行 ANALYZE 命令可以更新本地統(tǒng)計(jì),該命令會對遠(yuǎn)程表執(zhí)行掃描并計(jì)算和存儲統(tǒng)計(jì)信息。保存本地統(tǒng)計(jì)可以有效減少訪問遠(yuǎn)程表時的額外成本,但是如果遠(yuǎn)程表的更新很頻繁,本地統(tǒng)計(jì)很快就會失效。
遠(yuǎn)程執(zhí)行選項(xiàng)

默認(rèn)情況下,只有 WHERE 子句中的內(nèi)置操作符和函數(shù)可能會在遠(yuǎn)程服務(wù)器中執(zhí)行,非內(nèi)置函數(shù)在返回?cái)?shù)據(jù)之后在本地進(jìn)行處理。如果遠(yuǎn)程服務(wù)器中也存在這些函數(shù),并且可以產(chǎn)生相同的結(jié)果,在遠(yuǎn)程服務(wù)器中執(zhí)行可以提高查詢的性能。這個行為可以通過以下選項(xiàng)進(jìn)行控制:

    extensions,指定一個 PostgreSQL 擴(kuò)展模塊名稱的列表,這些模塊在本地和遠(yuǎn)程服務(wù)器中都需要安裝并且版本兼容。這些模塊中的 IMMUTABLE 函數(shù)和操作符可能發(fā)送到遠(yuǎn)程服務(wù)器執(zhí)行。該選項(xiàng)只能在外部服務(wù)器級別進(jìn)行設(shè)置,不支持表級設(shè)置。
    fetch_size,指定每個批次返回的行數(shù)。該選項(xiàng)支持外部表和外部服務(wù)器級別的設(shè)置,表級設(shè)置優(yōu)先級更高。默認(rèn)值為 100。

更新操作選項(xiàng)

默認(rèn)情況下,所有的外部表都支持 UPDATE 語句。postgres_fdw 通過以下選項(xiàng)控制該行為:

    updatable,指定 postgres_fdw 是否允許外部表的 INSERT、UPDATE 以及 DELETE 操作。該選項(xiàng)支持外部表和外部服務(wù)器級別的設(shè)置,表級設(shè)置優(yōu)先級更高。默認(rèn)值為 true。

顯然,如果遠(yuǎn)程表本身不允許更新,UPDATE 語句肯定會返回錯誤。該選項(xiàng)主要用于本地檢查,如果不允許更新就會返回錯誤,而不需要查詢遠(yuǎn)程服務(wù)器。本地?cái)?shù)據(jù)庫中的 information_schema 視圖將會根據(jù)該選項(xiàng)顯示外部表是否可更新,而不會檢查遠(yuǎn)程服務(wù)器。
導(dǎo)入選項(xiàng)

postgres_fdw 支持使用 IMPORT FOREIGN SCHEMA 語句為指定遠(yuǎn)程模式中的所有表或視圖創(chuàng)建外部表。如果遠(yuǎn)程表中的字段使用了自定義數(shù)據(jù)類型,本地服務(wù)器中也必須創(chuàng)建同名的兼容類型。

這種通過 IMPORT FOREIGN SCHEMA 導(dǎo)入外部模式的行為可以通過以下選項(xiàng)進(jìn)行控制:

    import_collate,控制導(dǎo)入外部表時是否包含字段的 COLLATE 屬性,默認(rèn)值為 true。如果遠(yuǎn)程服務(wù)器和本地服務(wù)器之間的排序規(guī)則名稱不同時,可以將該選項(xiàng)關(guān)閉。
    import_default,控制導(dǎo)入外部表時是否包含字段的 DEFAULT 表達(dá)式,默認(rèn)值為 false。 如果啟用了該選項(xiàng),需要注意本地服務(wù)器上的默認(rèn)值計(jì)算結(jié)果可能與遠(yuǎn)程服務(wù)器不同;例如 nextval() 函數(shù)的結(jié)果。如果默認(rèn)值表達(dá)式使用了本地服務(wù)器中不存在的函數(shù)或者操作符,所有導(dǎo)入操作都將失敗。
    import_not_null,控制導(dǎo)入外部表時是否包含字段的 NOT NULL 約束,默認(rèn)值為 true。

除了 NOT NULL 之外的其他約束不會從遠(yuǎn)程表中導(dǎo)入到外部表定義。雖然 PostgreSQL 支持外部表的 CHECK 約束,但是不會自動導(dǎo)入該約束,因?yàn)楸镜睾瓦h(yuǎn)程服務(wù)器中的約束表達(dá)式的結(jié)果可能不一致。任何 CHECK 約束行為的不一致性都可能導(dǎo)致難以發(fā)現(xiàn)的查詢優(yōu)化問題如果想要導(dǎo)入 CHECK 約束,必須手動執(zhí)行操作,并且仔細(xì)驗(yàn)證語義的一致性。

分區(qū)結(jié)構(gòu)中的分區(qū)不會被導(dǎo)入;分區(qū)表會導(dǎo)入,除非它又是其他表的分區(qū)。因?yàn)橥ㄟ^分區(qū)表(分區(qū)層級中的根節(jié)點(diǎn))可以訪問所有的數(shù)據(jù),這種方式可以訪問表中的所有數(shù)據(jù),不需要創(chuàng)建額外的對象。
連接管理

postgres_fdw 在第一次使用外部表時建立一個遠(yuǎn)程服務(wù)器連接,然后在同一個會話中保持并重用該連接。如果使用了多個用戶映射訪問遠(yuǎn)程服務(wù)器,每個用戶映射都會創(chuàng)建一個連接。
事務(wù)管理

如果查詢過程中引用了任何遠(yuǎn)程表,并且沒有打開與本地事務(wù)對應(yīng)的遠(yuǎn)程事務(wù)時,postgres_fdw 會在遠(yuǎn)程服務(wù)器中打開一個事務(wù)。遠(yuǎn)程事務(wù)隨著本地事務(wù)一起提交或者中斷。保存點(diǎn)的管理與此類似,也會在遠(yuǎn)程服務(wù)器中創(chuàng)建相應(yīng)的保存點(diǎn)。

如果本地事務(wù)使用了 SERIALIZABLE 隔離級別,遠(yuǎn)程事務(wù)也使用 SERIALIZABLE 隔離級別;否則遠(yuǎn)程事務(wù)使用 REPEATABLE READ 隔離級別。這個設(shè)置可以確保一個查詢掃描多個遠(yuǎn)程表時,可以獲得快照一致性的結(jié)果。在同一個事務(wù)中,多次查詢遠(yuǎn)程表將會返回相同的結(jié)果,即使其他事務(wù)修改了遠(yuǎn)程表中的數(shù)據(jù)。這個行為對于本地事務(wù)使用 SERIALIZABLE 或者 REPEATABLE READ 隔離級別時是預(yù)期行為,但是對于 READ COMMITTED 隔離級別可能有些奇怪;將來的 PostgreSQL 版本中可能會修改這些規(guī)則。

目前,postgres_fdw 還不支持遠(yuǎn)程事務(wù)的兩階段提交。
遠(yuǎn)程查詢優(yōu)化

postgres_fdw 可以嘗試通過優(yōu)化遠(yuǎn)程查詢減少網(wǎng)絡(luò)傳輸?shù)臄?shù)據(jù)量。這個功能通過將 WHERE 子句發(fā)送到遠(yuǎn)程服務(wù)器執(zhí)行,以及不返回查詢不需要的字段來實(shí)現(xiàn)。為了降低錯誤執(zhí)行查詢的風(fēng)險,只有當(dāng) WHERE 子句中只涉及內(nèi)置或者外部服務(wù)器 extensions 選項(xiàng)指定的模塊中的數(shù)據(jù)類型、操作符和函數(shù)時才會發(fā)送到遠(yuǎn)程服務(wù)器。同時,操作符和函數(shù)必須指定 IMMUTABLE 屬性。

對于 UPDATE 或者 DELETE 操作,如果不存在無法發(fā)送的 WHERE 子句、本地連接查詢、本地行級 BEFORE 或者 AFTER 觸發(fā)器、目標(biāo)表上的存儲計(jì)算列、父級視圖上的 CHECK OPTION 約束,postgres_fdw 可以嘗試將整個語句發(fā)送到遠(yuǎn)程服務(wù)器執(zhí)行。對于 UPDATE,賦值表達(dá)式中只能使用內(nèi)置數(shù)據(jù)類型、IMMUTABLE 操作符或者函數(shù),從而減少錯誤執(zhí)行的風(fēng)險。

如果 postgres_fdw 遇到了同一個遠(yuǎn)程服務(wù)器上的外部表連接查詢,會將整個連接操作發(fā)送到遠(yuǎn)程服務(wù)器,除非它認(rèn)為單獨(dú)返回每個表的效率更高,或者表的引用涉及了不同的用戶映射。發(fā)送 JOIN 子句時,采用和上述 WHERE 子句相同的策略。

實(shí)際發(fā)送到遠(yuǎn)程服務(wù)器的查詢可以通過 EXPLAIN VERBOSE 語句進(jìn)行查看。
遠(yuǎn)程查詢執(zhí)行環(huán)境

對于 postgres_fdw 打開的遠(yuǎn)程會話,search_path 參數(shù)被設(shè)置為 pg_catalog,因此默認(rèn)只能看見內(nèi)置的對象,除非使用模式名限定。這種行為對于postgres_fdw 提交的查詢而言沒有問題,因?yàn)樗偸菚付J矫?。不過,它可能對遠(yuǎn)程表上的觸發(fā)器函數(shù)或者規(guī)則函數(shù)的執(zhí)行帶來問題。例如,當(dāng)遠(yuǎn)程表是一個視圖時,該視圖中的任何函數(shù)都會在該搜索路徑中查找,結(jié)果可能是不存在。推薦對于這些函數(shù)使用時加上模式名限定,或者在創(chuàng)建函數(shù)時使用 SET search_path 選項(xiàng)設(shè)置搜索路徑。

另外,postgres_fdw 還會在建立遠(yuǎn)程會話時對以下參數(shù)進(jìn)行設(shè)置:

    TimeZone 設(shè)置為 UTC;
    DateStyle 設(shè)置為 ISO;
    IntervalStyle 設(shè)置為 postgres;
    extra_float_digits 設(shè)置為 (遠(yuǎn)程服務(wù)器為 PostgreSQL 9.0 以上版本) 或者 2(更早版本)。

這些參數(shù)通常不會像 search_path 一樣導(dǎo)致問題,但同樣可以使用函數(shù)的 SET 選項(xiàng)進(jìn)行設(shè)置。

不推薦通過會話級別的設(shè)置覆蓋這些參數(shù)的默認(rèn)值,這樣可能導(dǎo)致 postgres_fdw 運(yùn)行錯誤。