Windowsコマンドでみる PostgreSQL 13 のオンラインバックアップ＆リカバリ

はじめに

Postgresを運用するために、バックアップ、リカバリの手順を確立しましょう。
バックアップの方法にもいくつかあり、DBを停止して物理ファイルを保管したり、
データダンプコマンドである時点のデータを保管する方法などがありますが、
任意の時点へのリカバリ（PITR: Point In Time Recovery）を実現できる唯一の方法は
ベースバックアップとその取得時点以降のリカバリに必要なWALファイルを確実に保管することです。
これで「ベースバックアップ取得時点以降」の任意の時点へのリカバリが可能になります。
ここでいうベースバックアップとは、オンライン(サーバー稼働下)でデータベースクラスタの物理ファイルをまるっとコピーしたファイル群のことを指します。(詳細は、後述のバックアップに記載)

さらにリカバリに関しては 、ワンショットで完了するような用意されたコマンドはありません。
ユーザーにより自由にカスタマイズができる面はメリットですが、手順の間違いにより必要なデータを失ってしまうリスクはデメリットです。

PostgreSQL 13 のバックアップ・リカバリを実行するためのポイントをWindowsコマンドで実例を記載しながら解説します。

前提

PostgreSQLの バックアップ・リカバリの手順を確立するためには、データベースクラスタおよびWALファイルの管理フォルダに関する理解が重要です。

運用を考慮したファイル構成のポイント

物理バックアップは、データベースクラスタごとに保管します。
標準インストールで下記のようなフォルダ・ファイル構成になっています。
ここでいうデータベースクラスタはdataフォルダ全量のことです。

デフォルト構成

└─data
 　├─base
 　├─global
　 ├─log
　 ├─pg_commit_ts
　 ├─pg_dynshmem
　 ├─pg_logical
　 ├─pg_multixact
　 ├─pg_notify
　 ├─pg_replslot
　 ├─pg_serial
　 ├─pg_snapshots
　 ├─pg_stat
　 ├─pg_stat_tmp
　 ├─pg_subtrans
　 ├─pg_tblspc
　 ├─pg_twophase
　 ├─pg_wal
　 ├─pg_xact
　 ├─PG_VERSION
　 ├─pg_hba.conf
　 ├─pg_ident.conf
　 ├─postmaster.pid
　 ├─postmaster.opts
　 └─postgreql.conf

PITRを実現するためには、ベースバックアップ以降から最新までのWALファイルを確実に保管することが求められます。
また、サーバーログは出力した情報を失わないように、リカバリ直前の最新のログに追記したいところです。

この点を考慮して、オンライン物理バックアップからのリカバリを行うとき、
データベースクラスタ配下にWALファイル、サーバーログを保管していることは、
リカバリ手順を煩雑にします。（手順を誤ると、ファイル消失やデータ消失してしまうかもしれません。）

お勧めは、WALファイル、サーバーログは、データベースクラスタの外で保管することです。
データベースクラスタ中の元フォルダは、シンボリックリンクにしておけばサーバーの稼働に問題はありません。
また、PITRの実現のためにアーカイブされたWALファイルを保管するフォルダも用意しましょう。

カスタマイズした構成

C:\postgres13
├─data
│ ├─base
│ ├─log     　　　# ← シンボリックリンク化
│ ├─pg_wal  　　　# ← シンボリックリンク化
│ ├─...
│ └─...
├─log　　　　　　　# サーバーログ保管
├─pg_wal    　　　# WALファイル保管
└─pg_wal_archive  # アーカイブされたWALファイル保管

pg_walとlogのシンボリックリンクの作り方

1. サーバー停止

WALファイル、サーバーログはサーバー稼働中、常に書き出される可能性があります。
WALファイル、サーバーログの場所を変更するためにサーバーは一時停止します。
windowsでは pg_ctlコマンドを使えるようにするためにはいろいろ考える必要があるため、
タスクマネージャや管理者権限で次のscコマンドを使って実行するのが簡単です。

サーバー停止

sc stop postgresql-x64-13

2. pg_walとlogの移動とシンボリックリンクの作成

xcopy はフォルダ to フォルダでフォルダ配下のファイルを再帰的にコピーします。 rmdir はフォルダ削除です。
mklink はシンボリックリンク作成です。

物理ファイルの移動とシンボリックリンクの作成

