本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
使用 Amazon Aurora PostgreSQL 支援的外部資料包裝函式
外部資料包裝函式 (FDW) 是一種特定類型的擴充功能,可提供對外部資料的存取。例如,oracle_fdw
擴充功能可讓您的 Aurora PostgreSQL 資料庫執行個體 使用 Oracle 資料庫。
在下文中,您可以了解幾個受支援的 PostgreSQL 外部資料包裝函式的資訊。
主題
使用 log_fdw 擴充功能存取使用 SQL 的資料庫日誌
Aurora PostgreSQL 叢集支援 log_fdw
擴充功能,讓您可以使用 SQL 介面存取資料庫引擎日誌。log_fdw
擴充功能推出兩個新函數,可讓您輕鬆為資料庫日誌建立外部資料表:
-
list_postgres_log_files
– 列出資料庫日誌目錄中的檔案和檔案大小 (以位元組為單位)。 -
create_foreign_table_for_log_file(table_name text, server_name text, log_file_name text)
– 在目前資料庫中為指定的檔案建立外部資料表。
log_fdw
建立的所有函數皆為 rds_superuser
所擁有。rds_superuser
角色的成員可以將這些函數的存取權授予其他資料庫使用者。
依預設,日誌檔案由 Amazon Aurora 以 stderr
(標準錯誤) 格式產生,如 log_destination
參數中所指定。此參數只有兩個選項:stderr
和 csvlog
(逗號分隔值,CSV)。若將 csvlog
選項新增至參數,Amazon Aurora 將同時產生 stderr
和 csvlog
日誌。這可能會影響資料庫叢集的儲存容量,因此您需要了解影響日誌處理的其他參數。如需更多詳細資訊,請參閱 設定日誌目標 (stderr、csvlog)。
產生 csvlog
日誌的一個好處為 log_fdw
延伸允許您建置外部資料表,並將資料整齊分割成數個資料欄。為此,您的執行個體需要與自訂資料庫參數群組關聯,則您可變更 log_destination
的設定。如需如何執行作業的資訊,請參閱 Amazon Aurora 的參數組 RDS。
下列範例假設 log_destination
參數包括 cvslog
。
使用 log_fdw 擴充功能
-
安裝
log_fdw
擴充功能。postgres=>
CREATE EXTENSION log_fdw;
CREATE EXTENSION
-
建立日誌伺服器做為外部資料包裝函數。
postgres=>
CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw;
CREATE SERVER
-
叢日誌檔案清單全選。
postgres=>
SELECT * FROM list_postgres_log_files() ORDER BY 1;
範例回應如下所示。
file_name | file_size_bytes ------------------------------+----------------- postgresql.log.2023-08-09-22.csv | 1111 postgresql.log.2023-08-09-23.csv | 1172 postgresql.log.2023-08-10-00.csv | 1744 postgresql.log.2023-08-10-01.csv | 1102 (4 rows)
-
針對選取的檔案,建立只有單一 'log_entry' 資料欄的資料表。
postgres=>
SELECT create_foreign_table_for_log_file('my_postgres_error_log', 'log_server', 'postgresql.log.2023-08-09-22.csv');
除了目前存在的資料表之外,回應不提供任何其他詳細資訊。
----------------------------------- (1 row)
-
選取日誌檔案的範例。以下程式碼會擷取日誌時間和錯誤訊息描述。
postgres=>
SELECT log_time, message FROM my_postgres_error_log ORDER BY 1;
範例回應如下所示。
log_time | message ----------------------------------+--------------------------------------------------------------------------- Tue Aug 09 15:45:18.172 2023 PDT | ending log output to stderr Tue Aug 09 15:45:18.175 2023 PDT | database system was interrupted; last known up at 2023-08-09 22:43:34 UTC Tue Aug 09 15:45:18.223 2023 PDT | checkpoint record is at 0/90002E0 Tue Aug 09 15:45:18.223 2023 PDT | redo record is at 0/90002A8; shutdown FALSE Tue Aug 09 15:45:18.223 2023 PDT | next transaction ID: 0/1879; next OID: 24578 Tue Aug 09 15:45:18.223 2023 PDT | next MultiXactId: 1; next MultiXactOffset: 0 Tue Aug 09 15:45:18.223 2023 PDT | oldest unfrozen transaction ID: 1822, in database 1 (7 rows)
使用 postgres_fdw 擴充功能存取外部資料
您可以使用 postgres_fdw
若要使用 postgres_fdw 來存取遠端資料庫伺服器
安裝 postgres_fdw 擴充功能。
CREATE EXTENSION postgres_fdw;
使用 CREATE SERVER 建立外部資料伺服器。
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
建立使用者對應,找出要使用於遠端伺服器的角色。
CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'foreign_user', password 'password');
建立一個資料表,其對應至遠端伺服器上的資料表。
CREATE FOREIGN TABLE foreign_table ( id integer NOT NULL, data text) SERVER foreign_server OPTIONS (schema_name 'some_schema', table_name 'some_table');
使用 mysql_fdw 擴充功能處理 MySQL 資料庫
若要從 Aurora PostgreSQL 資料庫叢集存取相容於 MySQL 的資料庫,您可以安裝並使用 mysql_fdw
擴充功能。此外部資料包裝函數可讓您使用 MySQL、Aurora MySQL、MariaDB 和其他相容於 MySQL 的資料庫的 RDS。從 Aurora PostgreSQL 資料庫叢集到 MySQL 資料庫的連線是在最大努力的基礎上加密的,具體取決於用戶端和伺服器的組態。但是,如有需要,您可以強制執行加密。如需更多詳細資訊,請參閱 搭配此擴充功能使用傳輸中加密。
Amazon Aurora PostgreSQL 版本 15.4、14.9、13.12、12.16 及更高版本支援 mysql_fdw
擴充功能。它支援從 RDS for PostgreSQL DB 對相容於 MySQL 的資料庫執行個體上的資料表進行選擇、插入、更新和刪除。
主題
設定 Aurora PostgreSQL 資料庫以使用 mysql_fdw 擴充功能
在您的 Aurora PostgreSQL 資料庫叢集上設定 mysql_fdw
擴充功能包括在資料庫叢集中載入擴充功能,然後建立與 MySQL資料庫執行個體的連線點。針對此任務,您必須有以下關於 MySQL 資料庫執行個體的詳細內容:
主機名稱或端點。如果是 Aurora PostgreSQL 資料庫叢集,您可以使用主控台尋找端點。選擇 Connectivity & security (連線和安全) 索引標籤,然後查看「端點和連線埠」區段。
連線埠號碼。MySQL 的預設連線埠號為 3306。
資料庫的名稱。資料庫識別符。
您也必須提供 MySQL 連線埠 3306 的安全群組或存取控制清單 (ACL) 的存取權限。Aurora PostgreSQL 資料庫叢集和 Aurora MySQL 資料庫叢集 都需要存取連接埠 3306。如果未正確設定存取權限,當您嘗試連線至相容於 MySQL 的資料表時,會看到類似以下的錯誤訊息:
ERROR: failed to connect to MySQL: Can't connect to MySQL server on 'hostname
.aws-region
.rds.amazonaws.com:3306' (110)
在以下程序中,您 (作為 rds_superuser
帳戶) 建立外部伺服器。然後,您將外部伺服器的存取權限授予特定使用者。然後,這些使用者建立自己的映射到適合的 MySQL 使用者帳戶以使用 MySQL 資料庫執行個體。
使用 mysql_fdw 存取 MySQL 資料庫伺服器
使用有
rds_superuser
角色的帳戶連線到您的 PostgreSQL 資料庫執行個體。如果您在建立 Aurora PostgreSQL 資料庫叢集時接受了預設值,則使用者名稱為postgres
,而且您可以使用psql
命令列工具進行連線,如下所示:psql --host=
your-DB-instance
.aws-region
.rds.amazonaws.com --port=5432 --username=postgres –-password安裝
mysql_fdw
擴充功能,如下所示:postgres=>
CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
在 Aurora PostgreSQL 資料庫叢集上安裝擴充功能後,您可以設定提供連線至 MySQL 資料庫的外部伺服器。
建立外部伺服器
在 Aurora PostgreSQL 資料庫叢集上執行這些任務。這些步驟假設您以具有 rds_superuser
權限 (例如 postgres
) 的使用者進行連線。
在 Aurora PostgreSQL 資料庫叢集中建立外部伺服器:
postgres=>
CREATE SERVER
mysql-db
FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'db-name.111122223333
.aws-region
.rds.amazonaws.com', port '3306');CREATE SERVER
將適當的使用者存取權限授予外部伺服器。這些使用者應該是非管理員使用者,亦即沒有
rds_superuser
角色。postgres=>
GRANT USAGE ON FOREIGN SERVER
mysql-db
touser1
;GRANT
PostgreSQL 使用者透過外部伺服器建立和管理自己的 MySQL 資料庫連線。
範例:使用 Aurora PostgreSQL 中的 Aurora MySQL 資料庫
假設您在 Aurora PostgreSQL 資料庫執行個體上有一個簡單資料表。您的 Aurora PostgreSQL 使用者想要查詢 (SELECT
)、INSERT
、UPDATE
和 DELETE
該資料表上的項目。假設 mysql_fdw
擴充功能已在 RDS for PostgreSQL 資料庫執行個體上建立,詳情如上述程序所述。作為具有 rds_superuser
權限的使用者,在您連線到 RDS 的 PostgreSQL 資料庫執行個體後,可繼續執行以下步驟。
在 Aurora PostgreSQL 資料庫執行個體中建立外部伺服器:
test=>
CREATE SERVER
mysqldb
FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'your-DB
.aws-region
.rds.amazonaws.com', port '3306');CREATE SERVER
授與使用量給沒有
rds_superuser
許可的使用者,例如user1
:test=>
GRANT USAGE ON FOREIGN SERVER mysqldb TO user1;
GRANT
以
user1
身分連線,然後建立一個 MySQL 使用者的映射:test=>
CREATE USER MAPPING FOR
user1
SERVER mysqldb OPTIONS (username 'myuser
', password 'mypassword
');CREATE USER MAPPING
建立 MySQL 資料表的外部資料表連結。
test=>
CREATE FOREIGN TABLE
mytab
(a int, b text) SERVER mysqldb OPTIONS (dbname 'test', table_name '');CREATE FOREIGN TABLE
對外部資料表執行簡單查詢:
test=>
SELECT * FROM mytab;
a | b ---+------- 1 | apple (1 row)
您可以從 MySQL 資料表新增、變更和移除資料。例如:
test=>
INSERT INTO mytab values (2, 'mango');
INSERT 0 1
再次執行
SELECT
查詢以查看結果:test=>
SELECT * FROM mytab ORDER BY 1;
a | b ---+------- 1 | apple 2 | mango (2 rows)
搭配此擴充功能使用傳輸中加密
預設情況下,從 Aurora PostgreSQL 到 MySQL 的連線會使用傳輸中加密 (TLS/SSL)。但是,當用戶端和伺服器組態不同時,連線會回退到未加密。您可以在 RDS for MySQL 使用者帳戶上指定 REQUIRE SSL
選項,強制加密所有的對外連線。此方法也適用於 MariaDB 和 Aurora MySQL 使用者帳戶。
當 MySQL 使用者帳戶設定為 REQUIRE SSL
,如果無法建立安全連線,連線嘗試將會失敗。
若要強制加密現有 MySQL 資料庫使用者帳戶,可使用 ALTER USER
命令。語法依據 MySQL 版本而異,如下表所示。如需詳細資訊,請參閱《MySQL 參考手冊》中的 ALTER USER
MySQL 5.7、MySQL 8.0 | MySQL 5.6 |
---|---|
|
|
如需 mysql_fdw
擴充功能的詳細資訊,請參閱 mysql_fdw
使用 oracle_fdw 擴充功能處理 Oracle 資料庫
若要從您的 Aurora PostgreSQL 資料庫叢集 存取 Oracle 資料庫,您可以安裝並使用 oracle_fdw
擴充功能。此擴充功能是 Oracle 資料庫的外部資料包裝函式。若要進一步了解此擴充功能,請參閱 oracle_fdw
PostgreSQL 12.7 (Amazon Aurora PostgreSQL 版本 4.2) 及更新版本支援 oracle_fdw
擴充功能。
主題
開啟 oracle_fdw 擴充功能
如要使用 oracle_fdw 擴充功能,請執行下列程序。
如要開啟 oracle_fdw 擴充功能
-
使用具有
rds_superuser
許可的帳戶執行下列命令。CREATE EXTENSION oracle_fdw;
範例:使用 Amazon RDS for Oracle Database 的外部伺服器連結
下列範例顯示使用連結至 Amazon RDS for Oracle 資料庫的外部伺服器。
若要建立連結至 RDS for Oracle 資料庫的外部伺服器
-
請注意以下 RDS for Oracle 資料庫執行個體的事項:
-
端點
-
連線埠
-
資料庫名稱
-
-
建立外部伺服器。
test=>
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//
endpoint
:port
/DB_name
');CREATE SERVER
-
授予使用權限給沒有
rds_superuser
權限的使用者,例如user1
。test=>
GRANT USAGE ON FOREIGN SERVER oradb TO user1;
GRANT
-
連線為
user1
並建立一個對應至 Oracle 使用者的映射。test=>
CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user '
oracleuser'
, password 'mypassword'
);CREATE USER MAPPING
-
建立連結至 Oracle 資料表的外部資料表。
test=>
CREATE FOREIGN TABLE
mytab
(a int) SERVER oradb OPTIONS (table 'MYTABLE'
);CREATE FOREIGN TABLE
-
查詢外部資料表。
test=>
SELECT * FROM mytab;
a --- 1 (1 row)
如果查詢報告下列錯誤,請檢查您的安全群組和存取控制清單 (ACL),以確定這兩個執行個體可以通訊。
ERROR: connection for foreign table "mytab" cannot be established
DETAIL: ORA-12170: TNS:Connect timeout occurred
在傳輸中使用加密
傳輸中的 PostgreSQL-to-Oracle 加密是以從用戶端和伺服器組態參數的組合為依據。如需使用 Oracle 21c 的範例,請參閱 Oracle 文件中的關於溝通加密和完整性的值ACCEPTED
,表示加密取決於 Oracle 資料庫伺服器組態。
如果資料庫位於 RDS for Oracle,請參閱 Oracle 原生網路加密以設定加密。
了解 pg_user_mappings 檢視和許可權限
PostgreSQL 目錄 pg_user_mapping
儲存從 Aurora PostgreSQL 使用者新增至外部資料 (遠端) 伺服器上使用者的映射。存取目錄受到限制,但您使用 pg_user_mappings
檢視查看映射。接下來,您可找到一個範例,其顯示許可權限如何套用於範例 Oracle 資料庫,但此資訊通常適用於任何外部資料包裝函式。
在以下輸出中,您可以找到映射至三個不同範例使用者的角色和權限。使用者 rdssu1
和 rdssu2
是 rds_superuser
角色的成員,user1
則不是。此範例使用 psql
中繼命令 \du
來列出現有角色。
test=>
\du
List of roles Role name | Attributes | Member of -----------------+------------------------------------------------------------+------------------------------------------------------------- rdssu1 | | {rds_superuser} rdssu2 | | {rds_superuser} user1 | | {}
所有使用者,包括具有 rds_superuser
權限的使用者,可在 pg_user_mappings
資料表中查看自己的使用者映射 (umoptions
)。如以下範例所示,當 rdssu1
嘗試獲取所有使用者映射,即使有 rdssu1
rds_superuser
權限,仍會引起錯誤:
test=>
SELECT * FROM pg_user_mapping;
ERROR: permission denied for table pg_user_mapping
下列是一些範例。
test=>
SET SESSION AUTHORIZATION rdssu1;
SET
test=>
SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+---------------------------------- 16414 | 16411 | oradb | 16412 | user1 | 16423 | 16411 | oradb | 16421 | rdssu1 | {user=oracleuser,password=mypwd} 16424 | 16411 | oradb | 16422 | rdssu2 | (3 rows)
test=>
SET SESSION AUTHORIZATION rdssu2;
SET
test=>
SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+---------------------------------- 16414 | 16411 | oradb | 16412 | user1 | 16423 | 16411 | oradb | 16421 | rdssu1 | 16424 | 16411 | oradb | 16422 | rdssu2 | {user=oracleuser,password=mypwd} (3 rows)
test=>
SET SESSION AUTHORIZATION user1;
SET
test=>
SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+-------------------------------- 16414 | 16411 | oradb | 16412 | user1 | {user=oracleuser,password=mypwd} 16423 | 16411 | oradb | 16421 | rdssu1 | 16424 | 16411 | oradb | 16422 | rdssu2 | (3 rows)
由於實作 information_schema._pg_user_mappings
和 pg_catalog.pg_user_mappings
存在差異,手動建立的 rds_superuser
需要額外的許可才能在 pg_catalog.pg_user_mappings
上檢視密碼。
rds_superuser
不需要額外許可才能在 information_schema._pg_user_mappings
中檢視密碼。
沒有 rds_superuser
角色的使用者僅能在以下情況下在 pg_user_mappings
中檢視密碼:
-
目前使用者是被映射的使用者,且擁有伺服器或擁有伺服器上
USAGE
的權限。 -
目前使用者是伺服器擁有者,且映射適用於
PUBLIC
。
使用 tds_fdw 擴充功能處理 SQL 資料庫
您可以使用 PostgreSQL tds_fdw
擴充功能來存取支援表格式資料串流 (TDS) 協議的資料庫,如 Sybase 和 Microsoft SQL Server 資料庫。此外部資料包裝函式可讓您從 Aurora PostgreSQL 資料庫叢集連線到使用 TDS 協議的資料庫,包括 Amazon RDS for Microsoft SQL Server。如需詳細資訊,請參閱 GitHub 上的 tds-fdw/tds_fdw
Amazon Aurora PostgreSQL 版本 13.6 及更新版本支援 tds_fdw
擴充功能。
設定 Aurora PostgreSQL 資料庫以使用 tds_fdw 擴充功能
在以下程序中,您可以找到設定及使用 tds_fdw
與 Aurora PostgreSQL 資料庫叢集 的範例。您必須取得執行個體的以下詳細內容,然後才能使用 tds_fdw
連線到 SQL Server 資料庫:
主機名稱或端點。如果是 RDS for SQL Server 資料庫執行個體,您可以使用主控台找到端點。選擇 Connectivity & security (連線和安全) 索引標籤,然後查看「端點和連線埠」區段。
連線埠號碼。Microsoft SQL 伺服器的預設連線埠號是 1433。
資料庫的名稱。資料庫識別符。
您也必須提供 SQL 連線埠 1433 的安全群組或存取控制清單 (ACL) 的存取權限。Aurora PostgreSQL 資料庫叢集 和 RDS for SQL 資料庫執行個體都需要存取連線埠 1433。如果存取權限未正確設定,當您嘗試查詢 Microsoft SQL Server 時,您會看到以下錯誤訊息:
ERROR: DB-Library error: DB #: 20009, DB Msg: Unable to connect:
Adaptive Server is unavailable or does not exist (mssql2019
.aws-region
.rds.amazonaws.com), OS #: 0, OS Msg: Success, Level: 9
使用 tds_fdw 連線到 SQL Server 資料庫
使用有
rds_superuser
角色的帳戶連線到您的 Aurora PostgreSQL 資料庫叢集的主要執行個體:psql --host=
your-cluster-name-instance-1
.aws-region
.rds.amazonaws.com --port=5432 --username=test –-password安裝
tds_fdw
擴充功能:test=>
CREATE EXTENSION tds_fdw;
CREATE EXTENSION
擴充功能安裝到您的 Aurora PostgreSQL 資料庫叢集 之後,您就可以設定外部伺服器。
建立外部伺服器
使用有 rds_superuser
權限的帳戶,在 Aurora PostgreSQL 資料庫叢集上執行這些任務。
在 Aurora PostgreSQL 資料庫叢集中建立外部伺服器:
test=>
CREATE SERVER
sqlserverdb
FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019
.aws-region
.rds.amazonaws.com', port '1433', database 'tds_fdw_testing
');CREATE SERVER
如要存取 SQLServer 端上的非 ASCII 資料,請使用 Aurora PostgreSQL 資料庫叢集 中的 character_set 選項建立伺服器連結:
test=>
CREATE SERVER
sqlserverdb
FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019
.aws-region
.rds.amazonaws.com', port '1433', database 'tds_fdw_testing
', character_set'UTF-8'
);CREATE SERVER
將權限授予沒有
rds_superuser
角色權限的使用者,例如user1
:test=>
GRANT USAGE ON FOREIGN SERVER
sqlserverdb
TOuser1
;以 user1 身分連線,然後建立一個 SQL Server 使用者的映射:
test=>
CREATE USER MAPPING FOR user1 SERVER
sqlserverdb
OPTIONS (username 'sqlserveruser
', password 'password
');CREATE USER MAPPING
建立連結至 SQL Server 資料表的外部資料表:
test=>
CREATE FOREIGN TABLE mytab (a int) SERVER
sqlserverdb
OPTIONS (table 'MYTABLE
');CREATE FOREIGN TABLE
查詢外部資料表:
test=>
SELECT * FROM mytab;
a --- 1 (1 row)
對連線使用傳輸中加密
從 Aurora PostgreSQL 到 SQL Server 的連線使用傳輸中加密 (TLS/SSL),具體取決於 SQL Server 資料庫組態。如果 SQL 伺服器未設定為加密,則 RDS for PostgreSQL 用戶端對 SQL Server 資料庫發出的請求將回退為未加密。
您可以透過設定 rds.force_ssl
參數,強制加密 RDS for SQL Server 資料庫執行個體的連線。若要了解作法,請參閱強制使用 SSL 連線至資料庫執行個體。如需有關 RDS for SQL Server 的 SSL/TLS 組態的詳細資訊,請參閱對 Microsoft SQL Server 資料庫執行個體使用 SSL。