PostgreSQL のバックアップとリストア手順のメモです。

環境

• OS: Ubuntu 20.04
• DB: PostgreSQL 14

バックアップの種類

データベースのバックアップとリストアの方法には 2種類あります。

1つ目はデータベースのフルバックアップを取得して、それをそのまま復元する方法。これはバックアップを取得した時点まで復元することはできますが、バックアップの取得後に変更されたデータは復元できません。

2つ目はトランザクションログ単位でバックアップを取得し、任意の時点へデータを復元する方法。 “PITR” (Point-in-time Recovery) と呼ばれ、フルバックアップを取得した後、トランザクションログを継続してバックアップすることで、トランザクションログを使って任意の時点のデータを復元することができます。

PostgreSQL では、pg_dump または pg_dumpall というフルバックアップ用のコマンドが標準で提供されており、このツールを使うことでフルバックアップを簡単に取得することができます。またフルバックアップのリストアには pg_restore というコマンドが提供されているので、復元も簡単にできます。

ただ実際の運用ではトランザクションログをバックアップし、PITR を行えるように備えておくのが一般的だと思いますので、今回はこの手順を説明します。

事前準備

バックアップ フォルダの作成

PITR を行うためには、”ベースバックアップ” と呼ばれるフルバックアップと、トランザクションログのバックアップである “アーカイブログ” が必要となります。

今回、ベースバックアップとアーカイブログは以下のフォルダに保存するものとします。各フォルダへは、PostgreSQL の実行ユーザー (postgres ユーザー) が読み書きできるよう設定します。

• ベースバックアップ: /backup/basebackup/
• アーカイブログ: /backup/archivedir/

shell

$ sudo mkdir /backup
$ sudo mkdir /backup/basebackup
$ sudo mkdir /backup/archivedir
$ sudo chown postgres:postgres /backup -R
$ sudo chmod 775 /backup -R

アーカイブログの設定

PostgreSQL のトランザクションログは “WAL” (Write Ahead Log) と呼ばれます。PostgreSQL は複数の WAL ファイルに対して順に書き込みを行い、ファイルが 1巡したら古い WAL ファイルを上書きします。

WAL が上書きされたら、そこに書かれていたトランザクションログは消失しますので、WAL ファイルのコピーを別の場所に保存しておく必要があります。この WAL ファイルのコピーを “アーカイブログ” と呼びます。

アーカイブログを保存するようにするには以下 3つの設定が必要です。

/etc/postgresql/14/main/postgresql.conf

wal_level = logical
archive_mode = on
archive_command = 'cp %p /backup/archivedir/%f'

archive_command に cp コマンドを設定しておくことで、WAL の書き込み先ファイルが切り替わった時に、それまでトランザクションログが書き込まれていたファイルを別の場所にコピーすることができます。上記の例では /backup/archivedir/ フォルダへ WAL ファイルをコピーしています。

設定を変更したら、PostgreSQL を一度再起動しておきましょう。

shell

$ sudo systemctl restart postgresql

これで、WAL ファイルがいっぱいになり書き込み先が新しいファイルに切り替わった時に、自動で WAL ファイルがアーカイブログとしてバックアップ (コピー) されるようになります。

テストーデータの準備

次にテストデータを準備しておきます。ここでは 20件のデータを登録しています。

shell

$ sudo su postgres
$ psql
postgres=# CREATE DATABASE testdb;
postgres=# \c testdb

testdb=# CREATE TABLE table1 (id INT, name TEXT);
testdb=# INSERT INTO table1 (id, name) SELECT s, CONCAT('name ', s) FROM GENERATE_SERIES(1,10) s;
testdb=# SELECT * FROM table1;
...
(10 rows)

testdb=# INSERT INTO table1 (id, name) SELECT s, CONCAT('name ', s) FROM GENERATE_SERIES(11,20) s;
testdb=# SELECT * FROM table1;
...
(20 rows)
testdb=# exit

バックアップ手順

PITR を行うためにベースバックアップを取得します。アーカイブログは自動でバックアップされるよう設定したので、特に操作は不要です。

ベースバックアップにはその時点までのデータ変更が全て含まれるため、ベースバックアップ取得以前のアーカイブログは不要です。実際のシナリオでは、ベースバックアップを定期的に取得しながら、古い不要なアーカイブログを都度削除する、というように運用します。

1). ベースバックアップの取得

ベースバックアップの取得には pg_basebackup コマンドを使います。pg_basebackup はオンライン物理バックアップを行うので、バックアップを取得するためにデータベースを停止する必要はありません。

shell

$ sudo su postgres
$ pg_basebackup -U postgres -D /backup/basebackup/ -F plain --checkpoint=fast
$ ls /backup/basebackup/
backup_label	 pg_dynshmem   pg_serial     pg_tblspc	  postgresql.auto.conf
backup_manifest  pg_logical    pg_snapshots  pg_twophase
base		 pg_multixact  pg_stat	     PG_VERSION
global		 pg_notify     pg_stat_tmp   pg_wal
pg_commit_ts	 pg_replslot   pg_subtrans   pg_xact

# backup_label ファイルに取得したバックアップの概要が記載されています。
$ cat /backup/basebackup/backup_label
START WAL LOCATION: 0/2000028 (file 000000010000000000000002)
CHECKPOINT LOCATION: 0/2000060
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2023-01-21 14:22:43 JST
LABEL: pg_basebackup base backup
START TIMELINE: 1

2). 追加データの登録と WAL のローテーションを実行

ここで実際のシナリオをシミュレーションするために、追加データの登録と WAL のローテーションを行います。

