Amazon Relational Database Service
ユーザーガイド (API バージョン 2014-10-31)

PostgreSQL の一般的な DBA タスク

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

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

ロールの作成

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

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

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

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

  • rds_superuser コマンドを使用して、pg_stat_activity ロールが割り当てられていないすべてのユーザーを表示し、pg_terminate_backendpg_cancel_backend コマンドを使用して、それらのユーザーの接続を強制終了する。

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

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

create role testuser with password 'testuser' login; CREATE ROLE grant rds_superuser to testuser; GRANT ROLE

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_connectionsshared_bufferseffective_cache_size のメモリ設定が過度に大きいと、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

Amazon RDS での PostgreSQL Autovacuum の使用

PostgreSQL データベースの autovacuum 機能を使用して、PostgreSQL DB インスタンスの状態を維持することを強くお勧めします。autovacuum は、VACUUM コマンドと ANALYZE コマンドの実行を自動化します。autovacuum は、多数のタプルが挿入、更新、または削除されたテーブルを確認します。次に、autovacuum は PostgreSQL データベースから旧型のデータやタプルを削除することで、ストレージを解放します。

autovacuum は、すべての新しい Amazon RDS PostgreSQL DB インスタンスでデフォルトで有効になり、関連する autovacuum 設定パラメータがデフォルトで適切に設定されます。デフォルト値は汎用的であるため、特定のワークロードに合わせてパラメータを調整すると便利です。次のセクションは、autovacuum の必要な調整を行う際に役立ちます。

autovacuum のメモリを割り当てる

autovacuum のパフォーマンスに影響を与える最も重要なパラメータの 1 つは、maintenance_work_mem パラメータです。このパラメータでは、データベーステーブルをスキャンしたり、バキューム処理するすべての行 ID を保持したりするために、autovacuum に割り当てるメモリの量を指定します。maintenance_work_mem パラメータの設定値が低すぎる場合、バキューム処理は処理を完了するためにテーブルを複数回処理することが必要になる場合があります。このような複数のスキャンは、パフォーマンスに悪影響を及ぼすことがあります。

計算を行うときは、次の 2 つの点を念頭に置いて maintenance_work_mem パラメータ値を決定します。

  • このパラメータのデフォルト単位はキロバイト (KB) です。

  • maintenance_work_mem パラメータは、autovacuum_max_workers パラメータと連動して機能します。小さいテーブルが多数ある場合、autovacuum_max_workers の割り当てを増やして maintenance_work_mem の割り当てを減らします。大きなテーブル (100 GB 以上など) がある場合は、メモリの割り当てを増やしてワーカープロセス数を減らします。最も大きいテーブルを正常に処理するには、十分なメモリを割り当てる必要があります。各 autovacuum_max_workers は、割り当てたメモリを使用できます。したがって、ワーカープロセスとメモリの組み合わせが、割り当てる総メモリと等しくなるようにしてください。

通常、大きいホストの場合は、maintenance_work_mem パラメータを 1〜2 ギガバイト (1,048,576~2,097,152 KB) の値に設定します。非常に大きいホストの場合は、このパラメータを 2〜4 ギガバイト (2,097,152~4,194,304 KB) の値に設定します。このパラメータの設定値は、ワークロードによって異なります。Amazon RDS は、このパラメータのデフォルト値を更新し、以下の方法で計算したキロバイトになるようにしています。

GREATEST({DBInstanceClassMemory/63963136*1024},65536)

トランザクション ID の循環の可能性を減らす

autovacuum に関連するパラメータグループの設定は、トランザクション ID の循環を防ぐほどは排除率が高くない場合があります。この問題に対処するために、Amazon RDS for PostgreSQL は autovacuum パラメータ値を自動的に適応させる機構を提供しています。autovacuum パラメータのアダプティブチューニングは、RDS for PostgreSQL バージョン 9.4 以降の機能です。トランザクション ID の循環に関する詳しい説明については、PostgreSQL ドキュメントを参照してください。

