# エクステンションと外部データラッパーの使用
Aurora PostgreSQL 互換エディション DB クラスターに機能を拡張するには、さまざまな 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 拡張を作成できるため、セットアップと設定の要件および新しい拡張機能の予備テストの多くが簡素化されます。詳細については、「[Trusted Language Extensions for PostgreSQL を使用した操作](PostgreSQL_trusted_language_extension.md)」を参照してください。
拡張機能をインストールする代わりに、Aurora PostgreSQL DB クラスターのカスタム DB クラスターパラメータグループの `shared_preload_libraries` リストに特定の*モジュール*を追加することもできます。通常、デフォルトの DB クラスターパラメータグループでは、`pg_stat_statements` のみが読み込まれますが、リストに追加できるモジュールは他にもいくつかあります。例えば、[PostgreSQL pg\$1cron エクステンションによるメンテナンスのスケジューリング](PostgreSQL_pg_cron.md) で説明されているように、`pg_cron` モジュールを追加することでスケジュール機能を追加できます。別の例として、`auto_explain` モジュールをロードすることでクエリ実行計画を記録できます。詳細については、AWS ナレッジセンターの「[クエリ実行計画のログ記録](https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-tune-query-performance/#)」をご覧ください。
外部データへのアクセスを可能にするエクステンションは、具体的には*外部データラッパー* (FDW) と呼ばれます。一例として、`oracle_fdw` 拡張機能を使用すると、Aurora PostgreSQL DB クラスターが Oracle データベースと連動できるようになります。
また、Aurora PostgreSQL DB インスタンスにインストール可能な拡張機能は、`rds.allowed_extensions` パラメータにリストアップして、正確に指定することができます。詳細については、「[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 リリースノート*の「[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 Server データベースの操作](postgresql-tds-fdw.md)
# PostgreSQL での Amazon Aurora 委任拡張機能サポートの使用
PostgreSQL に対して Amazon Aurora 委任拡張機能のサポートを使用すると、拡張機能管理を `rds_superuser` である必要のないユーザーに委任できます。この委任拡張機能のサポートにより、`rds_extension` という新しいロールが作成され、他の拡張機能を管理するには、これをユーザーに割り当てる必要があります。このロールは、拡張機能を作成、更新、削除できます。
Aurora PostgreSQL DB インスタンスにインストール可能な拡張機能は、`rds.allowed_extensions` パラメータにそれらをリストアップして指定することができます。詳細については、「[Amazon RDS for PostgreSQL で 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)
+ [DROP EXTENSION CASCADE を無効化](#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` では、DB クラスターパラメータで `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` は、それらのオブジェクトへのアクセス権を委任されたユーザーに付与する必要があります。オプションの 1 つは、イベントトリガーを使用して、委任されたユーザーにアクセス許可を自動的に付与することです。
**イベントトリガーの例**
`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 委任拡張機能サポートで使用される設定
| 設定名 | 説明 | デフォルト値 | 注意事項 | アクセス許可を変更または付与できるユーザー |
| --- | --- | --- | --- | --- |
| `rds.allowed_delegated_extensions` | このパラメータは、rds\$1extension ロールがデータベースで管理できる拡張機能を制限します。rds.allowed\$1extensions のサブセットである必要があります。 | 空の文字列 | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/Aurora_delegated_ext.html) このパラメータの設定の詳細については、「[ユーザーに対する委任拡張機能のサポートの有効化](#AuroraPostgreSQL.delegated_ext_mgmt)」を参照してください。 | rds\$1superuser |
| `rds.allowed_extensions` | このパラメータにより、カスタマーは Aurora PostgreSQL DB インスタンスにインストールできる拡張機能を制限できます。詳細については、「[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 DB インスタンスに拡張機能をインストールできないことを意味します。 | 管理者 |
| `rds-delegated_extension_allow_drop_cascade` | このパラメータは、`rds_extension` を持つユーザーがカスケードオプションを使用して拡張機能を削除する機能を制御します。 | 化 | デフォルトで、`rds-delegated_extension_allow_drop_cascade` は `off` に設定されています。つまり、`rds_extension` を持つユーザーは、カスケードオプションを使用して拡張機能を削除することはできません。 その機能を許可するには、`rds.delegated_extension_allow_drop_cascade` パラメータを `on` に設定する必要があります。 | rds\$1superuser |
## 委任拡張機能のサポートの無効化
**部分的にオフにする**
委任されたユーザーは、新しい拡張機能を作成することはできませんが、既存の拡張機能を更新することはできます。
+ DB クラスターパラメータグループでデフォルト値に `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` ロールは不要です。
+ 同じ DB クラスター内の異なるデータベースに対して、異なる拡張機能セットをサポートする機能を提供します。
## 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` ロールを持つユーザーは、接続権限を持つすべてのデータベースの拡張機能を管理できることに注意してください。委任されたユーザーが 1 つのデータベースの拡張機能を管理する場合は、各データベースのパブリックからすべての権限を取り消し、その特定のデータベースの接続権限を委任されたユーザーに明示的に付与することをお勧めします。
ユーザーが複数のデータベースから情報にアクセスできる拡張機能がいくつかあります。これらの拡張機能を `rds.allowed_delegated_extensions` に追加する前に、`rds_extension` を付与するユーザーにクロスデータベース機能があることを確認してください。例えば、`postgres_fdw` と `dblink` は、同じインスタンスまたはリモートインスタンス上のデータベース間でクエリを実行する機能を提供します。`log_fdw` は postgres エンジンのログファイルを読み取ります。これらは、インスタンス内のすべてのデータベース用であり、複数のデータベースからのスロークエリやエラーメッセージが含まれている可能性があります。`pg_cron` は、DB インスタンスでスケジュールされたバックグラウンドジョブの実行を有効にし、別のデータベースで実行するようにジョブを設定できます。
## DROP EXTENSION CASCADE を無効化
`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` オプション (no-op) を使ってユーティリティを実行します。この方法については、下記の「[vacuumlo utility](#vacuumlo-utility)」を参照してください。
Lo モジュールは Aurora PostgreSQL 13.7、12.11、11.16、10.21 以降のマイナーバージョンで利用できます。
モジュール（拡張機能）をインストールするには、`rds_superuser` 権限が必要です。lo 拡張機能をインストールすると、データベースに次のものが追加されます。
+ `lo` — これは、バイナリラージオブジェクト (BLOB) やその他のラージオブジェクトに使用できるラージオブジェクト (lo) データ型です。`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` 権限があることを確認してください。
**lo 拡張機能をインストールするには**
1. `psql` を使用して、Aurora PostgreSQL DB クラスターのプライマリ DB インスタンスに接続します。
```
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`)。次の例に示すように、列 `raster` の `lo` データ型を使って、テーブルを作成できます。
```
postgres=> CREATE TABLE images (image_name text, raster lo);
```
## lo\$1manage トリガー関数を使用してオブジェクトを削除する
`lo` または他のラージオブジェクト列の `lo_manage` 関数を使って、`lo` が更新または削除されたときにクリーンアップ（および孤立したオブジェクトを防ぐ）ことができます。
**ラージオブジェクトを参照する列にトリガーを設定するには**
+ 次のいずれかを行います。
+ 引数に列名を使用して、ラージオブジェクトへの一意の参照を含むように、各列に BEFORE UPDATE トリガーまたは BEFORE 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 DB クラスターにメリットがあるかどうかを評価できます。これを行うには、`-n` オプション (no-op) を使用して `vacuumlo` を実行し、以下に示すように、削除される内容を表示します。
```
$ 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;
```
## ラージオブジェクトに関する考慮事項
ラージオブジェクトを操作するときに注意すべき重要な考慮事項を以下に示します。
+ 現在、孤立した LO を削除する方法はほかにないため、`Vacuumlo` が唯一の解決策です。
+ レプリケーションテクノロジーを使用する pglogical、ネイティブ論理レプリケーション、AWS DMS などのツールは、ラージオブジェクトのレプリケーションをサポートしていません。
+ データベーススキーマを設計するときは、可能な限りラージオブジェクトは使用せず、代わりに `bytea` などの代替データ型を使用することを検討してください。
+ 孤立した LO の問題を防ぐために、少なくとも毎週定期的に `vacuumlo` を実行します。
+ 孤立した LO が作成されないように、ラージオブジェクトを保存するテーブルで `lo_manage` 関数のトリガーを使用します。
# PostGIS 拡張機能を使用した空間データの管理
PostGIS は PostgreSQL の拡張機能であり、空間情報の保存と管理に使用します。PostGIS の詳細については、「[Postgis.net](https://postgis.net/)」を参照してください。
バージョン 10.5 以降の PostgreSQL では、PostGIS がマップボックスのベクトルタイルデータを操作するために使用する 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 DB インスタンスに接続します。インスタンスの設定時にデフォルトの名前を保持している場合は、次のように `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
```
セッションを終了し、RDS for PostgreSQL DB インスタンスに `gis_admin` として再接続します。
```
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 クエリを実行して、所有権の変更を確認できます。または、psql コマンドラインの `\dn` メタコマンドを使用します。
```
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 Security](https://postgis.net/workshops/postgis-intro/security.html)」を参照してください。
次の関数を使用して、`gis_admin` ロールに PostGIS テーブルの所有権を移管します。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)」を参照してください。
次の `topology` ステートメントを使用して `SELECT` スキーマへのアクセスをテストします。これにより、`createtopology` 関数を呼び出して、指定された空間参照識別子 (26986) とデフォルトの許容誤差 (0.5) を持つ新しいトポロジーオブジェクト (my\$1new\$1topo) を登録します。詳細については、PostGIS ドキュメントの「[CreateTopology](https://postgis.net/docs/CreateTopology.html)」を参照してください。
```
SELECT topology.createtopology('my_new_topo',26986,0.5);
createtopology
----------------
1
(1 row)
```
## ステップ 6: PostGIS 拡張機能を更新する
PostgreSQL の新しいリリースでは、それぞれのリリースと互換性のある 1 つまたは複数のバージョンの PostGIS 拡張機能をサポートしています。PostgreSQL エンジンを新しいバージョンにアップグレードしても、PostGIS 拡張機能は自動的にアップグレードされません。PostgreSQL エンジンをアップグレードする前に、通常 PostGIS を現在の PostgreSQL バージョンで使用可能な最新バージョンにアップグレードします。詳細については、「[PostGIS 拡張バージョン](#CHAP_PostgreSQL.Extensions.PostGIS)」を参照してください。
PostgreSQL エンジンのアップグレード後、PostGIS 拡張機能を再度アップグレードして、新しくアップグレードした PostgreSQL エンジンバージョンでサポートされているバージョンにアップグレードします。PostgreSQL のアップグレードの詳細については、「[本番稼働用の DB クラスターの新しいメジャーバージョンへのアップグレードをテストする](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary) 」を参照してください。
Aurora PostgreSQL DB クラスター では、利用可能な 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 エンジンのメジャーアップグレードの準備のためにこの拡張機能をアップグレードした場合は、他の準備作業を継続できます。詳細については、「[本番稼働用の DB クラスターの新しいメジャーバージョンへのアップグレードをテストする](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` を新しいバージョンにアップグレードするには 2 つ目のアップグレードコマンドが必要です。
**PostGIS 2 から PostGIS 3 にアップグレードするには**
1. お使いの Aurora PostgreSQL DB クラスターの PostgreSQL バージョンで利用可能な PostGIS のデフォルトバージョンを確認します。確認するために、以下のクエリを実行します。
```
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 DB クラスターのライターインスタンス、の各データベースにインストールされている 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 テーブルパーティションは、データ入力とレポートにおける高性能な処理のためのフレームワークを提供します。大量のデータを非常に速く入力する必要があるデータベースには、パーティションを使用します。またパーティションは、大きなテーブルでより高速のクエリを提供します。パーティションは、必要とする I/O リソースが少ないため、データベースインスタンスに影響を与えずにデータを維持するのに役立ちます。
パーティションを使用すると、データをカスタムサイズのチャンクに分割して処理することができます。例えば、時間単位、日単位、週単位、月単位、四半期単位、年単位、カスタム、またはこれらの組み合わせなどの範囲のパーティションの時系列データを選択できます。時系列データの例では、テーブルを時間単位でパーティション化した場合、各パーティションには 1 時間のデータが含まれます。時系列テーブルを日単位でパーティション化した場合、パーティションには 1 日分のデータが保持されます。パーティションキーは、パーティションのサイズを制御します。
パーティション化されたテーブルで `INSERT` SQL コマンドまたは `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 Partition Manager](https://github.com/pgpartman/pg_partman)」を参照してください。
**注記**
`pg_partman` エクステンションは、Aurora PostgreSQL のエンジンバージョン 12.6 以降でサポートされています。
各パーティションを手動で作成する代わりに、次の設定で `pg_partman` を設定します。
+ パーティション化するテーブル
+ パーティションタイプ
+ パーティションキー
+ パーティションの粒度
+ パーティションの事前作成および管理オプション
PostgreSQL のパーティション化されたテーブルの作成後、`create_parent` 関数を呼び出して、そのテーブルを `pg_partman` に登録します。これにより、関数に渡すパラメータに基づいて、必要なパーティションを作成します。
`pg_partman` エクステンションには、設定したスケジュールに基づいて呼び出しを行うことでパーティションを自動的に管理できる `run_maintenance_proc` 関数も用意されています。必要に応じて適切なパーティションが作成されるようにするには、この関数を定期的に (時間単位など) 実行するようにスケジュールします。また、パーティションが自動的に削除されるようにすることもできます。
## pg\$1partman エクステンションの有効化
パーティションを管理する同じ PostgreSQL DB インスタンス内に複数のデータベースがある場合は、データベースごとに `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`。
+ 2 つの外部キー。1 つ (`fk_orga_membership)`) は外部テーブル `organization` で、もう 1 つ (`fk_parent_event_id`) は自己参照外部キーです。
+ 2 つのインデックス。1 つ (`idx_org_id`) は外部キー用で、もう 1 つ (`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` ドキュメントの「[Creation Functions (関数の作成)](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#user-content-creation-functions)」を参照してください。
## run\$1maintenance\$1proc 関数を使用したパーティションのメンテナンス設定
パーティションのメンテナンスオペレーションを実行して、自動的に新しいパーティションの作成、パーティションのデタッチ、または古いパーティションの削除ができます。パーティションのメンテナンスは、内部のスケジューラをスタートする `pg_partman` および `pg_cron` エクステンション の `run_maintenance_proc` 関数により異なります。`pg_cron` スケジューラは、データベースで定義された SQL ステートメント、関数、および手順を自動的に実行します。
次の例では、`events` で作成した [pg\$1partman エクステンションの有効化run\$1maintenance\$1proc 関数を使用したパーティションのメンテナンス設定](#PostgreSQL_Partitions.enable) テーブルの例を使用して、パーティションのメンテナンスオペレーションを自動的に実行するように設定します。前提条件にあるように、DB インスタンスのパラメータグループで、`shared_preload_libraries` パラメータに `pg_cron` を追加します。
```
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. DB インスタンスに関連付けられているパラメータグループを変更して、`pg_cron` を `shared_preload_libraries` パラメータ値に追加します。この変更を有効にするには、DB インスタンスの再起動が必要です。詳細については、「[Amazon Aurora の DB パラメータグループのパラメータの変更](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',` - テーブルの最大保持期間を 3 か月に設定します。
1. `retention_keep_table=true ` - 保存期間の期限が過ぎてもテーブルが自動的に削除されないように、テーブルを構成します。代わりに、保持期間より古いパーティションは、親テーブルからのみデタッチされます。
1. `SELECT cron.schedule` . . .コマンドを実行して、`pg_cron` 関数を呼び出します。この呼び出は、`pg_partman` メンテナンスプロシージャの `partman.run_maintenance_proc` が、スケジューラにより実行される頻度を定義します。この例では、プロシージャは 1 時間ごとに実行されます。
`run_maintenance_proc` 関数の詳細については、`pg_partman` ドキュメントの「[Maintenance Functions (メンテナンス機能)](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#maintenance-functions)」を参照してください。
# PostgreSQL pg\$1cron エクステンションによるメンテナンスのスケジューリング
PostgreSQL `pg_cron` エクステンションを使用すると、PostgreSQL データベース内でメンテナンスコマンドのスケジュールを組むことができます。拡張機能の詳細については、pg\$1cron ドキュメントの 「[What is pg\$1cron?](https://github.com/citusdata/pg_cron)」 を参照してください。
`pg_cron` エクステンションは、Aurora PostgreSQL エンジンのバージョン 12.6 以降のバージョンでサポートされています。
`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 などの 2 桁のバージョンとして表示されます。1.6.4 や 1.6.5 などの 3 桁のバージョンが表示される場合がありますが、一部のコンテキストでは、拡張機能のアップグレードを実行するときに 2 桁のバージョンを指定する必要があります。
**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. `shared_preload_libraries` パラメータ値に `pg_cron` を追加して、PostgreSQL DB インスタンスに関連付けられているカスタムパラメータグループを変更します。
静的パラメータグループの変更を反映するために PostgreSQL DB インスタンスを再起動します。パラメータグループを使用する方法の詳細については、 [Amazon Aurora PostgreSQL のパラメータ](AuroraPostgreSQL.Reference.ParameterGroups.md) を参照してください。
1. PostgreSQL DB インスタンスが再起動したら、`rds_superuser` の許可を持つアカウントを使用して以下のコマンドを実行します。例えば、Aurora PostgreSQL DB クラスターの作成時にデフォルト設定を使用した場合は、ユーザー `postgres` として接続し拡張機能を作成します。
```
CREATE EXTENSION pg_cron;
```
`pg_cron` スケジューラは、`postgres` という名前のデフォルトの PostgreSQL データベースに設定されます。`pg_cron` オブジェクトはこの `postgres` データベースに作成され、すべてのスケジューリングアクションがこのデータベースで実行されます。
1. デフォルト設定を使用することも、ジョブをスケジュールして、PostgreSQL DB インスタンス内の他のデータベースで実行させることもできます。PostgreSQL DB インスタンス内の他のデータベースでジョブをスケジュールするには、[デフォルトのデータベース以外のデータベースでの 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;
```
これにより、アクセス権限のあるオブジェクトの cron ジョブをスケジュールするための `cron` スキーマへの *db-user* アクセス許可が付与されます。データベースユーザーに権限がない場合、以下に示すように、エラーメッセージを `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)
### テーブルのバキューム処理
Autovacuum は、ほとんどの場合、バキュームのメンテナンスを実行します。ただし、特定のテーブルのバキューム処理を、選択した特定の時点にスケジュールしたい、というケースも考えられます。
以下は、`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 ジョブの履歴が含まれています。そのため、このテーブルをクリアにするジョブをスケジュールすることをお勧めします。例えば、トラブルシューティングの目的では、1 週間分のエントリを保持するだけで十分です。
次の例では、[cron.schedule](#PostgreSQL_pg_cron.schedule) 関数を使用して、`cron.job_run_details` テーブルをクリアにするよう、毎日午前 0 時に実行されるジョブをスケジュールします。このジョブは過去 7 日間しか残せません。`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 DB インスタンスに関連付けられているパラメータグループを変更し、`cron.log_run` パラメータをオフに設定します。`pg_cron` 拡張機能によって対象のテーブルには書き込まなくなり、エラーは `postgresql.log` ファイルのみに記録されるようになります。詳細については、「[Amazon Aurora の DB パラメータグループのパラメータの変更](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 DB インスタンス内の任意のデータベースでジョブのスケジューリングが可能です。
**注記**
`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 DB インスタンス内の別のデータベースで実行できるようにします。
```
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 ドキュメントの [What is 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` エクステンションの動作を制御するパラメータの一覧を次に示します。
| Parameter | 説明 |
| --- | --- |
| cron.database\$1name | `pg_cron` メタデータが保持されるデータベース。 |
| cron.host | PostgresSQL に接続するためのホスト名。この値は変更できません。 |
| 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 DB インスタンス内の他のデータベースで実行するようにジョブをスケジュールするには、[デフォルトのデータベース以外のデータベースでの cron ジョブのスケジューリング](#PostgreSQL_pg_cron.otherDB) の例を参照してください。
この関数には、2 つの構文形式があります。
**構文**
```
cron.schedule (job_name,
schedule,
command
);
cron.schedule (schedule,
command
);
```
**パラメータ**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/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/ja_jp/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 を使用してデータベースのアクティビティを記録する
金融機関、政府機関、および多くの業界では、*規制要件を満たすために監査ログ*を保存する必要があります。Aurora PostgreSQL DB クラスター で PostgreSQL 監査拡張機能 (pgAudit) を使用することで、監査人が通常必要とする詳細なレコードや規制要件を満たすための詳細なレコードをキャプチャできます。例えば、pgAudit 拡張機能を設定して、特定のデータベースやテーブルに加えられた変更を追跡したり、変更を加えたユーザーやその他の多くの詳細を記録したりできます。
pgAudit 拡張機能は、ログメッセージをより詳細に拡張することにより、ネイティブの PostgreSQL ログ記録インフラストラクチャの機能に基づいて構築されています。つまり、監査ログは、他のログメッセージを表示するのと同じ方法を使用します。PostgreSQL ログ記録の詳細については、「[Aurora PostgreSQL データベースログファイル](USER_LogAccess.Concepts.PostgreSQL.md)」を参照してください。
pgAudit 拡張機能は、クリアテキストパスワードなどの機密データをログから編集します。Aurora PostgreSQL DB クラスター が、[Aurora PostgreSQL DB クラスター のクエリログ記録をオンにする](USER_LogAccess.Concepts.PostgreSQL.Query_Logging.md) で説明されているようにデータ操作言語 (DML) ステートメントをログに記録するように設定されている場合は、PostgreSQL Audit 拡張機能を使用することでクリアテキストパスワードの問題を回避できます。
データベースインスタンスの監査は、きわめて詳細に構成できます。すべてのデータベースとすべてのユーザーを監査できます。また、特定のデータベース、ユーザー、その他のオブジェクトのみを監査することもできます。特定のユーザーやデータベースを監査対象から明示的に除外することもできます。詳細については、「[監査ログからのユーザーまたはデータベースの除外](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md)」を参照してください。
キャプチャできる詳細の量を考慮すると、pgAudit を使用する場合はストレージ消費量を監視することをお勧めします。
pgAudit 拡張モジュールは、使用可能なすべての Aurora PostgreSQL バージョンでサポートされています。Aurora PostgreSQL のバージョンでサポートされている pgAudit のリストについては、*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 DB クラスターに pgAudit 拡張機能を設定するには、まず RDS for PostgreSQL DB インスタンスのの共有ライブラリに pgAudit を追加します。Aurora PostgreSQL DB クラスター用のカスタム DB クラスターでパラメータグループ。カスタム DB クラスターパラメータグループの詳細については、「[Amazon Aurora のパラメータグループ](USER_WorkingWithParamGroups.md)」を参照してください。次に、pgAudit 拡張機能をインストールします。最後に、監査するデータベースとオブジェクトを指定します。このセクションの手順で、方法を示します。AWS マネジメントコンソール または AWS CLI を使用できます。
これらすべてのタスクを実行するには、`rds_superuser` ロールとして権限が必要です。
以下の手順では、Aurora PostgreSQL DB クラスター がカスタム DB クラスター パラメータグループに関連付けられていることを前提としています。
## コンソール
**pgAudit 拡張機能をセットアップするには**
1. AWS マネジメントコンソール にサインインし、Amazon RDS コンソール [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/) を開きます。
1. ナビゲーションペインで、Aurora PostgreSQL DB クラスターのライターインスタンス を選択します。
1. Aurora PostgreSQL DB クラスターライターインスタンスの **[Configuration]** (設定) タブを開きます。インスタンスの詳細の中から、**パラメータグループ**のリンクを見つけてください。
1. リンクを選択して、Aurora PostgreSQL DB クラスターに関連するカスタムパラメータを開きます。
1. **パラメータ**検索フィールドに、`shared_pre` を入力して `shared_preload_libraries` パラメータを検索します。
1. プロパティ値にアクセスするには、**[Edit parameters]** (パラメータの編集) を選択します。
1. **[Values]** (値) フィールドのリストに `pgaudit` を追加します。値のリスト内の項目を区切るにはカンマを使用します。
![\[pgAudit が追加された shared_preload_libaries パラメータの画像。\]](http://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/images/apg_rpg_shared_preload_pgaudit.png)
1. Aurora PostgreSQL DB クラスターのライターインスタンス を再起動して、`shared_preload_libraries` パラメータの変更を有効にします。
1. インスタンスが使用可能になったら、pgAudit が初期化されていることを確認します。`psql` を使用して Aurora PostgreSQL DB クラスターのライターインスタンス、次のコマンドを実行します。
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
1. pgAudit を初期化すると、拡張機能を作成できるようになりました。`pgaudit` 拡張機能はデータ定義言語 (DDL) ステートメントを監査するためのイベントトリガーをインストールするため、ライブラリを初期化した後に拡張機能を作成する必要があります。
```
CREATE EXTENSION pgaudit;
```
1. `psql` セッションを終了します。
```
labdb=> \q
```
1. AWS マネジメントコンソール にサインインし、Amazon RDS コンソール [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/) を開きます。
1. リストで `pgaudit.log` パラメータを検索し、ユースケースに応じた値に設定します。例えば、次の画像に示すように `pgaudit.log` パラメータを `write` に設定すると、ログへの挿入、更新、削除、およびその他のタイプの変更がキャプチャされます。
![\[設定を含む pgaudit.log パラメータの画像。\]](http://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/images/rpg_set_pgaudit-log-level.png)
`pgaudit.log` パラメータには、次のいずれかの値を選択することもできます。
+ none – これはデフォルトです。データベースの変更は記録されません。
+ すべて — すべてをログに記録します (読み取り、書き込み、関数、ロール、DDL、その他)。
+ 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. [**Save changes**] (変更の保存) をクリックします。
1. Amazon RDS コンソール ([https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)) を開きます。
1. データベースリストから、Aurora PostgreSQL DB クラスターのライターインスタンス を選択します。
## 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 コマンドを使用して`shared_preload_libraries`パラメータに `pgaudit` を追加します。
```
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 DB クラスターのライターインスタンス を再起動し、pgaudit ライブラリを初期化します。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. インスタンスが使用可能になると、`pgaudit` が初期化されていることを確認できます。`psql` を使用して Aurora PostgreSQL DB クラスターのライターインスタンス、次のコマンドを実行します。
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
pgAudit を初期化すると、拡張機能を作成できるようになりました。
```
CREATE EXTENSION pgaudit;
```
1. AWS CLI を使用できるように `psql` セッションを終了します。
```
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 – これはデフォルトです。データベースの変更は記録されません。
+ すべて — すべてをログに記録します (読み取り、書き込み、関数、ロール、DDL、その他)。
+ 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 DB クラスターのライターインスタンス を再起動します。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# データベースオブジェクトの監査
Aurora PostgreSQL DB クラスター に 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/ja_jp/AmazonRDS/latest/AuroraUserGuide/images/pgaudit-log-example.png)
この例に示すように、「LOG: AUDIT: SESSION」行には、テーブルとそのスキーマなどの詳細情報が表示されます。
**オブジェクト監査をセットアップするには**
1. `psql` を使用して Aurora PostgreSQL DB クラスターのライターインスタンス、
```
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 DB クラスターのライターインスタンス を再起動し、パラメータの変更を有効にします。
```
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 DB クラスター内のすべてのユーザーまたはデータベースを監査する必要はないかもしれません。
ストレージへの影響を最小限に抑え、監査記録を不必要にキャプチャしないようにするには、ユーザーとデータベースを監査対象から除外できます。特定のセッション内のロギングを変更することもできます。次の例は、その方法を示しています。
**注記**
セッションレベルのパラメータ設定は、Aurora PostgreSQL DB クラスターのライターインスタンスのカスタム DB クラスターパラメータグループ の設定よりも優先されます。データベースユーザーに監査ログ設定の設定をバイパスさせたくない場合は、必ず権限を変更してください。
Aurora PostgreSQL DB クラスター が、すべてのユーザーとデータベースについて同じレベルのアクティビティを監査するように設定されているとします。次に、`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 の設定列を確認します。
```
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 拡張機能のリファレンス
このセクションにリストされている 1 つまたは複数のパラメータを変更することで、監査ログに必要な詳細レベルを指定できます。
## pgAudit 動作の制御
監査ログは、次のテーブルに示す 1 つ以上のパラメータを変更することで制御できます。
| パラメータ | 説明 |
| --- | --- |
| `pgaudit.log` | セッション監査ログ記録によってログに記録されるステートメントのクラスを指定します。許容値には、ddl、関数、その他、読み取り、ロール、書き込み、なし、すべてが含まれます。(詳しくは、「[`pgaudit.log` パラメータの許容設定のリスト](#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings)」を参照してください)。 |
| `pgaudit.log_catalog` | オンにすると (1 に設定)、ステートメント内のすべてのリレーションが pg\$1catalog 内にある場合に、ステートメントを監査証跡に追加します。 |
| `pgaudit.log_level` | ログエントリに使用されるログレベルを指定します。指定できる値は 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` パラメータの許容設定のリスト
| 値 | 説明 |
| --- | --- |
| なし | これがデフォルトです。データベースの変更は記録されません。 |
| すべて | すべてをログに記録します (読み取り、書き込み、関数、ロール、DDL、その他)。 |
| ddl | `ROLE` クラスに含まれていない、すべてのデータ定義言語 (DDL) ステートメントのログ記録。 |
| 関数 | 関数呼び出し、および `DO` ブロックのログ記録。 |
| misc | `DISCARD`、`FETCH`、`CHECKPOINT`、`VACUUM`、`SET` など、さまざまなコマンドのログ記録。 |
| 読む | `SELECT` および `COPY` のログ記録 (ソースがリレーション (テーブルなどの) またはクエリの場合)。 |
| ロール | `GRANT`、`REVOKE`、`CREATE ROLE`、`ALTER ROLE`、`DROP ROLE` など、ロールと権限に関連するステートメントのログ記録。 |
| 書き込み | `INSERT`、`UPDATE`、`DELETE`、`TRUNCATE`、および `COPY` のログ記録 (送信先がリレーションの場合)。 |
セッション監査で複数のイベントタイプをログ記録するには、カンマ区切りリストを使用します。すべてのイベントタイプをログ記録するには、`pgaudit.log` を `ALL` に設定します。DB インスタンスを再起動して、変更を適用します。
オブジェクト監査では、監査のログ記録を絞り込み、特定のリレーションを操作できます。例えば、1 つまたは複数のテーブルで、`READ` オペレーションのログ記録を監査するよう指定できます。
# pglogical を使用してインスタンス間でデータを同期する
現在利用可能なすべての Aurora PostgreSQL バージョンは、`pglogical` 拡張機能をサポートしています。pglogical 拡張は、バージョン 10 で PostgreSQL により導入された機能的に類似した論理レプリケーション機能よりも前のものです。詳細については、「[Aurora での PostgreSQL 論理レプリケーションの概要](AuroraPostgreSQL.Replication.Logical.md)」を参照してください。
`pglogical` 拡張が、2 つ以上の Aurora PostgreSQL DB クラスター間の論理レプリケーションをサポートします。また、異なる PostgreSQL バージョン間のレプリケーション、および RDS for PostgreSQL DB と Aurora PostgreSQL DB クラスターで実行されているデータベース間のレプリケーションもサポートしています。`pglogical` 拡張は、公開/サブスクライブモデルを使用して、テーブルやその他のオブジェクト (シーケンスなど) への変更をパブリッシャーからサブスクライバーに複製します。パブリッシャーノードからサブスクライバーノードに変更が確実に同期されるようにするには、レプリケーションスロットを使用し、次のように定義されます。
+ *パブリッシャーノード*は、他のノードにレプリケートされるデータのソースである Aurora PostgreSQL DB クラスターです。パブリッシャーノードは、パブリケーションセットでレプリケートするテーブルを定義します。
+ *サブスクライバーノード*は、公開者から WAL の更新を受け取る Aurora PostgreSQL DB クラスターです。サブスクライバーは、パブリッシャーに接続してデコードされた WAL データを取得するためのサブスクリプションを作成します。サブスクライバーがサブスクリプションを作成すると、パブリッシャーノードに複製スロットが作成されます。
`pglogical` 拡張の設定についての情報は、以下を参照してください。
**Topics**
+ [pglogical 拡張の要件と制限](#Appendix.PostgreSQL.CommonDBATasks.pglogical.requirements-limitations)
+ [pglogical 拡張のセットアップ](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md)
+ [Aurora PostgreSQL DB クラスターに論理レプリケーションを設定する](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 以降) に組み込まれている論理レプリケーション機能ではサポートされていない双方向レプリケーションなどの機能を提供します。詳細については、「[PostgreSQL bi-directional replication using pglogical](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/)」(pglogical を使用した PostgreSQL の双方向レプリケーション) を参照してください。
# pglogical 拡張のセットアップ
Aurora PostgreSQL DB クラスターに `pglogical` 拡張機能を設定するには、Aurora PostgreSQL DB クラスター用のカスタム DB クラスターのパラメータグループの共有ライブラリに `pglogical` を追加します。また、論理デコードをオンにするには、`rds.logical_replication` パラメータの値を `1` に設定する必要があります。最後に、データベースに拡張を作成します。これらのタスクには、AWS マネジメントコンソール または AWS CLI を使用できます。
これらのタスクを実行するには、`rds_superuser` ロールとしてアクセス許可が必要です。
以下のステップでは、Aurora PostgreSQL DB クラスター がカスタム DB クラスター パラメータグループに関連付けられていることを前提としています。カスタム DB クラスターパラメータグループの詳細については、「[Amazon Aurora のパラメータグループ](USER_WorkingWithParamGroups.md)」を参照してください。
## コンソール
**pglogical 拡張をセットアップするには**
1. AWS マネジメントコンソール にサインインし、Amazon RDS コンソール ([https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)) を開きます。
1. ナビゲーションペインで、Aurora PostgreSQL DB クラスターのライターインスタンス を選択します。
1. Aurora PostgreSQL DB クラスターライターインスタンスの **[Configuration]** (設定) タブを開きます。インスタンスの詳細の中から、**パラメータグループ**のリンクを見つけてください。
1. リンクを選択して、Aurora PostgreSQL DB クラスターに関連するカスタムパラメータを開きます。
1. **パラメータ**検索フィールドに、`shared_pre` を入力して `shared_preload_libraries` パラメータを検索します。
1. プロパティ値にアクセスするには、**[Edit parameters]** (パラメータの編集) を選択します。
1. **[Values]** (値) フィールドのリストに `pglogical` を追加します。値のリスト内の項目を区切るにはカンマを使用します。
![\[pglogical が追加された shared_preload_libraries パラメータの画像。\]](http://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/images/apg_rpg_shared_preload_pglogical.png)
1. `rds.logical_replication` パラメータを見つけて `1` に設定し、論理レプリケーションをオンにします。
1. Aurora PostgreSQL DB クラスターのライターインスタンス を再起動して、変更を有効にします。
1. インスタンスが使用可能になったら、`psql` (または pgAdmin) を使用して Aurora PostgreSQL DB クラスターのライターインスタンスに接続します。
```
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. **[Save changes]** (変更の保存) をクリックします。
1. Amazon RDS コンソール ([https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)) を開きます。
1. データベースリストから Aurora PostgreSQL DB クラスターのライターインスタンス を選択して選択し、アクションメニューから **[Reboot]** (再起動) を選択します。
## AWS CLI
**pglogical 拡張のセットアップするには**
AWS CLI を使用して pglogical を設定するには、次の手順に示すように、[modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) オペレーションを呼び出してカスタムパラメータグループの特定のパラメータを変更します。
1. 次の AWS CLI コマンドを使用して`shared_preload_libraries`パラメータに `pglogical` を追加します。
```
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 DB クラスターのライターインスタンスの論理デコード機能をオンにします。
```
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 DB クラスターのライターインスタンスを再起動し、pglogical ライブラリを初期化します。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. インスタンスが使用可能になったら、`psql` を使用して Aurora PostgreSQL DB クラスターのライターインスタンスに接続します。
```
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 DB クラスターのライターインスタンス を再起動します。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# Aurora PostgreSQL DB クラスターに論理レプリケーションを設定する
以下の手順では、2 つの Aurora PostgreSQL DB クラスター間で論理レプリケーションを開始する方法を示しています。これらのステップでは、ソース (パブリッシャー) とターゲット (サブスクライバー) の両方に、[pglogical 拡張のセットアップ](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md) で説明されているように `pglogical` 拡張が設定されていることを前提としています。
**注記**
サブスクライバーノードの `node_name` は、`rds` で開始することができません。
**パブリッシャーノードを作成し、複製するテーブルを定義するには**
これらのステップは、別のノードに複製する 1 つ以上のテーブルがあるデータベースを含むライターインスタンスが Aurora PostgreSQL DB クラスターにあることを前提としています。サブスクライバー上のパブリッシャーからテーブル構造を再作成する必要があるため、まず必要に応じてテーブル構造を取得します。そのためには、`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 DB クラスターをパブリッシャーノードとして指定します。
```
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 DB クラスターが `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 DB クラスターで、パブリッシャーに存在するのと同じテーブルを作成します。この例では、テーブルは `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 DB クラスターをサブスクライバーノードとして指定します。
```
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 DB クラスターのメジャーバージョンアップグレードを実行する前に、アクティブではないものを含め、すべてのレプリケーションスロットを削除する必要があります。パブリッシャーノードからデータベーストランザクションを一時的に迂回させ、レプリケーションスロットを削除し、Aurora PostgreSQL DB クラスターをアップグレードしてから、レプリケーションを再確立して再開することをお勧めします。
レプリケーションスロットはパブリッシャーノードでのみホストされます。論理レプリケーションシナリオの Aurora PostgreSQL サブスクライバーノードには、削除するスロットがありません。Aurora PostgreSQL のメジャーバージョンアップグレードプロセスでは、パブリッシャーノードとは関係なく、サブスクライバーを新しいメジャーバージョンの PostgreSQL にアップグレードできます。ただし、アップグレードプロセスによってレプリケーションプロセスが中断され、パブリッシャーノードとサブスクライバーノード間の WAL データの同期が妨げられます。パブリッシャーまたはサブスクライバー、あるいはその両方をアップグレードした後に、パブリッシャーとサブスクライバー間の論理レプリケーションを再確立する必要があります。以下の手順は、レプリケーションが中断されたことを確認する方法と問題を解決する方法を示しています。
## 論理レプリケーションが中断されたことの確認
次のように、パブリッシャーノードまたはサブスクライバーノードのいずれかにクエリを実行することで、レプリケーションプロセスが中断されたことを確認できます。
**パブリッシャーノードを確認するには**
+ `psql` を使用してパブリッシャーノードに接続して、`pg_replication_slots` 関数をクエリします。active 列の値に注目します。通常は `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)
```
**サブスクライバーノードを確認するには**
サブスクライバーノードでは、3 つの異なる方法でレプリケーションのステータスを確認できます。
+ サブスクライバーノードの 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 DB クラスターのライターインスタンスでメジャーバージョンアップグレードを実行する前に、インスタンスのレプリケーションスロットを削除する必要があります。メジャーバージョンアップグレードの事前確認プロセスにより、スロットが削除されるまでアップグレードを続行できないことが通知されます。
`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` などのパラメータは、更新の競合を処理するために使用されます。パブリッシャーから変更をサブスクライブしているテーブルにローカルで変更を加えると、競合が発生する可能性があります。これ以外にも、競合は、双方向のレプリケーションや、複数のサブスクライバーが同じパブリッシャーからレプリケートする場合など、さまざまなシナリオで発生する可能性があります。詳細については、「[PostgreSQL bi-directional replication using pglogical](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/)」(pglogical を使用した PostgreSQL の双方向レプリケーション) を参照してください。
| パラメータ | 説明 |
| --- | --- |
| 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 | 低レベル API の代わりに SPI (サーバープログラミングインターフェイス) を使用して変更を適用します。オンにする場合は「1」に、オフにする場合は「0」に設定します。SPI の詳細については、PostgreSQL ドキュメントの「[サーバープログラミングインターフェイス](https://www.postgresql.org/docs/current/spi.html)」を参照してください。 |
# Amazon Aurora PostgreSQL でサポートされている外部データラッパーを使用する
外部データラッパー (FDW) は、外部データへのアクセスを提供する特定のタイプの拡張機能です。例えば、`oracle_fdw` 拡張機能を使用すると、Aurora PostgreSQL DB インスタンス が Oracle データベースと連動できるようになります。
以下で、PostgreSQL でサポートされている、いくつかの 外部データラッパーについての情報を確認できます。
**Topics**
+ [SQL を使用した DB ログのアクセスのための log\$1fdw 拡張機能の使用](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 Server データベースの操作](postgresql-tds-fdw.md)
# SQL を使用した DB ログのアクセスのための log\$1fdw 拡張機能の使用
Aurora PostgreSQL DB クラスターは、SQL インターフェイスを通じてデータベースエンジンのログにアクセスする際に使用できる、`log_fdw` 拡張機能をサポートしています。`log_fdw` エクステンションは、データベースログ用の外部テーブルの作成を容易にする 2 つの関数を提供します。
+ `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` ロールのメンバーは、これらの関数へのアクセス権限を他のデータベースユーザーに付与することができます。
デフォルトでは、ログファイルは、`log_destination` パラメータで指定されたように、Amazon Aurora によって `stderr` (標準エラー) 形式で生成されます。このパラメータには、`stderr` と `csvlog` (カンマ区切り値、CSV) の 2 つのオプションしかありません。パラメータに `csvlog` オプションを追加すると、Amazon Aurora は `stderr` と `csvlog` 両方のログを生成します。これは DB クラスターのストレージ容量に影響を与える可能性があるため、ログ処理に影響を与える他のパラメータに注意する必要があります。詳細については、「[ログの送信先の設定 (`stderr`、`csvlog`)](USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.md#USER_LogAccess.Concepts.PostgreSQL.Log_Format)」を参照してください。
`csvlog` ログを生成すること 1 つの利点は、`log_fdw` 拡張機能により、データが複数の列にきちんと分割された外部テーブルを構築できることです。これを行うには、インスタンスをカスタム DB パラメータグループに関連付けて、`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. 選択したファイルの、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 DB インスタンスからリモート接続を設定すると、リードレプリカにもアクセスできます。
**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 DB クラスターから MySQL 互換データベースにアクセスするには、`mysql_fdw` 拡張機能をインストールしそれを使用します。この外部データラッパーを使用すると、RDS for MySQL、Aurora MySQL、MariaDB、その他の MySQL 互換データベースを操作できます。Aurora PostgreSQL DB クラスターから MySQL データベースへの接続は、クライアントとサーバーの設定に応じて、ベストエフォートベースで暗号化されます。ただし、必要に応じて暗号化を強制できます。詳細については、「[拡張機能で転送中の暗号化を使用する](#postgresql-mysql-fdw.encryption-in-transit)」を参照してください。
`mysql_fdw` 拡張機能は、Amazon Aurora PostgreSQL バージョン 15.4、14.9、13.12、12.16、 以降のリリースでサポートされています。MySQL 互換データベースインスタンス上のテーブルに対する RDS for PostgreSQL DB での選択、挿入、更新、および削除をサポートします。
**Topics**
+ [mysql\$1fdw 拡張機能を使用するように Aurora PostgreSQL DB をセットアップする](#postgresql-mysql-fdw.setting-up)
+ [例: Aurora PostgreSQL から Aurora MySQL データベースを操作する](#postgresql-mysql-fdw.using-mysql_fdw)
+ [拡張機能で転送中の暗号化を使用する](#postgresql-mysql-fdw.encryption-in-transit)
## mysql\$1fdw 拡張機能を使用するように Aurora PostgreSQL DB をセットアップする
Aurora PostgreSQL DB クラスターでの `mysql_fdw` 拡張機能のセットアップには、DB クラスターでの拡張機能のロードと、MySQL DB インスタンスへの接続ポイントの作成が関係しています。このタスクでは、MySQL DB インスタンスに関する次の詳細が必要です。
+ ホスト名またはエンドポイント。Aurora MySQL DB クラスターの場合、コンソールを使用してエンドポイントを見つけることができます。[接続とセキュリティ] タブを選択し、[エンドポイントとポート] セクションを確認します。
+ ポート番号。MySQL のデフォルトポート番号は 3306 です。
+ データベースの名前 DB 識別子。
また、MySQL ポート 3306 のセキュリティグループまたはアクセスコントロールリスト (ACL) へのアクセスを提供する必要があります。Aurora PostgreSQL DB クラスターと Aurora MySQL DB クラスターの両方がポート 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 DB インスタンスを操作するための適切な MySQL ユーザーアカウントへの独自のマッピングを作成します。
**mysql\$1fdw を使用して MySQL データベースサーバーにアクセスするには**
1. `rds_superuser` ロールがあるアカウントを使用して PostgreSQL DB インスタンスを接続します。Aurora PostgreSQL DB クラスター の作成時にデフォルトを受け入れた場合、ユーザー名は `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 DB クラスターにインストールされたら、MySQL データベースへの接続を提供する外部サーバーをセットアップします。
**外部サーバーを作成するには**
Aurora PostgreSQL DB クラスター でこれらのタスクを実行します。このステップは、`rds_superuser`特権 (`postgres` など) があるユーザーとして接続していることを前提としています。
1. Aurora PostgreSQL DB クラスターで外部サーバーを作成します。
```
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 DB インスタンスにシンプルなテーブルがあると仮定します。Aurora PostgreSQL ユーザーが、そのテーブルで (`SELECT`)、`INSERT`、`UPDATE`、`DELETE` の項目をクエリしたいと思っています。`mysql_fdw` 拡張機能は、前の手順で詳述されているように、RDS for PostgreSQL DB インスタンスで作成された、と仮定します。`rds_superuser` 権限のあるユーザーとして RDS for PostgreSQL DB インスタンスに接続した後、次の手順に進むことができます。
1. Aurora PostgreSQL DB インスタンスで外部サーバーを作成します。
```
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 ユーザーアカウントでも機能します。
`REQUIRE SSL` に構成された MySQL ユーザーアカウントの場合、安全な接続を確立できないと接続の試行は失敗します。
既存の 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 DB クラスター から Oracle データベースにアクセスするには、`oracle_fdw` 拡張機能をインストールして、使用します。この拡張機能は、Oracle データベース用の外部データラッパーです。この拡張機能の詳細については、[oracle\$1fdw](https://github.com/laurenz/oracle_fdw) のドキュメントを参照してください。
`oracle_fdw` 拡張機能は、PostgreSQL 12.7 (Amazon Aurora release 4.2) 以上のバージョンでサポートされています。
**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 DB インスタンスの以下の点を書き留めます。
+ エンドポイント
+ ポート
+ データベース名
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)
```
クエリで次のエラーが報告された場合は、セキュリティグループとアクセスコントロールリストをチェックして、両方のインスタンス間で通信が可能なことを確認します。
```
ERROR: connection for foreign table "mytab" cannot be established
DETAIL: ORA-12170: TNS:Connect timeout occurred
```
## 転送時の暗号化の使用
PostgreSQL から Oracle への転送時における暗号化は、クライアントとサーバーの設定パラメータの組み合わせに基づき構成されます。Oracle 21c の使用例については、Oracle ドキュメントの「[About the Values for Negotiating Encryption and Integrity](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 データベースの例で許可がどのように適用されるかを示す例がありますが、この情報は一般的に外部データラッパーに適用されます。
次の出力では、ロールとアクセス許可が、3 つの異なるサンプルユーザーにマップされていることが示されています。ここで、ユーザー `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 Server データベースの操作
PostgreSQL `tds_fdw` 拡張機能を使用して、Sybase や Microsoft SQL Server データベースなど、表形式データストリーム (TDS) プロトコルをサポートするデータベースにアクセスできます。この外部データラッパーを使用すると、 Aurora PostgreSQL DB クラスターを、Amazon RDS for Microsoft SQL Server を含む、TDS プロトコルを使用するデータベースに接続できます。詳細については、GitHub にある [tds-fdw/tds\$1fdw](https://github.com/tds-fdw/tds_fdw) に関するドキュメントを参照してください。
`tds_fdw` 拡張機能は、Amazon Aurora PostgreSQL バージョン 13.6 以降でサポートされています。
## tds\$1fdw 拡張機能を使用するように Aurora PostgreSQL DB をセットアップする
次の手順では、`tds_fdw` をセットアップして、Aurora PostgreSQL DB クラスターと使用する例を示します。`tds_fdw` を使用して SQL Server データベースに接続する前に、インスタンスの次の詳細を取得する必要があります。
+ ホスト名またはエンドポイント。RDS for SQL Server DB インスタンスの場合、コンソールを使用してエンドポイントを見つけることができます。[Connectivity & security] (接続とセキュリティ) タブを選択し、[Endpoint and port] (エンドポイントとポート) セクションを確認します。
+ ポート番号。Microsoft SQL Server のデフォルトポート番号は 1433 です。
+ データベースの名前 DB 識別子。
また、SQL Server ポート、1433 のセキュリティグループまたはアクセスコントロールリスト (ACL) でのアクセスを提供する必要があります。Aurora PostgreSQL DB クラスターと RDS for SQL Server DB インスタンスの両方が、ポート 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 DB クラスターのプライマリインスタンスに接続します。
```
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 DB クラスター に拡張機能をインストールした後、外部サーバーをセットアップします。
**外部サーバーを作成するには**
`rds_superuser` 権限があるアカウントを使用する Aurora PostgreSQL DB クラスターで次のタスクを実行します。
1. Aurora PostgreSQL DB クラスターで外部サーバーを作成します。
```
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 DB クラスターの 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 への接続には、SQL Server のデータベース設定に応じて、転送中の暗号化 (TLS/SSL) を使用します。SQL Server が暗号化用に設定されていない場合、SQL Server データベースへの要求を行う RDS for PostgreSQL クライアントは、暗号化されていない状態に戻ります。
`rds.force_ssl` パラメータを設定して、RDS for SQL Server DB インスタンスへの接続に暗号化を強制できます。この方法については、「[DB インスタンスへの接続に 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 DB インスタンスでの SSL の使用](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html)」を参照してください。