PostgreSQL の一般的な DBA タスク

このセクションでは、PostgreSQL データベースエンジンを実行している DB インスタンスに関するいくつかの一般的な DBA タスクの Amazon RDS の実装について説明します。マネージド型サービスの操作性を実現するために、Amazon RDS では DB インスタンスへのシェルアクセスはできません。また、上位の権限を必要とする特定のシステムプロシージャやシステムテーブルへのアクセスが制限されます。

Amazon RDS での PostgreSQL ログファイルの操作に関する詳細は、「PostgreSQL データベースのログファイル」を参照してください。

トピック

• ロールの作成
• PostgreSQL データベースへのアクセスの管理
• PostgreSQL パラメータの使用
• PostgreSQL DB インスタンスの監査ログ記録
• pgaudit 拡張機能の使用
• pg_repack 拡張機能の使用
• pgBadger を使用した PostgreSQL でのログ分析
• pg_config のコンテンツを表示する
• orafce 拡張機能を使用する
• postgres_fdw 拡張機能を使用した外部データへのアクセス
• oracle_fdw 拡張機能を使用した外部データへのアクセス
• パスワード管理を制限する
• Amazon RDS での PostgreSQL Autovacuum の使用
• PostGIS 拡張機能の使用
• アウトバウンドネットワークアクセスでカスタム DNS サーバーを使用する
• PostgreSQL pg_cron 拡張機能によるメンテナンスのスケジューリング
• pg_partman 拡張機能による PostgreSQL パーティションの管理
• RDS for PostgreSQL DB インスタンスから AWS Lambda 関数を呼び出す

ロールの作成

DB インスタンスを作成すると、作成したマスターユーザーシステムアカウントが rds_superuser ロールに割り当てられます。rds_superuser ロールは事前に定義された Amazon RDS ロールであり、PostgreSQL のスーパーユーザーロール (通常、ローカルインスタンスでは postgres という名前になります) と類似しています。ただし、制限がいくつかあります。PostgreSQL のスーパーユーザーロールと同様に、rds_superuser ロールは、DB インスタンスに対して最大の権限を持っています。また、ユーザーが DB インスタンスに対して最大のアクセス権を必要としない場合は、ユーザーにこのロールを割り当てないでください。

rds_superuser ロールでは以下の操作を実行できます。

• Amazon RDS で使用できる拡張機能の追加 詳細については、「サポートされている PostgreSQL の一部の機能」および PostgreSQL ドキュメントを参照してください。

• テーブルスペースを管理する (作成と削除も含む)。詳細については、 Amazon RDS の PostgreSQL のテーブルスペース と PostgreSQL のドキュメントの テーブルスペース セクションを参照してください。

• rds_superuser コマンドを使用して、pg_stat_activity ロールが割り当てられていないすべてのユーザーを表示し、pg_terminate_backend と pg_cancel_backend コマンドを使用して、それらのユーザーの接続を停止する。

• rds_replication ロール以外のすべてのロールに対して rds_superuser ロールを付与する/取り消す。詳細については、PostgreSQL のドキュメントの「GRANT」セクションを参照してください。

次の例は、ユーザーを作成して、ユーザーに rds_superuser ロールを付与する方法を示しています。ユーザー定義のロール (rds_superuser など) を付与する必要があります。

create role testuser with password 'testuser' login;
grant rds_superuser to testuser;

PostgreSQL データベースへのアクセスの管理

Amazon RDS for PostgreSQL ,では、どのユーザーがどのデータベースに接続する権限を持っているかを管理できます。他の PostgreSQL 環境では、pg_hba.conf ファイルを変更することでこの種の管理を実行できることがあります。Amazon RDS では、代わりにデータベース許可を使用できます。

PostgreSQL の新しいデータベースは、常にデフォルトの権限セットを使用して作成されます。デフォルト権限では、PUBLIC (すべてのユーザー) がデータベースに接続し、接続しながら一時テーブルを作成できます。

Amazon RDS で特定のデータベースに接続できるユーザーを管理するには、まずデフォルトの PUBLIC 権限を無効にします。次に、より細かく権限を付与しなおします。次のサンプルコードはその方法を示しています。

psql> revoke all on database <database-name> from public;
psql> grant connect, temporary on database <database-name> to <user/role name>;    

PostgreSQL データベースでの権限については、PostgreSQL のドキュメントで GRANT コマンドを参照してください。

PostgreSQL パラメータの使用

postgresql.conf ファイルでローカル PostgreSQL インスタンスに対して設定する PostgreSQL パラメータは、DB インスタンスの DB パラメータグループで保持されます。デフォルトのパラメータグループを使用して DB インスタンスを作成する場合、パラメータ設定は default.postgres9.6 というパラメータグループにあります。

DB インスタンスの作成時に、関連する DB パラメータグループ内のパラメータが読み込まれます。パラメータグループの値を変更することで、パラメータ値を変更できます。パラメータ値を変更するためのセキュリティ権限がある場合は、ALTER DATABASE、ALTER ROLE、および SET コマンドを使用して、パラメータ値を変更することもできます。ホストへのアクセス権がないため、コマンドラインで postgres コマンドと env PGOPTIONS コマンドのいずれも使用できません。

PostgreSQL パラメータの設定を把握しておくことが、難しいことがあります。次のコマンドを使用すると、現在のパラメータ設定とデフォルト値を表示することができます。

select name, setting, boot_val, reset_val, unit
from pg_settings
order by name;  