autovacuum パラメータのアダプティブチューニングは、動的パラメータ rds.adaptive_autovacuum が ON に設定されている RDS PostgreSQL インスタンスで、デフォルトで有効になります。この設定を有効にしておくことを強くお勧めします。ただし、autovacuum パラメータのアダプティブチューニングをオフにする場合は、rds.adaptive_autovacuum パラメータを 0 または OFF に設定します。

トランザクション ID の循環は、RDS で autovacuum パラメータをチューニングした後でも発生する場合があります。トランザクション ID の循環に対して Amazon CloudWatch アラームを実装することをお勧めします。詳細については、ブログ記事「Amazon RDS for PostgreSQL でトランザクション ID の循環の早期警告システムを実装する」を参照してください。

autovacuum パラメータのアダプティブチューニングを有効にすると、CloudWatch のメトリクス MaximumUsedTransactionIDs が以下の値のいずれか大きいほうに達したときに、RDS で autovacuum パラメータの調整が開始されます。

  • 750,000,000

  • autovacuum パラメータ autovacuum_freeze_max_age

テーブルに引き続きトランザクション ID の循環の傾向がある場合は、RDS で autovacuum のパラメータの調整が続行されます。続行される調整ごとに、循環を避けるために autovacuum に割り当てられる専用のリソースが増えます。RDS は、以下の autovacuum 関連のパラメータを更新します。

これらのパラメータが RDS で変更されるのは、新しい値で autovacuum による排除率が高くなる場合に限られます。パラメータは、DB インスタンスのメモリで変更されます。パラメータグループの値は変更されません。現在のメモリ内の設定を確認するには、PostgreSQL の SHOW SQL コマンドを使用します。

