本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
# 使用擴充功能和外部資料包裝函式
若要將功能擴充到您的 Aurora PostgreSQL 相容版本資料庫叢集，您可以安裝並使用各種「PostgreSQL 擴充功能」**。例如，如果您的使用案例要求跨越非常大的資料表進行密集型資料輸入，則可以安裝 `[pg\$1partman](https://pgxn.org/dist/pg_partman/doc/pg_partman.html)` 擴展功能對資料進行分割，藉此分散工作負載。
**注意**
從 Aurora PostgreSQL 14.5 開始，Aurora PostgreSQL 支援 Trusted Language Extensions for PostgreSQL。此功能會實作為延伸模組 `pg_tle`，您可以將其新增至 Aurora PostgreSQL。透過使用此延伸模組，開發人員可以在安全的環境中建立自己的 PostgreSQL 延伸模組，以簡化設定和組態需求，以及許多針對新延伸模組進行的初步測試。如需詳細資訊，請參閱[使用適用於 PostgreSQL 的受信任語言延伸模組](PostgreSQL_trusted_language_extension.md)。
在某些情況下，您可以將特定*模組*新增至 Aurora PostgreSQL 資料庫叢集的自訂資料庫叢集參數群組中的 `shared_preload_libraries` 清單，而不是安裝擴充功能。一般而言，預設資料庫叢集參數群組只會載入 `pg_stat_statements`，但有數個其他模組可供新增至清單。例如，您可以新增 `pg_cron` 模組來新增排程功能，如[使用 PostgreSQL pg\$1cron 擴充功能排程維護](PostgreSQL_pg_cron.md)中所詳述。另一個範例是，您可以載入 `auto_explain` 模組來記錄查詢執行計劃。若要進一步了解，請參閱 AWS 知識中心的[記錄查詢的執行計畫](https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-tune-query-performance/#)。
提供存取外部資料的擴充功能具體稱為「外部資料包裝函式」**(FDW)。例如，`oracle_fdw` 擴充功能可讓您的 Aurora PostgreSQL 資料庫叢集使用 Oracle 資料庫。
您還可在 `rds.allowed_extensions` 參數中列出擴充功能，精確指定可在 Aurora PostgreSQL 資料庫執行個體上安裝的擴充功能。如需詳細資訊，請參閱[限制安裝 PostgreSQL 擴充功能](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction.html)。
在下文中，您可以找到設定與使用一些可用於 Aurora PostgreSQL 版本的擴充功能、模組和 FDW 的相關資訊。為了簡單起見，這些都被稱為「擴充功能」。如需可與目前可用的 Aurora PostgreSQL 版本搭配使用的擴充功能清單，請參閱《Aurora PostgreSQL 版本資訊》**中的 [Amazon Aurora PostgreSQL 的擴充功能版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html)。
+ [使用 lo 模組管理大型物件](PostgreSQL_large_objects_lo_extension.md)
+ [使用 PostGIS 擴充功能管理空間資料](Appendix.PostgreSQL.CommonDBATasks.PostGIS.md)
+ [使用 pg\$1partman 擴充功能來管理 PostgreSQL 分割區](PostgreSQL_Partitions.md)
+ [使用 PostgreSQL pg\$1cron 擴充功能排程維護](PostgreSQL_pg_cron.md)
+ [使用 PgAudit 記錄資料庫活動](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md)
+ [使用 pglogical 跨執行個體同步資料](Appendix.PostgreSQL.CommonDBATasks.pglogical.md)
+ [使用 oracle\$1fdw 擴充功能處理 Oracle 資料庫](postgresql-oracle-fdw.md)
+ [使用 tds\$1fdw 擴充功能處理 SQL 資料庫](postgresql-tds-fdw.md)
# 使用 PostgreSQL 的 Amazon Aurora 委派的延伸模組支援
使用 PostgreSQL 的 Amazon Aurora 委派延伸模組支援，您就可以將延伸模組管理委派給不必是 `rds_superuser` 的使用者。系統會透過此委派延伸模組支援，建立名為 `rds_extension` 的新角色，而您必須將此角色指派給使用者以管理其他延伸模組。此角色可以建立、更新和捨棄延伸模組。
您可在 `rds.allowed_extensions` 參數中列出延伸模組，指定可在 Aurora PostgreSQL 資料庫執行個體上安裝的延伸模組。如需詳細資訊，請參閱[將 PostgreSQL 延伸模組與 Amazon RDS for PostgreSQL 搭配使用](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.html)。
您可以使用 `rds.allowed_delegated_extensions` 參數，限制可由具有 `rds_extension` 角色的使用者所管理的可用延伸模組清單。
您可在下列版本中取得委派的延伸模組支援：
+ 所有更高版本
+ 15.5 版和更新的 15 版本
+ 14.10 版和更新的 14 版本
+ 13.13 版和更新的 13 版本
+ 12.17 版和更新的 12 版本
**Topics**
+ [為使用者開啟委派延伸模組支援](#AuroraPostgreSQL.delegated_ext_mgmt)
+ [在 PostgreSQL 的 Aurora 委派延伸模組支援中使用的組態](#AuroraPostgreSQL.delegated_ext_config)
+ [關閉委派延伸模組的支援](#AuroraPostgreSQL.delegated_ext_disable)
+ [使用 Amazon Aurora 委派延伸模組支援的優勢](#AuroraPostgreSQL.delegated_ext_benefits)
+ [PostgreSQL 的 Aurora 委派延伸模組支援限制](#AuroraPostgreSQL.delegated_ext_limit)
+ [特定延伸模組所需的許可](#AuroraPostgreSQL.delegated_ext_perm)
+ [安全考量](#AuroraPostgreSQL.delegated_ext_sec)
+ [已停用捨棄延伸模組層疊](#AuroraPostgreSQL.delegated_ext_drop)
+ [可使用委派延伸模組支援新增的延伸模組範例](#AuroraPostgreSQL.delegated_ext_support)
## 為使用者開啟委派延伸模組支援
您必須執行下列動作，才能將延伸模組支援委派給使用者：
1. **將 `rds_extension` 角色授予使用者**：以 `rds_superuser` 身分連線至資料庫，以及執行下列命令：
```
Postgres => grant rds_extension to user_name;
```
1. **設定可供委派使用者管理的延伸模組清單**：`rds.allowed_delegated_extensions` 可讓您在資料庫叢集參數中使用 `rds.allowed_extensions` 指定可用延伸模組子集。您可以在下列其中一個層級執行此操作：
+ 在叢集或執行個體參數群組中，透過 AWS 管理主控台 或 API。如需詳細資訊，請參閱[Amazon Aurora 的參數群組](USER_WorkingWithParamGroups.md)。
+ 在資料庫層級使用以下命令：
```
alter database database_name set rds.allowed_delegated_extensions = 'extension_name_1,
extension_name_2,...extension_name_n';
```
+ 在使用者層級使用以下命令：
```
alter user user_name set rds.allowed_delegated_extensions = 'extension_name_1,
extension_name_2,...extension_name_n';
```
**注意**
變更 `rds.allowed_delegated_extensions` 動態參數後，您就不需要重新啟動資料庫。
1. **允許委派使用者存取在延伸模組建立程序期間建立的物件**：某些延伸模組會建立需要授予額外許可的物件，才能讓具有 `rds_extension` 角色的使用者存取這些物件。`rds_superuser` 必須向委派的使用者授予對那些物件的存取權。其中一個選項是使用事件觸發，自動將許可授予委派的使用者。
**事件觸發範例**
如要允許具備 `rds_extension` 的委派使用者使用延伸模組，而這些延伸模組需要針對延伸模組建立的物件設定許可，您可以自訂下列事件觸發範例，並僅新增您希望委派使用者能夠存取完整功能的延伸模組。系統可以在 template1 (預設範本) 建立此事件觸發，因此從 template1 建立的所有資料庫都會具有該事件觸發。當委派的使用者安裝此延伸模組時，此觸發會針對延伸模組建立的物件自動授予擁有權。
```
CREATE OR REPLACE FUNCTION create_ext()
RETURNS event_trigger AS $$
DECLARE
schemaname TEXT;
databaseowner TEXT;
r RECORD;
BEGIN
IF tg_tag = 'CREATE EXTENSION' and current_user != 'rds_superuser' THEN
RAISE NOTICE 'SECURITY INVOKER';
RAISE NOTICE 'user: %', current_user;
FOR r IN SELECT * FROM pg_catalog.pg_event_trigger_ddl_commands()
LOOP
CONTINUE WHEN r.command_tag != 'CREATE EXTENSION' OR r.object_type != 'extension';
schemaname = (
SELECT n.nspname
FROM pg_catalog.pg_extension AS e
INNER JOIN pg_catalog.pg_namespace AS n
ON e.extnamespace = n.oid
WHERE e.oid = r.objid
);
databaseowner = (
SELECT pg_catalog.pg_get_userbyid(d.datdba)
FROM pg_catalog.pg_database d
WHERE d.datname = current_database()
);
RAISE NOTICE 'Record for event trigger %, objid: %,tag: %, current_user: %, schema: %, database_owenr: %', r.object_identity, r.objid, tg_tag, current_user, schemaname, databaseowner;
IF r.object_identity = 'address_standardizer_data_us' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_gaz TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_lex TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_rules TO %I WITH GRANT OPTION;', schemaname, databaseowner);
ELSIF r.object_identity = 'dict_int' THEN
EXECUTE pg_catalog.format('ALTER TEXT SEARCH DICTIONARY %I.intdict OWNER TO %I;', schemaname, databaseowner);
ELSIF r.object_identity = 'pg_partman' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config_sub TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.custom_time_partitions TO %I WITH GRANT OPTION;', schemaname, databaseowner);
ELSIF r.object_identity = 'postgis_topology' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT USAGE ON SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
END IF;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE EVENT TRIGGER log_create_ext ON ddl_command_end EXECUTE PROCEDURE create_ext();
```
## 在 PostgreSQL 的 Aurora 委派延伸模組支援中使用的組態
| 組態名稱 | Description | 預設值 | 備註 | 誰可以修改或授予許可 |
| --- | --- | --- | --- | --- |
| `rds.allowed_delegated_extensions` | 此參數會限制 rds\$1extension 角色可在資料庫中管理的延伸模組。其必須是 rds.allowed\$1extensions 的子集。 | 空字串 | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/AuroraUserGuide/Aurora_delegated_ext.html) 若要進一步了解如何設定此參數，請參閱[為使用者開啟委派延伸模組支援](#AuroraPostgreSQL.delegated_ext_mgmt)。 | rds\$1superuser |
| `rds.allowed_extensions` | 此參數可讓客戶限制 Aurora PostgreSQL 資料庫執行個體中可安裝的延伸模組。如需詳細資訊，請參閱[限制安裝 PostgreSQL 延伸模組](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction)。 | "\$1" | 根據預設，此參數會設為「\$1」，這表示具有必要權限的使用者都能夠建立 RDS for PostgreSQL 和 Aurora PostgreSQL 上支援的所有延伸模組。 空白表示無法在 Aurora PostgreSQL 資料庫執行個體中安裝任何延伸模組。 | 管理員 |
| `rds-delegated_extension_allow_drop_cascade` | 此參數會控制具有 `rds_extension` 的使用者是否能使用層疊選項捨棄延伸模組。 | off | 根據預設，`rds-delegated_extension_allow_drop_cascade` 會設定為 `off`。這表示具有 `rds_extension` 的使用者無法使用層疊選項捨棄延伸模組。 若要授予該能力，則應將 `rds.delegated_extension_allow_drop_cascade` 參數設為 `on`。 | rds\$1superuser |
## 關閉委派延伸模組的支援
**部分關閉**
委派的使用者無法建立新的延伸模組，但仍可以更新現有的延伸模組。
+ 將資料庫叢集參數群組中的 `rds.allowed_delegated_extensions` 重設為預設值。
+ 在資料庫層級使用以下命令：
```
alter database database_name reset rds.allowed_delegated_extensions;
```
+ 在使用者層級使用以下命令：
```
alter user user_name reset rds.allowed_delegated_extensions;
```
**完全關閉**
從使用者撤銷 `rds_extension` 角色會將使用者還原為標準許可。使用者無法再建立、更新或捨棄延伸模組。
```
postgres => revoke rds_extension from user_name;
```
## 使用 Amazon Aurora 委派延伸模組支援的優勢
透過使用 PostgreSQL 的 Amazon Aurora 委派延伸模組支援，您就可以將延伸模組管理安全地委派給不具備 `rds_superuser` 角色的使用者。此功能提供下列優勢：
+ 您可以輕鬆地將延伸模組管理委派給所選的使用者。
+ 這不需要 `rds_superuser` 角色。
+ 提供支援相同資料庫叢集中不同資料庫不同延伸模組集的能力。
## PostgreSQL 的 Aurora 委派延伸模組支援限制
+ 在延伸模組建立程序期間建立的物件可能需要額外的權限，延伸模組才能正常運作。
## 特定延伸模組所需的許可
為了建立、使用或更新下列延伸模組，委派的使用者應具備下列功能、資料表和結構描述的必要權限。
| 需要擁有權或許可的延伸模組 | 函式 | 表格 | 結構描述 | 文字搜尋字典 | Comment |
| --- | --- | --- | --- | --- | --- |
| address\$1standardizer\$1data\$1us | | us\$1gaz、us\$1lex、us\$1lex、I.us\$1rules | | | |
| amcheck | bt\$1index\$1check、bt\$1index\$1parent\$1check | | | | |
| dict\$1int | | | | intdict | |
| pg\$1partman | | custom\$1time\$1partitions、part\$1config、part\$1config\$1sub | | | |
| pg\$1stat\$1statements | | | | | |
| PostGIS | st\$1tileenvelope | spatial\$1ref\$1sys | | | |
| postgis\$1raster | | | | | |
| postgis\$1topology | | 拓撲、層 | 拓撲 | | 委派使用者必須是資料庫擁有者 |
| log\$1fdw | create\$1foreign\$1table\$1for\$1log\$1file | | | | |
| rds\$1tools | role\$1password\$1encryption\$1type | | | | |
| postgis\$1tiger\$1geocoder | | geocode\$1settings\$1default、geocode\$1settings | tiger | | |
| pg\$1freespacemap | pg\$1freespace | | | | |
| pg\$1visibility | pg\$1visibility | | | | |
## 安全考量
請記住，具有 `rds_extension` 角色的使用者將能夠在其具有連線權限的所有資料庫上管理延伸模組。若需讓委派使用者管理單一資料庫的延伸模組，良好做法是撤銷每個資料庫上公有的所有權限，然後明確將該特定資料庫的連線權限授予委派的使用者。
有多種延伸模組，使用者可透過這些延伸模組從多個資料庫存取資訊。在將這些延伸模組新增至 `rds.allowed_delegated_extensions` 前，請確定您授予 `rds_extension` 的使用者具有跨資料庫功能。例如，`postgres_fdw` 和 `dblink` 會提供在相同執行個體或遠端執行個體上跨資料庫查詢的功能。`log_fdw` 會讀取適用於執行個體中所有資料庫的 postgres 引擎日誌檔案，可能包含來自多個資料庫的緩慢查詢或錯誤訊息。`pg_cron` 會在資料庫執行個體上執行排程背景任務，並可將任務設定為在不同的資料庫中執行。
## 已停用捨棄延伸模組層疊
具有 `rds_extension` 角色的使用者是否能捨棄具有層疊選項的延伸模組，由 `rds.delegated_extension_allow_drop_cascade` 參數所控制。根據預設，`rds-delegated_extension_allow_drop_cascade` 會設定為 `off`。如以下查詢所示，這表示具有 `rds_extension` 角色的使用者不能使用層疊選項捨棄延伸模組。
```
DROP EXTENSION CASCADE;
```
因為這會自動捨棄相依於延伸模組的物件，進而捨棄所有相依於這些物件的物件。嘗試使用層疊選項將導致錯誤。
若要授予該能力，則應將 `rds.delegated_extension_allow_drop_cascade` 參數設為 `on`。
變更 `rds.delegated_extension_allow_drop_cascade` 動態參數不需要重新啟動資料庫。您可以在以下其中一個層級執行此操作：
+ 在叢集或執行個體參數群組中，透過 AWS 管理主控台 或 API。
+ 在資料庫層級使用下列命令：
```
alter database database_name set rds.delegated_extension_allow_drop_cascade = 'on';
```
+ 在使用者層級使用下列命令：
```
alter role tenant_user set rds.delegated_extension_allow_drop_cascade = 'on';
```
## 可使用委派延伸模組支援新增的延伸模組範例
+ `rds_tools`
```
extension_test_db=> create extension rds_tools;
CREATE EXTENSION
extension_test_db=> SELECT * from rds_tools.role_password_encryption_type() where rolname = 'pg_read_server_files';
ERROR: permission denied for function role_password_encryption_type
```
+ `amcheck`
```
extension_test_db=> CREATE TABLE amcheck_test (id int);
CREATE TABLE
extension_test_db=> INSERT INTO amcheck_test VALUES (generate_series(1,100000));
INSERT 0 100000
extension_test_db=> CREATE INDEX amcheck_test_btree_idx ON amcheck_test USING btree (id);
CREATE INDEX
extension_test_db=> create extension amcheck;
CREATE EXTENSION
extension_test_db=> SELECT bt_index_check('amcheck_test_btree_idx'::regclass);
ERROR: permission denied for function bt_index_check
extension_test_db=> SELECT bt_index_parent_check('amcheck_test_btree_idx'::regclass);
ERROR: permission denied for function bt_index_parent_check
```
+ `pg_freespacemap`
```
extension_test_db=> create extension pg_freespacemap;
CREATE EXTENSION
extension_test_db=> SELECT * FROM pg_freespace('pg_authid');
ERROR: permission denied for function pg_freespace
extension_test_db=> SELECT * FROM pg_freespace('pg_authid',0);
ERROR: permission denied for function pg_freespace
```
+ `pg_visibility`
```
extension_test_db=> create extension pg_visibility;
CREATE EXTENSION
extension_test_db=> select * from pg_visibility('pg_database'::regclass);
ERROR: permission denied for function pg_visibility
```
+ `postgres_fdw`
```
extension_test_db=> create extension postgres_fdw;
CREATE EXTENSION
extension_test_db=> create server myserver foreign data wrapper postgres_fdw options (host 'foo', dbname 'foodb', port '5432');
ERROR: permission denied for foreign-data wrapper postgres_fdw
```
# 使用 lo 模組管理大型物件
lo 模組 (擴充功能) 適用於透過 JDBC 或 ODBC 驅動程式使用 PostgreSQL 資料庫的資料庫使用者與開發人員。JDBC 和 ODBC 都期望當對它們的參照變更時，資料庫會處理大型物件的刪除。但是，PostgreSQL 不是這樣運作的。PostgreSQL 不會假設當某個物件其參照變更時應該予以刪除。結果是物件會保留在磁碟上，未被參照。lo 擴充功能包含一個功能，用於在參照變更時視需要觸發該功能以刪除物件。
**提示**
若要判定資料庫是否可以從 lo 擴充功能受益，請使用 `vacuumlo` 公用程式檢查是否有孤立大型物件。若要在不採取任何動作的情況下取得孤立大型物件的計數，請以 `-n` 選項執行該公用程式 (無操作)。若要了解如何操作，請參閱下文中的 [vacuumlo utility](#vacuumlo-utility)。
lo 模組適用於 Aurora PostgreSQL 13.7、12.11、11.16、10.21 和更新的次要版本。
若要安裝此模組 (擴充功能)，您需要 `rds_superuser` 權限。安裝 lo 擴充功能會將下列項目新增至資料庫：
+ `lo` – 這是一個大型物件 (lo) 資料類型，可用於二進位大型物件 (BLOB) 和其他大型物件。`lo` 資料類型是 `oid` 資料類型的領域。換言之，它是具有選用限制的物件識別碼。如需詳細資訊，請參閱 PostgreSQL 文件中的[物件識別碼](https://www.postgresql.org/docs/14/datatype-oid.html)。簡言之，您可以使用 `lo` 資料類型，來區分保存大型物件參照的資料庫欄與其他物件識別碼 (OID)。
+ `lo_manage` – 這是一個函數，您可以在包含大型物件參照的資料表欄上的觸發程序中使用此函數。只要您刪除或修改參照大型物件的值時，觸發程序都會取消該物件 (`lo_unlink`) 與其參照的連結。只有在資料欄是對該大型物件的唯一資料庫參照時，才對該資料欄使用觸發程序。
如需大型物件模組的詳細資訊，請參閱 PostgreSQL 文件中的 [lo](https://www.postgresql.org/docs/current/lo.html)。
## 安裝 lo 擴充功能
安裝 lo 擴充功能之前，請確定您已具備 `rds_superuser` 權限。
**安裝擴充功能**
1. 使用 `psql` 連線到 Aurora PostgreSQL 資料庫叢集的主要資料庫執行個體。
```
psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
出現提示時，輸入您的密碼。`psql` 用戶端連接並顯示預設管理連接資料庫 `postgres=>` 作為提示。
1. 安裝擴充功能，如下所示。
```
postgres=> CREATE EXTENSION lo;
CREATE EXTENSION
```
您現在可以使用 `lo` 資料類型來定義資料表中的資料欄。例如，您可以建立一個資料表 (`images`)，其中包含點陣影像資料。您可以將 `lo` 資料類型用於資料欄 `raster`，如下列建立資料表的範例所示。
```
postgres=> CREATE TABLE images (image_name text, raster lo);
```
## 使用 lo\$1Manage 觸發程序函數刪除物件
您可以將 `lo_manage` 函數用於當更新或刪除 `lo` 時所要清理 `lo` 或其他大型物件欄中的觸發程序。
**在參照大型物件的資料欄上設定觸發程序**
+ 執行以下任意一項：
+ 使用引數的資料欄名稱，在每個資料欄上建立 BEFORE UPDATE OR DELETE 觸發程序，以包含對大型物件的唯一參照。
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
+ 僅正在更新資料欄時套用觸發程序。
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OF images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
`lo_manage` 觸發程序函數只有在插入或刪除資料欄資料 (視您定義觸發程序的方式而定) 的背景下才會運作。當您執行 `DROP` 或 `TRUNCATE` 操作時則不會起作用。這意味著您應先將任何資料表中的物件資料欄刪除,再捨棄資料表，以免產生孤立物件。
例如，假設您想要捨棄包含 `images` 資料表的資料庫。您如下所示刪除資料欄。
```
postgres=> DELETE FROM images COLUMN raster
```
假設在該資料欄上定義 `lo_manage` 函數來處理刪除，現在您可以放心地捨棄該資料表。
## 使用 `vacuumlo` 移除孤立的大型物件
`vacuumlo` 公用程式會識別孤立大型物件並從資料庫中移除。此公用程式自 PostgreSQL 9.1.24 起可供使用。如果您的資料庫使用者會例行性地使用大型物件，建議您偶爾執行 `vacuumlo` 以清理孤立大型物件。
在安裝 lo 擴充功能之前，您可以使用 `vacuumlo` 來評估您的 Aurora PostgreSQL 資料庫叢集是否會受益。若要這麼做，請將 `vacuumlo` 搭配 `-n` 選項 (無操作) 使用，以顯示要刪除的內容，如下所示：
```
$ vacuumlo -v -n -h your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com -p 5433 -U postgres docs-lab-spatial-db
Password:*****
Connected to database "docs-lab-spatial-db"
Test run: no large objects will be removed!
Would remove 0 large objects from database "docs-lab-spatial-db".
```
如輸出結果所示，孤立大型對象不是此特定資料庫的問題。
如需此公用程式的詳細資訊，請參閱 PostgreSQL 文件中的 [https://www.postgresql.org/docs/current/vacuumlo.html](https://www.postgresql.org/docs/current/vacuumlo.html)。
## 了解 `vacuumlo` 的運作方式
`vacuumlo` 命令會從 PostgreSQL 資料庫移除孤立的大型物件 (LO)，而不會影響您的使用者資料表或與之衝突。
命令運作方式如下所示：
1. `vacuumlo` 首先會建立暫時資料表，其中包含資料庫中大型物件的所有物件 ID (OID)。
1. `vacuumlo` 接著會掃描資料庫中使用資料類型 `oid` 或 `lo` 的每個欄位。如果 `vacuumlo` 在這些欄位中找到相符的 OID，則會從暫時資料表中移除 OID。`vacuumlo` 只會檢查特別命名為 `oid` 或 `lo` 的欄位，而不是根據這些類型的網域。
1. 暫時資料表中剩餘的項目代表孤立的 LO，`vacuumlo` 接著會將其安全地移除。
## 提升 `vacuumlo` 效能
您也許可以使用 `-l` 選項增加批次大小，藉此改善 `vacuumlo` 的效能。這可讓 `vacuumlo` 一次處理更多 LO。
如果您的系統有足夠的記憶體，而且您可以將暫時資料表完全容納在記憶體中，在資料庫層級增加 `temp_buffers` 設定可能會改善效能。這可讓資料表完全位於記憶體中，進而增強整體效能。
下列查詢會預估暫時資料表的大小：
```
SELECT
pg_size_pretty(SUM(pg_column_size(oid))) estimated_lo_temp_table_size
FROM
pg_largeobject_metadata;
```
## 大型物件的考量事項
以下是使用大型物件時需要注意的一些重要考量事項：
+ `Vacuumlo` 是唯一的解決方案，因為目前沒有其他方法可以移除孤立的 LO。
+ 使用複寫技術 AWS DMS 之 pglogical、原生邏輯複寫和 等工具不支援複寫大型物件。
+ 設計資料庫結構描述時，請盡可能避免使用大型物件，並考慮改用像是 `bytea` 的其他資料類型。
+ 至少每週定期執行 `vacuumlo` 一次，以防止孤立的 LO 發生問題。
+ 在存放大型物件的資料表上使用具有 `lo_manage` 函數的觸發程序，以協助防止建立孤立的 LO。
# 使用 PostGIS 擴充功能管理空間資料
PostGIS 是 PostgreSQL 的擴充功能，可用於儲存和管理空間資訊。若要進一步了解 PostGIS，請參閱 [PostGIS.net](https://postgis.net/)。
從 10.5 版開始，PostgreSQL 即支援 PostGIS 用於處理 Mapbox向量圖標資料的 libprotobuf 1.3.0 程式庫。
設定 PostGIS 擴充功能需要 `rds_superuser` 權限。我們建議您建立一使用者 (角色) 來管理 PostGIS 擴充功能及您的空間資料。PostGIS 擴充功能及其相關元件會為 PostgreSQL 新增數千個函數。若這對您的使用案例有意義，請考慮在其自己的結構描述中建立 PostGIS 擴充功能。下列範例會顯示如何在其自己的資料庫中安裝擴充功能，但這並非必要。
**Topics**
+ [步驟 1：建立使用者 (角色) 來管理 PostGIS 擴充功能](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Connect)
+ [步驟 2：載入 PostGIS 擴充功能](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.LoadExtensions)
+ [步驟 3：轉移延伸模組結構描述的擁有權](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferOwnership)
+ [步驟 4：轉移 PostGIS 物件的擁有權](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferObjects)
+ [步驟 5：測試擴充功能](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Test)
+ [步驟 6：升級 PostGIS 擴充功能](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Update)
+ [PostGIS 擴充功能版本](#CHAP_PostgreSQL.Extensions.PostGIS)
+ [將 PostGIS 2 升級到 PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3)
## 步驟 1：建立使用者 (角色) 來管理 PostGIS 擴充功能
首先，以具有 `rds_superuser` 權限的使用者身分連線至您的 RDS for PostgreSQL 資料庫執行個體。若您在設定執行個體時保留預設名稱，則以 `postgres` 身分連線：
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
建立一個單獨的角色 (使用者) 來管理 PostGIS 擴充功能。
```
postgres=> CREATE ROLE gis_admin LOGIN PASSWORD 'change_me';
CREATE ROLE
```
授予此角色 `rds_superuser` 權限，允許角色安裝擴充功能。
```
postgres=> GRANT rds_superuser TO gis_admin;
GRANT
```
建立用於 PostGIS 成品的資料庫：此為選擇性步驟。或者，您可以在 PostGIS 擴充功能的使用者資料庫中建立結構描述，但這也並非必要。
```
postgres=> CREATE DATABASE lab_gis;
CREATE DATABASE
```
授予 `gis_admin` 在 `lab_gis` 資料庫上的所有權限。
```
postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_gis TO gis_admin;
GRANT
```
退出工作階段，並以 `gis_admin` 身分重新連線至您的 RDS for PostgreSQL 資料庫執行個體。
```
postgres=> psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=gis_admin --password --dbname=lab_gis
Password for user gis_admin:...
lab_gis=>
```
按照後續步驟中的詳細說明，繼續設定擴充功能。
## 步驟 2：載入 PostGIS 擴充功能
PostGIS 擴充功能包含數個共同運作的相關擴充功能，以提供地理空間的功能。根據您的使用案例，可能不需要在此步驟中建立的所有擴充功能。
使用 `CREATE EXTENSION` 陳述式載入 PostGIS 擴充。
```
CREATE EXTENSION postgis;
CREATE EXTENSION
CREATE EXTENSION postgis_raster;
CREATE EXTENSION
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
CREATE EXTENSION postgis_topology;
CREATE EXTENSION
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION
```
您可以執行下例中顯示的 SQL 查詢來確認結果，其中列出擴充功能及其擁有者。
```
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name | Owner
--------------+-----------
public | postgres
tiger | rdsadmin
tiger_data | rdsadmin
topology | rdsadmin
(4 rows)
```
## 步驟 3：轉移延伸模組結構描述的擁有權
使用 ALTER SCHEMA 陳述式將結構描述的擁有權移轉至 `gis_admin` 角色。
```
ALTER SCHEMA tiger OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA tiger_data OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA topology OWNER TO gis_admin;
ALTER SCHEMA
```
您可執行下列 SQL 查詢，來確認所有權變更。您也可以使用 `\dn` 中繼命令和 psql 命令列。
```
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name | Owner
--------------+---------------
public | postgres
tiger | gis_admin
tiger_data | gis_admin
topology | gis_admin
(4 rows)
```
## 步驟 4：轉移 PostGIS 物件的擁有權
**注意**
請勿變更 PostGIS 函數的擁有權。PostGIS 的適當操作和未來升級需要這些函數來保留原始擁有權。如需 PostGIS 許可的詳細資訊，請參閱 [PostgreSQL 安全性](https://postgis.net/workshops/postgis-intro/security.html)。
使用下列函式將 PostGIS 資料表的擁有權移轉至 `gis_admin` 角色。從 psql 提示字元執行下列陳述式來建立函式。
```
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION
```
接下來，執行下列查詢來執行 `exec` 函數，該函數會執行陳述式及變更權限。
```
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO gis_admin;')
FROM (
SELECT nspname, relname
FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname in ('tiger','topology') AND
relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;
```
## 步驟 5：測試擴充功能
為避免需要指定結構描述名稱，請使用下列命令將 `tiger` 結構描述新增至您的搜尋路徑。
```
SET search_path=public,tiger;
SET
```
使用下列 SELECT 陳述式來測試 `tiger` 結構描述。
```
SELECT address, streetname, streettypeabbrev, zip
FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev | zip
---------+------------+------------------+-------
1 | Devonshire | Pl | 02109
(1 row)
```
要進一步了解此擴充功能，請參閱 PostGIS 文件中的 [Tiger Geocoder](https://postgis.net/docs/Extras.html#Tiger_Geocoder) (Tiger 地理編碼器)。
使用下列 `SELECT` 陳述式來測試存取 `topology` 結構描述。這樣會呼叫 `createtopology` 函數，以使用指定的空間參考識別碼 (26986) 和預設公差 (0.5) 註冊新拓撲物件 (my\$1new\$1topo)。如需進一步了解，請參閱 PostgreGIS 文件中的[建立拓撲](https://postgis.net/docs/CreateTopology.html)。
```
SELECT topology.createtopology('my_new_topo',26986,0.5);
createtopology
----------------
1
(1 row)
```
## 步驟 6：升級 PostGIS 擴充功能
每個新版本的 PostgreSQL 都支援一個或多個與該版本相容的 PostGIS 擴充功能版本。將 PostgreSQL 引擎升級到新版本並不會自動升級 PostGIS 擴充功能。升級 PostgreSQL 引擎之前，您通常會將 PostGIS 升級到目前 PostgreSQL 版本的最新可用版本。如需詳細資訊，請參閱[PostGIS 擴充功能版本](#CHAP_PostgreSQL.Extensions.PostGIS)。
PostgreSQL 引擎升級之後，接著再次將 PostGIS 擴充功能升級為支援新升級之 PostgreSQL 引擎版本的版本。如需升級 PostgreSQL 資料庫引擎的詳細資訊，請參閱 [測試執行生產資料庫叢集升級到新主要版本的程序](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary).
您可以隨時在 Aurora PostgreSQL 資料庫叢集上檢查可用的 PostGIS 擴充功能版本更新。若要這麼做，請執行下列命令。PostGIS 2.5.0 和更新版本可使用此功能。
```
SELECT postGIS_extensions_upgrade();
```
如果您的應用程式不支援最新的 PostGIS 版本，您仍然可以安裝主要版本中可用的舊版 PostGIS，如下所示。
```
CREATE EXTENSION postgis VERSION "2.5.5";
```
如果您想要從舊版本升級到特定的 PostGIS 版本，也可以使用以下命令。
```
ALTER EXTENSION postgis UPDATE TO "2.5.5";
```
視您要從哪個版本升級而定，您可能需要再次執行此函數。第一次執行函數的結果會決定是否需要額外的升級函數。例如，這是從 PostGIS 2 升級到 PostGIS 3 的情況。如需詳細資訊，請參閱[將 PostGIS 2 升級到 PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3)。
如果您升級此擴充功能以準備 PostgreSQL 引擎的主要版本升級，您可以繼續執行其他初步工作。如需詳細資訊，請參閱 [測試執行生產資料庫叢集升級到新主要版本的程序](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary)。
## PostGIS 擴充功能版本
我們建議您安裝所有擴充功能的版本，例如在《*Aurora PostgreSQL 版本備註*》中的 [Aurora PostgreSQL 相容擴充功能版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html)所列出的 PostGIS。若要取得您的發行版本中有哪些可用版本，請使用下列命令。
```
SELECT * FROM pg_available_extension_versions WHERE name='postgis';
```
您也可以在 *Aurora PostgreSQL 版本備註*的以下各節找到版本資訊：
+ [Aurora PostgreSQL 14 的擴充功能版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.14)
+ [Aurora PostgreSQL 相容版本 13 的擴充功能版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.13)
+ [Aurora PostgreSQL 相容版本 12 的擴充功能版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.12)
+ [Aurora PostgreSQL 相容版本 11 的擴充功能版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.11)
+ [Aurora PostgreSQL 相容版本 10 的擴充功能版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.10)
+ [Aurora PostgreSQL 相容版本 9.6 的擴充功能版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.96)
## 將 PostGIS 2 升級到 PostGIS 3
從 3.0 版開始，PostGIS 點陣函數現在是一個單獨的擴充功能，`postgis_raster`。此擴充功能具有自己的安裝和升級路徑。如此一來，可以從核心 `postgis` 擴充功能移除點陣影像處理所需的數十種函數、資料類型和其他成品。這意味著，如果您的使用案例不需要點陣處理，則不需要安裝 `postgis_raster` 擴充功能。
在以下升級範例中，第一個升級命令會將點陣函數擷取至 `postgis_raster` 擴充功能。接著便需要第二個升級命令將 `postgis_raster` 升級到新版本。
**從 PostGIS 2 升級到 PostGIS 3**
1. 識別可用於 PostgreSQL 版本的 PostGIS 預設版本，而其中 PostgreSQL 位於您的 Aurora PostgreSQL 資料庫叢集。若要這麼做，請執行下列查詢。
```
SELECT * FROM pg_available_extensions
WHERE default_version > installed_version;
name | default_version | installed_version | comment
---------+-----------------+-------------------+------------------------------------------------------------
postgis | 3.1.4 | 2.3.7 | PostGIS geometry and geography spatial types and functions
(1 row)
```
1. 識別 Aurora PostgreSQL 資料庫叢集的寫入器執行個體上，每個資料庫中安裝的 PostGIS 版本。換句話說，查詢每個使用者資料庫，如下所示。
```
SELECT
e.extname AS "Name",
e.extversion AS "Version",
n.nspname AS "Schema",
c.description AS "Description"
FROM
pg_catalog.pg_extension e
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
e.extname LIKE '%postgis%'
ORDER BY
1;
Name | Version | Schema | Description
---------+---------+--------+---------------------------------------------------------------------
postgis | 2.3.7 | public | PostGIS geometry, geography, and raster spatial types and functions
(1 row)
```
預設版本 (PostGIS 3.1.4) 與安裝的版本 (PostGIS 2.3.7) 之間不符，代表您需要升級 PostGIS 擴充功能。
```
ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION
WARNING: unpackaging raster
WARNING: PostGIS Raster functionality has been unpackaged
```
1. 執行下列查詢，以確認點陣函數現在位於其自己的套件中。
```
SELECT
probin,
count(*)
FROM
pg_proc
WHERE
probin LIKE '%postgis%'
GROUP BY
probin;
probin | count
--------------------------+-------
$libdir/rtpostgis-2.3 | 107
$libdir/postgis-3 | 487
(2 rows)
```
輸出結果顯示版本之間仍然存在差異。PostGIS 函數是第 3 版 (postgis-3)，而點陣函數 (rtpostgis) 是第 2 版 (rtpostgis-2.3)。若要完成升級，請再次執行升級命令，如下所示。
```
postgres=> SELECT postgis_extensions_upgrade();
```
您可以放心忽略警告訊息。再次執行下列查詢以確認升級是否已完成。當 PostGIS 和所有相關的擴充功能均未標記為需要升級時，則升級完成。
```
SELECT postgis_full_version();
```
1. 使用下列查詢來查看已完成的升級程序和個別套裝的擴充功能，並確認其版本是否相符。
```
SELECT
e.extname AS "Name",
e.extversion AS "Version",
n.nspname AS "Schema",
c.description AS "Description"
FROM
pg_catalog.pg_extension e
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
e.extname LIKE '%postgis%'
ORDER BY
1;
Name | Version | Schema | Description
----------------+---------+--------+---------------------------------------------------------------------
postgis | 3.1.5 | public | PostGIS geometry, geography, and raster spatial types and functions
postgis_raster | 3.1.5 | public | PostGIS raster types and functions
(2 rows)
```
輸出結果顯示 PostGIS 2 擴充功能已升級到 PostGIS 3，並且 `postgis` 和現在的個別 `postgis_raster` 擴充功能則為 3.1.5 版。
升級完成後，如果您不打算使用點陣函數，您可以按以下方式卸除擴充功能。
```
DROP EXTENSION postgis_raster;
```
# 使用 pg\$1partman 擴充功能來管理 PostgreSQL 分割區
PostgreSQL 表格分割區提供了用於高性能處理資料輸入和報告的框架。對需要非常快速輸入大量資料的資料庫使用分割區。分割區還提供了更快的大型表格查詢。分割區有助於維護資料，而不會影響資料庫執行個體，因為它需要較少的輸入/輸出資源。
透過使用分割區，您可以將資料分割為自訂大小的區塊進行處理。例如，您可以分割時間序列資料，例如每小時、每日、每週、每月、每季、年度、自訂或以上任何組合。對於時間序列資料範例，如果您依小時分割資料表，則每個分割區都將包含一小時的資料。如果您依每日分割時間序列資料表，則每個分割區將保存一天的資料，依此類推。分割區索引鍵控制分割區的大小。
當您在分割表格上使用 `INSERT` 或 `UPDATE` SQL 命令時，資料庫引擎會將資料路由至適當的分割區。儲存資料的 PostgreSQL 表格分割區是主表格的子表格。
在資料庫查詢讀取期間，PostgreSQL 最佳化器會檢查查詢的 `WHERE` 子句，如果可能的話，將資料庫掃描導向僅相關的分割區。
從 10 版開始，PostgreSQL 使用宣告式分割來實作資料表分割區。這也被稱為原生 PostgreSQL 分割區。在 PostgreSQL 10 版之前，您已使用觸發器來實作分割區。
PostgreSQL 表格分割區提供下列功能：
+ 隨時建立新的分割區。
+ 可變的分割區範圍。
+ 使用資料定義語言 (DDL) 陳述式的可分離和可重新連接分割區。
例如，可拆分的分割區對於從主磁碟分割區移除歷史資料，但保留歷史資料以供分析來說十分實用。
+ 新的分割區會繼承父資料庫表格屬性，包括以下各項：
+ 索引
+ 主索引鍵，其中必須包含分割區索引鍵資料欄
+ 外部索引鍵
+ 檢查限制
+ 參考
+ 建立完整資料表或每個特定分割區的索引。
您無法變更個別分割區的結構描述。不過，您可以變更父資料表格 (例如新增新資料欄)，此表格會傳播到分割區。
**Topics**
+ [PostgreSQL pg\$1partman 擴充功能概述](#PostgreSQL_Partitions.pg_partman)
+ [啟用 pg\$1partman 擴充功能](#PostgreSQL_Partitions.enable)
+ [使用 create\$1parent 函數設定分割區](#PostgreSQL_Partitions.create_parent)
+ [使用 run\$1maintenance\$1proc 函數來設定分割區維護](#PostgreSQL_Partitions.run_maintenance_proc)
## PostgreSQL pg\$1partman 擴充功能概述
您可以使用 PostgreSQL `pg_partman` 擴充功能，以自動化資料表分割區的建立和維護。如需更多一般資訊，請參閱 `pg_partman` 文件中的 [PG 分割區管理員](https://github.com/pgpartman/pg_partman)。
**注意**
Aurora PostgreSQL 版本 12.6 及更新版本支援此 `pg_partman` 擴充功能。
您可以使用下列設定來設定 `pg_partman`，而不必手動建立每個分割區：
+ 要分割的表格
+ 分割區類型
+ 分割區索引鍵
+ 分割區間隔
+ 分割區預先建立與管理選項
建立 PostgreSQL 分割區資料表之後，您可以透過呼叫 `create_parent` 函數，使用 `pg_partman` 進行註冊。這樣做會根據您傳遞給函數的參數來建立必要的分割區。
`pg_partman` 擴充功能還提供 `run_maintenance_proc` 函數，您可以按排程呼叫它以自動管理分割區。為了確保視需要建立適當的分割區，可以排程此函數定期執行 (例如每小時)。您還可以確保自動捨棄分割區。
## 啟用 pg\$1partman 擴充功能
如果您要管理相同 PostgreSQL 資料庫執行個體內多個資料庫的分割區，則必須分別為每個資料庫啟用 `pg_partman` 擴充功能。若要啟用特定資料庫的 `pg_partman` 擴充功能，請建立分割區維護結構描述，然後建立 `pg_partman` 擴充功能，如下所示。
```
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
```
**注意**
若要建立 `pg_partman` 擴充功能，請確定您具有 `rds_superuser` 權限。
如果您收到下列錯誤訊息，請將`rds_superuser`權限授與帳戶或使用您的超級使用者帳戶。
```
ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.
```
若要授予 `rds_superuser` 權限，請連線至您的超級使用者帳戶並執行下列命令。
```
GRANT rds_superuser TO user-or-role;
```
以顯示使用 pg\$1partman 擴充功能舉例，我們使用下面的範例資料庫表和分割區。此資料庫使用以時間戳記為基礎的分割表格。結構描述`data_mart`包含一個名為`events`的表格和一個名為`created_at`的欄。下列設定包含在`events`表格中：
+ 主索引鍵`event_id`和`created_at`，必須有用於引導分割區的欄。
+ 強制執行`ck_valid_operation`表格欄值的檢查約束`operation`。
+ 兩個外鍵，其中一個（`fk_orga_membership)`）指向外部表格`organization`，另一個（`fk_parent_event_id`）是自引用的外鍵。
+ 兩個索引，其中一個（`idx_org_id`）用於外鍵，另一個（`idx_event_type`）用於事件類型。
下列 DDL 陳述式會建立這些物件，這些物件會自動包含在每個分割區上。
```
CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
org_name TEXT,
CONSTRAINT pk_organization PRIMARY KEY (org_id)
);
CREATE TABLE data_mart.events(
event_id BIGSERIAL,
operation CHAR(1),
value FLOAT(24),
parent_event_id BIGINT,
event_type VARCHAR(25),
org_id BIGSERIAL,
created_at timestamp,
CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at),
CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'),
CONSTRAINT fk_orga_membership
FOREIGN KEY(org_id)
REFERENCES data_mart.organization (org_id),
CONSTRAINT fk_parent_event_id
FOREIGN KEY(parent_event_id, created_at)
REFERENCES data_mart.events (event_id,created_at)
) PARTITION BY RANGE (created_at);
CREATE INDEX idx_org_id ON data_mart.events(org_id);
CREATE INDEX idx_event_type ON data_mart.events(event_type);
```
## 使用 create\$1parent 函數設定分割區
啟用 `pg_partman` 擴充功能之後，可以使用此 `create_parent` 函數來設定在分割維護結構描述內的分割區。以下範例使用在 `events` 中建立的 [啟用 pg\$1partman 擴充功能使用 run\$1maintenance\$1proc 函數來設定分割區維護](#PostgreSQL_Partitions.enable) 資料表範例。如下所示呼叫 `create_parent` 函數。
```
SELECT partman.create_parent(
p_parent_table => 'data_mart.events',
p_control => 'created_at',
p_type => 'range',
p_interval => '1 day',
p_premake => 30);
```
參數如下：
+ `p_parent_table` – 父項分割表格。此表必須已經存在，並且完全符合資格 (包括結構描述)。
+ `p_control` – 分割區依據的欄。資料類型必須是整數或以時間為基礎。
+ `p_type` – 類型可以是 `'range'` 或 `'list'`。
+ `p_interval` – 每個分割區的時間間隔或整數範圍。範例值包括 `1 day`、`1 hour` 等。
+ `p_premake` – 預先建立以支援新插入的分割區數目。
如需 `create_parent` 函數的完整描述，請參閱 `pg_partman` 文件中的[建立函數](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#user-content-creation-functions)。
## 使用 run\$1maintenance\$1proc 函數來設定分割區維護
您可以執行分割區維護作業以自動建立新的分割區、分離分割區或移除舊的分割區。分割區維護依賴 `pg_partman` 擴充功能的 `run_maintenance_proc` 函數和 `pg_cron` 擴充功能，它們可以啟動內部排程器。`pg_cron`排程器會自動執行資料庫中定義的 SQL 陳述式、函數和程序。
下列範例使用中建立於`events`中的[啟用 pg\$1partman 擴充功能使用 run\$1maintenance\$1proc 函數來設定分割區維護](#PostgreSQL_Partitions.enable)表格範例，將分割區維護作業設定為自動執行。作為必要條件，將 `pg_cron` 新增至資料庫執行個體的參數群組中的 `shared_preload_libraries` 參數。
```
CREATE EXTENSION pg_cron;
UPDATE partman.part_config
SET infinite_time_partitions = true,
retention = '3 months',
retention_keep_table=true
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);
```
您可以在下面找到上述範例的逐步說明：
1. 修改與資料庫執行個體關聯的參數群組，並新增`pg_cron`至`shared_preload_libraries`參數值。此變更需要重新啟動資料庫執行個體才能生效。如需詳細資訊，請參閱 [修改 Amazon Aurora 中的資料庫參數群組中的參數](USER_WorkingWithParamGroups.Modifying.md)。
1. 使用具有 `CREATE EXTENSION pg_cron;` 許可的帳戶執行命令 `rds_superuser`。這會啟用 `pg_cron` 擴充功能。如需詳細資訊，請參閱 [使用 PostgreSQL pg\$1cron 擴充功能排程維護](PostgreSQL_pg_cron.md)。
1. 執行命令 `UPDATE partman.part_config` 以調整 `data_mart.events` 資料表的 `pg_partman` 設定。
1. 執行 `SET` . . . 命令 以使用這些子句設定 `data_mart.events` 資料表：
1. `infinite_time_partitions = true,` – 將表格設定為能夠沒有任何限制，自動建立新的分割區。
1. `retention = '3 months',` – 將表格設定為最多保留三個月。
1. `retention_keep_table=true `– 設定資料表，以便在保留期限到期時，資料表不會自動刪除。相反地，比保留期間還舊的分割區只會從父表格分離。
1. 執行 `SELECT cron.schedule` . . . 命令 進行 `pg_cron` 函數呼叫。此呼叫定義排程器執行 `pg_partman` 維護程序 `partman.run_maintenance_proc` 的頻率。在此範例中，程序會每小時執行一次。
如需 `run_maintenance_proc` 函數的完整描述，請參閱 `pg_partman` 文件中的[維護函數](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#maintenance-functions)。
# 使用 PostgreSQL pg\$1cron 擴充功能排程維護
您可以使用 PostgreSQL `pg_cron` 擴充功能，來排程 PostgreSQL 資料庫內的維護命令。如需擴充功能的詳細資訊，請參閱 pg\$1cron 文件中的[什麼是 pg\$1cron？](https://github.com/citusdata/pg_cron)。
Aurora PostgreSQL 引擎 12.6 版以及更新版本支援 `pg_cron` 擴充功能。
若要深入了解如何使用 `pg_cron`，請參閱[在 RDS for PostgreSQL 或 Aurora PostgreSQL 相容版本資料庫上使用 pg\$1cron 排程任務](https://aws.amazon.com/blogs/database/schedule-jobs-with-pg_cron-on-your-amazon-rds-for-postgresql-or-amazon-aurora-for-postgresql-databases/)
**注意**
`pg_cron` 延伸版本會在 pg\$1available\$1extensions 檢視中顯示為兩個數字的版本，例如 1.6。雖然在某些內容中可能會看到三個數字的版本，例如 1.6.4 或 1.6.5，但您必須在執行延伸升級時指定兩個數字的版本。
**Topics**
+ [設定 pg\$1cron 擴充功能](#PostgreSQL_pg_cron.enable)
+ [授與資料車使用者使用 pg\$1cron 的許可](#PostgreSQL_pg_cron.permissions)
+ [排定 pg\$1cron 任務](#PostgreSQL_pg_cron.examples)
+ [pg\$1cron 擴充功能的參考](#PostgreSQL_pg_cron.reference)
## 設定 pg\$1cron 擴充功能
設定 `pg_cron` 擴充功能，如下所示：
1. 修改與 PostgreSQL 資料庫執行個體關聯的自訂參數群組，並將 `pg_cron` 新增至 `shared_preload_libraries` 參數值。
重新啟動 PostgreSQL 資料庫執行個體，使參數群組的變更生效。若要深入了解如何使用參數群組，請參閱 [Amazon Aurora PostgreSQL 參數](AuroraPostgreSQL.Reference.ParameterGroups.md)。
1. 重新啟動 PostgreSQL 資料庫執行個體之後，請使用具有 `rds_superuser` 許可的帳戶執行下列命令。例如，如果您在為 Aurora PostgreSQL 資料庫叢集 建立 RDS 時使用了預設設定，請以使用者 `postgres` 身分連接並建立擴充功能。
```
CREATE EXTENSION pg_cron;
```
`pg_cron` 排程器設定於名為 `postgres` 的預設 PostgreSQL 資料庫內。`pg_cron` 物件會在此 `postgres` 資料庫中建立，而且所有排程動作都會在此資料庫中執行。
1. 您可以使用預設設定，或將任務排程在 PostgreSQL 資料庫執行個體的其他資料庫中執行。若要將任務排程至 PostgreSQL 資料庫執行個體中的其他資料庫，請參閱 [為預設資料庫以外的資料庫排程 cron 任務](#PostgreSQL_pg_cron.otherDB) 中的範例。
## 授與資料車使用者使用 pg\$1cron 的許可
安裝 `pg_cron` 擴充功能需要 `rds_superuser` 權限。然而，使用 `pg_cron` 的許可能夠授予 (由 `rds_superuser` 群組/角色的成員) 其他資料庫使用者，讓他們可以安排自己的任務。建議您只有在能改善生產環境中的作業時，才視需要授予對 `cron` 結構描述的許可。
若要授予資料庫使用者對 `cron` 結構描述的許可，請執行以下命令：
```
postgres=> GRANT USAGE ON SCHEMA cron TO db-user;
```
這樣會給予 *db-user* 存取 `cron` 結構描述的許可，以針對它們有權存取的物件排程 cron 作業。如果資料庫使用者沒有許可，則作業會在將錯誤訊息發佈至 `postgresql.log` 檔案後失敗，如下所示：
```
2020-12-08 16:41:00 UTC::@:[30647]:ERROR: permission denied for table table-name
2020-12-08 16:41:00 UTC::@:[27071]:LOG: background worker "pg_cron" (PID 30647) exited with exit code 1
```
換句話說，請確保被授予 `cron` 結構描述許可的資料庫使用者也具有計劃排程之物件 (資料表、結構描述等) 的許可。
Cron 作業及其成功或失敗的詳細資訊也會擷取於 `cron.job_run_details` 資料表。如需詳細資訊，請參閱[用於排程工作和擷取狀態的資料表](#PostgreSQL_pg_cron.tables)。
## 排定 pg\$1cron 任務
以下各節示範如何使用 `pg_cron` 任務排程各種管理任務。
**注意**
建立 `pg_cron` 工作時，請檢查 `max_worker_processes` 設定大於數目 `cron.max_running_jobs`。如果 `pg_cron` 任務耗盡背景工作者程序，則會失敗。`pg_cron` 任務的預設數字為 `5`。如需詳細資訊，請參閱[用於管理 pg\$1cron 擴充功能的參數](#PostgreSQL_pg_cron.parameters)。
**Topics**
+ [正在清理資料表](#PostgreSQL_pg_cron.vacuum)
+ [正在清除 pg\$1cron 歷史記錄資料表](#PostgreSQL_pg_cron.job_run_details)
+ [僅將錯誤記錄到 postgresql.log 檔案](#PostgreSQL_pg_cron.log_run)
+ [為預設資料庫以外的資料庫排程 cron 任務](#PostgreSQL_pg_cron.otherDB)
### 正在清理資料表
自動資料清理功能可處理大多數情況下的清理維護。但是，您可能想要在您選定的時間來排定清理特定的資料表。
以下為使用 `cron.schedule` 函數來設定一個任務，以在每天 22:00 (GMT) 於特定資料表上使用 `VACUUM FREEZE` 的範例。
```
SELECT cron.schedule('manual vacuum', '0 22 * * *', 'VACUUM FREEZE pgbench_accounts');
schedule
----------
1
(1 row)
```
執行上述範例之後，您可以如下所示檢查 `cron.job_run_details` 資料表中的歷史記錄。
```
postgres=> SELECT * FROM cron.job_run_details;
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+----------+----------+--------------------------------+-----------+----------------+-------------------------------+-------------------------------
1 | 1 | 3395 | postgres | adminuser| vacuum freeze pgbench_accounts | succeeded | VACUUM | 2020-12-04 21:10:00.050386+00 | 2020-12-04 21:10:00.072028+00
(1 row)
```
以下是查詢 `cron.job_run_details` 資料表以查看失敗的任務。
```
postgres=> SELECT * FROM cron.job_run_details WHERE status = 'failed';
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
------+-------+---------+----------+----------+-------------------------------+--------+--------------------------------------------------+-------------------------------+------------------------------
5 | 4 | 30339 | postgres | adminuser| vacuum freeze pgbench_account | failed | ERROR: relation "pgbench_account" does not exist | 2020-12-04 21:48:00.015145+00 | 2020-12-04 21:48:00.029567+00
(1 row)
```
如需詳細資訊，請參閱[用於排程工作和擷取狀態的資料表](#PostgreSQL_pg_cron.tables)。
### 正在清除 pg\$1cron 歷史記錄資料表
`cron.job_run_details` 資料表包含 cron 任務的歷史記錄，它可能會隨著時間的推移而變得非常大。建議您排程清除此資料表的任務。例如，保留一週的項目可能足以進行疑難排解。
下列範例會使用 [cron.schedule](#PostgreSQL_pg_cron.schedule) 函數來排程每天在午夜執行的任務，以清除 `cron.job_run_details` 資料表。這項任務只保留過去七天。如下所示使用您的 `rds_superuser` 帳戶來排程任務。
```
SELECT cron.schedule('0 0 * * *', $$DELETE
FROM cron.job_run_details
WHERE end_time < now() - interval '7 days'$$);
```
如需詳細資訊，請參閱[用於排程工作和擷取狀態的資料表](#PostgreSQL_pg_cron.tables)。
### 僅將錯誤記錄到 postgresql.log 檔案
若要防止寫入至 `cron.job_run_details` 資料表，請修改與 PostgreSQL 資料庫執行個體關聯的參數群組，並將 `cron.log_run` 參數設定為關閉。`pg_cron` 延伸模組不再寫入至資料表，而且只會將錯誤擷取至 `postgresql.log` 檔案。如需詳細資訊，請參閱[修改 Amazon Aurora 中的資料庫參數群組中的參數](USER_WorkingWithParamGroups.Modifying.md)。
使用下列命令來檢查 `cron.log_run` 參數的值。
```
postgres=> SHOW cron.log_run;
```
如需詳細資訊，請參閱[用於管理 pg\$1cron 擴充功能的參數](#PostgreSQL_pg_cron.parameters)。
### 為預設資料庫以外的資料庫排程 cron 任務
`pg_cron` 的中繼資料都保留在名為 `postgres` 的 PostgreSQL 預設資料庫中。由於使用背景工作者來執行維護 cron 任務，因此您可以在 PostgreSQL 資料庫執行個體內的任何資料庫中排程任務︰
**注意**
只有具有 `rds_superuser` 角色或 `rds_superuser` 權限的使用者才能列出資料庫中的所有 Cron 任務。其他使用者在 `cron.job` 資料表中只能檢視自己的任務。
1. 在 cron 資料庫中，使用 [cron.schedule](#PostgreSQL_pg_cron.schedule) 如常排程任務。
```
postgres=> SELECT cron.schedule('database1 manual vacuum', '29 03 * * *', 'vacuum freeze test_table');
```
1. 作為使用 `rds_superuser` 角色的使用者，請為您剛建立的任務更新資料庫欄，以便在 PostgreSQL 資料庫執行個體內的另一個資料庫中執行。
```
postgres=> UPDATE cron.job SET database = 'database1' WHERE jobid = 106;
```
1. 透過查詢 `cron.job` 資料表進行驗證。
```
postgres=> SELECT * FROM cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
------+-------------+--------------------------------+-----------+----------+----------+-----------+--------+-------------------------
106 | 29 03 * * * | vacuum freeze test_table | localhost | 8192 | database1| adminuser | t | database1 manual vacuum
1 | 59 23 * * * | vacuum freeze pgbench_accounts | localhost | 8192 | postgres | adminuser | t | manual vacuum
(2 rows)
```
**注意**
在某些情況下，您可能會新增一個打算在其他資料庫上執行的 cron 任務。在此情況下，任務可能會嘗試在預設資料庫 (`postgres`) 中執行，然後再更新正確的資料庫資料欄。如果使用者名稱具有許可，則任務會在預設資料庫中成功執行。
## pg\$1cron 擴充功能的參考
您可以使用下列參數、函數和資料表搭配 `pg_cron` 擴充功能。如需詳細資訊，請參閱 pg\$1cron 文件中的[什麼是 pg\$1cron？](https://github.com/citusdata/pg_cron)。
**Topics**
+ [用於管理 pg\$1cron 擴充功能的參數](#PostgreSQL_pg_cron.parameters)
+ [函數參考：cron.schedule](#PostgreSQL_pg_cron.schedule)
+ [函數參考：cron.unschedule](#PostgreSQL_pg_cron.unschedule)
+ [用於排程工作和擷取狀態的資料表](#PostgreSQL_pg_cron.tables)
### 用於管理 pg\$1cron 擴充功能的參數
以下是用來控制 `pg_cron` 擴充功能行為的參數清單。
| 參數 | Description |
| --- | --- |
| cron.database\$1name | 在其中保留 `pg_cron` 中繼資料的資料庫。 |
| cron.host | 要連線至 PostgreSQL 的主機名稱。您無法修改此值。 |
| cron.log\$1run | 記錄 `job_run_details` 資料表執行的每個任務。值為 `on` 或 `off`。如需詳細資訊，請參閱 [用於排程工作和擷取狀態的資料表](#PostgreSQL_pg_cron.tables)。 |
| cron.log\$1statement | 在執行之前記錄所有 cron 陳述式。值為 `on` 或 `off`。 |
| cron.max\$1running\$1jobs | 可同時執行的任務數量上限。 |
| cron.use\$1background\$1workers | 使用背景工作者而不是用戶端工作階段。您無法修改此值。 |
使用下列 SQL 命令來顯示這些參數及其值。
```
postgres=> SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%' ORDER BY name;
```
### 函數參考：cron.schedule
這個函數會排程一個 cron 任務。任務最初是在預設 `postgres` 資料庫中進行排程。該函數會返回表示任務識別符的 `bigint` 值。若要將任務排程在 PostgreSQL 資料庫執行個體中的其他資料庫中執行，請參閱 [為預設資料庫以外的資料庫排程 cron 任務](#PostgreSQL_pg_cron.otherDB) 中的範例。
該函數有兩種語法格式。
**語法**
```
cron.schedule (job_name,
schedule,
command
);
cron.schedule (schedule,
command
);
```
**參數**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_pg_cron.html)
**範例**
```
postgres=> SELECT cron.schedule ('test','0 10 * * *', 'VACUUM pgbench_history');
schedule
----------
145
(1 row)
postgres=> SELECT cron.schedule ('0 15 * * *', 'VACUUM pgbench_accounts');
schedule
----------
146
(1 row)
```
### 函數參考：cron.unschedule
這個函數會刪除 cron 任務。您可以指定 `job_name` 或 `job_id`，兩者之一。政策可確保您是移除任務排程的擁有者。該函數會返回一個表示成功或失敗的布林值。
此函數的語法格示如下。
**語法**
```
cron.unschedule (job_id);
cron.unschedule (job_name);
```
**參數**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_pg_cron.html)
**範例**
```
postgres=> SELECT cron.unschedule(108);
unschedule
------------
t
(1 row)
postgres=> SELECT cron.unschedule('test');
unschedule
------------
t
(1 row)
```
### 用於排程工作和擷取狀態的資料表
下表用於排程 cron 任務以及記錄任務完成的方式。
| 表格 | 描述 |
| --- | --- |
| cron.job | 包含每個排程任務的中繼資料。大部分與此資料表的互動應透過使用 `cron.schedule` 和 `cron.unschedule` 函數來完成。 我們建議您不要將更新或插入權限直接提供給此資料表。這樣做將允許使用者更新 `username` 資料欄，以 `rds-superuser` 身分執行任務。 |
| cron.job\$1run\$1details | 包含過去排程任務執行的歷史記錄資訊。這對於調查任務執行的狀態、傳回訊息以及開始和結束時間非常有用。 若要防止此資料表無限期增長，請定期清除。如需範例，請參閱 [正在清除 pg\$1cron 歷史記錄資料表](#PostgreSQL_pg_cron.job_run_details)。 |
# 使用 PgAudit 記錄資料庫活動
金融機構、政府機構和許多產業都需要保留「稽核日誌」**，以符合法規要求。透過使用 PostgreSQL 稽核擴充功能 (PgAudit) 搭配 Aurora PostgreSQL 資料庫叢集，您可以擷取稽核人員通常需要的詳細記錄，以符合法規要求。例如，您可以設定 pgAudit 擴充功能來追蹤對特定資料庫和資料表所做的變更、記錄進行變更的使用者，以及許多其他詳細資訊。
pgAudit 擴充功能建置在原生 PostgreSQL 記錄基礎結構的功能之上，以更多的詳細資訊擴充日誌訊息。換言之，您可以使用與檢視任何日誌訊息相同的方法來檢視稽核日誌。如需 PostgreSQL 記錄的詳細資訊，請參閱 [Aurora PostgreSQL 資料庫日誌檔案](USER_LogAccess.Concepts.PostgreSQL.md)。
pgAudit 擴充功能會從日誌中刪減敏感資料，例如純文字密碼。如果您的 Aurora PostgreSQL 資料庫叢集設定為記錄資料操作語言 (DML) 陳述式 (如[開啟 Aurora PostgreSQL 資料庫叢集 的查詢記錄](USER_LogAccess.Concepts.PostgreSQL.Query_Logging.md)中所述)，您可以使用 PostgreSQL 稽核擴充功能避免純文字密碼問題。
您可以非常具體地設定對資料庫執行個體的稽核。您可以稽核所有資料庫和所有使用者。或者，您可以選擇僅稽核某些資料庫、使用者和其他物件。您也可以明確排除特定使用者和資料庫，使其不受稽核。如需更多詳細資訊，請參閱 [從稽核記錄中排除使用者或資料庫](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md)。
鑑於可以擷取的詳細資訊量，我們建議您，如果的確使用 pgAudit，請監控您的儲存耗用量。
所有可用的 Aurora PostgreSQL 版本都支援 PgAudit 擴充功能。如需 Aurora PostgreSQL 版本支援的 pgSQL 版本清單，請參閱《Aurora PostgreSQL 版本資訊》**中的 [Amazon Aurora PostgreSQL 的擴充功能版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html)。
**Topics**
+ [設定 pgAudit 擴充功能](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md)
+ [稽核資料庫物件](Appendix.PostgreSQL.CommonDBATasks.pgaudit.auditing.md)
+ [從稽核記錄中排除使用者或資料庫](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md)
+ [pgAudit 擴充功能的參考](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md)
# 設定 pgAudit 擴充功能
若要在 Aurora PostgreSQL 資料庫叢集上設定 PgAudit 擴充功能，您必須先將 PgAudit 新增至 Aurora PostgreSQL 資料庫叢集的自訂資料庫叢集參數群組上的共用程式庫。如需建立自訂資料庫參數群組的相關資訊，請參閱 [Amazon Aurora 的參數群組](USER_WorkingWithParamGroups.md)。接下來，您會安裝 pgAudit 擴充功能。最後，您會指定要稽核的資料庫和物件。本節中的程序展示做法。您可以使用 AWS 管理主控台 或 AWS CLI。
您必須具有做為 `rds_superuser` 角色的許可，才能執行所有這些任務。
以下步驟假設您的 Aurora PostgreSQL 資料庫叢集與自訂資料庫叢集參數群組相關聯。
## 主控台
**設定 pgAudit 擴充功能**
1. 登入 AWS 管理主控台，開啟位於 [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/) 的 Amazon RDS 主控台。
1. 在導覽窗格中，選擇您的 Aurora PostgreSQL 資料庫叢集的寫入器執行個體。
1. 針對您的 Aurora PostgreSQL 資料庫叢集的寫入器執行個體開啟 **Configuration** (組態) 索引標籤。在執行個體詳細資訊之間，尋找 **Parameter group** (參數群組) 連結。
1. 選擇連結以開啟與 Aurora PostgreSQL 資料庫叢集相關聯的自訂參數。
1. 在 **Parameters** (參數) 搜尋欄位中，輸入 `shared_pre` 以尋找 `shared_preload_libraries` 參數。
1. 選擇 **Edit parameters** (編輯參數) 以存取屬性值。
1. 在 **Values** (值) 欄位中，將 `pgaudit` 新增至清單。使用逗號區隔值清單中的項目。
![\[已新增 pgAudit 之 shared_preload_libaries 參數的影像。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/AuroraUserGuide/images/apg_rpg_shared_preload_pgaudit.png)
1. 重新啟動 Aurora PostgreSQL 資料庫叢集的寫入器執行個體，以便您對 `shared_preload_libraries` 參數所做的變更生效。
1. 當執行個體可用時，請驗證 pgAudit 是否已初始化。使用 `psql` 連線至 Aurora PostgreSQL 資料庫叢集的寫入器執行個體，然後執行下列命令。
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
1. 在初始化 pgAudit 之後，您現在可以建立擴充功能。您必須在初始化程式庫之後建立擴充功能，因為 `pgaudit` 擴充功能會安裝事件觸發條件，以稽核資料定義語言 (DDL) 陳述式。
```
CREATE EXTENSION pgaudit;
```
1. 關閉 `psql` 工作階段。
```
labdb=> \q
```
1. 登入 AWS 管理主控台，開啟位於 [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/) 的 Amazon RDS 主控台。
1. 在清單中尋找 `pgaudit.log` 參數，並設定為適合您使用案例的適當值。例如，將 `pgaudit.log` 參數設定為 `write` (如下圖所示) 會擷取日誌的插入、更新、刪除，以及其他一些類型的變更。
![\[pgaudit.log 參數與設定的影像。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/AuroraUserGuide/images/rpg_set_pgaudit-log-level.png)
您也可以針對 `pgaudit.log` 參數選擇下列其中一個值。
+ none – 這是預設值。不會記錄任何資料庫變更。
+ all – 記錄一切 (read、write、function、role、ddl、misc)。
+ ddl – 記錄未包含在 `ROLE` 類別中的所有資料定義語言 (DDL) 陳述式。
+ function – 記錄函數呼叫和 `DO` 區塊。
+ misc – 記錄其他命令，例如 `DISCARD`、`FETCH`、`CHECKPOINT`、`VACUUM` 和 `SET`。
+ read – 來源為關聯 (例如資料表) 或查詢時，記錄 `SELECT` 和 `COPY`。
+ role – 記錄與角色和權限相關的陳述式，例如 `GRANT`、`REVOKE`、`CREATE ROLE`、`ALTER ROLE` 和 `DROP ROLE`。
+ write – 目的地為關聯 (資料表) 時，記錄 `INSERT`、`UPDATE`、`DELETE`、`TRUNCATE` 和 `COPY`。
1. 選擇**儲存變更**。
1. 前往 [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)，開啟 Amazon RDS 主控台。
1. 在資料庫清單中，選擇您的 Aurora PostgreSQL 資料庫叢集的寫入器執行個體 。
## AWS CLI
**設定 pgAudit**
若要使用 AWS CLI 設定 pgAudit，請呼叫 [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) 操作，來修改自訂參數群組中的稽核日誌參數，如下列程序所示。
1. 使用下列 AWS CLI 命令，將 `pgaudit` 新增至 `shared_preload_libraries` 參數。
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \
--region aws-region
```
1. 使用下列 AWS CLI 命令重新啟動 Aurora PostgreSQL 資料庫叢集的寫入器執行個體，以便初始化 pgaudit 程式庫。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. 當執行個體可用時，您可以驗證 `pgaudit` 是否已初始化。使用 `psql` 連線至 Aurora PostgreSQL 資料庫叢集的寫入器執行個體，然後執行下列命令。
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
在初始化 PgAudit 之後，您現在可以建立擴充功能。
```
CREATE EXTENSION pgaudit;
```
1. 關閉 `psql` 工作階段，以便您可以使用 AWS CLI。
```
labdb=> \q
```
1. 使用下列 AWS CLI 命令，來指定要由工作階段稽核記錄所記錄的陳述式類別。此範例會將 `pgaudit.log` 參數設定為 `write`，此參數會擷取日誌的插入、更新和刪除操作。
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=pgaudit.log,ParameterValue=write,ApplyMethod=pending-reboot" \
--region aws-region
```
您也可以針對 `pgaudit.log` 參數選擇下列其中一個值。
+ none – 這是預設值。不會記錄任何資料庫變更。
+ all – 記錄一切 (read、write、function、role、ddl、misc)。
+ ddl – 記錄未包含在 `ROLE` 類別中的所有資料定義語言 (DDL) 陳述式。
+ function – 記錄函數呼叫和 `DO` 區塊。
+ misc – 記錄其他命令，例如 `DISCARD`、`FETCH`、`CHECKPOINT`、`VACUUM` 和 `SET`。
+ read – 來源為關聯 (例如資料表) 或查詢時，記錄 `SELECT` 和 `COPY`。
+ role – 記錄與角色和權限相關的陳述式，例如 `GRANT`、`REVOKE`、`CREATE ROLE`、`ALTER ROLE` 和 `DROP ROLE`。
+ write – 目的地為關聯 (資料表) 時，記錄 `INSERT`、`UPDATE`、`DELETE`、`TRUNCATE` 和 `COPY`。
使用下列 AWS CLI 命令，重新啟動 Aurora PostgreSQL 資料庫叢集的寫入器執行個體。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# 稽核資料庫物件
在您的 Aurora PostgreSQL 資料庫叢集上設定 pgAudit，並針對您的要求進行設定後，會在 PostgreSQL 日誌中擷取更詳細的資訊。例如，雖然預設 PostgreSQL 記錄組態會識別在資料庫資料表中進行變更的日期和時間，但使用 pgAudit 擴充功能時，日誌項目可以包括結構描述、進行變更的使用者，以及其他詳細資訊，視擴充功能參數的設定方式而定。您可以將稽核設定為以下列方式追蹤變更。
+ 對於每個工作階段，依使用者。對於工作階段層級，您可以擷取完整的命令文字。
+ 對於每個物件，依使用者和資料庫。
在系統上建立 `rds_pgaudit` 角色，然後將此角色新增至自訂參數群組中的 `pgaudit.role` 參數時，便會啟用物件稽核功能。根據預設，未設定 `pgaudit.role` 參數，且唯一允許的值為 `rds_pgaudit`。下列步驟假設 `pgaudit` 已初始化，且您已遵循[設定 pgAudit 擴充功能](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md)中的程序建立 `pgaudit` 擴充功能。
![\[設定 pgAudit 後 PostgreSQL 日誌檔的影像。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/AuroraUserGuide/images/pgaudit-log-example.png)
如此範例所示，「LOG: AUDIT: SESSION」一行提供資料表及其結構描述的相關資訊，以及其他詳細資訊。
**設定物件稽核**
1. 使用 `psql` 連線至 Aurora PostgreSQL 資料庫叢集的寫入器執行個體。。
```
psql --host=your-instance-name.aws-region.rds.amazonaws.com --port=5432 --username=postgrespostgres --password --dbname=labdb
```
1. 使用下列命令建立名為 `rds_pgaudit` 的資料庫角色。
```
labdb=> CREATE ROLE rds_pgaudit;
CREATE ROLE
labdb=>
```
1. 關閉 `psql` 工作階段。
```
labdb=> \q
```
在接下來的幾個步驟中，使用 AWS CLI 修改自訂參數群組中的稽核日誌參數。
1. 使用下列 AWS CLI 命令，將 `pgaudit.role` 參數設定為 `rds_pgaudit`。根據預設，此參數是空的，且 `rds_pgaudit` 是唯一允許的值。
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" \
--region aws-region
```
1. 使用下列 AWS CLI 命令，重新啟動 Aurora PostgreSQL 資料庫叢集的寫入器執行個體，以便您對參數所做的變更生效。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. 執行下列命令來確認 `pgaudit.role` 已設定為 `rds_pgaudit`。
```
SHOW pgaudit.role;
pgaudit.role
------------------
rds_pgaudit
```
如要測試 pgAudit 記錄功能，您可以執行幾個要稽核的範例命令。例如，您可以執行下列命令。
```
CREATE TABLE t1 (id int);
GRANT SELECT ON t1 TO rds_pgaudit;
SELECT * FROM t1;
id
----
(0 rows)
```
資料庫記錄應包含類似以下的項目。
```
...
2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT:
OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1;
...
```
如需有關檢視日誌的資訊，請參閱 [監控 Amazon Aurora 日誌檔案](USER_LogAccess.md)。
若要進一步了解 pgAudit 擴充功能，請參閱 GitHub 上的 [pgAudit](https://github.com/pgaudit/pgaudit/blob/master/README.md)。
# 從稽核記錄中排除使用者或資料庫
如[Aurora PostgreSQL 資料庫日誌檔案](USER_LogAccess.Concepts.PostgreSQL.md)中所述，PostgreSQL 日誌會取用儲存空間。使用 pgAudit 擴充功能會在不同程度上增加日誌中收集的資料量，取決於您追蹤的變更。您可能不需要稽核 Aurora PostgreSQL 資料庫叢集中的每個使用者或資料庫。
若要將對儲存的影響降到最低，並避免不必要地擷取稽核記錄，您可以將使用者和資料庫排除在稽核之外。您也可以在給定的工作階段中變更記錄。下列範例向您展示做法。
**注意**
優先處理工作階段層級的參數設定，再處理 Aurora PostgreSQL 資料庫叢集寫入器執行個體的自訂資料庫參數群組中的設定。如果您不想要資料庫使用者略過稽核記錄組態設定，請務必變更其許可。
假設您的 Aurora PostgreSQL 資料庫叢集已設定為稽核所有使用者和資料庫的相同層級活動。然後，您決定不想要稽核使用者 `myuser`。您可以使用下列 SQL 命令來關閉 `myuser` 的稽核。
```
ALTER USER myuser SET pgaudit.log TO 'NONE';
```
然後，您可以使用下列查詢來檢查 `pgaudit.log` 的 `user_specific_settings` 欄，以確認參數已設定為 `NONE`。
```
SELECT
usename AS user_name,
useconfig AS user_specific_settings
FROM
pg_user
WHERE
usename = 'myuser';
```
您會看到如下輸出。
```
user_name | user_specific_settings
-----------+------------------------
myuser | {pgaudit.log=NONE}
(1 row)
```
您可以使用下列命令，在資料庫的工作階段當中關閉特定使用者的記錄。
```
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'none';
```
使用下列查詢，針對特定使用者和資料庫組合檢查 pgaudit.log 的 settings 欄。
```
SELECT
usename AS "user_name",
datname AS "database_name",
pg_catalog.array_to_string(setconfig, E'\n') AS "settings"
FROM
pg_catalog.pg_db_role_setting s
LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
LEFT JOIN pg_catalog.pg_user r ON r.usesysid = setrole
WHERE
usename = 'myuser'
AND datname = 'mydatabase'
ORDER BY
1,
2;
```
您會看到類似下列的輸出。
```
user_name | database_name | settings
-----------+---------------+------------------
myuser | mydatabase | pgaudit.log=none
(1 row)
```
在關閉 `myuser` 的稽核之後，您決定不想要追蹤 `mydatabase` 的變更。您可以使用下列命令來關閉該特定資料庫的稽核。
```
ALTER DATABASE mydatabase SET pgaudit.log to 'NONE';
```
然後，使用下列查詢來檢查 database\$1specific\$1settings 欄，以確認 pgaudit.log 已設定為 NONE。
```
SELECT
a.datname AS database_name,
b.setconfig AS database_specific_settings
FROM
pg_database a
FULL JOIN pg_db_role_setting b ON a.oid = b.setdatabase
WHERE
a.datname = 'mydatabase';
```
您會看到如下輸出。
```
database_name | database_specific_settings
---------------+----------------------------
mydatabase | {pgaudit.log=NONE}
(1 row)
```
若要將設定回復為 myuser 的預設設定，請使用下列命令：
```
ALTER USER myuser RESET pgaudit.log;
```
若要將設定回復為資料庫的預設設定，請使用下列命令。
```
ALTER DATABASE mydatabase RESET pgaudit.log;
```
若要將使用者和資料庫重設為預設設定，請使用下列命令。
```
ALTER USER myuser IN DATABASE mydatabase RESET pgaudit.log;
```
您也可以將 `pgaudit.log` 設定為 `pgaudit.log` 參數的其他允許值之一，將特定事件擷取至日誌。如需更多詳細資訊，請參閱 [允許的 `pgaudit.log` 參數設定清單](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings)。
```
ALTER USER myuser SET pgaudit.log TO 'read';
ALTER DATABASE mydatabase SET pgaudit.log TO 'function';
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'read,function'
```
# pgAudit 擴充功能的參考
您可以變更本節中列出的一或多個參數，針對稽核日誌指定您所需的詳細資訊層級。
## 控制 pgAudit 行為
您可以變更下表中列出的一或多個參數來控制稽核記錄。
| 參數 | Description |
| --- | --- |
| `pgaudit.log` | 指定工作階段稽核記錄將記錄哪些陳述式類別。允許的值包括 ddl、function、misc、read、role、write、none、all。如需詳細資訊，請參閱[允許的 `pgaudit.log` 參數設定清單](#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings)。 |
| `pgaudit.log_catalog` | 開啟 (設定為 1) 時，如果陳述式中的所有關聯都在 pg\$1catalog 中，則會將陳述式新增至稽核線索。 |
| `pgaudit.log_level` | 指定要用於日誌項目的日誌層級。允許的值：disabled、debug5、debug4、debug3、debug2、debug1、info、notice、warning、log |
| `pgaudit.log_parameter` | 開啟 (設定為 1) 時，會在稽核日誌中擷取隨陳述式傳遞的參數。 |
| `pgaudit.log_relation` | 開啟 (設定為 1) 時，工作階段的稽核日誌會為 SELECT 或 DML 陳述式中參考的每個關聯 (TABLE、VIEW 等) 建立個別的日誌項目。 |
| `pgaudit.log_statement_once` | 指定日誌記錄包含的陳述式文字和參數，具有陳述式/子陳述式組合的第一個日誌項目，還是具有每個項目。 |
| `pgaudit.role` | 指定用於物件稽核日誌記錄的主要角色。唯一允許的項目為 `rds_pgaudit`。 |
## 允許的 `pgaudit.log` 參數設定清單
| Value | Description |
| --- | --- |
| 無 | 這是預設值。不會記錄任何資料庫變更。 |
| 全部 | 記錄一切 (read、write、function、role、ddl、misc)。 |
| ddl | 記錄未包含在 `ROLE` 類別中的所有資料定義語言 (DDL) 陳述式。 |
| 函數 | 記錄函數呼叫和 `DO` 區塊。 |
| misc | 記錄其他命令，例如 `DISCARD`、`FETCH`、`CHECKPOINT`、`VACUUM` 和 `SET`。 |
| 讀取 | 來源為關聯 (例如資料表) 或查詢時，記錄 `SELECT` 和 `COPY`。 |
| role | 記錄與角色和權限相關的陳述式，例如 `GRANT`、`REVOKE`、`CREATE ROLE`、`ALTER ROLE` 和 `DROP ROLE`。 |
| write | 目的地為關聯 (資料表) 時，記錄 `INSERT`、`UPDATE`、`DELETE`、`TRUNCATE` 和 `COPY`。 |
若要使用工作階段稽核功能記錄多個事件類型，請使用逗號分隔清單。若要記錄所有事件類型，請將 `pgaudit.log` 設定為 `ALL`。重新啟動資料庫執行個體以套用變更。
您可以透過物件稽核，調整稽核記錄以使用特定關聯。例如，您可以指定您想要針對一或多個資料表上的 `READ` 操作稽核記錄。
# 使用 pglogical 跨執行個體同步資料
所有目前可用的 Aurora PostgreSQL 版本都支援 `pglogical` 延伸模組。pglogical 延伸模組早於 PostgreSQL 在第 10 版中引入的功能，其類似於邏輯複寫功能。如需詳細資訊，請參閱 [以 Aurora 進行 PostgreSQL 邏輯複寫的概觀](AuroraPostgreSQL.Replication.Logical.md)。
`pglogical` 延伸模組支援在兩個或以上 Aurora PostgreSQL 資料庫叢集之間進行邏輯複寫。它也支援在不同的 PostgreSQL 版本之間，以及在 RDS for PostgreSQL 資料庫執行個體和 Aurora PostgreSQL 資料庫叢集上執行的資料庫之間進行複寫。`pglogical` 延伸模組會使用發佈-訂閱模型，將資料表和其他物件 (例如序列) 的變更從發佈者複製到訂閱者。它依賴複寫槽，以確保變更從發佈者節點同步到訂閱者節點，其定義如下。
+ *發佈者節點*是 Aurora PostgreSQL 資料庫叢集，其是要複寫到其他節點的資料來源。發佈者節點定義要在發佈集中複製的資料表。
+ *訂閱者節點*是 Aurora PostgreSQL 資料庫叢集，其會從發佈者接收 WAL 更新。訂閱者會建立訂閱以連線至發佈者，並取得解碼的 WAL 資料。訂閱者建立訂閱時，系統會在發佈者節點上建立複寫槽。
您可以在以下內容中找到如何設定 `pglogical` 延伸模組的相關資訊。
**Topics**
+ [pglogical 延伸模組的需求和限制](#Appendix.PostgreSQL.CommonDBATasks.pglogical.requirements-limitations)
+ [設定 pglogical 延伸模組](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md)
+ [針對 Aurora PostgreSQL 資料庫叢集設定邏輯複寫](Appendix.PostgreSQL.CommonDBATasks.pglogical.setup-replication.md)
+ [在重大升級之後重新建立邏輯複寫](Appendix.PostgreSQL.CommonDBATasks.pglogical.recover-replication-after-upgrade.md)
+ [管理 Aurora PostgreSQL 的邏輯複寫槽](Appendix.PostgreSQL.CommonDBATasks.pglogical.handle-slots.md)
+ [pglogical 延伸模組的參數參考](Appendix.PostgreSQL.CommonDBATasks.pglogical.reference.md)
## pglogical 延伸模組的需求和限制
所有目前可用的 Aurora PostgreSQL 版本都支援 `pglogical` 延伸模組。
發佈者節點和訂閱者節點都必須針對邏輯複寫進行設定。
您想要從發佈者複寫到訂閱者的資料表必須具有相同的名稱和相同的結構描述。這些資料表亦須包含相同的資料欄，而且這些資料欄必須使用相同的資料類型。發佈者和訂閱者資料表都必須具有相同的主索引鍵。建議您僅使用 PRIMARY KEY 作為唯一限制條件。
對於 CHECK 限制條件和 NOT NULL 限制條件，訂閱者節點上的資料表與發佈者節點上的資料表相比之下，可以具有更寬鬆的限制條件。
`pglogical` 延伸模組會提供 PostgreSQL (第 10 版及更新版本) 內建邏輯複寫功能不支援的功能，例如雙向複寫。如需詳細資訊，請參閱[使用 pglogical 進行 PostgreSQL 雙向複寫](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/)。
# 設定 pglogical 延伸模組
若要在 Aurora PostgreSQL 資料庫叢集上設定 `pglogical` 延伸模組，請將 `pglogical` 新增至下列群組上的共用程式庫：RDS for PostgreSQL 資料庫執行個體的自訂資料庫參數群組。Aurora PostgreSQL 資料庫叢集的自訂資料庫叢集參數群組。您亦需將 `rds.logical_replication` 參數的值設為 `1`，以開啟邏輯解碼。最後，您可以在資料庫中建立延伸模組。您可以使用 AWS 管理主控台 或 AWS CLI 進行這些任務。
您必須具有做為 `rds_superuser` 角色的許可，才能執行這些任務。
以下步驟假設您的 Aurora PostgreSQL 資料庫叢集與自訂資料庫叢集參數群組相關聯。如需建立自訂資料庫參數群組的相關資訊，請參閱 [Amazon Aurora 的參數群組](USER_WorkingWithParamGroups.md)。
## 主控台
**設定 pglogical 延伸模組**
1. 登入 AWS 管理主控台 並開啟位於 https：//[https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/) 的 Amazon RDS 主控台。
1. 在導覽窗格中，選擇您的 Aurora PostgreSQL 資料庫叢集的寫入器執行個體。
1. 針對您的 Aurora PostgreSQL 資料庫叢集的寫入器執行個體開啟 **Configuration** (組態) 索引標籤。在執行個體詳細資訊之間，尋找 **Parameter group** (參數群組) 連結。
1. 選擇連結以開啟與 Aurora PostgreSQL 資料庫叢集相關聯的自訂參數。
1. 在 **Parameters** (參數) 搜尋欄位中，輸入 `shared_pre` 以尋找 `shared_preload_libraries` 參數。
1. 選擇 **Edit parameters** (編輯參數) 以存取屬性值。
1. 在 **Values** (值) 欄位中，將 `pglogical` 新增至清單。使用逗號區隔值清單中的項目。
![\[已新增 pglogical 之 shared_preload_libraries 參數的影像。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/AuroraUserGuide/images/apg_rpg_shared_preload_pglogical.png)
1. 尋找 `rds.logical_replication` 參數並將其設為 `1`，以開啟邏輯複寫。
1. 重新啟動 Aurora PostgreSQL 資料庫叢集的寫入器執行個體，讓您的變更生效。
1. 當執行個體可用時，您可以使用 `psql` (或 pgAdmin) 連線至 Aurora PostgreSQL 資料庫叢集的寫入器執行個體。。
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. 若要驗證 pglogical 是否已初始化，請執行下列命令。
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pglogical
(1 row)
```
1. 驗證啟用邏輯解碼的設定，如下所示。
```
SHOW wal_level;
wal_level
-----------
logical
(1 row)
```
1. 建立延伸模組，如下所示。
```
CREATE EXTENSION pglogical;
EXTENSION CREATED
```
1. 選擇**儲存變更**。
1. 前往 [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)，開啟 Amazon RDS 主控台。
1. 從資料庫清單中選擇 Aurora PostgreSQL 資料庫叢集的寫入器執行個體以選取它，然後從 Actions (動作) 功能表中選擇 **Reboot** (重新啟動)。
## AWS CLI
**設定 pglogical 延伸模組**
若要使用 設定 pglogical AWS CLI，您可以呼叫 [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) 操作來修改自訂參數群組中的特定參數，如下列程序所示。
1. 使用下列 AWS CLI 命令將 `pglogical`新增至 `shared_preload_libraries` 參數。
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=shared_preload_libraries,ParameterValue=pglogical,ApplyMethod=pending-reboot" \
--region aws-region
```
1. 使用下列 AWS CLI 命令將 `rds.logical_replication` 設定為 `1`，以開啟 Aurora PostgreSQL 資料庫叢集寫入器執行個體的邏輯解碼功能。
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot" \
--region aws-region
```
1. 使用以下 AWS CLI 命令重新啟動 Aurora PostgreSQL 資料庫叢集的寫入器執行個體以便初始化 pglogical 程式庫。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. 當執行個體可用時，請使用 `psql` 連線至 Aurora PostgreSQL 資料庫叢集的寫入器執行個體。。
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. 建立延伸模組，如下所示。
```
CREATE EXTENSION pglogical;
EXTENSION CREATED
```
1. 使用以下 AWS CLI 命令重新啟動 Aurora PostgreSQL 資料庫叢集的寫入器執行個體。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# 針對 Aurora PostgreSQL 資料庫叢集設定邏輯複寫
下列程序說明如何在兩個 Aurora PostgreSQL 資料庫叢集之間啟動邏輯複寫。這些步驟假設來源 (發佈者) 和目標 (訂閱者) 都已設定 `pglogical` 延伸模組，如 [設定 pglogical 延伸模組](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md) 中所述。
**注意**
訂閱者節點的 `node_name` 不能以 `rds` 開頭。
**建立發佈者節點並定義要複製的資料表**
這些步驟假設您的 Aurora PostgreSQL 資料庫叢集具有一個內含資料庫的寫入器執行個體，其中包含一或多個您要複寫到另一個節點的資料表。您必須在訂閱者上重新建立來自發佈者的資料表結構，因此必要時先取得資料表結構。您可以使用 `psql` 中繼命令 `\d tablename`，然後在訂閱者執行個體上建立相同的資料表，以執行該操作。下列程序會在發佈者 (來源) 上建立範例資料表，以供示範之用。
1. 使用 `psql` 連線至具有資料表的執行個體，您想要將該資料表用作訂閱者的來源。
```
psql --host=source-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
如果您沒有要複寫的現有資料表，則可以建立如下的範例資料表。
1. 使用下列 SQL 陳述式建立範例資料表。
```
CREATE TABLE docs_lab_table (a int PRIMARY KEY);
```
1. 使用下列 SQL 陳述式，將產生的資料填入資料表中。
```
INSERT INTO docs_lab_table VALUES (generate_series(1,5000));
INSERT 0 5000
```
1. 使用下列 SQL 陳述式，驗證資料是否存在於資料表中。
```
SELECT count(*) FROM docs_lab_table;
```
1. 將此 Aurora PostgreSQL 資料庫叢集識別為發佈者節點，如下所示。
```
SELECT pglogical.create_node(
node_name := 'docs_lab_provider',
dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 dbname=labdb');
create_node
-------------
3410995529
(1 row)
```
1. 將您要複寫的資料表新增至預設複寫集。如需複寫集的詳細資訊，請參閱 pglogical 文件中的 [Replication sets](https://github.com/2ndQuadrant/pglogical/tree/REL2_x_STABLE/docs#replication-sets) (複寫集)。
```
SELECT pglogical.replication_set_add_table('default', 'docs_lab_table', 'true', NULL, NULL);
replication_set_add_table
---------------------------
t
(1 row)
```
發佈者節點設定完成。您現在可以設定訂閱者節點，從發佈者接收更新。
**設定訂閱者節點並建立訂閱來接收更新**
這些步驟假設 Aurora PostgreSQL 資料庫叢集已使用 `pglogical` 延伸模組進行設定。如需詳細資訊，請參閱[設定 pglogical 延伸模組](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md)。
1. 使用 `psql` 來連線至您想要從發佈者接收更新的執行個體。
```
psql --host=target-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. 在訂閱者 Aurora PostgreSQL 資料庫叢集上，建立存在於發佈者上的相同資料表。在此範例中，資料表是 `docs_lab_table`。您可以建立資料表，如下所示。
```
CREATE TABLE docs_lab_table (a int PRIMARY KEY);
```
1. 驗證此資料表是否為空的。
```
SELECT count(*) FROM docs_lab_table;
count
-------
0
(1 row)
```
1. 將此 Aurora PostgreSQL 資料庫叢集識別為訂閱者節點，如下所示。
```
SELECT pglogical.create_node(
node_name := 'docs_lab_target',
dsn := 'host=target-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=********');
create_node
-------------
2182738256
(1 row)
```
1. 建立訂閱。
```
SELECT pglogical.create_subscription(
subscription_name := 'docs_lab_subscription',
provider_dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=*******',
replication_sets := ARRAY['default'],
synchronize_data := true,
forward_origins := '{}' );
create_subscription
---------------------
1038357190
(1 row)
```
完成此步驟時，會在訂閱者上的資料表中建立來自發佈者上資料表的資料。您可以使用下列 SQL 查詢來驗證是否已發生此情況。
```
SELECT count(*) FROM docs_lab_table;
count
-------
5000
(1 row)
```
從此開始，對發佈者上資料表所做的變更會複寫到訂閱者上的資料表。
# 在重大升級之後重新建立邏輯複寫
在對設為邏輯複寫之發佈者節點的 Aurora PostgreSQL 資料庫叢集執行主要版本升級之前，您必須捨棄所有複寫槽，即使是未作用中的複寫槽也一樣。建議您暫時從發佈者節點轉移資料庫交易、捨棄複寫槽、升級 Aurora PostgreSQL 資料庫叢集，然後重新建立並重新啟動複寫。
複寫槽僅託管於發佈者節點上。邏輯複寫案例中的 Aurora PostgreSQL 訂閱者節點沒有要捨棄的複寫槽。Aurora PostgreSQL 主要版本升級程序支援將訂閱者獨立升級至與發佈者節點不同的新主要 PostgreSQL 版本。不過，升級程序確實會中斷複寫程序，並干擾發佈者節點與訂閱者節點之間的 WAL 資料同步。在升級發佈者、訂閱者或兩者之後，您必須重新建立發佈者與訂閱者之間的邏輯複寫。下列程序說明如何判斷複寫是否已中斷，以及如何解決問題。
## 判斷邏輯複寫是否已中斷
您可以查詢發佈者節點或訂閱者節點，來判斷複寫程序是否已中斷，如下所示。
**檢查發佈者節點**
+ 使用 `psql` 連線到發佈者節點，然後查詢 `pg_replication_slots` 函數。請注意作用中資料欄中的值。通常，這會傳回 `t` (true)，表明複寫作用中。如果查詢傳回 `f` (false)，表示已停止複寫至訂閱者。
```
SELECT slot_name,plugin,slot_type,active FROM pg_replication_slots;
slot_name | plugin | slot_type | active
-------------------------------------------+------------------+-----------+--------
pgl_labdb_docs_labcb4fa94_docs_lab3de412c | pglogical_output | logical | f
(1 row)
```
**檢查訂閱者節點**
在訂閱者節點上，您可以採取三種不同的方式來檢查複寫的狀態。
+ 查看訂閱者節點上的 PostgreSQL 日誌，以找出失敗訊息。日誌會以包含結束代碼 1 的訊息識別失敗，如下所示。
```
2022-07-06 16:17:03 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 14610) exited with exit code 1
2022-07-06 16:19:44 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 21783) exited with exit code 1
```
+ 查詢 `pg_replication_origin` 函數。使用 `psql` 連線至訂閱者節點上的資料庫，然後查詢 `pg_replication_origin` 函數，如下所示。
```
SELECT * FROM pg_replication_origin;
roident | roname
---------+--------
(0 rows)
```
空白結果集表示複寫已中斷。通常，您會看到如下輸出。
```
roident | roname
---------+----------------------------------------------------
1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
+ 查詢 `pglogical.show_subscription_status` 函數，如下列範例所示。
```
SELECT subscription_name,status,slot_name FROM pglogical.show_subscription_status();
subscription_name | status | slot_name
---====----------------+--------+-------------------------------------
docs_lab_subscription | down | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
此輸出表明複寫已中斷。其狀態為 `down`。通常，輸出會將狀態顯示為 `replicating`。
如果您的邏輯複寫程序已中斷，您可以遵循下列步驟重新建立複寫。
**重新建立發佈者與訂閱者節點之間的邏輯複寫**
若要重新建立複寫，首先中斷訂閱者與發佈者節點的連線，然後重新建立訂閱，如下列步驟所述。
1. 使用 `psql` 連線至訂閱者節點，如下所示。
```
psql --host=222222222222.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. 使用 `pglogical.alter_subscription_disable` 函數停用訂閱。
```
SELECT pglogical.alter_subscription_disable('docs_lab_subscription',true);
alter_subscription_disable
----------------------------
t
(1 row)
```
1. 透過查詢 `pg_replication_origin` 取得發佈者節點的識別符，如下所示。
```
SELECT * FROM pg_replication_origin;
roident | roname
---------+-------------------------------------
1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
1. 使用上一個步驟的回應搭配 `pg_replication_origin_create` 命令，來指派訂閱可在重新建立時使用的識別符。
```
SELECT pg_replication_origin_create('pgl_labdb_docs_labcb4fa94_docs_lab3de412c');
pg_replication_origin_create
------------------------------
1
(1 row)
```
1. 透過傳送訂閱的名稱來開啟訂閱，其狀態為 `true`，如下列範例所示。
```
SELECT pglogical.alter_subscription_enable('docs_lab_subscription',true);
alter_subscription_enable
---------------------------
t
(1 row)
```
檢查節點的狀態。其狀態應為 `replicating`，如這個範例所示。
```
SELECT subscription_name,status,slot_name
FROM pglogical.show_subscription_status();
subscription_name | status | slot_name
-------------------------------+-------------+-------------------------------------
docs_lab_subscription | replicating | pgl_labdb_docs_lab98f517b_docs_lab3de412c
(1 row)
```
檢查發佈者節點上訂閱者複寫槽的狀態。複寫槽的 `active` 資料欄應傳回 `t` (true)，表示已重新建立複寫。
```
SELECT slot_name,plugin,slot_type,active
FROM pg_replication_slots;
slot_name | plugin | slot_type | active
-------------------------------------------+------------------+-----------+--------
pgl_labdb_docs_lab98f517b_docs_lab3de412c | pglogical_output | logical | t
(1 row)
```
# 管理 Aurora PostgreSQL 的邏輯複寫槽
在對充當邏輯複寫案例中發佈者節點的 Aurora PostgreSQL 資料庫叢集寫入器執行個體執行主要版本升級之前，您必須捨棄執行個體上的複寫槽。主要版本升級預先檢查程序會通知您，除非捨棄複寫槽，否則升級無法繼續進行。
若要識別已使用 `pglogical` 延伸模組建立的複寫槽，請登入每個資料庫並取得節點的名稱。查詢訂閱者節點時，您會在輸出中同時取得發佈者和訂閱者節點，如本範例所示。
```
SELECT * FROM pglogical.node;
node_id | node_name
------------+-------------------
2182738256 | docs_lab_target
3410995529 | docs_lab_provider
(2 rows)
```
您可以使用下列查詢，取得有關訂閱的詳細資訊。
```
SELECT sub_name,sub_slot_name,sub_target
FROM pglogical.subscription;
sub_name | sub_slot_name | sub_target
----------+--------------------------------+------------
docs_lab_subscription | pgl_labdb_docs_labcb4fa94_docs_lab3de412c | 2182738256
(1 row)
```
您現在可以捨棄訂閱，如下所示。
```
SELECT pglogical.drop_subscription(subscription_name := 'docs_lab_subscription');
drop_subscription
-------------------
1
(1 row)
```
捨棄訂閱之後，您可以刪除節點。
```
SELECT pglogical.drop_node(node_name := 'docs-lab-subscriber');
drop_node
-----------
t
(1 row)
```
您可以驗證節點是否不再存在，如下所示。
```
SELECT * FROM pglogical.node;
node_id | node_name
---------+-----------
(0 rows)
```
# pglogical 延伸模組的參數參考
在資料表中，您可以尋找與 `pglogical` 延伸模組相關聯的參數。`pglogical.conflict_log_level` 和 `pglogical.conflict_resolution` 這類參數用來處理更新衝突。對從發佈者訂閱變更的相同資料表進行本機變更時，可能會出現衝突。在各種案例期間 (例如雙向複寫或從相同發佈者複寫多個訂閱者時)，也會發生衝突。如需詳細資訊，請參閱[使用 pglogical 進行 PostgreSQL 雙向複寫](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/)。
| 參數 | Description |
| --- | --- |
| pglogical.batch\$1inserts | 可能情況下批次插入。依預設不會設定。變更為 '1' 以開啟、變更為 '0' 以關閉。 |
| pglogical.conflict\$1log\$1level | 設定用於記錄已解決衝突的日誌層級。支援的字串值為 debug5、debug4、debug3、debug2、debug1、info、notice、warning、error、log、fatal、panic。 |
| pglogical.conflict\$1resolution | 設定用來在衝突可解決時解決衝突的方法。支援的字串值為 error、apply\$1remote、keep\$1local、last\$1update\$1wins、first\$1update\$1wins。 |
| pglogical.extra\$1connection\$1options | 要新增到所有對等節點連線的連線選項。 |
| pglogical.synchronous\$1commit | pglogical 特定的同步遞交值 |
| pglogical.use\$1spi | 使用 SPI (伺服器程式設計介面) 而不是低階 API 來套用變更。設為 '1' 以開啟、設為 '0' 以關閉。如需 SPI 的詳細資訊，請參閱 PostgreSQL 文件中的 [Server Programming Interface](https://www.postgresql.org/docs/current/spi.html) (伺服器程式設計介面)。 |
# 使用 Amazon Aurora PostgreSQL 支援的外部資料包裝函式
外部資料包裝函式 (FDW) 是一種特定類型的擴充功能，可提供對外部資料的存取。例如，`oracle_fdw` 擴充功能可讓您的 Aurora PostgreSQL 資料庫執行個體 使用 Oracle 資料庫。
在下文中，您可以了解幾個受支援的 PostgreSQL 外部資料包裝函式的資訊。
**Topics**
+ [使用 log\$1fdw 擴充功能存取使用 SQL 的資料庫日誌](CHAP_PostgreSQL.Extensions.log_fdw.md)
+ [使用 postgres\$1fdw 擴充功能存取外部資料](postgresql-commondbatasks-fdw.md)
+ [使用 mysql\$1fdw 擴充功能處理 MySQL 資料庫](postgresql-mysql-fdw.md)
+ [使用 oracle\$1fdw 擴充功能處理 Oracle 資料庫](postgresql-oracle-fdw.md)
+ [使用 tds\$1fdw 擴充功能處理 SQL 資料庫](postgresql-tds-fdw.md)
# 使用 log\$1fdw 擴充功能存取使用 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`)](USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.md#USER_LogAccess.Concepts.PostgreSQL.Log_Format)。
產生 `csvlog` 日誌的一個好處為 `log_fdw` 延伸允許您建置外部資料表，並將資料整齊分割成數個資料欄。為此，您的執行個體需要與自訂資料庫參數群組關聯，則您可變更 `log_destination` 的設定。如需如何執行作業的資訊，請參閱 [Amazon Aurora 的參數群組](USER_WorkingWithParamGroups.md)。
下列範例假設 `log_destination` 參數包括 `cvslog`。
**使用 log\$1fdw 擴充功能**
1. 安裝 `log_fdw` 擴充功能。
```
postgres=> CREATE EXTENSION log_fdw;
CREATE EXTENSION
```
1. 建立日誌伺服器做為外部資料包裝函數。
```
postgres=> CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw;
CREATE SERVER
```
1. 叢日誌檔案清單全選。
```
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)
```
1. 針對選取的檔案，建立只有單一 'log\$1entry' 資料欄的資料表。
```
postgres=> SELECT create_foreign_table_for_log_file('my_postgres_error_log',
'log_server', 'postgresql.log.2023-08-09-22.csv');
```
除了目前存在的資料表之外，回應不提供任何其他詳細資訊。
```
-----------------------------------
(1 row)
```
1. 選取日誌檔案的範例。以下程式碼會擷取日誌時間和錯誤訊息描述。
```
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\$1fdw 擴充功能存取外部資料
您可以使用 [postgres\$1fdw](https://www.postgresql.org/docs/current/static/postgres-fdw.html) 擴充功能，存取遠端資料庫伺服器上資料表中的資料。如果您設定來自 PostgreSQL 資料庫執行個體的遠端連線，則也可以存取您的僅供讀取複本。
**若要使用 postgres\$1fdw 來存取遠端資料庫伺服器**
1. 安裝 postgres\$1fdw 擴充功能。
```
CREATE EXTENSION postgres_fdw;
```
1. 使用 CREATE SERVER 建立外部資料伺服器。
```
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
```
1. 建立使用者對應，找出要使用於遠端伺服器的角色。
**重要**
若要修訂密碼使其不會在日誌中顯示，請在工作階段層級設定 `log_statement=none`。在參數層級進行設定不會修訂密碼。
```
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password');
```
1. 建立一個資料表，其對應至遠端伺服器上的資料表。
```
CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL,
data text)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table');
```
# 使用 mysql\$1fdw 擴充功能處理 MySQL 資料庫
若要從 Aurora PostgreSQL 資料庫叢集存取相容於 MySQL 的資料庫，您可以安裝並使用 `mysql_fdw` 擴充功能。此外部資料包裝函數可讓您使用 MySQL、Aurora MySQL、MariaDB 和其他相容於 MySQL 的資料庫的 RDS。從 Aurora PostgreSQL 資料庫叢集到 MySQL 資料庫的連線是在最大努力的基礎上加密的，具體取決於用戶端和伺服器的組態。但是，如有需要，您可以強制執行加密。如需詳細資訊，請參閱[搭配此擴充功能使用傳輸中加密](#postgresql-mysql-fdw.encryption-in-transit)。
Amazon Aurora PostgreSQL 版本 15.4、14.9、13.12、12.16 及更高版本支援 `mysql_fdw` 擴充功能。它支援從 RDS for PostgreSQL DB 對相容於 MySQL 的資料庫執行個體上的資料表進行選擇、插入、更新和刪除。
**Topics**
+ [設定 Aurora PostgreSQL 資料庫以使用 mysql\$1fdw 擴充功能](#postgresql-mysql-fdw.setting-up)
+ [範例：使用 Aurora PostgreSQL 中的 Aurora MySQL 資料庫](#postgresql-mysql-fdw.using-mysql_fdw)
+ [搭配此擴充功能使用傳輸中加密](#postgresql-mysql-fdw.encryption-in-transit)
## 設定 Aurora PostgreSQL 資料庫以使用 mysql\$1fdw 擴充功能
在您的 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\$1fdw 存取 MySQL 資料庫伺服器**
1. 使用有 `rds_superuser` 角色的帳戶連線到您的 PostgreSQL 資料庫執行個體。如果您在建立 Aurora PostgreSQL 資料庫叢集時接受了預設值，則使用者名稱為 `postgres`，而且您可以使用 `psql` 命令列工具進行連線，如下所示：
```
psql --host=your-DB-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres –-password
```
1. 安裝 `mysql_fdw` 擴充功能，如下所示：
```
postgres=> CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
```
在 Aurora PostgreSQL 資料庫叢集上安裝擴充功能後，您可以設定提供連線至 MySQL 資料庫的外部伺服器。
**建立外部伺服器**
在 Aurora PostgreSQL 資料庫叢集上執行這些任務。這些步驟假設您以具有 `rds_superuser` 權限 (例如 `postgres`) 的使用者進行連線。
1. 在 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
```
1. 將適當的使用者存取權限授予外部伺服器。這些使用者應該是非管理員使用者，亦即沒有 `rds_superuser` 角色。
```
postgres=> GRANT USAGE ON FOREIGN SERVER mysql-db to user1;
GRANT
```
PostgreSQL 使用者透過外部伺服器建立和管理自己的 MySQL 資料庫連線。
## 範例：使用 Aurora PostgreSQL 中的 Aurora MySQL 資料庫
假設您在 Aurora PostgreSQL 資料庫執行個體上有一個簡單資料表。您的 Aurora PostgreSQL 使用者想要查詢 (`SELECT`)、`INSERT`、`UPDATE` 和 `DELETE` 該資料表上的項目。假設 `mysql_fdw` 擴充功能已在 RDS for PostgreSQL 資料庫執行個體上建立，詳情如上述程序所述。作為具有 `rds_superuser` 權限的使用者，在您連線到 RDS 的 PostgreSQL 資料庫執行個體後，可繼續執行以下步驟。
1. 在 Aurora PostgreSQL 資料庫執行個體中建立外部伺服器：
```
test=> CREATE SERVER mysqldb FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'your-DB.aws-region.rds.amazonaws.com', port '3306');
CREATE SERVER
```
1. 授與使用量給沒有 `rds_superuser` 許可的使用者，例如 `user1`：
```
test=> GRANT USAGE ON FOREIGN SERVER mysqldb TO user1;
GRANT
```
1. 以 *user1* 身分連線，然後建立一個 MySQL 使用者的映射：
```
test=> CREATE USER MAPPING FOR user1 SERVER mysqldb OPTIONS (username 'myuser', password 'mypassword');
CREATE USER MAPPING
```
1. 建立 MySQL 資料表的外部資料表連結。
```
test=> CREATE FOREIGN TABLE mytab (a int, b text) SERVER mysqldb OPTIONS (dbname 'test', table_name '');
CREATE FOREIGN TABLE
```
1. 對外部資料表執行簡單查詢：
```
test=> SELECT * FROM mytab;
a | b
---+-------
1 | apple
(1 row)
```
1. 您可以從 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](https://dev.mysql.com/doc/refman/8.0/en/alter-user.html)。
| MySQL 5.7、MySQL 8.0 | MySQL 5.6 |
| --- | --- |
| `ALTER USER 'user'@'%' REQUIRE SSL;` | `GRANT USAGE ON *.* to 'user'@'%' REQUIRE SSL;` |
如需 `mysql_fdw` 擴充功能的詳細資訊，請參閱 [mysql\$1fdw](https://github.com/EnterpriseDB/mysql_fdw) 文件。
# 使用 oracle\$1fdw 擴充功能處理 Oracle 資料庫
若要從您的 Aurora PostgreSQL 資料庫叢集 存取 Oracle 資料庫，您可以安裝並使用 `oracle_fdw` 擴充功能。此擴充功能是 Oracle 資料庫的外部資料包裝函式。若要進一步了解此擴充功能，請參閱 [oracle\$1fdw](https://github.com/laurenz/oracle_fdw) 文件。
PostgreSQL 12.7 (Amazon Aurora PostgreSQL 版本 4.2) 及更新版本支援 `oracle_fdw` 擴充功能。
**Topics**
+ [開啟 oracle\$1fdw 擴充功能](#postgresql-oracle-fdw.enabling)
+ [範例：使用 Amazon RDS for Oracle Database 的外部伺服器連結](#postgresql-oracle-fdw.example)
+ [在傳輸中使用加密](#postgresql-oracle-fdw.encryption)
+ [了解 pg\$1user\$1mappings 檢視和許可權限](#postgresql-oracle-fdw.permissions)
## 開啟 oracle\$1fdw 擴充功能
如要使用 oracle\$1fdw 擴充功能，請執行下列程序。
**如要開啟 oracle\$1fdw 擴充功能**
+ 使用具有 `rds_superuser` 許可的帳戶執行下列命令。
```
CREATE EXTENSION oracle_fdw;
```
## 範例：使用 Amazon RDS for Oracle Database 的外部伺服器連結
下列範例顯示使用連結至 Amazon RDS for Oracle 資料庫的外部伺服器。
**若要建立連結至 RDS for Oracle 資料庫的外部伺服器**
1. 請注意以下 RDS for Oracle 資料庫執行個體的事項：
+ 端點
+ 連線埠
+ 資料庫名稱
1. 建立外部伺服器。
```
test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name');
CREATE SERVER
```
1. 授予使用權限給沒有 `rds_superuser` 權限的使用者，例如 `user1`。
```
test=> GRANT USAGE ON FOREIGN SERVER oradb TO user1;
GRANT
```
1. 連線為 `user1` 並建立一個對應至 Oracle 使用者的映射。
```
test=> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword');
CREATE USER MAPPING
```
1. 建立連結至 Oracle 資料表的外部資料表。
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. 查詢外部資料表。
```
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 文件中的[關於溝通加密和完整性的值](https://docs.oracle.com/en/database/oracle/oracle-database/21/dbseg/configuring-network-data-encryption-and-integrity.html#GUID-3A2AF4AA-AE3E-446B-8F64-31C48F27A2B5)。在 Amazon RDS 上用於 oracle\$1fdw 的用戶端已設定為 `ACCEPTED`，表示加密取決於 Oracle 資料庫伺服器組態，其會使用 Oracle 安全性程式庫 (libnnz) 進行加密。
如果資料庫位於 RDS for Oracle，請參閱 [Oracle 原生網路加密](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.NetworkEncryption.html)以設定加密。
## 了解 pg\$1user\$1mappings 檢視和許可權限
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\$1fdw 擴充功能處理 SQL 資料庫
您可以使用 PostgreSQL `tds_fdw` 擴充功能來存取支援表格式資料串流 (TDS) 協議的資料庫，如 Sybase 和 Microsoft SQL Server 資料庫。此外部資料包裝函式可讓您從 Aurora PostgreSQL 資料庫叢集連線到使用 TDS 協議的資料庫，包括 Amazon RDS for Microsoft SQL Server。如需詳細資訊，請參閱 GitHub 上的 [tds-fdw/tds\$1fdw](https://github.com/tds-fdw/tds_fdw) 文件。
Amazon Aurora PostgreSQL 版本 13.6 及更新版本支援 `tds_fdw` 擴充功能。
## 設定 Aurora PostgreSQL 資料庫以使用 tds\$1fdw 擴充功能
在以下程序中，您可以找到設定及使用 `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\$1fdw 連線到 SQL Server 資料庫**
1. 使用有 `rds_superuser` 角色的帳戶連線到您的 Aurora PostgreSQL 資料庫叢集的主要執行個體：
```
psql --host=your-cluster-name-instance-1.aws-region.rds.amazonaws.com --port=5432 --username=test –-password
```
1. 安裝 `tds_fdw` 擴充功能：
```
test=> CREATE EXTENSION tds_fdw;
CREATE EXTENSION
```
擴充功能安裝到您的 Aurora PostgreSQL 資料庫叢集 之後，您就可以設定外部伺服器。
**建立外部伺服器**
使用有 `rds_superuser` 權限的帳戶，在 Aurora PostgreSQL 資料庫叢集上執行這些任務。
1. 在 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\$1set 選項建立伺服器連結：
```
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
```
1. 將權限授予沒有 `rds_superuser` 角色權限的使用者，例如 `user1`：
```
test=> GRANT USAGE ON FOREIGN SERVER sqlserverdb TO user1;
```
1. 以 user1 身分連線，然後建立一個 SQL Server 使用者的映射：
```
test=> CREATE USER MAPPING FOR user1 SERVER sqlserverdb OPTIONS (username 'sqlserveruser', password 'password');
CREATE USER MAPPING
```
1. 建立連結至 SQL Server 資料表的外部資料表：
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER sqlserverdb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. 查詢外部資料表：
```
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 連線至資料庫執行個體](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html#SQLServer.Concepts.General.SSL.Forcing)。如需有關 RDS for SQL Server 的 SSL/TLS 組態的詳細資訊，請參閱[對 Microsoft SQL Server 資料庫執行個體使用 SSL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html)。