PostgreSQL 13 PITR(Point In Time Recovery)タイムライン操作

PostgreSQLのタイムラインIDを意識したPITR(Point In Time Recovery)の操作についてまとめます。PostgreSQLは、レプリケーション環境においてスタンバイからマスターへ昇格した時やPITRによる復旧を行なった時にタイムラインIDが1つ大きくなります。このタイムラインを意識した復旧操作についてまとめます。

• PostgreSQL インストール
• PostgreSQL データベースの作成
• PostgreSQL contribモジュールの使い方
• PostgreSQL パラメタの設定方法
• PostgreSQL ベンチマークツールの紹介
• PostgreSQL WAL(Write Ahead Log)の基本説明
• PostgreSQL バックアップとリストア
• PostgreSQL PITR(Point In Time Recovery)基本概念の説明
• PostgreSQL PITR(Point In Time Recovery)操作方法の説明
• PostgreSQL PITR(Point In Time Recovery)タイムライン操作 (いまここ)
• PostgreSQL ストリーミングレプリケーションの最小構成
• PostgreSQL ストリーミングレプリケーションのパラメタ説明
• PostgreSQL ストリーミングレプリケーションの障害復旧
• PostgreSQL ストリーミングレプリケーションの多段構成
• PostgreSQL ストリーミングレプリケーションのDR向け設定
• PostgreSQL ストリーミングレプリケーションのコンフリクト
• PostgreSQL 自動バキューム(AUTO VACUUM)
• PostgreSQL HOT(Heap Only Tuple)
• PostgreSQL インデックスのメンテナンス
• PostgreSQL 統計情報の更新
• PostgreSQL 実行計画
• PostgreSQL スロークエリの調査方法

検証シナリオ

以下のような操作シナリオを考えます。「太郎、次郎、三郎、四郎」の順にINSERTする事を想定していましたが、操作ミスをしてしまい、それを復旧させるシナリオを考えます。

タイムライン1

誤って「太郎、次郎、四郎」の順にINSERTしてしまいました。

タイムライン2

本来「次郎」まで遡らなければならないところを誤って、「太郎」まで遡ってしまいました。操作ミスに気づかず、その後、「三郎、四郎」をINSERTしてしまいます。

タイムライン3

操作ミスに気付き、タイムライン1の「次郎」まで遡ります。その後、「三郎、四郎」をINSERTし、復旧に成功させます。

事前準備

アーカイビング

アーカイブの出力先としてNFSサーバを構築し、そこにWAL出力先用途のディレクトリを作成します。

NFSサーバの構築方法については説明を省略します。

mkdir -p /nfs/postgres/wal
chown -R postgres:postgres /nfs/postgres

postgresql.confを以下のように編集します。

# vi /var/lib/pgsql/13/data/postgresql.conf 

 <omitted>

archive_mode = on
archive_command = 'cp -p %p /nfs/postgres/wal/%f'
archive_timeout = 60

設定を反映させます。

systemctl restart postgresql-13.service

テストデータの投入

「PostgreSQLのチュートリアル」から、DVDレンタルショップのサンプルデータベースをインポートします。

createdb dvdrental
wget https://sp.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
unzip dvdrental.zip
createdb dvdrental
pg_restore -d dvdrental dvdrental.tar

バックアップの取得

バックアップを取得します。

rm -rf /nfs/postgres/backup
pg_basebackup -D /nfs/postgres/backup -F t -X n -z

復旧操作例

タイムライン1

誤って「太郎、次郎、四郎」の順にINSERTしてしまいました。

psql dvdrental << EOF
INSERT INTO actor (first_name, last_name) VALUES ('taro','yamada');
INSERT INTO actor (first_name, last_name) VALUES ('jiro','yamada');
INSERT INTO actor (first_name, last_name) VALUES ('shiro','yamada');
SELECT pg_switch_wal();
EOF

タイムライン2

本来「次郎」まで遡らなければならないところを誤って、「太郎」まで遡ってしまいました。操作ミスに気づかず、その後、「三郎、四郎」をINSERTしてしまいます。

現在のLSNおよびWALファイルを確認します。

[postgres@centos10 ~]$ psql << EOF
> SELECT pg_walfile_name(pg_current_wal_lsn());
> SELECT pg_current_wal_lsn();
> EOF
     pg_walfile_name      
--------------------------
 000000010000000000000005
(1 row)

 pg_current_wal_lsn 
--------------------
 0/5000060
(1 row)

現在のWALが000000010000000000000005ですので、「太郎、次郎、四郎」がINSERTされたのは1つ前のWALである000000010000000000000004です。これをpg_waldumpコマンドで閲覧すると、「太郎、次郎、四郎」に対応した3回のCOMMIT操作があるのが読み取れます。