これらの autovacuum パラメータが RDS で変更されるたびに、影響を受ける DB インスタンスでイベントが生成され、AWS マネジメントコンソール (https://console.aws.amazon.com/rds/) および RDS API に表示されます。CloudWatch のメトリクス MaximumUsedTransactionIDs がしきい値より低い値に戻ると、RDS はメモリ内の autovacuum 関連のパラメータをリセットしてパラメータグループで指定されている値に戻し、この変更に応じた別のイベントを生成します。

データベース内のテーブルにバキューム処理が必要かどうかの判別

次のクエリを使用して、データベース内のバキューム処理されていないトランザクションの数を表示できます。データベースの pg_database 行の datfrozenxid 列は、そのデータベースに表示されている正常なトランザクション ID の下限です。この列は、データベース内のテーブルあたりの relfrozenxid 値の最小数です。

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20;

たとえば、前述のクエリの実行結果は以下のようになります。

datname | age mydb | 1771757888 template0 | 1721757888 template1 | 1721757888 rdsadmin | 1694008527 postgres | 1693881061 (5 rows)

データベースのトランザクション ID 数が 20 億に達すると、トランザクション ID (XID) の循環が発生し、データベースは読み取り専用になります。このクエリを使用すると、メトリクスを生成し、1 日数回実行できます。デフォルトでは、autovacuum は保持するトランザクション数が 200,000,000 以下になるように設定されます (autovacuum_freeze_max_age)。

サンプルモニタリング戦略は次のようになります。

  • autovacuum_freeze_max_age の値を 2 億トランザクションに設定します。

  • テーブルのバキューム処理されていないトランザクション数が 5 億に達すると、重大度が低いアラームがトリガーされます。これは無効な値ではありませんが、autovacuum が遅れていることを示している場合があります。

  • テーブルのトランザクション数が 10 億に達した場合は、対処を要するアラームとして扱う必要があります。一般的に、パフォーマンス上の理由から、トランザクション数は autovacuum_freeze_max_age に近い値にしてください。以下の推奨事項を使用して調査することをお勧めします。

  • テーブルのバキューム処理されていないトランザクション数が 15 億に達すると、重大度が高いアラームがトリガーされます。データベースでトランザクション ID をどれだけ速く使用するかによりますが、このアラームは、システムに autovacuum を実行する時間がないことを示している場合があります。この場合は、この問題を早急に解決することをお勧めします。

テーブルがこれらのしきい値を頻繁に超える場合は、autovacuum パラメータをさらに変更する必要があります。デフォルトでは、手動で VACUUM (コストベースの遅延が無効) を使用するほうが、デフォルトの autovacuum を使用するより排除率が高くなりますが、システム全体に与える負担が増えます。

次の構成を推奨します。

  • 最も古いトランザクションの数に気付くことができるように、モニタリングメカニズムに注意して有効にしてください。

    トランザクション ID の循還について警告するプロセスを作成する方法については、AWS Database Blog 記事「Amazon RDS for PostgreSQL のトランザクション ID の循環に早期警告システムを実装」を参照してください。

  • 処理の多いテーブルでは、autovacuum の使用に加えて、メンテナンスウィンドウ中に手動でバキュームフリーズを定期的に実行してください。手動バキュームフリーズの実行については、「 手動バキュームフリーズの実行」を参照してください。

現在 autovacuum の対象となっているテーブルの判別

多くの場合、1 つ以上のテーブルにバキューム処理が必要です。relfrozenxid の値が autovacuum_freeze_max_age のトランザクション数を超えているテーブルは、常に autovacuum の処理対象となります。それ以外の場合、前回の VACUUM 以降「古い」とされたタプルの数が「バキュームしきい値」を超えると、テーブルがバキューム処理されます。

autovacuum しきい値は、次のように定義されます。

Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples

データベースに接続しているときに、次のクエリを実行し、autovacuum がバキューム処理の対象と見なしているテーブルのリストを表示します。

WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold') , vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor') , fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') , sto AS (select opt_oid, split_part(setting, '=', 1) as param, split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt) SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation , pg_size_pretty(pg_table_size(c.oid)) as table_size , age(relfrozenxid) as xid_age , coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age , (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) as autovacuum_vacuum_tuples , n_dead_tup as dead_tuples FROM pg_class c join pg_namespace ns on ns.oid = c.relnamespace join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1) left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid WHERE c.relkind = 'r' and nspname <> 'pg_catalog' and ( age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float) or coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup -- or 1 = 1 ) ORDER BY age(relfrozenxid) DESC LIMIT 50;

Autovacuum が現在実行されているかどうかと実行されている時間の判別

テーブルを手動でバキューム処理する必要がある場合、autovacuum が現在実行されているかどうかを判別する必要があります。実行されている場合、さらに効率的に実行されるようにパラメータを調整するか、VACUUM を手動で実行できるように autovacuum を終了する必要があります。

次のクエリを使用して、autovacuum が実行中か、どのくらいの時間実行中か、また別のセッションの待機中かを判別します。

Amazon RDS PostgreSQL バージョン 9.6+ 以上を使用している場合は、次のクエリを使用します。

SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;

クエリが実行されると、次のような出力が表示されます。

datname | usename | pid | state | wait_event | xact_runtime | query --------+----------+-------+--------+------------+-------------------------+-------------------------------------------------------------------------------------------------------- mydb | rdsadmin | 16473 | active | | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound) mydb | rdsadmin | 22553 | active | | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound) mydb | rdsadmin | 41909 | active | | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3 mydb | rdsadmin | 618 | active | | 00:00:00 | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+ | | | | | | FROM pg_stat_activity + | | | | | | WHERE query like '%VACUUM%' + | | | | | | ORDER BY xact_start; +

Amazon RDS PostgreSQL 9.6 より前で、かつ 9.3.12 以降、9.4.7 以降、または 9.5.2+ のバージョンを使用している場合は、次のクエリを使用します。

SELECT datname, usename, pid, waiting, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;

クエリが実行されると、次のような出力が表示されます。

