PostgreSQL のデータベースメンテナンス

2019/02/26 fukuoka

WebClass が使用している PostgreSQL で、DB の作り直しを行う作業についてご説明します。

この操作はDBサーバを切り替える場合や、DB のエンコーディングの指定を変更する場合、Vacuum 処理の代わりに行うことがあります。

前提

データベースのサイズが数Gバイト程度であれば、一般に公開されている pg_dump 等の利用手順をそのままやってうまくいきます。ですが、１万人等のアカウントで何年も運用していると、データベースサイズは数十GBにもなります。これくらいの規模になると、ダンプファイルの扱いにくさやデータの重要さもさることながら、サービスの重要さも増して保守作業のスケジュールもシビアになってきます。このようなケースを想定したダンプ手順を解説します。

ここでは、骨格となる一通りの流れの説明の後で、状況に応じた別のやり方を説明しています。したがって、この文書を頭から読みながら作業を進めるのではなく、事前に一通り読んで、対象ケースにあった具体的な作業計画をお手元で作っていただいてから作業に取り掛かることを想定しています。

script コマンド等を利用してコンソールアウトプットを記録しながら、手でコンソール操作することを想定し、時刻や結果の確認では極力ピンポイントで画面表示させる方針でコマンドを選んでいます。

データのダンプ

次の操作の流れでダンプすることができます。

# su postgres

$ du -s /var/lib/postgres/data
$ psql webclass
> SELECT count(*) FROM contents_name;
> SELECT count(*) FROM contents_log;
> SELECT count(*) FROM each_answers;

$ time pg_dump webclass > BACKUPDIR/db_dump_YYYYMMDD.sql
$ time pg_dump --column-inserts webclass > BACKUPDIR/db_dump_YYYYMMDD-D.sql
$ ls -l
$ psql
> ALTER DATABASE webclass RENAME TO old-webclass;

$ exit
# systemctl stop postgresql

最初に postgres のデータ保存ディレクトリの消費サイズを確認することで、取り扱うデータベースのサイズを確認しておきます。作業領域の空きや、転送に必要な時間などを見積もるのに役立ちます。続けて、いくつかのテーブルでレコード数を数えておきます。

time コマンドでダンプの所用時間を計測しているのは、見積もりの精度改善と、のちに同じ作業を行う時の作業時間の見積もりに使うためです。

ここでは念のため、 --column-inserts オプションを付けたタイプのダンプも取得しています。想定としては文字化けなどにより特定のレコードだけINSERTできないときなどで使用するダンプです。ただし、このダンプには通常よりも時間がかかり、出力量も増えます。もともとのDBサイズが大きいと、この予備は現実的に意味がない場合があります。なお、レストア時に文字コードで引っかかるようなケースは、最近はUTF8で統一して扱っており、起きていません。

また、DBへの更新を止めるためにダンプ後に postgresql のサービスを止めてしまっています。Webサービス側で操作を止めるなどして対策されていれば、必ずしも必要ではありません。

ダンプの最後に dump complete の文字が出れば最後までダンプが完了しています。

$ tail db_dump_YYYYMMDD.sql
....

--
-- PostgreSQL database dump complete
--

出力の圧縮

出力サイズがあまりにも大きいときは gz コマンドにパイプして圧縮ファイルとして保存します。例えばダンプデータが14GB程度になる場合、gz圧縮をして保存することで 3GB程度に抑えられるケースもあります。バックアップデータの保存と転送で取り扱い負担が軽くなるメリットがあります。

$ time pg_dump webclass | gzip -c > BACKUPDIR/db_dump_YYMMDD.sql.gz

一方で、このようなケースでは扱うデータサイズが大きい故に、この方法には以下の注意点があります。

ダンプが正常に完了したかどうか、上記のように最終行を目で見て確認しにくくなります。特にスクリプトで自動処理する場合は、ファイルが生成されたかどうかだけでなく、コマンドの終了コードなどで確認しておく必要があります。
展開後サイズを把握できていないと、レストア作業の際に作業スペースの残り容量が足りなくて二度手間になるなどの扱いにくさを伴うことがあります。

注意点１に関して、圧縮出力したダンプファイルでdump complete の行を確認するには以下のコマンドを使用することができます。ファイルサイズが大きい場合は出力を得るまでにしばらく時間がかかります。処理にCPUやメモリを消費しますが、メモリを食いつぶすようなことは普通は起きないです。

$ time gzip -cd BACKUPDIR/db_dump_YYMMDD.sql.gz | tail

レストア

DB を作成するコマンドは以下のようになります。

# systemctl start postgresql
# su - postgres

$ createdb -E UTF8 --lc-ctype=ja_JP.UTF-8 --lc-collate=ja_JP.UTF-8 -T template0 webclass
$ time psql webclass < BACKUPDIR/db_dump_YYYYMMDD-D.sql

$ psql webclass
> SELECT count(*) FROM contents_name;
> SELECT count(*) FROM contents_log;
> SELECT count(*) FROM each_answers;

ここでは、ダンプデータをレストアするところで時間を計測しています。処理中には標準出力も多数出るので、エラー出力をファイル等にリダイレクトしておくと確認しやすいです。

レストアが終わったら、事前に数えておいたレコード数とあっているか確認します。WebClassから接続して動作確認もできます。

確認出来たら、古いデータベースを削除します。

$ psql
> DROP DATABASE old-webclass;

$ du -s /var/lib/postgres/data // 消費ファイルサイズを確認、記録

最初に記録した消費ファイルサイズと比べると、保存サイズの削減効果が確認できます。

ダンプファイルを圧縮していた場合

gzip で圧縮していた場合は、一度解凍してから取り込むこともできますが、以下のコマンドも使えます。

$ time gzip -cd BACKUP/db_dump_YYMMDD.sql.gz | psql webclass

通常は CPU よりも postgres がディスクに書き込むほうがボトルネックになります。この方法ではディスクから読み出すデータサイズが減りますので、gzip でCPUを消費したとしても、全部展開してから psql コマンドに流し込むより短時間で終わることが多いです。

参考

Vacuumについて

PostgreSQL は追記型DBと呼ばれ、レコードの書き換えなどの処理を行うごとに保存ファイルサイズは増えていきます。あまり増えすぎるとパフォーマンスを落としてしまいます。

AutoVacuum 機能によって定期的に掃除する処理が動いていますが、普段からチューニングが行き届かないと、クリーンに保つことは難しいです。完全な Vacuum を行うための FullVacuum の処理が用意されていますが、この処理を実行している間は PostgreSQL は書き込み不可能となり、また処理を途中でとめることが難しいので決まった時間内での作業では注意が必要です。

ダンプ＆レストアの操作では、バックアップの作成ができ、かつ元のデータベースを退避しておくことですぐに戻すことができます。

コマンドリファレンス

pg_dump( postgresql 9.2)