OSS Fan ~OSSでLinuxサーバ構築~

作成日: 2017/05/28

OSSでLinuxサーバ構築

PostgreSQL 9.6.3で時刻指定のPITR(Point In Time Recovery)を実行する

トップページOSSでLinuxサーバ構築 > 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の動作検証データ準備

(1) テーブルの作成と初期データ挿入

 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$

(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

(3) 正常なテーブル更新処理

 ベースバックアップ取得後にデータの挿入処理を続けます。 ここまでは処理として何の問題もないと仮定します。

-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=>

(4) 間違ったテーブル更新処理

 ここで必要のない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と呼びます。

(1) PostgreSQLの停止

 データベースを復旧するに当たり、まずはPostgreSQLを停止します。

-bash-4.2$ pg_ctl stop -m fast
サーバ停止処理の完了を待っています....完了
サーバは停止しました

(2) データベースクラスタの退避

 データベースクラスタ内のデータは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

(3) ベースバックアップからのリストア

 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

(4) 最新の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'

(5) リカバリ設定ファイル作成

 データベースクラスタのパス直下に recovery.conf ファイルを作成します。 このファイルに、どのアーカイブファイルを使用してリカバリするか、どの時点(時刻)にリカバリするかを記述します。 今回はデータベースが正常だった2017年5月28日 0時47分00秒に戻したいとします。 アーカイブファイルは /bkup/pgdata1/pg_arch/ ディレクトリに格納されています。

-bash-4.2$ cd /data/pgdata1/ ←データベースクラスタ内に作成
-bash-4.2$ vi recovery.conf ←ファイルを新規作成
ファイル名:/data/pgdata1/recovery.conf
※ファイルを新規作成※
restore_command = 'cp /bkup/pgdata1/pg_arch/%f %p'
recovery_target_time = '2017-05-28 00:47:00 JST' ←リカバリで戻したい時点(日時)を指定

(6) 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".
完了
サーバ起動完了

(7) 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がリネームされる

(8) リカバリ後のデータの確認

 ではデータベースに接続してデータの状態を確認します。 間違って削除してしまった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$

プロフィール

らのっち

損害保険会社のIT企画部に勤務するSEです。OSSを勉強中です。

<所属>
日本PostgreSQLユーザ会とくしまOSS普及協議会


第000414号