AWS ドキュメント » Amazon Relational Database Service (RDS) » ユーザーガイド » Amazon RDS 上の PostgreSQL » PostgreSQL の一般的な DBA タスク

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_backend と pg_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_connections、shared_buffers、effective_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 データを処理しているときにクエリをキャンセルするまでの最大遅延間隔を設定します。
quote_all_identifiers	動的	SQL フラグメントを生成するときに、すべての識別子に引用符 (") を追加します。
random_page_cost	動的	非連続的に取得されたディスクページのコストに対するプランナーの見積もりを設定します。
rds.log_retention_period	動的	Amazon RDS によって、N 分よりも古い PostgreSQL ログが削除されます。
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 は、挿入、更新、削除されたタプルが多数あるテーブルを確認するため、autovacuum を使用して、トランザクション ID の循環を防ぐことができます。autovacuum は、VACUUM および ANALYZE コマンドの実行を自動化します。autovacuum の使用は、PostgreSQL が必要とするもので、Amazon RDS では必須ではありませんが、良い性能を出すためには重要な要素です。この機能は、すべての新しい Amazon RDS PostgreSQL DB インスタンスで、デフォルトで有効になり、関連する設定パラメータがデフォルトで適切に設定されます。デフォルトはいくらか汎用的であるため、パラメータを特定のワークロードに調整すると恩恵を受けることができます。このセクションでは、autovacuum の必要な調整を実行するのに役立ちます。

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

トピック

• メンテナンス作業メモリ
• データベース内のテーブルにバキューム処理が必要かどうかの判別
• 現在 autovacuum の対象となっているテーブルの判別
• Autovacuum が現在実行されているかどうかと実行されている時間の判別
• 手動バキュームフリーズの実行
• 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 ギガバイトの値に設定します。非常に大きいホストの場合、パラメータを 2〜4 ギガバイトの値に設定します。このパラメータに設定する値は、ワークロードによって異なります。Amazon RDS は、このパラメータのデフォルトが GREATEST({DBInstanceClassMemory/63963136*1024},65536) となるように更新しました。

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

PostgreSQL データベースは、PostgreSQL がデータの損失を避ける大規模なアクションを実行する前に、「処理中」の未バキュームトランザクションを 20 億個保持することができます。未バキュームトランザクションの数が (2^31 - 10,000,000) に到達した場合、バキューム処理が必要であるという警告がログで開始されます。未バキュームトランザクションの数が (2^31 - 1,000,000) に到達した場合、PostgreSQL はデータベースを読み取り専用に設定し、オフラインの単一ユーザー、スタンドアロンバキュームを要求します。これには、数時間または数日 (サイズによって異なる) のダウンタイムが必要です。PostgreSQL のドキュメントでは、TransactionID の循環についてかなり詳しく説明されています。

次のクエリを使用して、データベース内の未バキュームトランザクションの数を表示できます。database の pg_database 行の datfrozenxid 列は、そのデータベースに表示される通常の XID の下限です。これは、データベース内のテーブルあたりの 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)  

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

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

• Autovacuum_freeze_max_age は 2 億に設定されます。

• テーブルの未バキュームトランザクション数が 5 億に到達した場合、緊急度が低いアラームがトリガーされます。これは不合理な値ではありませんが、autovacuum が保持されていないことを示している可能性があります。

• テーブルのトランザクション数が 10 億に達した場合、アクション可能なアラームとして扱う必要があります。通常は、パフォーマンス上の理由からトランザクション数を autovacuum_freeze_max_age に近い値にしてください。次のステップを使用した調査をお勧めします。

• テーブルの未バキュームトランザクション数が 15 億に到達した場合、緊急度が高いアラームがトリガーされます。データベースが XID を使用するスピードによっては、このアラームはシステムに autovacuum を実行する時間がないことを示している可能性があるため、すぐに解決することを検討してください。

テーブルがこれらのしきい値を頻繁に超える場合、さらに autovacuum パラメータを変更する必要があります。デフォルトでは、VACUUM (コストベースの遅延を無効にします) の方がデフォルトの autovacuum より積極的ですが、システム全体に大きい負担をかけます。

推奨事項は次のとおりです。

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

• 処理の多いテーブルでは、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___ を実行し、正確な原因を調べる必要があります。

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

バキュームプロセスが既に実行されているテーブルで、手動バキュームを実行できます。これは、「XID のトランザクション数」が 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 でコマンドを開始し、セッション 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 に影響を与えますが、最も重要なものは以下のとおりです。

• Maintenance_Work_mem

• Autovacuum_freeze_max_age

• Autovacuum_max_workers

• Autovacuum_vacuum_cost_delay

• Autovacuum_vacuum_cost_limit

テーブルレベルパラメータ

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

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

select relname, reloptions
from pg_class
where reloptions is not null;

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

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 パラメータを 1000〜5000 の値に設定することをお勧めします。この値を 5000 に設定した場合、Amazon RDS はアクティビティをログに書き込み (5 秒以上かかります)、アプリケーションがロックされたために autovacuum がテーブルを意図的にスキップすると「vacuum skipped」メッセージを表示します。問題をトラブルシューティングしていてさらに詳細が必要な場合、debug1 や debug3 などの別のログレベルの値を使用できます。これらのデバッグパラメータを使用するのは短期間にしてください。このような設定では、かなり詳細な内容がエラーログファイルに書き込まれるためです。これらのデバッグ設定の詳細については、 PostgreSQL のドキュメントを参照してください。

注意: PostgreSQL バージョン 9.4.7 以降では、rds_superuser アカウントが pg_stat_activity 内の autovacuum セッションを表示できるようにしたことで、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 の値に設定します。この値を 5000 に設定すると、5 秒より長くかかったアクティビティがログへ書き込まれ、「vacuum skipped」メッセージが表示されます。このパラメータの詳細については、「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.role が rds_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 解決の有効化
• カスタム DNS 解決の無効化
• カスタム 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 属性を持つロールが必要です。