Amazon Web Services ブログ

Amazon RDS および Amazon Aurora for PostgreSQL データベースの一般的な管理者の責任

アマゾン ウェブ サービス (AWS) は、完全マネージド型のリレーショナルデータベースサービスとして、Amazon Relational Database Service (RDS) および Amazon Aurora を提供します。コマンドをいくつか使用するだけで、本稼働データベースのインスタンスを AWS で起動して実行することができます。

オンラインデータベースを使用すれば、データベース管理者 (DBA) は多くのメンテナンスタスクや管理タスクから解放されます。ただし、注意すべき重要な責任がいくつかあります。この記事では、PostgreSQL との互換性のあるデータベースを備えた Amazon RDS for PostgreSQL および Aurora で実行するための DBA タスクについて説明します。

DBA として、お客様はさまざまな分野でビジネスに価値を提供するという日々のプレッシャーに直面しています。ミッションクリティカルなデータベースを実行するための適切なプラットフォームを維持することは、ますます困難になってきています。メンテナンスも、課題の多い作業です。

Amazon RDS と Aurora のリリースにより、インストール、構成、モニタリング、セキュリティなどのタスクに費やす時間は大幅に短縮されました。それでも、複数の重要なタスクを実行する必要はあります。そのいくつかは毎日または週に数回実行し、またいくつかは Amazon RDS または Aurora のインストール時 (インスタンスの作成時) にのみ実行するものです。

実行する必要がある管理タスクには、以下が含まれます。

  • パラメータグループの設定
  • セキュリティ グループを使用した IP トラフィックの管理
  • データベースログファイルの監査
  • メンテナンスおよび管理アクティビティ
  • バックアップおよびリカバリ戦略の計画
  • ユーザー管理
  • データベースのモニタリング

パラメータグループの設定

オンプレミスの PostgreSQL クラスターのデータディレクトリには、設定ファイル postgresql.conf が含まれます。この設定ファイルを使用してパラメーターを管理することができます。同様に、Amazon RDS および Aurora PostgreSQL インスタンスでは、パラメータグループを使用してパラメータを管理します。

新しい Amazon RDS および Aurora インスタンスを作成する前に、DB パラメータグループをカスタマイズします。新しいパラメータグループの作成、パラメータの変更とインスタンスへのアタッチの詳細については、「DB パラメータグループの操作」を参照してください。

作成の時点でカスタマイズされたパラメータグループがない場合は、インスタンスの再起動を実行できます。デフォルトの DB パラメータグループをカスタムパラメータグループに置き換えます。これにより、カスタマイズされたパラメータが有効になります。

次の概要では、最適なパフォーマンスのためにオンにするパラメータについて説明します。

次のログ記録パラメータを入力します。

log_autovacuum_min_duration 0
log_checkpoints '1'
log_connection  '1'
log_disconnection '1'
log_min_duration_statement  ''
log_temp_files  '1'
log_statement='ddl'
rds.force_autovacuum_logging_level='log'

次の自動バキュームパラメータを入力します。

autovacuum_max_workers
autovacuum_vacuum_cost_limit
autovacuum_vacuum_cost_delay

他のパラメータとして以下を入力します。

random_page_cost
default_statistics_target
shared_preload_libraries='pg_hint_plan, pg_stat_statements'

セキュリティ グループを使用した IP トラフィックの管理

Amazon RDS と Aurora では、セキュリティ グループがインスタンスの内外のトラフィックを制御します。セキュリティ グループに適切なルールを適用することで、受信、送信トラフィックの両方を制御します。

たとえば、次のスクリーンショットは、ポート 5432 を介してアプリケーションからデータベースへの PG トラフィックを許可する方法を示しています。

0.0.0.0/0 を使用してデータベースを公開しないでください。

データベースログファイルの監査

データベースがビジーであるほど、トランザクション数が多くなります。トランザクションが多いほど、生成されるログも多くなります。ログファイルの数が多いほど、それらのログファイルから特定の情報を抽出するのが複雑になります。

ほとんどの DBA は最後の手段としてログファイルを確認しますが、それらに含まれる ERROR、FATAL、WARNING、HINTS メッセージについては頻繁に注意を払う必要があります。ログファイルを定期的に確認、監査することが重要です。

サイズが原因でログファイルを毎日分析することが困難になったら、GitHub から入手可能な pgBadger を使用できます。pgBadger は、PostgreSQL ログファイルから HTML レポートを生成するオープンソースの PostgreSQL ログ分析ツールです。