以下ログの場合は「太郎」のCOMMITである「lsn : 0/04002CD8」までの遡りを試みます。

[postgres@centos10 ~]$ /usr/pgsql-13/bin/pg_waldump /nfs/postgres/wal/000000010000000000000004 
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/04000028, prev 0/03000100, desc: RUNNING_XACTS nextXid 676 latestCompletedXid 675 oldestRunningXid 676
rmgr: Sequence    len (rec/tot):     99/    99, tx:        676, lsn: 0/04000060, prev 0/04000028, desc: LOG rel 1663/16384/16419, blkref #0: rel 1663/16384/16419 blk 0
rmgr: Heap        len (rec/tot):     54/  3410, tx:        676, lsn: 0/040000C8, prev 0/04000060, desc: INSERT off 59 flags 0x00, blkref #0: rel 1663/16384/16421 blk 1 FPW
rmgr: Btree       len (rec/tot):     53/  4113, tx:        676, lsn: 0/04000E20, prev 0/040000C8, desc: INSERT_LEAF off 201, blkref #0: rel 1663/16384/16556 blk 1 FPW
rmgr: Btree       len (rec/tot):     53/  3773, tx:        676, lsn: 0/04001E38, prev 0/04000E20, desc: INSERT_LEAF off 122, blkref #0: rel 1663/16384/16587 blk 1 FPW
rmgr: Transaction len (rec/tot):     34/    34, tx:        676, lsn: 0/04002D10, prev 0/04001E38, desc: COMMIT 2020-12-24 21:54:55.566531 JST
rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 0/04002D38, prev 0/04002D10, desc: RUNNING_XACTS nextXid 677 latestCompletedXid 675 oldestRunningXid 676; 1 xacts: 676
rmgr: Heap        len (rec/tot):     79/    79, tx:        677, lsn: 0/04002D70, prev 0/04002D38, desc: INSERT off 60 flags 0x00, blkref #0: rel 1663/16384/16421 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:        677, lsn: 0/04002DC0, prev 0/04002D70, desc: INSERT_LEAF off 202, blkref #0: rel 1663/16384/16556 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:        677, lsn: 0/04002E00, prev 0/04002DC0, desc: INSERT_LEAF off 123, blkref #0: rel 1663/16384/16587 blk 1
rmgr: Transaction len (rec/tot):     34/    34, tx:        677, lsn: 0/04002E40, prev 0/04002E00, desc: COMMIT 2020-12-24 21:54:55.569405 JST
rmgr: Heap        len (rec/tot):     87/    87, tx:        678, lsn: 0/04002E68, prev 0/04002E40, desc: INSERT off 61 flags 0x00, blkref #0: rel 1663/16384/16421 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:        678, lsn: 0/04002EC0, prev 0/04002E68, desc: INSERT_LEAF off 203, blkref #0: rel 1663/16384/16556 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:        678, lsn: 0/04002F00, prev 0/04002EC0, desc: INSERT_LEAF off 124, blkref #0: rel 1663/16384/16587 blk 1
rmgr: Transaction len (rec/tot):     34/    34, tx:        678, lsn: 0/04002F40, prev 0/04002F00, desc: COMMIT 2020-12-24 21:54:55.570508 JST
rmgr: XLOG        len (rec/tot):     24/    24, tx:          0, lsn: 0/04002F68, prev 0/04002F40, desc: SWITCH

PostgreSQLの復旧データを所定のディレクトリに配置します。