datname | usename | pid | waiting | xact_runtime | query --------+----------+-------+---------+-------------------------+---------------------------------------------------------------------------------------------- mydb | rdsadmin | 16473 | f | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound) mydb | rdsadmin | 22553 | f | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound) mydb | rdsadmin | 41909 | f | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3 mydb | rdsadmin | 618 | f | 00:00:00 | SELECT datname, usename, pid, waiting, current_timestamp - xact_start AS xact_runtime, query+ | | | | | FROM pg_stat_activity + | | | | | WHERE query like '%VACUUM%' + | | | | | ORDER BY xact_start; +

いくつかの問題が原因で autovacuum セッションの実行が長期間 (複数日) に渡る場合があります。最もよくある問題は、maintenance_work_mem パラメータ値で設定されたテーブルのサイズまたは更新速度が小さすぎることです。

次の計算式を使用して、maintenance_work_mem パラメータ値を設定することをお勧めします。

GREATEST({DBInstanceClassMemory/63963136*1024},65536)

実行時間が短い autovacuum セッションは、以下の問題を示している可能性もあります。

  • ワークロード用の autovacuum_max_workers が十分ではないことを示している場合があります。この場合は、ワーカーの数を指定する必要があります。

  • インデックスの破損を示している場合があります (autovacuum がクラッシュし、同じリレーションで再起動されますが、進展はありません)。この場合は、手動の vacuum freeze verbose ___table___ を実行し、正確な原因を調べます。

手動バキュームフリーズの実行

バキュームプロセスが既に実行されているテーブルで、手動バキュームを実行できます。これは、トランザクション数が 20 億に近づいている (または、モニタリングしているしきい値を上回った) テーブルに気付いた場合に役立ちます。

以下のステップはガイドラインであり、プロセスにはいくつかのバリエーションがあります。たとえば、テスト時に、maintenance_work_mem パラメータの設定値が小さすぎて、テーブルに早急な対処が必要であることに気づいたとします。ただし、今はインスタンスをバウンスしたくない場合があります。前のセクションのクエリを使用することで、問題のあるテーブルを判別し、長時間実行されている autovacuum セッションを確認できます。maintenance_work_mem パラメータ設定の変更が必要であることがわかっていても、該当するテーブルにすぐに対処してバキューム処理を実行する必要があります。このような場合にどうするかを、以下の手順で示します。

バキュームフリーズを手動で実行するには

  1. バキュームを実行するテーブルを含むデータベースへのセッションを 2 つ開きます。2 番目のセッションで、接続が中断された場合にセッションを維持する「screen」または他のユーティリティを使用します。

  2. セッション 1 で、テーブルを実行している autovacuum セッションの PID を取得します。このアクションでは、実行中の rdsadmin プロセスをすべて把握するため、Amazon RDS PostgreSQL 9.3.12 以降、9.4.7 以降、または 9.5.2 以降が実行されている必要があります。

    次のクエリを実行し、autovacuum セッションの PID を取得します。

    SELECT datname, usename, pid, waiting, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;
  3. セッション 2 で、このオペレーションに必要なメモリの量を計算します。この例では、このオペレーションに最大 2GB のメモリを使用できると決めたため、現在のセッションの maintenance_work_mem を 2GB に設定します。

    set maintenance_work_mem='2 GB'; SET
  4. セッション 2 で、テーブルに対して vacuum freeze verbose コマンドを発行します。現在のところ PostgreSQL には進行状況レポートがないため、verbose 設定はアクティビティを確認するのに役立ちます。

    \timing on Timing is on. vacuum freeze verbose pgbench_branches;
    INFO: vacuuming "public.pgbench_branches" INFO: index "pgbench_branches_pkey" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pgbench_branches_test_index" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pgbench_branches": found 0 removable, 50 nonremovable row versions in 43 out of 43 pages DETAIL: 0 dead row versions cannot be removed yet. There were 9347 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Time: 2.765 ms
  5. セッション 1 で、autovacuum でブロックしていた場合、pg_stat_activity に、バキュームセッションの待機が「T」であることを確認します。この場合、次のようにして autovacuum プロセスを終了する必要があります。

    SELECT pg_terminate_backend('the_pid');
  6. この時点で、セッションが開始されます。このテーブルは作業リストの一番上にあると思われるため、autovacuum が即座に再開される点に注意することが重要です。セッション 2 で vacuum freeze verbose コマンドを開始し、セッション 1 で autovacuum プロセスを終了します。