デフォルトでは、RDS および Aurora インスタンスは 3 〜 7 日間ログを保持します。カスタム bash スクリプトを実行してログファイルをローカルや Amazon EC2 インスタンスまたは Amazon S3 バケットにダウンロードすることで、ログファイルをより長期間維持します。

pgBadger レポートをインストールして生成するには、以下の手順を実行します。

  1. AWS マネジメントコンソールにサインインし、EC2 RHEL または CentOS インスタンスを 1 つ作成します。
  2. Amazon EC2 で pgdg リポジトリをダウンロードします。
  3. インストールするには、次のコードを入力します。 
    sudo yum install ftp://ftp.pbone.net/mirror/apt.sw.be/redhat/7.3/en/i386/rpmforge/RPMS/perl-Text-CSV_XS-0.65-1.rh7.rf.i386.rpm perl perl-devel
 
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
 
sudo yum install pgbadger -y

この記事では、pgdg-10 (PostgreSQL リポジトリ) を使用する RHEL 7 で前述の手順をテストしました。

レポートを生成するには、以下の手順を実行します。

  1. PostgreSQL ログファイルを Amazon RDS または Aurora から Amazon EC2 にダウンロードして、pgBadger を実行します。
  2. DB パラメータグループでログ記録パラメータを有効にします。
  3. cron ジョブをスケジューリングしてログファイルを Amazon EC2 の適切な場所にダウンロードし、pgBadger レポートを生成します。
  4. 次のコードでログファイルをダウンロードして変換します。 
    #This Script helps to download the Postgres log files from cloud and store it on EC2.
## 1.Delete the logs and pgBadger reports older than 3 days.
## 2.Download the latest Postgres log from Amazon RDS instance: <instance_name>.
## 3.Generate the pgBadger report for newly downloaded log file.
#create pgBadger dir under /home/ec2-user mkdir -p /home/ec2-user/pgBadger,
# mkdir -p /home/ec2-user/pgBadger/logs , mkdir -p /home/ec2-user/pgBadger/reports
#Use must install pgbadger and it should be in path.
#Here is link for pgbadger installation: https://github.com/darold/pgbadger
#Install awscli on EC2 instance set the env (https://docs.thinkwithwp.com/cli/latest/topic/config-vars.html)
# to download the log files.
 
home_dir="/home/postgres/pgBadger"
logDir="/home/postgres/pgBadger/logs"
rptDir="/var/www/pgbadger"
identifier='<instance_identifier>'
 
date=`date -d "-1 days" +%Y-%m-%d`
 
sudo find $logDir -name '*.log.*' -type f -mtime 0 -exec rm {} \;
sudo find $rptDir -name 'postgresql*.html' -type f -mtime +10 -exec rm {} \;
 
sudo mkdir -p $logDir/$date
sudo chown -R postgres:postgres $logDir/$date
 
#how to generate pgbadger report
#Install pgbadger on EC2 .To install, follow the link: https://github.com/darold/pgbadger
 
for i in `seq -w 00 23`
do
sudo aws rds download-db-log-file-portion --db-instance-identifier $identifier
--log-file-name error/postgresql.log.$date-$i --starting-token 0 --output text
> $logDir/$date/postgresql.log.$date-$i
done
 
if [ $? -eq 0 ] ; then
 
