使用 postgres_fdw 和 dblink 擴充功能在 OCI 資料庫中啟用 PostgreSQL 的跨資料庫存取

簡介

Oracle Cloud Infrastructure Database with PostgreSQL (OCI Database with PostgreSQL) 支援使用 postgres_fdw 和 dblink 等擴充功能進行資料庫間連線。這些擴充功能可讓您跨 PostgreSQL 資料庫順暢地存取和共用資料，適用於分析、聯合查詢或舊版整合案例。

什麼是 dblink？

DBlink 是 PostgreSQL 擴充功能，可直接在遠端 PostgreSQL 資料庫上執行 SQL 敘述句。它會以資料列形式傳回結果集，或在遠端伺服器上執行命令。

什麼是 postgres_fdw？

postgres_fdw (外來資料包裝程式) 是 PostgreSQL 擴充功能，可讓資料庫連線遠端 PostgreSQL 伺服器中的表格和查詢表格，就像是本機表格一樣。與 dblink 不同，它會將遠端表格對應為本機物件，並提供更多的程序控制。

在本教學課程中，我們將探討 OCI 資料庫中的 postgres_fdw 和 dblink 擴充功能與 PostgreSQL 執行處理，以及每個執行處理的範例。

目標

使用位於不同虛擬雲端網路 (VCN) 和不同區域的 PostgreSQL 資料庫系統，在兩個 OCI 資料庫之間啟用安全可靠的網路通訊。此設定可確保資料庫連線能夠跨 VCN 界限順暢運作。包括設定用於路由的本機對等互連閘道 (LPG)，以及使用專用檢視設定 DNS 解析。目標是支援 postgres_fdw 或 dblink 等資料庫擴充功能的跨 VCN/ 跨區域連線。

必備條件

• 若要在具有 PostgreSQL 資料庫的 OCI 資料庫中建立及管理 postgres_fdw 或 dblink 擴充功能，使用者必須具備：
  • 資料庫存取權限。
  • 授予使用者的 OCI_ADMIN_ROLE 。
• 具備 PostgreSQL 資料庫系統 (來源和目標) 的 OCI 資料庫必須具備網路連線。

工作 1：使用 PostgreSQL 在 OCI 資料庫中啟用 postgres_fdw 和 dblink 擴充功能

1. 從 OCI 主控台啟用擴充。

   1. 建立配置檔案，並啟用 dblink 或 postgres_fdw 副檔名。

      

   2. 將組態套用至資料庫系統。

      

2. 在資料庫層次啟用擴充功能。

   在 OCI 主控台中啟用擴充之後，請連線至資料庫並執行下列 SQL 命令。

    CREATE EXTENSION postgres_fdw;
   
    CREATE EXTENSION dblink;
   

工作 2：設定不同 VCN 中資料庫系統之間的網路連線

若要啟用兩個 OCI 資料庫與位於不同 VCN 的 PostgreSQL 資料庫系統之間的通訊，請依照下列步驟進行：

1. 建立本地對等互連閘道 (LPG) 。

   在每個 VCN 中建立 LPG 以建立對等互連連線。

2. 設定路由表。

   1. 針對每個 VCN，更新與 LPG 關聯的路由表以包含路由規則。

   2. 將目的地 CIDR 區塊設為其他目標 VCN 的位址範圍，然後指定下一個躍點作為對應的 LPG。

3. 使用專用檢視設定 DNS 解析。

   1. 前往 OCI 主控台，搜尋專用檢視以存取這兩個 VCN 的 DNS 組態。

   2. 開啟每個 VCN 的專用檢視並建立區域項目。

4. 為每個資料庫系統建立區域記錄。

   在每個 VCN 的專用視觀表中，新增代表另一個 VCN 中資料庫系統之完整網域名稱 (FQDN) 的區域。

5. 新增 DNS 記錄。

   1. 按一下新區域項目旁的三個點圖示，或按一下 FQDN 連結。

   2. 選取管理記錄，然後按一下新增記錄。

   3. 選取類型 A (IPv4 位址) ，將 TTL 設為 500 ，然後在相反的 VCN 中輸入目標資料庫系統的主要端點 IP 位址。

   4. 按一下新增記錄，然後按一下發布變更。

工作 3：在不同 VCN 和不同區域中設定資料庫系統之間的網路連線

啟用兩個 OCI 資料庫與位於不同 VCN 和不同區域的 PostgreSQL 資料庫系統之間的通訊。

1. 建立動態路由閘道 (DRG) 。

   在每個區域建立一個 DRG，並將其連附至個別的 VCN。

2. 設定路由表。

   1. 針對每個 VCN，更新與 DRG 關聯的路由表。

   2. 新增路由規則，其中包含其他 VCN 的目的地 CIDR 區塊，指定下一個躍點作為連附的 DRG。

3. 使用專用檢視設定 DNS 解析。

   1. 前往 OCI 主控台，搜尋專用檢視以存取每個 VCN 的 DNS 設定值。

   2. 開啟每個 VCN 的專用檢視，然後建立區域項目。

4. 為每個資料庫系統建立區域記錄。

   在每個 VCN 的專用視觀表中，在另一個 VCN 中新增代表資料庫系統 FQDN 的區域。