systemctl stop postgresql-13.service 
rm -rf /var/lib/pgsql/13/data/*
touch /var/lib/pgsql/13/data/recovery.signal
cd /var/lib/pgsql/13/data/
tar xvzf /nfs/postgres/backup/base.tar.gz

リストアに関するパラメタをpostgresql.confに加筆します。後述の検証シナリオの都合上、アーカイビングに関するパラメタは変更せず、そのままとして下さい。

# vi postgresql.conf

archive_mode = on
archive_command = 'cp -p %p /nfs/postgres/wal/%f'
archive_timeout = 60
restore_command = 'cp /nfs/postgres/wal/%f %p'
recovery_target_action = 'promote'
recovery_target_lsn = '0/04002D10'
archive_cleanup_command = '/usr/pgsql-13/bin/pg_archivecleanup /nfs/postgres/wal/ %r'

PostgreSQLを復旧させ、PITRを実行します。

systemctl start postgresql-13.service

「三郎、四郎」をINSERTします。

psql dvdrental << EOF
INSERT INTO actor (first_name, last_name) VALUES ('saburo','yamada');
INSERT INTO actor (first_name, last_name) VALUES ('shiro','yamada');
SELECT pg_switch_wal();
EOF

WALのファイル名を確認します。00000001で始まるファイルと00000002で始まるファイルがあります。この数字がタイムラインIDです。

[postgres@centos10 ~]$ ls -l /nfs/postgres/wal/
total 114696
-rw-------. 1 postgres postgres 16777216 Dec 24 21:51 000000010000000000000001
-rw-------. 1 postgres postgres 16777216 Dec 24 21:52 000000010000000000000002
-rw-------. 1 postgres postgres 16777216 Dec 24 21:54 000000010000000000000003
-rw-------. 1 postgres postgres      337 Dec 24 21:54 000000010000000000000003.00000028.backup
-rw-------. 1 postgres postgres 16777216 Dec 24 21:54 000000010000000000000004
-rw-------. 1 postgres postgres 16777216 Dec 24 21:56 000000010000000000000005
-rw-------. 1 postgres postgres 16777216 Dec 24 22:04 000000020000000000000004
-rw-------. 1 postgres postgres 16777216 Dec 24 22:08 000000020000000000000005
-rw-------. 1 postgres postgres       33 Dec 24 22:02 00000002.history
[postgres@centos10 ~]$ 

WALの格納先を見ると、.historyというファイルがあります。これがどのようにタイムラインが遷移したかを記録するファイルです。例えば以下のような出力ならば、タイムライン1のLSN 0/4002D10の後にタイムライン2に遷移した事が分かります。

[postgres@centos10 ~]$ cat /nfs/postgres/wal/00000002.history
1 0/4002D38 after LSN 0/4002D10

[postgres@centos10 ~]$ 

actorテーブルを確認します。すると、「次郎」のINSERTを忘れている事に気づきます。

[postgres@centos10 ~]$ psql dvdrental -c "SELECT * FROM actor"


      200 | Thora       | Temple       | 2013-05-26 14:47:57.62
      201 | taro        | yamada       | 2020-12-24 21:54:55.565001
      234 | saburo      | yamada       | 2020-12-24 22:04:21.666698
      235 | shiro       | yamada       | 2020-12-24 22:04:21.672565
(203 rows)

タイムライン2

操作ミスに気付き、タイムライン1の「次郎」まで遡ります。その後、「三郎、四郎」をINSERTし、復旧に成功させます。

タイムラインWALファイルの中身をもう一度確認します。タイムライン1の「次郎」に相当するLSNは「lsn: 0/04002E40」である事が分かります。

[postgres@centos10 ~]$ /usr/pgsql-13/bin/pg_waldump /nfs/postgres/wal/000000010000000000000004 
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/04000028, prev 0/03000100, desc: RUNNING_XACTS nextXid 676 latestCompletedXid 675 oldestRunningXid 676
rmgr: Sequence    len (rec/tot):     99/    99, tx:        676, lsn: 0/04000060, prev 0/04000028, desc: LOG rel 1663/16384/16419, blkref #0: rel 1663/16384/16419 blk 0
rmgr: Heap        len (rec/tot):     54/  3410, tx:        676, lsn: 0/040000C8, prev 0/04000060, desc: INSERT off 59 flags 0x00, blkref #0: rel 1663/16384/16421 blk 1 FPW
rmgr: Btree       len (rec/tot):     53/  4113, tx:        676, lsn: 0/04000E20, prev 0/040000C8, desc: INSERT_LEAF off 201, blkref #0: rel 1663/16384/16556 blk 1 FPW
rmgr: Btree       len (rec/tot):     53/  3773, tx:        676, lsn: 0/04001E38, prev 0/04000E20, desc: INSERT_LEAF off 122, blkref #0: rel 1663/16384/16587 blk 1 FPW
rmgr: Transaction len (rec/tot):     34/    34, tx:        676, lsn: 0/04002D10, prev 0/04001E38, desc: COMMIT 2020-12-24 21:54:55.566531 JST
rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 0/04002D38, prev 0/04002D10, desc: RUNNING_XACTS nextXid 677 latestCompletedXid 675 oldestRunningXid 676; 1 xacts: 676
rmgr: Heap        len (rec/tot):     79/    79, tx:        677, lsn: 0/04002D70, prev 0/04002D38, desc: INSERT off 60 flags 0x00, blkref #0: rel 1663/16384/16421 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:        677, lsn: 0/04002DC0, prev 0/04002D70, desc: INSERT_LEAF off 202, blkref #0: rel 1663/16384/16556 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:        677, lsn: 0/04002E00, prev 0/04002DC0, desc: INSERT_LEAF off 123, blkref #0: rel 1663/16384/16587 blk 1
rmgr: Transaction len (rec/tot):     34/    34, tx:        677, lsn: 0/04002E40, prev 0/04002E00, desc: COMMIT 2020-12-24 21:54:55.569405 JST
rmgr: Heap        len (rec/tot):     87/    87, tx:        678, lsn: 0/04002E68, prev 0/04002E40, desc: INSERT off 61 flags 0x00, blkref #0: rel 1663/16384/16421 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:        678, lsn: 0/04002EC0, prev 0/04002E68, desc: INSERT_LEAF off 203, blkref #0: rel 1663/16384/16556 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:        678, lsn: 0/04002F00, prev 0/04002EC0, desc: INSERT_LEAF off 124, blkref #0: rel 1663/16384/16587 blk 1
rmgr: Transaction len (rec/tot):     34/    34, tx:        678, lsn: 0/04002F40, prev 0/04002F00, desc: COMMIT 2020-12-24 21:54:55.570508 JST
rmgr: XLOG        len (rec/tot):     24/    24, tx:          0, lsn: 0/04002F68, prev 0/04002F40, desc: SWITCH

PostgreSQLの復旧データを所定のディレクトリに配置します。

systemctl stop postgresql-13.service 
rm -rf /var/lib/pgsql/13/data/*
touch /var/lib/pgsql/13/data/recovery.signal
cd /var/lib/pgsql/13/data/
tar xvzf /nfs/postgres/backup/base.tar.gz

リストアに関するパラメタをpostgresql.confに加筆します。後述の検証シナリオの都合上、アーカイビングに関するパラメタは変更せず、そのままとして下さい。

注意すべきはrecovery_target_timelineというパラメタです。デフォルト設定の場合は最新のタイムラインのWALを適用しますが、最新のWALを適用したくない場合はrecovery_target_timelineを明示指定する必要があります。

# vi postgresql.conf

archive_mode = on
archive_command = 'cp -p %p /nfs/postgres/wal/%f'
archive_timeout = 60
restore_command = 'cp /nfs/postgres/wal/%f %p'
recovery_target_action = 'promote'
recovery_target_lsn = '0/04002E40'
archive_cleanup_command = '/usr/pgsql-13/bin/pg_archivecleanup /nfs/postgres/wal/ %r'
recovery_target_timeline = '1'

PostgreSQLを復旧させ、PITRを実行します。

systemctl start postgresql-13.service

「三郎、四郎」をINSERTします。

psql dvdrental << EOF
INSERT INTO actor (first_name, last_name) VALUES ('saburo','yamada');
INSERT INTO actor (first_name, last_name) VALUES ('shiro','yamada');
SELECT pg_switch_wal();
EOF

WALのファイル名を確認します。タイムライン1から3までのファイルがある事が分かります。

[root@centos10 data]# ls -l /nfs/postgres/wal/
合計 131084
-rw-------. 1 postgres postgres 16777216 12月 24 21:51 000000010000000000000001
-rw-------. 1 postgres postgres 16777216 12月 24 21:52 000000010000000000000002
-rw-------. 1 postgres postgres 16777216 12月 24 21:54 000000010000000000000003
-rw-------. 1 postgres postgres      337 12月 24 21:54 000000010000000000000003.00000028.backup
-rw-------. 1 postgres postgres 16777216 12月 24 21:54 000000010000000000000004
-rw-------. 1 postgres postgres 16777216 12月 24 21:56 000000010000000000000005
-rw-------. 1 postgres postgres       33 12月 24 22:02 00000002.history
-rw-------. 1 postgres postgres 16777216 12月 24 22:04 000000020000000000000004
-rw-------. 1 postgres postgres 16777216 12月 24 22:08 000000020000000000000005
-rw-------. 1 postgres postgres       33 12月 24 22:21 00000003.history
-rw-------. 1 postgres postgres 16777216 12月 24 22:24 000000030000000000000004
[root@centos10 data]# 

WALの格納先を見ると、.historyというファイルがあります。これがどのようにタイムラインが遷移したかを記録するファイルです。例えば以下のような出力ならば、タイムライン1のLSN 0/4002E40の後にタイムライン3に遷移した事が分かります。

[root@centos10 data]# cat /nfs/postgres/wal/00000003.history
1 0/4002E68 after LSN 0/4002E40

[root@centos10 data]# 

actorテーブルを確認します。「太郎、次郎、三郎、四郎」の順にINSERTできている事が分かります。

[postgres@centos10 ~]$ psql dvdrental -c "SELECT * FROM actor"


      200 | Thora       | Temple       | 2013-05-26 14:47:57.62
      201 | taro        | yamada       | 2020-12-24 21:54:55.565001
      202 | jiro        | yamada       | 2020-12-24 21:54:55.568998
      234 | saburo      | yamada       | 2020-12-24 22:24:40.04214
      235 | shiro       | yamada       | 2020-12-24 22:24:40.044846
(204 rows)