autovacuum の実行中のテーブルのインデックス再作成

インデックスが破損した場合、autovacuum はテーブルの処理を続けますが失敗します。この状況で手動バキュームを試みると、次のようなエラーメッセージが表示されます。

mydb=# vacuum freeze pgbench_branches; ERROR: index "pgbench_branches_test_index" contains unexpected zero page at block 30521 HINT: Please REINDEX it.

インデックスが破損しているときに、autovacuum をテーブルに対して実行しようとすると、既に実行中の autovacuum セッションと競合します。「REINDEX」コマンドを発行する場合は、テーブルに対する排他ロックを取り除きます。書き込みオペレーションがブロックされ、この特定のインデックスを使用する読み取りもブロックされます。

autovacuum がテーブルに対して実行されているときにテーブルのインデックスを再作成するには

  1. バキュームを実行するテーブルを含むデータベースへのセッションを 2 つ開きます。2 番目のセッションで、接続が中断された場合にセッションを維持する「screen」または他のユーティリティを使用します。

  2. セッション 1 で、テーブルを実行している autovacuum セッションの PID を取得します。このアクションでは、実行中の rdsadmin プロセスをすべて把握するため、Amazon RDS PostgreSQL 9.3.12 以降、9.4.7 以降、または 9.5.2 以降が実行されている必要があります。

    次のクエリを実行し、autovacuum セッションの PID を取得します。

    SELECT datname, usename, pid, waiting, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY xact_start;
  3. セッション 2 で、reindex コマンドを発行します。

    \timing on Timing is on. reindex index pgbench_branches_test_index; REINDEX Time: 9.966 ms
  4. セッション 1 で、autovacuum でブロックしていた場合、pg_stat_activity に、バキュームセッションの待機が「T」であることを確認します。この場合、autovacuum プロセスを終了する必要があります。

    select pg_terminate_backend('the_pid');
  5. この時点で、セッションが開始されます。このテーブルは作業リストの一番上にあると思われるため、autovacuum が即座に再開される点に注意することが重要です。セッション 2 でコマンドを開始し、セッション 1 で autovacuum プロセスを終了します。

autovacuum に影響を与える他のパラメータ

次のクエリは、autovacuum とその動作に直接影響を与えるパラメータのいくつかについて値を表示します。autovacuum パラメータの詳細については、PostgreSQL のドキュメントを参照してください。

SELECT name, setting, unit, short_desc FROM pg_settings WHERE name IN ( 'autovacuum_max_workers', 'autovacuum_analyze_scale_factor', 'autovacuum_naptime', 'autovacuum_analyze_threshold', 'autovacuum_analyze_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_cost_delay', 'autovacuum_vacuum_cost_limit', 'vacuum_cost_limit', 'autovacuum_freeze_max_age', 'maintenance_work_mem', 'vacuum_freeze_min_age');

これらはすべて autovacuum に影響を与えますが、最も重要なものは以下のとおりです。

テーブルレベルの Autovacuum パラメータを設定する

autovacuum 関連のストレージパラメータは、テーブルレベルで設定できます。これは、データベース全体の動作を変更するより適切である場合があります。大きなテーブルでは、極端な設定にする必要が生じる場合がありますが、autovacuum がすべてのテーブルに対してそのように動作するわけではありません。

次のクエリは、現在テーブルレベルのオプションが設定されているテーブルを表示します。

SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT null;

