PostgreSQL 9.6.3で時刻指定のPITR(Point In Time Recovery)を実行する
目次
概要
PostgreSQL 9.6.3で構築したデータベースを運用している際に、 データベースとしては正常に稼働し続けている状態だが、 操作ミスなどによってデータの更新処理を間違ってしまうようなケースがあります。 例えば必要なテーブルを丸ごと削除してしまったり、 削除する対象を間違って必要なデータを削除してしまったりなど誰にでも経験があると思います。 PostgreSQLにはPoint In Time Recoveryと呼ばれる特定の時点の状態にデータベースを復旧する機能があります。 この機能を利用すると、間違ってデータを消してしまう前の状態にデータベースを復旧することができます。 今回は簡単な例を用いてリカバリ処理の流れを追ってみます。
構成
検証環境
PITRで利用するデータの格納先を以下に示します。
| データベースクラスタ | /data/pgdata1/ |
|---|---|
| データベースクラスタの退避先 | /data/pgdata1.bk20170528/ |
| WALファイル | /data/pgdata1/pg_xlog/ |
| アーカイブファイル | /bkup/pgdata1/pg_arch/ |
| ベースバックアップ | /bkup/20170528_pgdata1_backup/ |
今回の操作の時系列を以下にまとめます。
2017-05-27 22:30:xx テーブルの作成
2017-05-27 22:41:32 1件目のデータ挿入
2017-05-27 23:01:43 2件目のデータ挿入
2017-05-27 23:21:50 3件目のデータ挿入
2017-05-28 00:05:xx ベースバックアップの取得（pg_basebackupの実行）
2017-05-28 00:18:17 4件目のデータ挿入
2017-05-28 00:30:51 5件目のデータ挿入
2017-05-28 00:35:22 6件目のデータ挿入
2017-05-28 00:42:02 7件目のデータ挿入
2017-05-28 00:45:51 8件目のデータ挿入
★----------ここの状態に戻したい----------★
2017-05-28 00:50:37 9件目のデータ挿入 ★誤操作★
2017-05-28 00:51:xx 1件目のデータ削除 ★誤操作★
2017-05-28 01:17:xx PostgreSQLを停止し、リカバリ作業開始
サーバ構成
OSバージョン
CentOS 7.3.1611 x86_64
ソフトウェア・パッケージ一覧
- postgresql96-9.6.3-1PGDG.rhel7.x86_64.rpm
- postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64.rpm
- postgresql96-server-9.6.3-1PGDG.rhel7.x86_64.rpm
手順
PITRの動作検証データ準備
テーブルの作成と初期データ挿入
PITRの動作検証用のテーブル（テーブル名：testtbl）を1つ作成し、初期データを3件挿入します。 リカバリポイントが分かりやすいように、テーブルにはデータを挿入したタイムスタンプを格納するカラムを持たせます。
-bash-4.2$ psql -U pguser01 testdb ←pguser01でtestdbへ接続 ユーザ pguser01 のパスワード: ←データベースユーザのパスワードを入力 psql (9.6.3) "help" でヘルプを表示します. testdb=> \dt リレーションがありません。 ←まだテーブルが存在しない testdb=> create table testtbl ( ←testtblテーブルを作成 testdb(> id integer primary key, testdb(> last_update timestamp testdb(> ); CREATE TABLE testdb=> \dt リレーションの一覧 スキーマ | 名前 | 型 | 所有者 ----------+---------+----------+---------- pguser01 | testtbl | テーブル | pguser01 ←テーブルが作成された (1 行) testdb=> insert into testtbl values (1, now()); ←1件目のデータ挿入 INSERT 0 1 testdb=> insert into testtbl values (2, now()); ←2件目のデータ挿入 INSERT 0 1 testdb=> insert into testtbl values (3, now()); ←3件目のデータ挿入 INSERT 0 1 testdb=> select * from testtbl; ←テーブルに格納されたデータの確認 id | last_update ----+---------------------------- 1 | 2017-05-27 22:41:32.648895 2 | 2017-05-27 23:01:43.841115 3 | 2017-05-27 23:21:50.680096 (3 行) testdb=> \q -bash-4.2$
ベースバックアップの取得
動作検証用のテーブルにデータが3件入っている状態で、 データベースクラスタのベースバックアップを取得します。 ベースバックアップを実行するときに、PostgreSQLを停止する必要はありません。 ベースバックアップはpostgresユーザで実行し、 /bkup/20170528_pgdata1_backup という名前でバックアップします。 バックアップされるデータは、データベースクラスタ（今回の環境では PGDATA=/data/pgdata1）内の ファイルから、リカバリに不要なものが除外されたファイルとなります。
-bash-4.2$ pg_basebackup -U postgres -D /bkup/20170528_pgdata1_backup --xlog --checkpoint=fast --progress
46755/46755 kB (100%), 1/1 テーブル空間
-bash-4.2$ ls -l /bkup/
合計 4
drwx------ 20 postgres postgres 4096 5月 28 00:05 20170528_pgdata1_backup ←データベースクラスタのベースバックアップ
drwx------ 3 postgres postgres 21 5月 24 00:29 pgdata1
正常なテーブル更新処理
ベースバックアップ取得後にデータの挿入処理を続けます。 ここまでは処理として何の問題もないと仮定します。
-bash-4.2$ psql -U pguser01 testdb ユーザ pguser01 のパスワード: ←データベースユーザのパスワードを入力 psql (9.6.3) "help" でヘルプを表示します. testdb=> select * from testtbl; id | last_update ----+---------------------------- 1 | 2017-05-27 22:41:32.648895 2 | 2017-05-27 23:01:43.841115 3 | 2017-05-27 23:21:50.680096 (3 行) testdb=> insert into testtbl values (4, now()); ←4件目のデータ挿入 INSERT 0 1 testdb=> insert into testtbl values (5, now()); ←5件目のデータ挿入 INSERT 0 1 testdb=> insert into testtbl values (6, now()); ←6件目のデータ挿入 INSERT 0 1 testdb=> insert into testtbl values (7, now()); ←7件目のデータ挿入 INSERT 0 1 testdb=> insert into testtbl values (8, now()); ←8件目のデータ挿入 INSERT 0 1 testdb=> select * from testtbl; id | last_update ----+---------------------------- 1 | 2017-05-27 22:41:32.648895 2 | 2017-05-27 23:01:43.841115 3 | 2017-05-27 23:21:50.680096 4 | 2017-05-28 00:18:17.79352 5 | 2017-05-28 00:30:51.038546 6 | 2017-05-28 00:35:22.722102 7 | 2017-05-28 00:42:02.108531 8 | 2017-05-28 00:45:51.250507 ←正常に8件目までのデータが挿入された (8 行) testdb=>
間違ったテーブル更新処理
ここで必要のない9件目のデータをウッカリ挿入してしまい、 これを削除しようとしたが、間違って1件目のデータを削除してしまったとします。
testdb=> insert into testtbl values (9, now()); ←間違って9件目のデータ挿入 INSERT 0 1 testdb=> select * from testtbl; id | last_update ----+---------------------------- 1 | 2017-05-27 22:41:32.648895 2 | 2017-05-27 23:01:43.841115 3 | 2017-05-27 23:21:50.680096 4 | 2017-05-28 00:18:17.79352 5 | 2017-05-28 00:30:51.038546 6 | 2017-05-28 00:35:22.722102 7 | 2017-05-28 00:42:02.108531 8 | 2017-05-28 00:45:51.250507 9 | 2017-05-28 00:50:37.47484 ←不要なデータをウッカリ挿入してしまった (9 行) testdb=> delete from testtbl where id = 1; ←9件目のデータではなく1件目のデータを間違って削除 DELETE 1 testdb=> select * from testtbl; id | last_update ----+---------------------------- 2 | 2017-05-27 23:01:43.841115 ←1件目のデータが消えてしまった 3 | 2017-05-27 23:21:50.680096 4 | 2017-05-28 00:18:17.79352 5 | 2017-05-28 00:30:51.038546 6 | 2017-05-28 00:35:22.722102 7 | 2017-05-28 00:42:02.108531 8 | 2017-05-28 00:45:51.250507 9 | 2017-05-28 00:50:37.47484 ←不要な9件目のデータは削除できていない (8 行) testdb=> \q -bash-4.2$
この状態からPITRでリカバリを行い、データベースを正しい状態に戻します。
データベースのリカバリ
これからデータベースをデータが正しい時の状態にリカバリします。 使用するのは、ベースバックアップ、最新のWALファイル、最新のアーカイブファイル、リカバリ設定ファイル（recovery.conf）の4種類です。 これらファイルを使用してデータベースが正常だった時刻（2017年5月28日 0時47分00秒）の状態に戻します。 このようにデータベースを任意の時点の状態にリカバリすることをPoint In Time Recoveryと呼びます。
PostgreSQLの停止
データベースを復旧するに当たり、まずはPostgreSQLを停止します。
-bash-4.2$ pg_ctl stop -m fast サーバ停止処理の完了を待っています....完了 サーバは停止しました
データベースクラスタの退避
データベースクラスタ内のデータはWALファイル（/data/pgdata1/pg_xlog/）以外はもう使用しません。 ただ、念のためデータベースクラスタ全体をリネームして退避しておきます。 これはリカバリが成功してから消せば良いです。（本手順では消さずに残していますが）
-bash-4.2$ cd /data/
-bash-4.2$ ls -l
合計 4
drwx------ 20 postgres postgres 4096 5月 28 01:17 pgdata1
-bash-4.2$ mv pgdata1 pgdata1.bk20170528 ←リネームして退避
-bash-4.2$ ls -l
合計 4
drwx------ 20 postgres postgres 4096 5月 28 01:17 pgdata1.bk20170528
ベースバックアップからのリストア
pg_basebackupコマンドで取得したベースバックアップをリストアします。 リストアといってもベースバックアップのデータをデータベースクラスタのパス（/data/pgdata1/）にコピーするだけです。
-bash-4.2$ cp -pivr /bkup/20170528_pgdata1_backup /data/pgdata1 `/bkup/20170528_pgdata1_backup' -> `/data/pgdata1' `/bkup/20170528_pgdata1_backup/backup_label' -> `/data/pgdata1/backup_label' `/bkup/20170528_pgdata1_backup/pg_xlog' -> `/data/pgdata1/pg_xlog' `/bkup/20170528_pgdata1_backup/pg_xlog/archive_status' -> `/data/pgdata1/pg_xlog/archive_status' `/bkup/20170528_pgdata1_backup/pg_xlog/archive_status/00000001000000000000000E.done' -> `/data/pgdata1/pg_xlog/archive_status/00000001000000000000000E.done' （中略） `/bkup/20170528_pgdata1_backup/pg_log/postgresql-20170524.log' -> `/data/pgdata1/pg_log/postgresql-20170524.log' `/bkup/20170528_pgdata1_backup/pg_log/postgresql-20170525.log' -> `/data/pgdata1/pg_log/postgresql-20170525.log' `/bkup/20170528_pgdata1_backup/pg_log/postgresql-20170527.log' -> `/data/pgdata1/pg_log/postgresql-20170527.log' `/bkup/20170528_pgdata1_backup/pg_hba.conf.bk20170525' -> `/data/pgdata1/pg_hba.conf.bk20170525' `/bkup/20170528_pgdata1_backup/pg_hba.conf' -> `/data/pgdata1/pg_hba.conf' -bash-4.2$ ls -l 合計 8 drwx------ 20 postgres postgres 4096 5月 28 00:05 pgdata1 ←リストア後のデータベースクラスタ drwx------ 20 postgres postgres 4096 5月 28 01:17 pgdata1.bk20170528
最新のWALファイルを格納
ベースバックアップのファイルを戻しただけだと、ベースバックアップを取得した時点にしかリカバリできないため、 最新のWALファイルと最新のアーカイブファイルを使用してロールフォワード（データの更新ログを元にデータベースを再更新）します。
ベースバックアップを取得した時点のWALファイルは使用しないためフォルダごと削除してしまいます。 そして先ほど退避しておいたデータベースクラスタからWALファイルだけを取り出してデータベースクラスタにコピーします。
-bash-4.2$ cd /data/pgdata1/ ←ベースバックアップからリストアしたデータベースクラスタのパスへ移動 -bash-4.2$ rm -rf pg_xlog ←ベースバックアップ時点のWALファイルを削除 -bash-4.2$ cp -pivr /data/pgdata1.bk20170528/pg_xlog . ←退避しておいた最新のWALファイルをコピー `/data/pgdata1.bk20170528/pg_xlog' -> `./pg_xlog' `/data/pgdata1.bk20170528/pg_xlog/archive_status' -> `./pg_xlog/archive_status' `/data/pgdata1.bk20170528/pg_xlog/archive_status/00000001000000000000000E.00000028.backup.done' -> `./pg_xlog/archive_status/00000001000000000000000E.00000028.backup.done' `/data/pgdata1.bk20170528/pg_xlog/archive_status/00000001000000000000000F.done' -> `./pg_xlog/archive_status/00000001000000000000000F.done' `/data/pgdata1.bk20170528/pg_xlog/00000001000000000000000F' -> `./pg_xlog/00000001000000000000000F' `/data/pgdata1.bk20170528/pg_xlog/00000001000000000000000E.00000028.backup' -> `./pg_xlog/00000001000000000000000E.00000028.backup' `/data/pgdata1.bk20170528/pg_xlog/000000010000000000000010' -> `./pg_xlog/000000010000000000000010' `/data/pgdata1.bk20170528/pg_xlog/000000010000000000000011' -> `./pg_xlog/000000010000000000000011'
リカバリ設定ファイル作成
データベースクラスタのパス直下に recovery.conf ファイルを作成します。 このファイルに、どのアーカイブファイルを使用してリカバリするか、どの時点（時刻）にリカバリするかを記述します。 今回はデータベースが正常だった2017年5月28日 0時47分00秒に戻したいとします。 アーカイブファイルは /bkup/pgdata1/pg_arch/ ディレクトリに格納されています。
-bash-4.2$ cd /data/pgdata1/ ←データベースクラスタ内に作成 -bash-4.2$ vi recovery.conf ←ファイルを新規作成
※ファイルを新規作成※ restore_command = 'cp /bkup/pgdata1/pg_arch/%f %p' recovery_target_time = '2017-05-28 00:47:00 JST' ←リカバリで戻したい時点(日時)を指定
PostgreSQLの起動
ここまででPITRに必要な情報が揃ったため、PostgreSQLを起動します。 起動時にrecovery.confを読み込んでPITRが実行されます。
-bash-4.2$ pg_ctl start -w サーバの起動完了を待っています....2017-05-28 02:23:25 JST [3027] LOG: redirecting log output to logging collector process 2017-05-28 02:23:25 JST [3027] HINT: Future log output will appear in directory "pg_log". 完了 サーバ起動完了
PITRでのリカバリ完了の確認
先ほど作成した recovery.conf ファイルは、リカバリが正常に終了すると recovery.done に自動でリネームされます。 そのため、これ以降PostgreSQLを再起動しても、間違ってまたリカバリが実行されることはありません。
-bash-4.2$ cd /data/pgdata1/
-bash-4.2$ ls -l recovery.*
-rw-r--r-- 1 postgres postgres 100 5月 28 02:20 recovery.done ←recovery.confがリネームされる
リカバリ後のデータの確認
ではデータベースに接続してデータの状態を確認します。 間違って削除してしまった1件目のデータも復活していますし、 間違って挿入してしまった9件目のデータも削除されていることが分かります。 ここで行われた動作は、ベースバックアップの状態（1～3件目のデータが挿入された状態）を元に、 アーカイブファイルとWALファイルに書かれた更新情報を使用して、4～8件目のデータ挿入処理を実行しています。 間違って実行してしまった9件目のデータ挿入と1件目のデータ削除は実行されなかったため、 データベースが正しい状態になっています。
-bash-4.2$ psql -U pguser01 testdb
ユーザ pguser01 のパスワード: ←データベースユーザの
psql (9.6.3)
"help" でヘルプを表示します.
testdb=> select * from testtbl;
id | last_update
----+----------------------------
1 | 2017-05-27 22:41:32.648895
2 | 2017-05-27 23:01:43.841115
3 | 2017-05-27 23:21:50.680096
4 | 2017-05-28 00:18:17.79352
5 | 2017-05-28 00:30:51.038546
6 | 2017-05-28 00:35:22.722102
7 | 2017-05-28 00:42:02.108531
8 | 2017-05-28 00:45:51.250507
(8 行)
testdb=> \q
-bash-4.2$