出力値の説明については、PostgreSQL ドキュメントの「pg_settings」トピックを参照してください。

max_connections または shared_buffers のメモリ設定が過度に大きいと、PostgreSQL インスタンスが起動できなくなります。一部のパラメータでは馴染みのない単位が使用されます。例えば、shared_buffers はサーバーで使用される 8 KB 単位の共有メモリバッファの数を設定します。

次のエラーは、インスタンスを起動しようとしたが、誤ったパラメータ設定が原因で起動できなかった場合に、postgres.log ファイルに書き込まれます。

2013-09-18 21:13:15 UTC::@:[8097]:FATAL:  could not map anonymous shared
memory: Cannot allocate memory
2013-09-18 21:13:15 UTC::@:[8097]:HINT:  This error usually means that 
PostgreSQL's request for a shared memory segment exceeded available memory or 
swap space. To reduce the request size (currently 3514134274048 bytes), reduce 
PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or 
max_connections.  

PostgreSQL パラメータには、静的と動的の 2 種類があります。静的パラメータを適用するには、DB インスタンスを再起動する必要があります。動的パラメータは、すぐに適用できます。次の表は、PostgreSQL DB インスタンスに対して変更できるパラメータと各パラメータの種類を示しています。

パラメータ名	Apply_Type	説明
application_name	動的	統計情報とログで報告されるアプリケーション名を設定します。
array_nulls	動的	配列での NULL 要素の入力を有効にします。
authentication_timeout	動的	クライアント認証の実行で許可する最大時間を設定します。
autovacuum	動的	autovacuum サブプロセスを起動します。
autovacuum_analyze_scale_factor	動的	分析する前のタプルの挿入、更新、削除の数 (reltuples の割合として指定)。
autovacuum_analyze_threshold	動的	分析する前のタプルの挿入、更新、削除の最小数。
autovacuum_naptime	動的	autovacuum の実行の間で休止状態になっている時間。
autovacuum_vacuum_cost_delay	動的	autovacuum でのバキューム処理のコスト遅延の値 (ミリ秒単位)。
autovacuum_vacuum_cost_limit	動的	autovacuum でバキューム処理を停止する制限値となるバキューム処理のコスト
autovacuum_vacuum_scale_factor	動的	バキューム処理する前のタプルの更新または削除の数 (reltuples の割合として指定)。
autovacuum_vacuum_threshold	動的	バキューム処理する前のタプルの更新また削除の最小数。
backslash_quote	動的	文字列リテラルでバックスラッシュ (\) を許可するかどうかを指定します。
bgwriter_delay	動的	ラウンド間でのバックグラウンドライターの休止時間。
bgwriter_lru_maxpages	動的	ラウンドあたりのフラッシュするバックグラウンドライター LRU ページの最大数。
bgwriter_lru_multiplier	動的	ラウンドあたりの解放される平均バッファー使用量の倍数。
bytea_output	動的	バイトの出力形式を設定します。
check_function_bodies	動的	CREATE FUNCTION の実行中に関数の本文をチェックします。
checkpoint_completion_target	動的	チェックポイント中にダーティバッファのフラッシュにかかった時間 (チェックポイント間隔の割合として指定)。
checkpoint_segments	動的	自動 WAL (先書きログ) チェックポイント間のログセグメントの最大間隔を設定します。
checkpoint_timeout	動的	自動 WAL チェックポイント間の最大時間を設定します。
checkpoint_warning	動的	チェックポイントセグメントがこの値よりも頻繁に満杯になる場合に警告を出します。
client_encoding	動的	クライアントの文字セットエンコードを設定します。
client_min_messages	動的	クライアントへ送信されるメッセージレベルを設定します。
commit_delay	動的	トランザクションのコミットからディスクへの WAL のフラッシュまでの遅延間隔をマイクロ秒単位で設定します。
commit_siblings	動的	commit_delay を実行する前に同時に開いている必要があるトランザクションの最少数を設定します。
constraint_exclusion	動的	クエリを最適化するために、プランナーが制約を使用できるようにします。
cpu_index_tuple_cost	動的	インデックススキャンの実行中に各インデックスエントリを処理する際にかかるコストに対するプランナーの見積もりを設定します。
cpu_operator_cost	動的	演算子の呼び出しや関数呼び出しのそれぞれを処理する際にかかるコストに対するプランナーの見積もりを設定します。
cpu_tuple_cost	動的	各タプル (行) の処理にかかるコストに対するプランナーの見積もりを設定します。
cursor_tuple_fraction	動的	取得されるカーソル行の割合に対するプランナーの見積もりを設定します。
datestyle	動的	日付と時刻の値の表示形式を設定します。
deadlock_timeout	動的	デッドロックをチェックするまでロックを待機する時間を設定します。
debug_pretty_print	動的	解析ツリーや計画ツリーの表示をインデントして見やすくします。
debug_print_parse	動的	各クエリの解析ツリーをログに記録します。
debug_print_plan	動的	各クエリの実行計画をログに記録します。
debug_print_rewritten	動的	各クエリの書き直された解析ツリーをログに記録します。
default_statistics_target	動的	デフォルトの統計情報の対象を設定します。
default_tablespace	動的	テーブルとインデックスを作成するためのデフォルトのテーブルスペースを設定します。
default_transaction_deferrable	動的	新しいトランザクションのデフォルトの遅延ステータスを設定します。
default_transaction_isolation	動的	新しい各トランザクションのトランザクション分離レベルを設定します。
default_transaction_read_only	動的	新しいトランザクションのデフォルトの読み取り専用ステータスを設定します。
default_with_oids	動的	デフォルトで OID を使用して新しいテーブルを作成します。
effective_cache_size	動的	ディスクキャッシュのサイズに関するプランナーの予測を設定します。
effective_io_concurrency	動的	ディスクサブシステムで効率的に処理できる同時リクエストの数。
enable_bitmapscan	動的	プランナーがビットマップスキャン計画を使用できるようにします。
enable_hashagg	動的	プランナーがハッシュされた集計計画を使用できるようにします。
enable_hashjoin	動的	プランナーがハッシュ結合計画を使用できるようにします。
enable_indexscan	動的	プランナーがインデックススキャン計画を使用できるようにします。
enable_material	動的	プランナーがマテリアル化を使用できるようにします。
enable_mergejoin	動的	プランナーがマージ結合計画を使用できるようにします。
enable_nestloop	動的	プランナーがネステッドループ結合計画を使用できるようにします。
enable_seqscan	動的	プランナーがシーケンシャルスキャン計画を使用できるようにします。
enable_sort	動的	プランナーが明示的なソートステップを使用できるようにします。
enable_tidscan	動的	プランナーが TID スキャン計画を使用できるようにします。
escape_string_warning	動的	通常の文字列リテラルにバックスラッシュ (\) が含まれている場合に警告を出します。
extra_float_digits	動的	浮動小数点値の表示桁数を設定します。
from_collapse_limit	動的	FROM リストのサイズを設定します。この値を超えるとサブクエリが折りたたまれなくなります。
fsync	動的	ディスクへの更新の同期を強制的に行います。
full_page_writes	動的	チェックポイントの後でページに最初の変更を加えた時点で、WAL にすべてのページを書き込みます。
geqo	動的	遺伝的クエリ最適化を有効にします。
geqo_effort	動的	GEQO: 他の GEQO パラメータのデフォルト値を設定するために使用されます。
geqo_generations	動的	GEQO: アルゴリズムの反復の数。
geqo_pool_size	動的	GEQO: 母集団内の個体の数。
geqo_seed	動的	GEQO: 無作為のパスを選択するための初期値。
geqo_selection_bias	動的	GEQO: 母集団内の選択圧。
geqo_threshold	動的	FROM 項目のしきい値を設定します。この値を超えると GEQO が使用されます。
gin_fuzzy_search_limit	動的	GIN による完全一致検索で許可される結果の最大数を設定します。
hot_standby_feedback	動的	ホットスタンバイがフィードバックメッセージをプライマリあるいはアップストリームスタンバイに送信するかを決定します。
intervalstyle	動的	間隔値の表示形式を設定します。
join_collapse_limit	動的	FROM リストのサイズを設定します。この値を超えると JOIN 構造が平坦化されなくなります。
lc_messages	動的	メッセージを表示する言語を設定します。
lc_monetary	動的	金額の書式のロケールを設定します。
lc_numeric	動的	数値の書式のロケールを設定します。
lc_time	動的	日付と時刻の書式のロケールを設定します。
log_autovacuum_min_duration	動的	autovacuum に関する最小実行時間を設定します。この値を超えると autovacuum アクションがログに記録されます。
log_checkpoints	動的	各チェックポイントをログに記録します。
log_connections	動的	成功した各接続をログに記録します。
log_disconnections	動的	セッションの終了をログに記録します (セッションの有効期間も含まれます)。
log_duration	動的	完了した各 SQL ステートメントの期間をログに記録します。
log_error_verbosity	動的	ログに記録されるメッセージの詳細を設定します。
log_executor_stats	動的	実行プログラムのパフォーマンスの統計情報をサーバーログに書き込みます。
log_filename	動的	ログファイルのファイル名のパターンを設定します。
log_hostname	動的	接続ログにホスト名を記録します。
log_lock_waits	動的	長期間にわたるロックの待機をログに記録します。
log_min_duration_statement	動的	最小実行時間を設定します。この値を超えるとステートメントがログに記録されます。
log_min_error_statement	動的	設定したレベル以上のエラーが発生したすべてのステートメントをログに記録します。
log_min_messages	動的	ログに記録するメッセージレベルを設定します。
log_parser_stats	動的	解析のパフォーマンスの統計情報をサーバーログに書き込みます。
log_planner_stats	動的	プランナーのパフォーマンスの統計情報をサーバーログに書き込みます。
log_rotation_age	動的	N 分が経過するとログファイルのローテーションが自動的に発生します。
log_rotation_size	動的	N キロバイトを超えるとログファイルのローテーションが自動的に発生します。
log_statement	動的	ログに記録するステートメントのタイプを設定します。
log_statement_stats	動的	累積処理のパフォーマンスの統計情報をサーバーログに書き込みます。
log_temp_files	動的	指定したサイズ (キロバイト) を超える一時ファイルの使用をログに記録します。
maintenance_work_mem	動的	メンテナンスオペレーションに使用するメモリの最大量を設定します。
max_stack_depth	動的	スタックの深度の最大値をキロバイト単位で指定します。
max_standby_archive_delay	動的	ホットスタンバイサーバーがアーカイブされた WAL データを処理しているときにクエリをキャンセルするまでの最大遅延間隔を設定します。
max_standby_streaming_delay	動的	ホットスタンバイサーバーがストリームされた WAL データを処理しているときにクエリをキャンセルするまでの最大遅延間隔を設定します。
max_wal_size	動的	チェックポイントをトリガーする WAL サイズを設定します。PostgreSQL バージョン 9.6 以前の場合、max_wal_size の単位は 16 MB です。PostgreSQL バージョン 10 以降の場合、max_wal_size の単位は 1 MB です。
min_wal_size	動的	WAL を縮小する最小サイズを設定します。PostgreSQL バージョン 9.6 以前の場合、min_wal_size の単位は 16 MB です。PostgreSQL バージョン 10 以降の場合、min_wal_size の単位は 1 MB です。
quote_all_identifiers	動的	SQL フラグメントを生成するときに、すべての識別子に引用符 (") を追加します。
random_page_cost	動的	非連続的に取得されたディスクページのコストに対するプランナーの見積もりを設定します。
rds.adaptive_autovacuum	動的	トランザクション ID のしきい値を超えるたびに、autovacuum パラメータを自動的に微調整します。
rds.log_retention_period	動的	N 分より古い PostgreSQL ログは Amazon RDS で削除されるようにログ保持期間を設定します。
rds.restrict_password_commands	静的	rds_password ロールを持つユーザーに対して、だれがパスワードを管理するかを制限します。パスワードの制限を有効にするには、このパラメータを 1 に設定します。デフォルトは 0 です。
search_path	動的	スキーマによって修飾されていない名前でスキーマを検索する順序を設定します。
seq_page_cost	動的	連続的に取得されたディスクページのコストに対するプランナーの見積もりを設定します。
session_replication_role	動的	トリガーと再書き込みルールに対するセッション動作を設定します。
sql_inheritance	動的	さまざまなコマンドにデフォルトでサブテーブルが取り込まれます。
ssl_renegotiation_limit	動的	暗号化キーを再度ネゴシエートする前に送受信されるトラフィックの量を設定します。
standard_conforming_strings	動的	... 文字列をリテラルのバックスラッシュとして扱います。
statement_timeout	動的	すべてのステートメントに許可される最大実行時間を設定します。
synchronize_seqscans	動的	シーケンシャルスキャンの同期を有効にします。
synchronous_commit	動的	現在のトランザクションの同期レベルを設定します。
tcp_keepalives_count	動的	TCP キープアライブを再送信する最大回数。
tcp_keepalives_idle	動的	TCP キープアライブを発行する間隔の時間。
tcp_keepalives_interval	動的	TCP キープアライブを再送信する間隔の時間。
temp_buffers	動的	各セッションで使用される一時バッファの最大数を設定します。
temp_tablespaces	動的	一時テーブルとソートファイルで使用するテーブルスペースを設定します。
timezone	動的	表示やタイムスタンプの解釈で必要となるタイムゾーンを設定します。
track_activities	動的	コマンドの実行に関する情報を収集します。
track_counts	動的	データベースアクティビティの統計情報を収集します。
track_functions	動的	データベースアクティビティの関数レベルの統計情報を収集します。
track_io_timing	動的	データベース I/O アクティビティのタイミングに関する統計情報を収集します。
transaction_deferrable	動的	読み取り専用のシリアル化可能なトランザクションを、シリアル化が失敗する可能性がない状況で開始できるようになるまで延期するかどうかを示します。
transaction_isolation	動的	現在のトランザクションの分離レベルを設定します。
transaction_read_only	動的	現在のトランザクションの読み取り専用ステータスを設定します。
transform_null_equals	動的	expr=NULL を expr IS NULL として扱います。
update_process_title	動的	アクティブな SQL コマンドを表示するようにプロセスのタイトルを更新します。
vacuum_cost_delay	動的	バキューム処理のコスト遅延の値 (ミリ秒単位)。
vacuum_cost_limit	動的	バキューム処理を停止する制限値となるバキューム処理のコスト。
vacuum_cost_page_dirty	動的	バキューム処理によってダーティになったページに対するバキューム処理のコスト。
vacuum_cost_page_hit	動的	バッファキャッシュ内で検出されたページに対するバキューム処理のコスト。
vacuum_cost_page_miss	動的	バッファキャッシュ内で検出されなかったページに対するバキューム処理のコスト。
vacuum_defer_cleanup_age	動的	バキューム処理とホットクリーンアップが延期されるトランザクションの数 (存在する場合)。
vacuum_freeze_min_age	動的	バキューム処理でテーブルの行をフリーズする最小期間。
vacuum_freeze_table_age	動的	バキューム処理でテーブル全体をスキャンしタプルをフリーズするための期間。
wal_writer_delay	動的	WAL のフラッシュが行われる間の WAL ライターの休止時間。
work_mem	動的	クエリワークスペースに使用するメモリの最大量を設定します。
xmlbinary	動的	バイナリ値を XML にエンコードする方法を設定します。
xmloption	動的	黙示的な解析とシリアル化オペレーションでの XML データをドキュメントとして見なすか、コンテンツのフラグメントとして見なすかを設定します。
autovacuum_freeze_max_age	静的	トランザクション ID の循環を防ぐためにテーブルに対して autovacuum を実行する期間。
autovacuum_max_workers	静的	同時に実行される autovacuum ワーカープロセスの最大数を設定します。
max_connections	静的	同時接続の最大数を設定します。
max_files_per_process	静的	各サーバープロセスで同時に開くことができるファイルの最大数を設定します。
max_locks_per_transaction	静的	トランザクションあたりのロックの最大数を設定します。
max_pred_locks_per_transaction	静的	トランザクションあたりの述語ロックの最大数を設定します。
max_prepared_transactions	静的	同時に準備できるトランザクションの最大数を設定します。
shared_buffers	静的	サーバーで使用される共有メモリバッファの数を設定します。
ssl	静的	SSL 接続を有効にします。
temp_file_limit	静的	一時ファイルの最大サイズを KB 単位で設定します。
track_activity_query_size	静的	pg_stat_activity.current_query 用に予約するサイズをバイト単位で設定します。
wal_buffers	静的	WAL 用の共有メモリ内のディスクページバッファの数を設定します。

Amazon RDS では、すべてのパラメータについて PostgreSQL のデフォルトの単位を使用します。次の表は、各パラメータに対する PostgreSQL のデフォルトの単位と値を示しています。

パラメータ名	単位
effective_cache_size	8 KB
segment_size	8 KB
shared_buffers	8 KB
temp_buffers	8 KB
wal_buffers	8 KB
wal_segment_size	8 KB
log_rotation_size	KB
log_temp_files	KB
maintenance_work_mem	KB
max_stack_depth	KB
ssl_renegotiation_limit	KB
temp_file_limit	KB
work_mem	KB
log_rotation_age	分
autovacuum_vacuum_cost_delay	ms
bgwriter_delay	ms
deadlock_timeout	ms
lock_timeout	ms
log_autovacuum_min_duration	ms
log_min_duration_statement	ms
max_standby_archive_delay	ms
max_standby_streaming_delay	ms
statement_timeout	ms
vacuum_cost_delay	ms
wal_receiver_timeout	ms
wal_sender_timeout	ms
wal_writer_delay	ms
archive_timeout	s
authentication_timeout	s
autovacuum_naptime	s
checkpoint_timeout	s
checkpoint_warning	s
post_auth_delay	s
pre_auth_delay	s
tcp_keepalives_idle	s
tcp_keepalives_interval	s
wal_receiver_status_interval	s

PostgreSQL DB インスタンスの監査ログ記録

いくつかのパラメータを設定して、PostgreSQL DB インスタンスで発生するアクティビティのログを作成できます。これらのパラメータには、以下が含まれます。

• log_statement パラメータは PostgreSQL データベースのユーザー操作のログを作成するのに使用できます。詳細については、「PostgreSQL データベースのログファイル」を参照してください。

• rds.force_admin_logging_level パラメータは、DB インスタンス上のデータベースでの RDS 内部ユーザー (rdsadmin) によるアクションをログに記録し、その出力を PostgreSQL エラーログに書き込みます。指定できる値は disabled、debug5、debug4、debug3、debug2、debug1、info、notice、warning、error、log、fatal、panic です。デフォルト値は disabled です。

• rds.force_autovacuum_logging_level パラメータは、DB インスタンス上のすべてのデータベースでの autovacuum ワーカーによるオペレーションをログに記録し、その出力を PostgreSQL エラーログに書き込みます。指定できる値は disabled、debug5、debug4、debug3、debug2、debug1、info、notice、warning、error、log、fatal、panic です。デフォルト値は disabled です。rds.force_autovacuum_logging_level: 用の Amazon RDS の推奨設定は LOG です。log_autovacuum_min_duration を 1000 または 5000 の値に設定します。この値を 5,000 に設定すると、5 秒を超えるアクティビティがログに書き込まれ、「バキュームのスキップ」メッセージが表示されます。このパラメータの詳細については、「PostgreSQL を使用するためのベストプラクティス」を参照してください。

pgaudit 拡張機能の使用

pgaudit 拡張機能により、RDS for PostgreSQL の詳細セッションおよびオブジェクト監査のログ記録が提供されます。この拡張機能を使用して、セッション監査またはオブジェクト監査を有効にできます。pgaudit 拡張機能は、次のバージョンで使用できます。

• RDS for PostgreSQL バージョン 13、すべてのマイナーバージョン

• RDS for PostgreSQL バージョン 12、すべてのマイナーバージョン

• RDS for PostgreSQL バージョン 11、すべてのマイナーバージョン

• RDS for PostgreSQL バージョン 10、すべてのマイナーバージョン

• RDS for PostgreSQL バージョン 9.6.3 以降の 9.6 バージョン

• RDS for PostgreSQL バージョン 9.5.7 以降の 9.5 バージョン

pgaudit 拡張機能の詳細については、GitHub プロジェクトのドキュメントをご覧ください。お使いの PostgreSQL バージョンのドキュメントを選択してください。

pgaudit 拡張機能を使用するには、カスタム DB パラメータグループが DB インスタンスに関連付けられていることを確認し、このカスタム DB パラメータグループでパラメータを設定します。パラメータグループの詳細については、「DB パラメータグループを使用する」を参照してください。

セッション監査では、さまざまなソースから監査イベントをログ記録できます。利用可能な場合は、完全修飾コマンドテキストも含めることができます。DB インスタンスに関連付けられているカスタムパラメータグループを変更して、shared_preload_libraries に pgaudit が含まれるようにしてから、次のいずれかのタイプのイベントをログ記録するように pgaudit.log パラメータを設定します。

• READ - SELECT および COPY の監査 (ソースがリレーションまたはクエリの場合)。

• WRITE - INSERT、UPDATE、DELETE、TRUNCATE、および COPY の監査 (送信先がリレーションの場合)。

• FUNCTION - 関数呼び出し、および DO ブロックの監査。

• ROLE - GRANT、REVOKE、CREATE ROLE、ALTER ROLE、DROP ROLE など、ロールと権限に関連するステートメントの監査。

• DDL - ROLE クラスに含まれていない、すべてのデータ定義言語 (DDL) ステートメントの監査。

• MISC - DISCARD、FETCH、CHECKPOINT、VACUUM、SET など、さまざまなコマンドの監査。

セッション監査でこれらのタイプのイベントを複数ログ記録するには、カンマ区切りリストを使用します。これらすべてのタイプのイベントをログ記録するには、pgaudit.log を ALL に設定します。変更を適用するために、DB インスタンスの再起動が必要になる場合があります。

オブジェクト監査では、監査のログ記録を絞り込み、特定のリレーションを操作できます。例えば、特定の数のテーブルで、READ オペレーションのログ記録を監査するよう指定できます。

pgaudit 拡張機能でオブジェクト監査を使用する方法

1. 次のコマンドを使用して、rds_pgaudit というデータベースロールを作成します。

   CREATE ROLE rds_pgaudit;

2. DB インスタンスに関連付けられているカスタムパラメータグループを変更して、次の操作を実行します。

   • pgaudit.role をロール rds_pgaudit に設定します。

   • pgaudit を含む共有プリロードライブラリを使用します。

   次のコマンドは、カスタムパラメータグループを変更します。

   aws rds modify-db-parameter-group \
      --db-parameter-group-name my-parameter-group \
      --parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" \
      --region us-west-2
   
   aws rds modify-db-parameter-group \
      --db-parameter-group-name my-parameter-group \
      --parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \
      --region us-west-2

3. DB インスタンスを再起動し、パラメータグループへの変更をインスタンスに反映します。

   aws rds reboot-db-instance \
       --db-instance-identifier rds-test-instance \
       --region us-west-2      

4. 次のコマンドを実行して、pgaudit が初期化されたことを確認します。

   SHOW shared_preload_libraries;
   
   shared_preload_libraries 
   --------------------------
   rdsutils,pgaudit
   (1 row)      

5. pgaudit 拡張機能を作成するには、次のコマンドを実行します。

   CREATE EXTENSION pgaudit;

6. 次のコマンドを実行して、pgaudit.role が rds_pgaudit に設定されたことを確認します。

   SHOW pgaudit.role;
   
   pgaudit.role 
   ------------------
   rds_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 RDS データベースログファイルの操作」を参照してください。

pg_repack 拡張機能の使用

pg_repack 拡張機能を使用して、テーブルやインデックスの膨張を取り除くことができます。この拡張機能は Amazon RDS for PostgreSQL バージョン 9.6.3 以降でサポートされています。pg_repack 拡張機能の詳細については、GitHub プロジェクトのドキュメントをご覧ください。

pg_repack 拡張機能を使用するには

1. 次のコマンドを実行して、Amazon RDS for PostgreSQL DB インスタンスに pg_repack 拡張機能をインストールします。

   CREATE EXTENSION pg_repack;

2. 次のコマンドを実行して、pg_repack によって作成された一時ログテーブルを再パックするための書き込みアクセス権を付与します。

   ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC;
   ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;

3. pg_repack クライアントユーティリティを使用してデータベースに接続します。rds_superuser 権限を持つデータベースロールを使用してデータベースに接続します。次の接続例で、rds_test ロールには rds_superuser 権限があり、使用されるデータベースエンドポイントは rds-test-instance.cw7jjfgdr4on8.us-west-2.rds.amazonaws.com です。

   pg_repack -h rds-test-instance.cw7jjfgdr4on8.us-west-2.rds.amazonaws.com -U rds_test -k postgres

   -k オプションを使用して接続します。-a オプションはサポートされていません。

4. pg_repack クライアントからの応答により、再パッケージされる DB インスタンスのテーブルに関する情報が提供されます。

   INFO: repacking table "pgbench_tellers"
   INFO: repacking table "pgbench_accounts"
   INFO: repacking table "pgbench_branches"

pgBadger を使用した PostgreSQL でのログ分析

pgbadger などのログ分析ツールを使用して、PostgreSQL のログを分析できます。pgbadger のドキュメントでは %l パターン (セッションやプロセスに関するログの行) はプレフィックスに含める必要があると説明されています。ただし、最新の rds log_line_prefix をパラメータとして pgbadger に渡すことで、レポートを作成できます。

例えば、次のコマンドでは、pgbadger を使用して、2014 年 2 月 4 日の Amazon RDS for PostgreSQL のログファイルを適切にフォーマットします。

./pgbadger -f stderr -p '%t:%r:%u@%d:[%p]:' postgresql.log.2014-02-04-00 

pg_config のコンテンツを表示する

PostgreSQL バージョン 9.6.1 では、新しいビューの view pg_config を使用して、現在インストールされている PostgreSQL のバージョンのコンパイル時設定パラメータを表示できます。次の例に示すように、pg_config 関数を呼び出してビューを使用できます。

select * from pg_config();
       name        |             setting

-------------------+---------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
 BINDIR            | /rdsdbbin/postgres-9.6.1.R1/bin
 DOCDIR            | /rdsdbbin/postgres-9.6.1.R1/share/doc
 HTMLDIR           | /rdsdbbin/postgres-9.6.1.R1/share/doc
 INCLUDEDIR        | /rdsdbbin/postgres-9.6.1.R1/include
 PKGINCLUDEDIR     | /rdsdbbin/postgres-9.6.1.R1/include
 INCLUDEDIR-SERVER | /rdsdbbin/postgres-9.6.1.R1/include/server
 LIBDIR            | /rdsdbbin/postgres-9.6.1.R1/lib
 PKGLIBDIR         | /rdsdbbin/postgres-9.6.1.R1/lib
 LOCALEDIR         | /rdsdbbin/postgres-9.6.1.R1/share/locale
 MANDIR            | /rdsdbbin/postgres-9.6.1.R1/share/man
 SHAREDIR          | /rdsdbbin/postgres-9.6.1.R1/share
 SYSCONFDIR        | /rdsdbbin/postgres-9.6.1.R1/etc
 PGXS              | /rdsdbbin/postgres-9.6.1.R1/lib/pgxs/src/makefiles/pgxs.mk
 CONFIGURE         | '--prefix=/rdsdbbin/postgres-9.6.1.R1' '--with-openssl' '--with-perl' 
 '--with-tcl' '--with-ossp-uuid' '--with-libxml' '--with-libraries=/rdsdbbin
/postgres-9.6.1.R1/lib' '--with-includes=/rdsdbbin/postgres-9.6.1.R1/include' '--enable-debug'
 CC                | gcc
 CPPFLAGS          | -D_GNU_SOURCE -I/usr/include/libxml2 -I/rdsdbbin/postgres-9.6.1.R1/include
 CFLAGS            | -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement 
 -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-
aliasing -fwrapv -fexcess-precision=standard -g -O2
 CFLAGS_SL         | -fpic
 LDFLAGS           | -L../../src/common -L/rdsdbbin/postgres-9.6.1.R1/lib -Wl,--as-needed -Wl,
 -rpath,'/rdsdbbin/postgres-9.6.1.R1/lib',--enable-new-dtags
 LDFLAGS_EX        |
 LDFLAGS_SL        |
 LIBS              | -lpgcommon -lpgport -lxml2 -lssl -lcrypto -lz -lreadline -lrt -lcrypt 
 -ldl -lm
 VERSION           | PostgreSQL 9.6.1
(23 rows)
    

orafce 拡張機能を使用する

orafce 拡張機能では、商用データベースの一般的な機能が提供されており、商用データベースの PostgreSQL への移行を簡単に行うことができます。この拡張機能は Amazon RDS for PostgreSQL バージョン 9.6.6 以降でサポートされています。orafce についての詳細は、「GitHub に関する orafce プロジェクト」を参照してください。

注記

Amazon RDS for PostgreSQL は、utl_file 拡張の一部である orafce パッケージをサポートしていません。これは、utl_file スキーマ関数が、基になるモストへのスーパーユーザーアクセスに必要なオペレーティングシステムテキストファイルで読み書き操作を実行するためです。

orafce 拡張機能を使用するには

1. DB インスタンスの作成で使用したマスターユーザー名を使用して DB インスタンスに接続します。

   注記

   同じインスタンスの別のデータベースで orafce を有効にする場合は、/c dbname psql コマンドを使用して、接続初期化後にプライマリデータベースから変更することができます。

2. CREATE EXTENSION ステートメントを使用して、orafce 拡張機能を有効にします。

   CREATE EXTENSION orafce;

3. ALTER SCHEMA ステートメントを使用して、oracle スキーマの所有権を rds_superuser ロールに転送します。

   ALTER SCHEMA oracle OWNER TO rds_superuser;

   注記

   oracle スキーマの所有者のリストを表示する場合は、\dn psql コマンドを使用します。

postgres_fdw 拡張機能を使用した外部データへのアクセス

postgres_fdw 拡張を使用してリモートデータベースサーバーにあるテーブルのデータにアクセスできます。PostgreSQL DB インスタンスからリモート接続を設定すると、リードレプリカにもアクセスできます。

postgres_fdw を使用してリモートデータベースサーバーにアクセスするには

1. postgres_fdw 拡張をインストールします。

   
   CREATE EXTENSION postgres_fdw;
                   

2. CREATE SERVER を使用して外部データサーバーを作成します。

   
   CREATE SERVER foreign_server
   FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db'); 
           

3. リモートサーバーで使用するロールを識別するためのユーザーマッピングを作成します。

   
   CREATE USER MAPPING FOR local_user
   SERVER foreign_server
   OPTIONS (user 'foreign_user', password 'password');           
           

4. リモートサーバーのテーブルにマッピングするテーブルを作成します。

   
   CREATE FOREIGN TABLE foreign_table (
           id integer NOT NULL,
           data text)
   SERVER foreign_server
   OPTIONS (schema_name 'some_schema', table_name 'some_table');          
             
           

oracle_fdw 拡張機能を使用した外部データへのアクセス

PostgreSQL oracle_fdw 拡張機能を使用すると、Oracleデータベースへの簡単かつ効率的なアクセスを実現するための、外部データラッパーを提供できます。詳細な説明については、「oracle_fdw」ドキュメントを参照してください。

oracle_fdw 拡張機能は、Amazon RDS for PostgreSQL のバージョン 12.7、13.3、およびそれ以降でサポートされています。

oracle_fdw 拡張機能の有効化

oracle_fdw 拡張機能を有効にするには

• rds_superuser のアクセス許可を持つアカウントを使用して、次のコマンドを実行します。

  CREATE EXTENSION oracle_fdw;

Amazon RDS for Oracle Database にリンクされた外部サーバーの使用例

RDS for Oracle データベースにリンクされた外部サーバーを作成するには

1. RDS for Oracle DB インスタンスの以下の点を書き留めます。

   • エンドポイント

   • ポート

   • データベース名

2. 外部サーバを作成します。

   test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name');
   CREATE SERVER

3. rds_superuser のアクセス許可を持たないユーザーに、(例えば user1 として) 使用を許可します。

   test=> GRANT USAGE ON FOREIGN SERVER oradb TO user1;
   GRANT

4. user1 として接続し、Oracle ユーザーへのマッピングを作成します。

   test=> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword');
   CREATE USER MAPPING

5. Oracle テーブルにリンクされた外部テーブルを作成します。

   test=> create foreign table mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE');
   CREATE FOREIGN TABLE

6. 外部テーブルに対しクエリを実行します。

   test=> select * from mytab;
    a
   ---
    1
   (1 row)

クエリで次のエラーが報告された場合は、セキュリティグループと アクセスコントロールリスト (ACL) をチェックして、両方のインスタンス間で通信が可能なことを確認します。

ERROR: connection for foreign table "mytab" cannot be established
DETAIL: ORA-12170: TNS:Connect timeout occurred

転送時の暗号化

PostgreSQL から Oracle への転送時における暗号化は、クライアントとサーバの設定パラメータの組み合わせに基づき構成されます。Oracle 21c の使用例については、Oracle ドキュメントの「About the Values for Negotiating Encryption and Integrity」を参照してください。Amazon RDS で oracle_fdw 用に使用されるクライアントは、ACCEPTED に設定されています。つまり、暗号化は Oracle データベースサーバーの設定に依存します。

データベースが RDS for Oracle 上にある場合の暗号化の設定については、「Oracle ネイティブネットワーク暗号化」を参照してください。

pg_user_mapping および pg_user_mappings のアクセス許可

次の表に示すロールの例により、ユーザーマッピングでのアクセス許可の方法を確認できます。rdssu1 および rdssu2 ユーザーには rds_superuser ロールがありますが、user1 には付与されていません。

注記

既存のロールを一覧表示するには、\du メタコマンドを使用します。

test=> \du
                                                               List of roles
    Role name    |                         Attributes                         |                          Member of
-----------------+------------------------------------------------------------+-------------------------------------------------------------
 rdssu1          |                                                            | {rds_superuser}
 rdssu2          |                                                            | {rds_superuser}
 user1           |                                                            | {}

ユーザーは、rds_superuser ロールを使用して pg_user_mapping テーブルをクエリすることはできません。次の例では rdssu1 を使用しています。

test=> SET SESSION AUTHORIZATION rdssu1;
SET
test=> select * from pg_user_mapping;
ERROR: permission denied for table pg_user_mapping

RDS for PostgreSQL では、すべてのユーザーは (rds_superuser ロールがある場合も含め)、pg_user_mappings テーブル内にある自分のユーザーマッピング (umoptions) のみを参照できます。次に例を示します。

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 となっています。

パスワード管理を制限する

データベースユーザーのパスワードの管理担当者を特定のロールに限定できます。これにより、クライアント側でのパスワード管理をより柔軟にコントロールできます。

静的パラメータ rds.restrict_password_commands でパスワード管理の制限を有効にし、rds_password というロールを使用します。パラメータ rds.restrict_password_commands を 1 に設定すると、rds_password ロールのメンバーユーザーのみが特定の SQL コマンドを実行できます。制限された SQL コマンドは、データベースユーザーのパスワードとパスワードの有効期限を変更するコマンドです。

制限されたパスワード管理を使用するには、DB インスタンスで Amazon RDS for PostgreSQL 10.6 以上を実行している必要があります。rds.restrict_password_commands パラメータは静的であるため、このパラメータを変更するにはデータベースの再起動が必要です。

制限されたパスワード管理がデータベースで有効になっている場合、制限された SQL コマンドを実行しようとすると、「エラー: パスワードを変更するには rds_password のメンバーであることが必要です」というエラーが表示されます。

制限されたパスワード管理を有効にした場合に制限される SQL コマンドの例は以下のとおりです。

postgres=> CREATE ROLE myrole WITH PASSWORD 'mypassword';
postgres=> CREATE ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2020-01-01';
postgres=> ALTER ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2020-01-01';
postgres=> ALTER ROLE myrole WITH PASSWORD 'mypassword';
postgres=> ALTER ROLE myrole VALID UNTIL '2020-01-01';
postgres=> ALTER ROLE myrole RENAME TO myrole2;

ALTER ROLE が含まれている RENAME TO コマンドのいくつかも制限される場合があります。これらのコマンドが制限されるのは、MD5 パスワードが含まれている PostgreSQL ロールの名前を変更すると、パスワードが消去されるためです。

rds_superuser ロールは、rds_password ロールのデフォルトメンバーであるため、これを変更することはできません。他のロールに対して rds_password ロールのメンバーシップを付与するには、SQL の GRANT コマンドを使用します。rds_password のメンバーシップは、パスワード管理専用の少数のロールにのみ付与することをお勧めします。これらのロールは、他のロールを変更するために、CREATEROLE 属性を必要とします。

パスワード要件 (クライアント側の有効期限や必要な複雑さなど) を確認してください。クライアント側の独自のユーティリティを使用してパスワード関連の変更を制限することをお勧めします。このユーティリティには、rds_password のメンバーであり、CREATEROLE 属性を持つロールが必要です。