xcopy C:\postgres13\data\pg_wal C:\postgres13\pg_wal
rmdir /s /q C:\postgres13\data\pg_wal
mklink /D C:\postgres13\data\pg_wal C:\postgres13\pg_wal

xcopy C:\postgres13\data\log C:\postgres13\log
rmdir /s /q C:\postgres13\data\log
mklink /D C:\postgres13\data\log C:\postgres13\log

3. WALファイルのアーカイブ設定

WALファイルは、サーバーの設定でファイル数が制限されて、古いファイルはpg_walフォルダから削除（このことをアーカイブと呼んでいる）されます。（ローテーションしているらしい。）
アーカイブされたファイルを捨てるか、保管するかは、postgresql.confで設定します。
archive_modeをonにして、archive_commandにWALファイルのコピーコマンドを記載します。
このコマンドが正常終了すると、アーカイブ対象のWALファイルはpg_walフォルダからサーバーによって削除されます。
restore_commandは、リカバリ時に使用するコマンドです。アーカイブフォルダからリカバリに必要なアーカイブされたWALファイルを
pg_walファイルにコピーするコマンドを記載します。

postgresql.conf抜粋

# - Archiving -

archive_mode = on		# enables archiving; off, on, or always
				# (change requires restart)
archive_command = 'copy "%p" "C:\\postgres13\\pg_wal_archive\\%f"'		# command to use to archive a logfile segment
				# placeholders: %p = path of file to archive
				#               %f = file name only
				# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0		# force a logfile segment switch after this
				# number of seconds; 0 disables

# - Archive Recovery -

# These are only used in recovery mode.

restore_command = 'copy "C:\\postgres13\\pg_wal_archive\\%f" "%p"'		# command to use to restore an archived logfile segment
#restore_command = ''		# command to use to restore an archived logfile segment
				# placeholders: %p = path of file to restore
				#               %f = file name only
				# e.g. 'cp /mnt/server/archivedir/%f %p'
				# (change requires restart)
#archive_cleanup_command = ''	# command to execute at every restartpoint
#recovery_end_command = ''	# command to execute at completion of recovery

4. サーバー起動

サーバー起動

sc start postgresql-x64-13

問題なくサーバーが起動されたか、ログが書き出されているか
sc query コマンドや、サーバーログを参照して確認してください。

バックアップ（オンライン物理バックアップ）

ここから本題のバックアップ＆リカバリです。
バックアップに関して、pg_basebackup というコマンドもありますが、
この解説では、それを使用しない手順を記載します。

1. バックアップ取得開始コマンド

オンライン物理バックアップの取得では、その前後にPostgresのコマンドを実行する必要があります。
これによりサーバー内にバックアップを取得していた時間帯を記録したり、チェックポイント処理を実行して物理ファイルに書き出ししたりしています。
次のようにコマンドにuserid, passwordを記載してバッチコマンドで実行することができます。

取得開始コマンド

"C:\Program Files\PostgreSQL\13\bin\psql.exe" -c "SELECT pg_start_backup(now()::text);" "user=userid password=password host=localhost dbname=postgres"

2. data → backup (copy)

データベースクラスタの物理ファイルをバックアップフォルダにコピーします。

空のバックアップ先フォルダを用意してxcopyでコピーします。

バックアップコマンド

mkdir C:\Users\Public\Documents\pg13\backup\data
xcopy /e C:\postgres13\data C:\Users\Public\Documents\pg13\backup\data

3. バックアップ取得完了コマンド

データベースクラスタの物理ファイルコピー が完了したら、次のコマンドを実行してサーバーに通知します。

取得完了コマンド

"C:\Program Files\PostgreSQL\13\bin\psql.exe" -c "SELECT pg_stop_backup();" "user=postgres password=postgres host=localhost dbname=postgres"

このコマンドが完了するとバックアップ先のデータベースクラスタの直下に
backup_labelというファイルが作成されます。
このファイルには、バックアップに関する情報が記録されています。
pg_controldataというコマンドでユーザーが内容を確認することもできます。

確認

"C:\Program Files\PostgreSQL\13\bin\pg_controldata.exe" -D C:\Users\Public\Documents\pg13\backup\data

4. 不要なWALファイルのアーカイブ削除

