Aurora PostgreSQL 相容與遠端 PostgreSQL 資料庫的整合

本節討論使用 postgres_fdw（外部資料包裝函式）擴充功能或 dblink功能的 Amazon Aurora PostgreSQL 相容版本與遠端 PostgreSQL 資料庫的整合。postgres_fdw 模組提供與遠端 PostgreSQL 型資料庫互動的聯合查詢功能。遠端資料庫可以在 Amazon EC2 或內部部署上受管或自我管理。postgres_fdw 延伸模組適用於 PostgreSQL 和 Aurora PostgreSQL 相容的所有目前支援的 Amazon Relational Database Service (Amazon RDS) 版本。

您可以使用 postgres_fdw擴充功能，從遠端 PostgreSQL 資料庫存取和查詢資料，就像它們是本機資料表一樣。postgres_fdw 擴充功能也支援下列項目：

從執行不同版本的外部 PostgreSQL 伺服器存取資料的跨版本相容性。
交易管理，可在跨本機和外部 PostgreSQL 伺服器執行操作時，協助確保資料一致性和完整性。
當您跨多個外部 PostgreSQL 伺服器執行操作時，提供原子性 (ACID 交易的屬性）和隔離保證的分散式交易。這有助於確保交易中的所有操作都已遞交或不遞交，以保持資料一致性和完整性。

雖然dblink模組提供與遠端 PostgreSQL 資料庫互動的方式，但它不支援分散式交易或其他進階功能。如果您需要更進階的功能，請考慮改用 postgres_fdw擴充功能。postgres_fdw 擴充功能提供更多整合和最佳化功能。

postgres_fdw 使用案例和高階步驟

Aurora PostgreSQL 相容postgres_fdw擴充功能用量支援下列使用案例和案例：

聯合查詢和資料整合 ‒ 在單一 Aurora PostgreSQL 相容執行個體中查詢和合併來自多個 PostgreSQL 資料庫的資料
卸載讀取工作負載 ‒ 連線至外部 PostgreSQL 伺服器的讀取複本、卸載大量讀取的工作負載，以及改善查詢效能
跨資料庫操作 ‒ 跨多個 PostgreSQL 資料庫執行 DELETE、、和 INSERT UPDATECOPY操作，啟用跨資料庫資料處理和維護任務

若要設定 postgres_fdw，請使用下列高階步驟：

使用 PostgreSQL 用戶端連線至 Aurora PostgreSQL 相容叢集，並建立postgres_fdw擴充功能：
```
CREATE EXTENSION postgres_fdw;
```
此擴充功能提供連線至遠端 PostgreSQL 資料庫的功能。
使用 CREATE SERVER命令建立名為 my_fdw_target 的外部伺服器。此伺服器代表您要連線的遠端 PostgreSQL 資料庫。指定資料庫名稱、主機名稱和 SSL 模式做為此伺服器的選項。
確保已備妥必要的安全群組和網路組態，以允許 Aurora PostgreSQL 相容連線到遠端 PostgreSQL 資料庫。

如果遠端資料庫託管在內部部署，您可能需要設定虛擬私有網路 (VPN) 或 AWS Direct Connect 連線。

執行以下命令：
```
CREATE SERVER my_fdw_target Foreign Data Wrapper postgres_fdw OPTIONS (DBNAME 'postgres', HOST 'SOURCE_HOSTNAME', SSLMODE 'require');
```
為my_fdw_target伺服器上的使用者建立dbuser使用者映射。此映射會將本機 Aurora PostgreSQL 相容執行個體上的dbuser使用者和密碼與遠端資料庫上的對應使用者建立關聯。
```
CREATE USER MAPPING FOR dbuser SERVER my_fdw_target OPTIONS (user 'DBUSER', password 'PASSWORD');
```
此步驟是驗證並提供遠端資料庫存取權的必要步驟。
使用您先前設定的my_fdw_target伺服器和使用者映射建立名為 customer_fdw 的外部資料表：
```
CREATE FOREIGN TABLE customer_fdw( id int, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');
```
customer_fdw 資料表會對應至my_fdw_target伺服器所指定遠端資料庫中的customers資料表。外部資料表的結構與遠端資料表相同，因此您可以像本機資料表一樣與遠端資料互動。

您可以在customer_fdw外部資料表上執行各種資料處理操作，例如 INSERT、 UPDATE和 SELECT查詢。指令碼示範插入新資料列並更新現有資料列、刪除記錄，以及透過customer_fdw外部資料表截斷遠端customers資料表中的資料表：


INSERT INTO customer_fdw values ( 1, 'Test1', 'Test1@email.com', 'LMS1', '888888888');
INSERT INTO customer_fdw values ( 2, 'Test2', 'Test2@email.com', 'LMS2', '999999999');
INSERT INTO customer_fdw values ( 3, 'Test3', 'Test3@email.com', 'LMS3', '111111111');
UPDATE customer_fdw set contactnumber = '123456789' where id = 2;
DELETE FROM customer_fdw where id = 1;
TRUNCATE TABLE customer_fdw;

您可以使用 EXPLAIN陳述式來分析customer_fdw資料表上查詢的查詢計畫，以驗證 SQL SELECT查詢計畫：
```
EXPLAIN select * from customer_fdw where id =1;
```
這可協助您了解查詢的執行方式，以及如何將其最佳化。如需使用 EXPLAIN陳述式的詳細資訊，請參閱 AWS 方案指引中的最佳化 PostgreSQL 查詢效能。
若要從遠端資料庫將多個資料表匯入本機結構描述，請使用 IMPORT FOREIGN SCHEMA命令：
```
CREATE SCHEMA public_fdw;
IMPORT FOREIGN SCHEMA public LIMIT TO (employees, departments)
    FROM SERVER my_fdw_target INTO public_fdw;
```
這會為public_fdw結構描述中的指定資料表建立本機外部資料表。在此範例中，特定資料表是員工和部門。
若要將必要的許可授予特定資料庫使用者，以便他們可以存取和使用 FDW 和相關聯的外部伺服器，請執行下列命令：
```
GRANT USAGE ON FOREIGN SERVER my_fdw_target TO targetdbuser;
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO targetdbuser;
```
當多個使用者需要存取外部資料包裝函式所協助的外部資料表時，此步驟會很有幫助。

使用外部資料表時，請注意下列限制：

從遠端來源存取資料可能會導致資料傳輸成本和網路延遲造成的效能額外負荷。對於需要在 Aurora PostgreSQL 相容執行個體與遠端資料來源之間進行大量資料傳輸的大型資料集或查詢，效能問題可能明顯可見。
在涉及視窗函數等功能的複雜查詢中，遞迴查詢可能無法如預期運作，也可能不受支援。
目前不支援密碼加密。實作控制項，以確保只有授權的使用者才能存取 FDWs並從遠端資料庫擷取資料。

無法在外部資料表上定義主索引鍵限制條件，如下列資料表建立指令碼嘗試所示：


CREATE FOREIGN TABLE customer_fdw2( id int primary key, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');
Primary keys cannot be defined on Foreign table

外部資料表不支援 INSERT 陳述式的 ON CONFLICT子句，如下列範例所示：


INSERT INTO customer_fdw (id, name, emailid, projectname, contactnumber) VALUES
(1, 'test1', 'test@email.com', 'LMS', 11111111 ),
(3, 'test3', 'test3@email.com', 'LMS', 22222222 )
ON CONFLICT (id) DO UPDATE 
SET name = EXCLUDED.name;
On Conflict option doesnot work.

清除

若要清除建立的物件，包括捨棄postgres_fdw擴充功能、my_fdw_target伺服器、使用者映射和外部資料表，請執行下列命令：


DROP FOREIGN TABLE customer_fdw;
DROP USER MAPPING for postgres;
DROP SERVER  my_fdw_target;
DROP EXTENSION postgres_fdw cascade;

使用 dblink 建立連線

dblink 模組函數提供另一種方法來建立連線，並在遠端 PostgreSQL 資料庫上執行 SQL 陳述式。dblink 解決方案是對遠端資料庫執行一次性查詢或操作的更簡單且更靈活的方式。對於涉及大規模資料整合、效能最佳化和資料完整性需求的更複雜案例，建議使用 postgres_fdw。

使用 dblink包含下列高階步驟：

建立dblink擴充功能：
```
CREATE EXTENSION dblink;
```
此擴充功能提供連線至遠端 PostgreSQL 資料庫的功能。

若要建立與遠端 PostgreSQL 資料庫的連線，請使用 dblink_connect函數：


SELECT dblink_connect('myconn', 'dbname=postgres port=5432 host=SOURCE_HOSTNAME user=postgres password=postgres');

連線至遠端 PostgreSQL 資料庫後，請使用 dblink函數在遠端資料庫上執行 SQL 陳述式：
```
SELECT  FROM dblink('myconn', 'SELECT col1, col2 FROM remote_table') AS remote_data(col1 int, col2 text);
```
此查詢會使用 myconn連線在遠端資料庫上執行 SELECT * FROM remote_table陳述式。查詢會將結果擷取至具有資料欄 col1和的本機暫存資料表col2。
您也可以使用 dblink_exec函數，在遠端資料庫上執行非查詢陳述式DELETE，例如 INSERTUPDATE、或：
```
SELECT dblink_exec('myconn', 'INSERT INTO remote_table VALUES (1, ''value'')');
```

您的瀏覽器已停用或無法使用 Javascript。

您必須啟用 Javascript，才能使用 AWS 文件。請參閱您的瀏覽器說明頁以取得說明。

文件慣用形式

Aurora PostgreSQL 相容整合

異質資料庫