これが役立つ可能性がある例として、残りのテーブルよりかなり大きいテーブルがあります。1 個の 300 GB のテーブルと、他の 30 個の 1 GB 未満のテーブルがあるとします。この場合、システム全体の動作を変更しないで、大きなテーブルのいくつかの特定のパラメータを設定できます。

ALTER TABLE mytable set (autovacuum_vacuum_cost_delay=0);

これにより、このテーブルでコストベースの autovacuum 遅延がなくなりますが、システムでのリソース使用量が多くなります。通常は、autovacuum_cost_limit に到達する度に、autovacuum は autovacuum_vacuum_cost_delay によって 一時停止されます。「コストベースのバキューム処理」の詳細については、PostgreSQL のドキュメントを参照してください。

autovacuum のログ記録

デフォルトでは、postgresql.log には autovacuum プロセスに関する情報が含まれていません。PostgreSQL 9.4.5 以降を使用している場合、rds.force_autovacuum_logging_level パラメータを設定することで、autovacuum ワーカーオペレーションから生成された PostgreSQL エラーログの出力を参照できます。指定できる値は disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal,panic です。デフォルト値は disabled です。指定できる他の値では、ログの情報の量が大幅に増える可能性があるためです。

rds.force_autovacuum_logging_level パラメータの値を log に設定し、log_autovacuum_min_duration パラメータを 1,000〜5,000 ミリ秒の値に設定することをお勧めします。この値を 5,000 に設定すると、Amazon RDS は、5 秒を超えるすべてのアクティビティをログに書き込みます。また、アプリケーションのロックによって autovacuum で意図的にテーブルがスキップされた場合は、「バキュームのスキップ」メッセージも表示されます。問題をトラブルシューティングしていてさらに詳細が必要な場合、debug1debug3 などの別のログレベルの値を使用できます。これらのデバッグパラメータを使用するのは短期間にしてください。このような設定では、かなり詳細な内容がエラーログファイルに書き込まれるためです。これらのデバッグ設定の詳細については、 PostgreSQL のドキュメントを参照してください。

注記

PostgreSQL バージョン 9.4.7 以降では、pg_stat_activity 内の autovacuum セッションを表示することを rds_superuser アカウントに許可することで、autovacuum セッションの可視性が向上しています。たとえば、コマンドの実行をブロックしている autovacuum セッション、あるいは手動で発行される vacuum コマンドよりも実行スピードが遅い autovacuum セッションを特定して終了することもできます。

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 拡張機能は、Amazon RDS for PostgreSQL バージョン 9.6.3 以降、およびバージョン 9.5.7 以降の詳細セッションおよびオブジェクトの監査ログを提供します。この拡張機能を使用して、セッション監査またはオブジェクト監査を有効にできます。

セッション監査により、さまざまなソースから監査イベントを記録できます。利用可能な場合は、完全修飾コマンドテキストも含まれます。たとえば、セッション監査を使用すると、pgaudit.log を「READ」に設定することで、データベースに接続するすべての READ ステートメントを記録できます。

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

pgaudit 拡張機能でオブジェクトベースのログ記録を使用するには

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

    CREATE ROLE rds_pgaudit; CREATE ROLE
  2. DB インスタンスに関連付けられているパラメータグループを変更し、pgaudit が含まれている共有事前ロードライブラリを使用して、パラメータ pgaudit.role を設定します。pgaudit.role はロール rds_pgaudit に設定する必要があります。

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

    aws rds modify-db-parameter-group --db-parameter-group-name rds-parameter-group-96 --parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" --parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" --region us-west-2
  3. DB インスタンスを再起動し、パラメータグループへの変更をインスタンスに反映します。以下のコマンドにより 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; CREATE EXTENSION
  6. 次のコマンドを実行して、pgaudit.rolerds_pgaudit に設定されたことを確認します。

    show pgaudit.role; pgaudit.role ------------------ rds_pgaudit

監査ログをテストするには、監査対象として選択した複数のコマンドを実行します。たとえば、次のコマンドを実行します。