sudo pgbadger --prefix '%t:%r:%u@%d:[%p]:'  $logDir/$date/*.log.* -o $rptDir/postgresql.$date.html
-f stderr   #-f $logDir/*.log.*
sudo chmod -R 777 $rptDir/postgresql.$date.html
 
if [ $? -eq 0 ]; then
#mailx -s "Successfully Generated the pgbadger report for Date: $date"
echo "Successfully Generated the pgbadger report for Date: $date"
else
 #mailx -s "UNSUCESSFUL GENERATION of pgbadger report for Date: $date"
 echo "Successfully Generated the pgbadger report for Date: $date"
fi
gzip -r9 $logDir/$date
fi

    このスクリプトは、データベースで実行されたアクティビティの分析に利用できる pgbadger レポートを生成します。pgBadger レポートのサンプルについては、postgres_sample を参照してください。

メンテナンスおよび管理アクティビティ

リモートデータベースは引き続きメンテナンスが必要です。次のセクションでは、自動バキューム、VACUUM ANALYZE コマンド、長時間実行されるクエリとセッションについて説明します。

自動バキューム

テーブルまたはインデックスの膨張によるクエリの遅延は、PostgreSQL で最も一般的なシナリオの 1 つです。Amazon RDS と Aurora ではデフォルトで自動バキュームを有効にすることで、この膨張を軽減します。スローダウンを管理するときは、以下に注意してください。

  • 自動バキュームは、優先度の低いロックをテーブルに保持します。別の優先度の高い操作がテーブルのロックを取得しようとすると、独自のジョブをキャンセルする場合があります。
  • 同じテーブルが繰り返し自動バキュームの候補になる可能性があり、他のテーブルが膨張したままになります。

これらは PostgreSQL の一般的なシナリオであるため、自動バキュームパラメータを適切に調整する必要があります。調整が機能しない場合は、手動のバキューム/分析スクリプトをスケジューリングする必要があります。膨張の頻度に基づいて、VACUUM ANALYZE、VACUUM FULL、PG_REPACK を実行するかどうかを決定できます。

VACUUM ANALYZE のスケジューリング

統計を最新の状態に保ち、再利用されたスペースの膨張を取り除き、トランザクションのラップアラウンドを回避するには、データベースで VACUUM ANALYZE をスケジューリングします。VACUUM は膨張を取り除き、トランザクションのラップアラウンドを回避します。ANALYZE はデータベースの統計を更新するのに役立ち、これによりプランナーはクエリの適切な計画を生成することができます。

先に進む前に、VACUUM ANALYZE、VACUUM FULL、PG_REPACK の違いを理解する必要があります。

  • VACUUM ANALYZE – テーブルとインデックスから膨張を取り除き、テーブルの統計を更新します。これはロックされない操作です。テーブルレベルまたはデータベースレベルで実行できます。これにより膨張したページを浄化しますが、スペースは再利用しません。
  • VACUUM FULL – テーブルのコンテンツ全体を新しいディスクファイルに書き込み、無駄なスペースを OS に解放します。これによりテーブルレベルのロックが発生し、速度が低下します。高負荷システムでは VACUUM FULL を使用しないでください。
  • PG_REPACK – テーブルの内容全体を新しいディスクファイルに書き込み、無駄なスペースを OS に解放します。テーブルのロックを持続することなくオンラインでこれを実行します。VACUUM FULL よりも高速で、Amazon Aurora と Amazon RDS が拡張機能としてサポートしています。インデックスを再作成したり、VACUUM FULL を実行したりする代わりに、PG_REPACK を使用してバックアップする必要があります。PG_REPACK は、Amazon Aurora for PostgreSQL および Amazon RDS PostgreSQL の拡張機能として利用できます。

次のコードでは、膨張したページが占める肥大化した余分なスペースを計算します。

SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size,
(tblpages-est_tblpages)*bs AS extra_size,
CASE WHEN tblpages - est_tblpages > 0
THEN 100 * (tblpages - est_tblpages)/tblpages::float
ELSE 0
END AS extra_ratio, fillfactor,
CASE WHEN tblpages - est_tblpages_ff > 0
THEN (tblpages-est_tblpages_ff)*bs
ELSE 0
END AS bloat_size,
CASE WHEN tblpages - est_tblpages_ff > 0
THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float
ELSE 0
END AS bloat_ratio, is_na
-- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag
FROM (
SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages,
ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
-- , stattuple.pgstattuple(tblid) AS pst
FROM (
SELECT
( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
- CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
- CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na
FROM (
SELECT
tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
coalesce(toast.reltuples, 0) AS toasttuples,
coalesce(substring(
array_to_string(tbl.reloptions, ' ')
FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
current_setting('block_size')::numeric AS bs,
CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
24 AS page_hdr,
23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END
+ CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,
sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024) ) AS tpl_data_size,
bool_or(att.atttypid = 'pg_catalog.name'::regtype)
OR count(att.attname) <> count(s.attname) AS is_na
FROM pg_attribute AS att
JOIN pg_class AS tbl ON att.attrelid = tbl.oid
JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
WHERE att.attnum > 0 AND NOT att.attisdropped
AND tbl.relkind = 'r'
GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids
ORDER BY 2,3
) AS s
) AS s2
) AS s3;

次のコードを出力として受け取ります。

 current_database |     schemaname     |         tblname         | real_size  | extra_size |   extra_ratio    | fillfactor | bloat_size |   bloat_ratio    | is_na
------------------+--------------------+-------------------------+------------+------------+------------------+------------+------------+------------------+-------
 postgres         | public             | sample_table             | 1565351936 |  239951872 | 15.3289408267611 |        100 |  239951872 | 15.3289408267611 | f

スペースを再利用するには、VACUUM FULL または PG_REPACK を実行します。

Postgres#  vacuum full analyze sample_table;

VACUUM FULL を実行すると、クエリでは次のような出力を返します。

current_database |     schemaname     |         tblname         | real_size | extra_size |    extra_ratio     | fillfactor | bloat_size |    bloat_ratio     | is_na
------------------+--------------------+-------------------------+-----------+------------+--------------------+------------+------------+--------------------+-------
postgres         | public             | sample_table             |  41746432 |      24576 | 0.0588697017268446 |        100 |      24576 | 0.0588697017268446 | f

VACUUM FULL とインデックスの再作成は他のセッションをブロックするロック操作ですが、PG_REPACK は、テーブルとインデックスを再編成するオンラインのメソッドです。pg_stat_all_tables と pg_stat_user_tables をクエリして、最後の自動バキュームまたは手動バキュームの実行を確認できます。

たとえば、次のコードを参照してください。

SELECT schemaname,relname as table_name, last_vacuum, last_analyze, last_autovacuum, last_autoanalyze, n_live_tup,n_dead_tup from pg_stat_user_tables;

次のコードを出力として受け取ります。

schemaname  | table_name  | last_vacuum | last_analyze | last_autovacuum |       last_autoanalyze        | n_live_tup | n_dead_tup
------------+-------------+-------------+--------------+-----------------+-------------------------------+------------+------------
 public     | vacuum_test |             |              |                 | 2019-01-23 06:44:56.257586+00 |   13671089 |          0

このコードを使用することもできます。

SELECT schemaname, relname as table_name, last_vacuum, last_analyze, last_autovacuum, last_autoanalyze, n_live_tup, n_dead_tup from pg_stat_all_tables;

次のコードを出力として受け取ります。

     schemaname     |   table_name   |          last_vacuum          |         last_analyze          |  last_autovacuum   | last_autoanalyze | n_live_tup | n_dead_tup
--------------------+----------------+-------------------------------+-------------------------------+--------------------+------------------+------------+------------
 information_schema | sql_sizing     | 2019-01-23 07:05:06.524004+00 | 2019-01-23 07:05:06.52429+00  |                    |                  |         23 |          0

テーブルで VACUUM ANALYZE を実行するには、次のコードを入力します。

Vacuum analyze <table_name>;

データベースで VACUUM ANALYZE を実行するには、次のコードを入力します。

Vacuum analyze verbose;

システムのテーブルに対してバキュームを実行できるのは、スーパーユーザーまたはデータベース所有者だけです。システムのテーブルの大幅な膨張によりパフォーマンスが低下する場合、または膨張したスペースをディスクに解放する必要がある場合は、VACUUM FULL を実行する必要があります。このコマンドは実行するテーブルをロックするため、業務時間外にのみ実行してください。

データベースのトランザクション経過時間を確認するには、次のコードを入力します。

SELECT datname, age(datfrozenxid) from pg_database order by age(datfrozenxid) desc limit 20;

データベースでトランザクションラップアラウンドの問題を回避するには、次のコードを入力します。

Vacuum freeze;

自動バキュームでもこれらのアクティビティを実行できるため、有効にしておくことを強くお勧めします。Amazon RDS for PostgreSQL では、デフォルトで自動バキュームが有効になっています。

必要に応じて自動バキュームパラメータを調整してください。Amazon RDS では、パラメーター rds.adaptive_autovacuum は、データベースがトランザクション ID のしきい値を超えるたびに自動バキュームパラメーターを自動的に調整するのに役立ちます。

次のコードを入力して、自動バキュームが PostgreSQL バージョン 9.6 以降で実行されているかどうかを確認します。

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;

長時間実行しているクエリとセッション

長時間実行されたクエリまたは別のセッションをブロックしているクエリを停止するには、pg_stat_activity テーブルからクエリの PID を確認します。クエリを強制終了させるには、以下のコマンドを実行します。

接続を切断せずにクエリをキャンセルするには、次のコードを入力します。

SELECT pg_cancel_backend(pid);

接続を終了し、その接続の他のクエリをすべてキャンセルするには、次のコードを入力します。

SELECT pg_terminate_backend(pid);

実行中のクエリをキャンセルするには、常に PG_CANCEL_BACKEND を使用します。クエリがスタックして他のプロセスをロックしている場合は、PG_TERMINATE_BACKEND を使用できます。終了後、接続を確立するためにセッションを再実行する必要がある可能性があります。

バックアップおよびリカバリ戦略の計画

手動のバックアップと復元が必要なオンプレミスデータベースとは異なり、Aurora for PostgreSQL と RDS PostgreSQL インスタンスには、スナップショットを使用してバックアップを自動化する機能が組み込まれています。Amazon RDS または Aurora インスタンスの作成中に、これらを有効にする必要があります。Amazon RDS は、ストレージボリュームスナップショットを作成して、データベースインスタンス全体をバックアップします。

DB スナップショットを作成するときは、バックアップする DB インスタンスを特定し、DB スナップショットに名前を付けて、後でそこから復元できるようにする必要があります。スナップショットの作成にかかる時間は、データベースのサイズに応じて異なります。詳細については、「DB スナップショットからの復元」を参照してください。

ユーザー管理

ユーザー管理は最も重要な管理タスクの 1 つであり、細心の注意を払って実行する必要があります。新しい Amazon RDS PostgreSQL または Aurora for PostgreSQL インスタンスを作成すると、RDS_SUPERUSER ロールが作成されます。これは、一般的な PostgreSQL インスタンスの PostgreSQL ユーザーに似ていますが、いくつかの制限があります。

適切なアクセス許可レベルを設定することで、データベースに接続するユーザーを管理できます。デフォルトの PostgreSQL 環境では、pg_hba.conf ファイルを使用してユーザー接続を管理できますが、Amazon RDS for PostgreSQL では GRANT/REVOKE を使用する必要があります。スキーマレベルまたはテーブルレベルでユーザーにアクセス権や権限を割り当てることもできます。ユーザーに提供する権限の種類を決定できます。詳細については、「PostgreSQL ユーザーとロールの管理」を参照してください。

データベースのモニタリング

モニタリングは、Amazon RDS と AWS ソリューションの信頼性、可用性、パフォーマンスを維持するために不可欠なものです。AWS ソリューションの全部分からモニタリングデータを収集して、マルチポイント障害が発生した場合にデバッグできるようにします。主なタスクの 1 つは、Amazon RDS および Aurora インスタンスのモニタリングの詳細レベルを設定することです。

Amazon Aurora と Amazon RDS では、デフォルトで Amazon CloudWatch と Amazon RDS Performance Insights の 2 つのタイプののモニタリングを提供します。

CloudWatch によるモニタリング

CloudWatch は、Amazon RDS および Aurora PostgreSQL で利用可能な以下のメトリクスを提供します。

  • 高い CPU または RAM 消費
  • ディスク容量の消費
  • ネットワークトラフィック
  • データベース接続
  • IOPS メトリクス
  • 最大使用されたトランザクション ID
  • キューの深さ

詳細については、「Amazon Aurora DB クラスターメトリクスのモニタリング」を参照してください。

CloudWatch には、ハードウェアレベルで Amazon RDS および Aurora インスタンスの状態をモニタリングするための多くのメトリクスがあります。ただし、各メトリクスで Amazon SNS (アラーム) を設定する必要があります。

Performance Insights によるモニタリング

Amazon RDS Performance Insights は、アプリケーションのパフォーマンスに影響を与えることなく、パフォーマンスに合わせてデータベースを調整する軽量なデータ収集方法を採用しています。

Performance Insights では、以下のメトリクスを提供します。

OS メトリクス:

  • CPU 使用率 – Wait, Idle, Steal, Nice
  • ディスク I/O – 読み取り KbPS、書き込み IOsPS
  • ロード平均
  • スワップ – Cached, Free, Total

データベースメトリクス:

  • キャッシュ – ヒットしたブロック、割り当てられたバッファ
  • チェックポイント – 計算されたチェックポイント、バッファチェックポイント、チェックポイントでの書き込みのレイテンシー

詳細については、「Performance Insights for Amazon RDS for PostgreSQL」を参照してください。

まとめ

この記事では、Amazon RDS および Amazon Aurora for PostgreSQL データベースの一般的な管理者の責任をいくつかお伝えしました。これにより、テストおよび本番ワークロードに実装できる基本的なフレームワークを提供しています。また、インスタンスをより良く管理するためのロギングとログ監査についても取り上げています。

この記事についてのご質問やご意見は、コメント欄にお寄せください。

 

著者について

 

John Solomon 氏は、AWS Global Competency Center India のコンサルタントで、オンプレミスから AWS クラウドに移行するお客様と緊密に連携しています。AWS 認定スピーカーで、さまざまなミートアップ、ブレークアウトセッション、オンラインセミナーで講演しています。また、PostgreSQL コミュニティの熱心なメンバーでもあり、PostgreSQL データベースのデータベース管理者として働いています。