バックアップの取得が完了したら、ベースバックアップ以前のWALのアーカイブファイルはリカバリに必要がなくなります。
バックアップ取得の中で、アーカイブフォルダに .backup拡張子のバックアップのラベルがあります。その記録を起点にそれ以前の不要な古い世代のWALファイルをアーカイブフォルダから削除します。
pg_archivecleanupというコマンドが用意されているため、それを使用するのが便利です。
ただし、削除するのはWALファイルであり.backup拡張子のラベルファイルは削除されません。
ラベルファイルの容量は1kb未満で大きくはありませんが、削除したい場合は、個別に削除するか削除のスクリプトを追加します。

不要なWALファイルのアーカイブ削除のbatスクリプトを次に記載します。
.backup拡張子のファイルを 名前降順でファイルに出力して、その先頭のファイルを引数にpg_archivecleanupを実行するバッチです。

archivecleanup.bat

@echo off

dir "C:\postgres13\pg_wal_archive" /b /o:-n| findstr /EI ".backup" > bkuplist.txt
for /f %%a in (bkuplist.txt) do (
  "C:\Program Files\PostgreSQL\13\bin\pg_archivecleanup.exe" "C:\postgres13\pg_wal_archive" %%a
  echo pg_archivecleanup before %%a 
  goto :exit_for
)
:exit_for
del bkuplist.txt

pause

バックアップは最新1世代のみ保管するポリシーであれば、これでokです。
複数世代のバックアップを保管する場合は、どの.backupファイルを起点にWALファイルが不要になるかを整理して実装しましょう。

リカバリ

1. サーバー停止

リカバリのために、サーバーが止まった状態で行う必要があります。しｄｄｄｄｄ
サーバーが稼働中であれば停止しましょう。

サーバー停止

sc stop postgresql-x64-13

2. data → data.tmp (move)

念のため、データベースクラスタdataのフォルダ名をdata.tmpに変更します。
これはデータベースクラスタdataのクリーンアップと、旧データベースクラスタのバックアップを意味します。

2回目以降だと古いdata.tmpがあるかもしれません。
古い data.tmp を rmdirで削除します。
moveで現存のデータベースクラスタをdata.tmpにリネームしますが、
その前にデータベースクラスタ配下のWALフォルダ、サーバーログフォルダのシンボリックリンクを削除しておきます。
シンボリックリンクを削除しておかないと、必要なファイルが消失してしまうかもしれません。

現時点のデータベースクラスタを退避

SET PGDATA="C:\postgres13\data"
SET PGBKUP="C:\Users\Public\Documents\pg13\backup\data"

rmdir /s /q %PGDATA%.temp
rmdir C:\postgres13\data\pg_wal
rmdir C:\postgres13\data\log
move %PGDATA% %PGDATA%.temp

3. backup → data (copy)

オンライン物理バックアップで保管していたデータベースクラスタの物理ファイルをデータベースクラスタにコピーします。

データベースクラスタとなるフォルダを作成します。
そこに xcopy で保管していたベースバックアップ（データベースクラスタの物理ファイル）をコピーします。
walファイル、サーバーログはデータベースクラスタの外にあるものが実態です。mklink でシンボリックリンクを作り直します。

データベースクラスタのファイル配置、再構成

mkdir "C:\postgres13\data"
xcopy /e %PGBKUP% %PGDATA%

rmdir /s /q C:\postgres13\data\pg_wal
mklink /D C:\postgres13\data\pg_wal C:\postgres13\pg_wal

rmdir /s /q C:\postgres13\data\log
mklink /D C:\postgres13\data\log C:\postgres13\log

4. サーバー起動（リカバリーモード）

リカバリーモードでサーバーを起動します。

リカバリーモードで起動するために、データベースクラスタに recovery.signal という名前の空ファイルを配置します。
type nul > 出力ファイル で空ファイルが作れます。
recovery.signal はPostgresSQL 12から導入された仕組みです。
それ以前では、リカバリーの設定情報も保持する recovery.conf がその役目を担っていましたが、
リカバリのconfigの設定が postgresql.confに統合されたことで、signal用の空ファイルで制御する仕組みになったようです。

リカバリーモードで起動する

type nul > "C:/postgres13/data/recovery.signal"
sc start postgresql-x64-13

以上です。
リカバリが recovery.signal を使用する仕組みになったことはご存じでしたか？
あたりまえですが、メジャーバージョンアップは、仕様変更にも注意しないといけませんね。