CREATE TABLE t1 (id int); CREATE TABLE GRANT SELECT ON t1 TO rds_pgaudit; GRANT 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 クライアントユーティリティを使用してデータベースに接続します。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 オプションはサポートされていません。

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

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

PostGIS の使用

PostGIS は PostgreSQL の拡張機能であり、空間情報の保存と管理に使用します。PostGIS に慣れていない場合は、「PostGIS の概要」で全般的な概要を参照できます。

PostGIS 拡張機能を使用するには、事前にいくつかのセットアップを行う必要があります。必要な作業を次に示します。各ステップの詳細は、このセクションで後述します。

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

  • PostGIS 拡張機能をロードします。

  • rds_superuser ロールに拡張機能の所有権を移転します。

  • rds_superuser ロールにオブジェクトの所有権を移転します。

  • 拡張機能をテストします。

ステップ 1: DB インスタンスの作成で使ったマスターユーザー名を使用して DB インスタンスに接続する

最初に、DB インスタンスの作成で使ったマスターユーザー名を使用して DB インスタンスに接続します。その名前は自動的に rds_superuser ロールに割り当てられます。残りのステップを行うための rds_superuser ロールが必要です。

次の例では、SELECT を使用して現在のユーザーが示されています。この場合、現在のユーザーは、DB インスタンスの作成時に選択したマスターユーザー名になっています。

select current_user; current_user ------------- myawsuser (1 row)

ステップ 2: PostGIS 拡張機能をロードする

CREATE EXTENSION ステートメントを使用して、PostGIS 拡張機能をロードします。また、 拡張機能もロードする必要があります。その後で、\dn psql コマンドを使用して、PostGIS スキーマの所有者を表示します。

create extension postgis; CREATE EXTENSION create extension fuzzystrmatch; CREATE EXTENSION create extension postgis_tiger_geocoder; CREATE EXTENSION create extension postgis_topology; CREATE EXTENSION \dn List of schemas Name | Owner --------------+----------- public | myawsuser tiger | rdsadmin tiger_data | rdsadmin topology | rdsadmin (4 rows)

ステップ 3: rds_superuser ロールに拡張機能の所有権を移転する

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

alter schema tiger owner to rds_superuser; ALTER SCHEMA alter schema tiger_data owner to rds_superuser; ALTER SCHEMA alter schema topology owner to rds_superuser; ALTER SCHEMA \dn List of schemas Name | Owner --------------+--------------- public | myawsuser tiger | rds_superuser tiger_data | rds_superuser topology | rds_superuser (4 rows)

手順 4: rds_superuser ロールにオブジェクトの所有権を移転する

次の関数を使用して、rds_superuser ロールに PostGIS オブジェクトの所有権を移転します。psql プロンプトから次のステートメントを実行して関数を作成します。

CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;

次に、次のクエリを実行して exec 関数を実行すると、ステートメントが実行されてアクセス許可が変更されます。

SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser;') 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;

次の SELECT ステートメントを使用して、tiger をテストします。

select na.address, na.streetname, na.streettypeabbrev, na.zip from normalize_address('1 Devonshire Place, Boston, MA 02109') as na; address | streetname | streettypeabbrev | zip ---------+------------+------------------+------- 1 | Devonshire | Pl | 02109 (1 row)

次の SELECT ステートメントを使用して、topology をテストします。

select topology.createtopology('my_new_topo',26986,0.5); createtopology ---------------- 1 (1 row)

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

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

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

./pgbadger -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)

ビューに直接アクセスしようとすると、リクエストが失敗します。

select * from pg_config; ERROR: permission denied for relation pg_config

orafce 拡張機能を使用する

orafce 拡張機能は、商用データベースの共通機能であるため、商用データベースを PostgreSQL に移行しやすくなります。この拡張機能は Amazon RDS for PostgreSQL バージョン 9.6.6 以降でサポートされています。orafce についての詳細は、「GitHub に関する orafce プロジェクト」を参照してください。

注記