5. 新增 DNS 記錄。

   1. 按一下新區域項目旁的三個點圖示，或按一下 FQDN 連結。

   2. 選取管理記錄，然後按一下新增記錄。

   3. 選取類型 A (IPv4 位址) ，將 TTL 設為 500 ，然後輸入相反資料庫系統的主要端點 IP 位址。

   4. 按一下新增記錄，然後按一下發布變更。

工作 4：以範例設定 postgres_fdw 副檔名

1. 建立外部資料包裝程式 (FDW) 伺服器。

   CREATE SERVER 命令會使用 postgres_fdw 包裝程式定義名為 fdw_serv 的外部伺服器。它會指定連線詳細資訊，例如來源 PostgreSQL 資料庫的主機、資料庫名稱和連接埠。這會作為存取目標 PostgreSQL 執行處理內來源資料的邏輯參照。

    CREATE SERVER <fdw_servername> FOREIGN DATA WRAPPER <fdw_servername> OPTIONS (host 'primary.xxxxxx.FQDN_Remote_source_DBSystem.... oci.oraclecloud.com', dbname 'remote_source_dbname', port '5432');
   

2. 對應使用者。

   CREATE USER MAPPING 命令會將目標 PostgreSQL 使用者 (arvindya) 與來源資料庫證明資料建立關聯。這可讓目標伺服器連線至定義的外部伺服器時進行目標認證。這可確保在來源伺服器上正確授權執行之目標的查詢。

    CREATE USER MAPPING FOR <user_name> SERVER <fdw_servername> OPTIONS (user <user_name>, password <'user_password'>);
   

3. 匯入結構來源。

   IMPORT FOREIGN SCHEMA 命令會將所有表格從來源伺服器的公用綱要匯入目標資料庫作為外部表格。這些表格會以本機方式顯示，但會即時從來源資料庫擷取資料。

    IMPORT FOREIGN SCHEMA <schema_name> FROM SERVER <fdw_server_name> INTO public;
   

舉例而言：

-- Create extension
CREATE EXTENSION postgres_fdw;

-- Create FDW Server
CREATE SERVER fdw_serv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    host 'primary.xxxxxx.Remote Source DBSystem FQDN.....xxxxx.oci.oraclecloud.com',
    dbname 'source',
    port '5432'
);

-- User Mapping
CREATE USER MAPPING FOR arvindya SERVER fdw_serv OPTIONS (
    user '<user_name>',
    password '<user_password>'
);

-- Import Schema from source
IMPORT FOREIGN SCHEMA public FROM SERVER fdw_serv INTO public;

-- Validate Data
SELECT * FROM emp;

工作 5：以範例設定 dblink 擴充功能

dblink_connect 函數會使用提供的連線字串，建立從目標 PostgreSQL 資料庫到來源 PostgreSQL 資料庫的連線。它包含像資料庫名稱、主機、連接埠、使用者名稱及密碼等參數。連線是以唯一的名稱 (myconn) 來識別，此名稱用於後續的 dblink 作業。成功的連線會傳回確定，表示遠端查詢的整備度。

SELECT dblink_connect ('Conn_name', 'dbname=<Remote_source_DBName> port=5432 host=primary.xxxxxx..Remote_source_DbSystem FQDN..xxxx.oci.oraclecloud.com user=<user_name> password=<'user_password'>);

舉例而言：

CREATE DATABASE target;
\c target
CREATE EXTENSION dblink;

-- Establish Connection
SELECT dblink_connect ('myconn', 'dbname=source port=5432 host=primary.xxxxxx..Remote_source_DbSystem FQDN..xxxx.oci.oraclecloud.com user=<user_name> password=<user_password>');

-- Select from Source
SELECT * FROM dblink('myconn', 'SELECT * FROM testing') AS t(a int);

-- Execute SQL remotely
SELECT * FROM dblink_exec('myconn', 'create table dblinking_table (id int)');
SELECT * FROM dblink_exec('myconn', 'insert into dblinking_table values(generate_series(1,10))');
SELECT * FROM dblink_exec('myconn', 'delete from dblinking_table where id <= 5');

-- Final Validation
SELECT * FROM dblink('myconn', 'SELECT * FROM dblinking_table') AS t(a int);

相關連結

• DBLink

• Postgres_fdw

• 啟用資料庫的擴充功能

• 建立本機對等互連閘道

• 使用舊版 DRG 的遠端 VCN 對等互連

• 使用 PostgreSQL 進行 OCI 資料庫疑難排解

確認

• 作者 - Arvind Yadav (主要會員技術人員)

其他學習資源

在 docs.oracle.com/learn 上探索其他實驗室，或在 Oracle Learning YouTube 頻道上存取更多免費學習內容。此外，請造訪 education.oracle.com/learning-explorer 以成為 Oracle Learning Explorer。

如需產品文件，請造訪 Oracle Help Center 。

---

標題與著作權資訊

Enable Inter-Database Access in OCI Database with PostgreSQL using the postgres_fdw and dblink Extensions

G40422-02

Copyright ©2025,

Oracle 和 (或) 其關係企業。