PostgreSQL 13 PITR検証

初めに

PostgreSQL の学習のためPITRを検証した
PITRとはWALを利用して、任意の時点までデータベースの内容をリカバリする。

用途：データベースクラッシュ時のロールバックやロールフォワード、DML誤りのリカバリ等

検証環境

CentOS 7
PostgreSQL 13.15

1.PostgreSQL のインストール

最小限でインストール

1-1.ファイルダウンロード

wget --no-check-certificate https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7.7-x86_64/postgresql13-libs-13.15-1PGDG.rhel7.x86_64.rpm  
wget --no-check-certificate https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7.7-x86_64/postgresql13-13.15-1PGDG.rhel7.x86_64.rpm  
wget --no-check-certificate https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7.7-x86_64/postgresql13-server-13.15-1PGDG.rhel7.x86_64.rpm 

1-2.インストール

rpm -Uvh postgresql13-libs-13.15-1PGDG.rhel7.x86_64.rpm  
rpm -Uvh postgresql13-13.15-1PGDG.rhel7.x86_64.rpm  
rpm -Uvh postgresql13-server-13.15-1PGDG.rhel7.x86_64.rpm 

※環境変数は以下とする
export PATH=/usr/pgsql-13/bin:$PATH
export PGDATA=/var/lib/pgsql/13/data

1-3.データベースクラスタ作成

su - postgres
initdb --encoding=UTF8 --no-locale

1-4.postgresql.conf編集
バックアップのため、以下の設定を行う

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

・バックアック取得時のクライアント接続数を設定
max_wal_senders = 1 　

・WALの取得レベル
wal_level = replica

1-5.データベース起動

pg_ctl  start

2.テストデータ投入

2-1.DB接続

psql

2-2.テストテーブル作成、インサート

create table testtbl (  id integer primary key,  update timestamp);
insert into testtbl values (1, now()); 
insert into testtbl values (2, now()); 
insert into testtbl values (3, now()); 
insert into testtbl values (4, now()); 

2-3.切断

\q

2-4.ベースバックアップ取得

pg_basebackup -D /var/lib/pgsql/13/backups/data_old/

2-5.DB接続

psql

2-6.更にデータをインサートする

insert into testtbl values (5, now()); 
insert into testtbl values (6, now()); 
insert into testtbl values (7, now()); 
insert into testtbl values (8, now()); 
insert into testtbl values (9, now()); 

2-7.データ確認

postgres=# select * from testtbl;

 id |           update
----+----------------------------
  1 | 2024-06-26 23:10:23.932686
  2 | 2024-06-26 23:10:23.942614
  3 | 2024-06-26 23:10:23.958787
  4 | 2024-06-26 23:10:23.97873
  5 | 2024-06-26 23:11:25.854284
  6 | 2024-06-26 23:11:25.867728
  7 | 2024-06-26 23:11:25.879649
  8 | 2024-06-26 23:11:25.90037
  9 | 2024-06-26 23:11:25.911275

2-8.ここで1レコードだけ削除したかったが、誤って全件削除した。と想定する

postgres=# delete from testtbl;
DELETE 9

3.PITRの実行

3-1.DB切断

\q

3-2.DB停止

pg_ctl stop

3-3.最新walが必要なため、データベースを退避する

cp -R /var/lib/pgsql/13/data /var/lib/pgsql/13/data_new

3-4.既存のデータベースを削除する

rm -fR /var/lib/pgsql/13/data

3-5.ベースバックアップを戻す

cp -R /var/lib/pgsql/13/backups/data_old/ /var/lib/pgsql/13/data

3-6.PITRを実行するためpostgres.confを修正する

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

3-7.最新walを適用するため、退避したpg_walの場所を記載する

restore_command = 'cp /var/lib/pgsql/13/data_new/pg_wal/%f %p'

WALを適用するターゲットタイムを記載する
（誤ったsqlを実行する直前の時間を指定する。）
recovery_target_time = '2024-06-26 23:11:28'

3-8.PITRを実行するため、リカバリ設定ファイルを作成する

touch  /var/lib/pgsql/13/data/recovery.signal

3-9.PostgreSQLの起動

pg_ctl  start

3-10.DB接続

psql

3-11.テーブルの状態を確認する

postgres=# select * from testtbl;
 id |           update
----+----------------------------
  1 | 2024-06-26 23:10:23.932686
  2 | 2024-06-26 23:10:23.942614
  3 | 2024-06-26 23:10:23.958787
  4 | 2024-06-26 23:10:23.97873
  5 | 2024-06-26 23:11:25.854284
  6 | 2024-06-26 23:11:25.867728
  7 | 2024-06-26 23:11:25.879649
  8 | 2024-06-26 23:11:25.90037
  9 | 2024-06-26 23:11:25.911275
(9 行)

削除前のデータであることが、確認できた。

以上

2024/6/26　作成