Amazon RDS for PostgreSQL は、orafce 拡張の一部である utl_file パッケージをサポートしていません。これは、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');

アウトバウンドネットワークアクセスでのカスタム DNS サーバーの使用

Amazon RDS for PostgreSQL では、DB インスタンスでのアウトバウンドネットワークアクセスをサポートし、お客様が所有するカスタム DNS サーバーからのドメインネームサービス (DNS) 解決を許可します。Amazon RDS DB インスタンスからカスタム DNS サーバーを介して完全修飾ドメイン名のみを解決することができます。

カスタム DNS 解決の有効化

お客様の VPC で DNS 解決を有効にするには、カスタム DB パラメータグループを RDS PostgreSQL インスタンスに関連付け、パラメータ rds.custom_dns_resolution を 1 に設定してオンにし、DB インスタンスを再起動して変更を反映する必要があります。

カスタム DNS 解決の無効化

お客様の VPC で DNS 解決を無効にするには、カスタム DB パラメータグループのパラメータ rds.custom_dns_resolution を 0 に設定してオフにし、DB インスタンスを再起動して変更を反映する必要があります。

カスタム DNS サーバーのセットアップ

カスタム DNS ネームサーバーを設定後、変更を DB インスタンスに反映させるまで約 30 分ほどかかります。DB インスタンスへの変更が反映されたら、すべてのアウトバウンドネットワークトラフィックのポート 53 の DNS サーバーにおいて DNS ルックアップクエリを行う必要があります。

Amazon RDS PostgreSQL DB インスタンスのカスタム DNS サーバーをセットアップするには、次の手順に従います。

  1. VPC の DHCP オプションで domain-name-servers を DNS ネームサーバーの IP アドレスに設定します。詳細については、「DHCP オプションセット」を参照してください。

    注記

    domain-name-servers オプションが許可する値は 4 つまでになりますが、Amazon RDS DB インスタンスが使用するのは最初の値のみです。

  2. DNS サーバーが、パブリック DNS 名、Amazon EC2 プライベート DNS 名、ユーザー固有の DNS 名を含むすべてのルックアップクエリを解決できることを確認します。DNS サーバーが処理できない DNS ルックアップがアウトバウンドネットワークトラフィックにある場合は、状況に適したアップストリーム DNS プロバイダを必ず設定してください。

  3. 512 バイト以下の User Datagram Protocol (UDP) レスポンスを生成するように DNS サーバーを設定します。

  4. 1024 バイト以下の Transmission Control Protocol (TCP) レスポンスを生成するように DNS サーバーを設定します。

  5. ポート 53 で Amazon RDS DB インスタンスからのインバウンドトラフィックを許可するように DNS サーバーを設定します。DNS サーバーが Amazon VPC にある場合、VPC にはポート 53 で UDP と TCP トラフィックを許可するインバウンドルールを含むセキュリティグループが必要になります。DNS サーバーが Amazon VPC にない場合は、ポート 53 で UDP と TCP インバウンドトラフィックを許可できるように、適切なファイアウォールのホワイトリストが必要になります。

    詳細については、「VPC のセキュリティグループ」と「ルールを追加および削除する」を参照してください。

  6. ポート 53 でアウトバウンドトラフィックを許可するため、Amazon RDS DB インスタンスの VPC を設定します。VPC には、ポート 53 で UDP および TCP トラフィックを許可するアウトバウンドルールを含むセキュリティグループが必要になります。

    詳細については、「VPC のセキュリティグループ」と「ルールを追加および削除する」を参照してください。

  7. Amazon RDS DB インスタンスと DNS サーバー間のルーティングパスは、DNS トラフィックを許可できるよう適切に設定してください。

    Amazon RDS DB インスタンスと DNS サーバーが同じ VPC にない場合は、両者の間でピア接続をセットアップする必要があります。詳細については、「VPC ピア機能とは」を参照してください。

厳密なパスワード管理

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

静的パラメータ 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;

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

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

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