実際のシナリオでは、ベースバックアップ取得後もデータ更新が続き、WAL が自動的にローテーションされている状況と考えてください。

ここで登録するデータはベースバックアップに含まれずアーカイブログにのみ含まれるので、後でアーカイブログからもデータが復元されているのを確認することができます。

shell

# 追加データを 10件登録
$ sudo su postgres
$ psql testdb
testdb=# testdb=# INSERT INTO table1 (id, name) SELECT s, CONCAT('name ', s) FROM GENERATE_SERIES(21,30) s;
testdb=# SELECT * FROM table1;
(30 rows)

# 現在の WAL の Log Sequence Number (LSN) を確認
testdb=# SELECT pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 000000010000000000000003
(1 row)

# WAL のローテーションを実行
testdb=# SELECT pg_switch_wal();
 pg_switch_wal 
---------------
 0/3000838
(1 row)

# WAL の LSN が進んだのを確認
testdb=# SELECT pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 000000010000000000000004
(1 row)

testdb=# exit

# アーカイブログが保存されているのを確認
# 先ほどローテーションした WAL 000000010000000000000003 がコピーされている
$ ls /backup/archivedir
000000010000000000000001  000000010000000000000002.00000060.backup
000000010000000000000002  000000010000000000000003

リストア手順

先ほど取得したベースバックアップとアーカイブログから、データベースを復元します。

1). 新しいサーバーに PostgreSQL をインストールし、バックアップファイルをコピー

新しいサーバーに PostgreSQL をインストールしたら、先ほど取得したベースバックアップとアーカイブログを新しいサーバーにコピーしておきます。

ここでは、バックアップファイルは取得時と同じ /backup フォルダにコピーしたと想定します。

2). PostgreSQL を停止しデータファイルを移動

PostgreSQL を停止し、既存のデータファイルを移動しておきます。

(既存のデータファイルは使用しないため、削除して大丈夫です)

shell

# PostgreSQL を停止
$ sudo systemctl stop postgresql
$ sudo systemctl status postgresql
$ postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pr>
     Active: inactive (dead) since Sat 2023-01-21 15:20:10 JST; 19s ago
     ...

# 既存のデータファイルを別の場所に移動
$ sudo mkdir /postgresql-14-main-moved
$ sudo mv /var/lib/postgresql/14/main /postgresql-14-main-moved

3). リカバリ用の設定

PITR のリカバリを行う場合、restore_command と recovery_target_action という2つの項目を設定しておく必要があります。

/etc/postgresql/14/main/postgresql.conf

restore_command = 'cp /backup/archivedir/%f %p'
recovery_target_action = 'promote'

ここでは、アーカイブログをバックアップフォルダ (/backup/archivedir) からコピーして復元するよう設定しています。

4). ベースバックアップファイルの復元

ベースバックアップで取得したファイルを、PostgreSQL のデータ ディレクトリにコピーします。

ベースバックアップは物理バックアップのため、ファイルをコピーするだけでデータを復元できます。

shell

# ベースバックアップのファイルをデータディレクトリーにコピー
$ sudo cp -r /backup/basebackup /var/lib/postgresql/14/main
$ sudo chown -R postgres:postgres /var/lib/postgresql/14/main
$ sudo chmod -R 700 /var/lib/postgresql/14/main

ここで一度 PostgreSQL を起動してみると、データがベースバックアップから復元されているのを確認できます。この時点ではまだアーカイブログからはデータが復元されていません。

shell

# PostgreSQL を一度開始し、ベースバックアップからデータが復元されているのを確認
$ sudo systemctl start postgresql
$ sudo su postgres
$ psql testdb
testdb=# SELECT * FROM table1;
...
(20 rows)

testdb=# exit

# アーカイブログを復元するため、再度 PostgreSQL を停止
$ sudo systemctl stop postgresql

5). アーカイブログの復元

アーカイブログを復元するには、recovery.signal という名前のファイルをデータディレクトリに作成します。

この名前のファイルがある状態で PostgreSQL を起動すると、restore_command で設定したコマンドが実行されて WAL が復元され、その後 recovery_target_action の設定値に応じて PostgreSQL が停止したり起動したりします。

recovery.signal ファイルの中身は空で構いません。この名前のファイルが作成されていれば、リカバリー処理が実行されます。

shell

# recovery.signal ファイルを作成
$ sudo touch /var/lib/postgresql/14/main/recovery.signal
$ sudo chown postgres:postgres /var/lib/postgresql/14/main/recovery.signal
$ sudo chmod 700 /var/lib/postgresql/14/main/recovery.signal

# PostgreSQL を起動
$ sudo systemctl start postgresql

# PostgreSQL のログでアーカイブログが復元されたのを確認できます
$ sudo tail /var/log/postgresql/postgresql-14-main.log
...
2023-01-21 16:14:46.573 JST [1798] LOG:  starting archive recovery
2023-01-21 16:14:46.694 JST [1798] LOG:  restored log file "000000010000000000000002" from archive
2023-01-21 16:14:46.942 JST [1798] LOG:  restored log file "000000010000000000000003" from archive
...
2023-01-21 16:14:47.577 JST [1798] LOG:  archive recovery complete
2023-01-21 16:14:47.626 JST [1797] LOG:  database system is ready to accept connections

PostgreSQL を起動し、アーカイブログからデータが復元されているのを確認します。

shell

$ sudo su postgres
$ psql testdb
testdb=# SELECT * FROM table1;
...
(30 rows)

データが復元されたら、自動的に recovery.signal ファイルは削除されます。

その後は従来通りに PostgreSQL を使うことができます。