はじめに
この記事は NTTテクノクロス Advent Calendar 2022 の6日目の記事です。
こんにちは、NTTテクノクロス 山口です。
普段はPostgreSQLを使った開発業務に従事しています。
この記事では PostgreSQL15 で新たに入った以下contrib(※)について、動作確認もしながら説明したいと思います。
※ PostgreSQLの拡張モジュールの事で、特定の操作をしないと使えるようになりません。この記事ではその操作含めて記載します。
- basebackup_to_shell
- basic_archive
- pg_walinspect
「1」と「2」はバックアップ運用にも役立つツールとなるので詳細めに触れて、「3」は参考がてらの記載としたいと思います。
なお、動作確認はPostgreSQL15.1、Ubuntu1804 LTS 上でのものとなります。
psql --version
===出力===
psql (PostgreSQL) 15.1 (Ubuntu 15.1-1.pgdg18.04+1)
==========
lsb_release -a
===出力===
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.4 LTS
Release: 18.04
Codename: bionic
==========
basebackup_to_shell
これはpg_basebackupコマンドでバックアップを取得する際に独自のスクリプトを実行可能にする、というものになります。
これにより、バックアップを取る前の前処理や後処理をまとめて実行が可能となります。
basebackup_to_shell は実行時に用意したスクリプトファイルを指定するわけではなく、各ファイルの関係性がわかりづらい為、概要を以下に示します。(※ 細部は以降に説明します）
では、使う手順について以下で記載をしていきます。
① まず、postgresql.conf （図のAのファイル）に設定を記載します。
具体的には以下の記載をする必要があります。
# basebackup_to_shellを使う為の記載
shared_preload_libraries = 'basebackup_to_shell'
# 上記を実行時に動かすコマンド。%dはスクリプト実行時の引数を渡す物。
# ③で細部を記載するが、/tmp配下にbackup.shというスクリプトを指定。任意のもの指定可。
basebackup_to_shell.command = '/tmp/backcp.sh %d '
※ マニュアルによると、shared_preload_librariesのかわりに「local_preload_libraries」でもよいとのこと。
In order to function, this module must be loaded via shared_preload_libraries or local_preload_libraries.
このモジュールを機能させるには、 shared_preload_librariesまたはlocal_preload_librariesを使用してロードする必要があります。
② 設定変更後はPostgreSQLを起動、あるいは再起動してください。
③ バックアップスクリプト（図のBのファイル）等、必要な物を準備します。
※ ①②の前に対応でも問題ありません。定期処理等でPostgreSQL起動後、自動バックアップが動いてしまうのであれば先にやっておいた方が良いですが、今回は説明の都合上、③とします。
バックアップスクリプト(/tmp/backup.sh)は今回は例として、日ごとにディレクトリを作成してバックアップを取得するものとします。
なお、今回は動確用の為、tmp配下に配置しています。
vi /tmp/backup.sh
#!/bin/bash
# 標準入力に入る値を排除。
cat - > /dev/null
# スクリプト開始表示
echo "Shell Backup Start"
# スクリプト実行時に受け取った値(%d)の確認
echo "${1}"
# YYYYMMDDHHMMSS形式のディレクトリ作成。
backup_dire=`date "+%Y%m%d%H%M%S"`
mkdir /tmp/$backup_dire
# バックアップの実行
pg_basebackup -U postgres -D /tmp/$backup_dire
# スクリプト完了表示
echo "Shell Backup Done"
exit 0
作ったスクリプトに対し、postgresユーザに実行権を持たせる必要があります。
chmod +x /tmp/backup.sh
これで事前準備（設定）完了です。
④ 実行は以下のように行います。
pg_basebackup -t shell:typeA -X none
今回はスクリプトに渡す引数として「typeA」という値を投げています。
なお、バックアップ取得中に発生した変更(WAL)までは取得できない為、 -X none をつけています。
処理完了後にPostgreSQLログ（図のCのファイル。一部抜粋。）を確認すると、実行時に「shell:typeA」(%d)で渡した値が確認できる事から、受け渡しが成功していることがわかります。
その他、スクリプトで標準出力するように仕掛けたものはPostgreSQLのログに表示されます。
less /var/log/postgresql/postgresql-15-main.log
Shell Backup Start
typeA
2022-11-23 06:34:11.630 UTC [29645] LOG: checkpoint starting: force wait
2022-11-23 06:34:11.658 UTC [29645] LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 3 recycled; write=0.001 s, sync=0.001 s, total=0.028 s; sync files=0, longest=0.000 s, average=0.000 s; distance=32768 kB, estimate=32768 kB
Shell Backup Done
今回は単純な例で実行しましたが、様々な使い道がありそうです。
[参考] basebackup_to_shellに関する補足 ～スクリプトが複数実行される対策～
これまで説明してきた方法でbasebackup_to_shellは動くのですが、一方で指定したスクリプトが複数回実行される事があります。（この事はPostgereSQLログを確認するとわかります。上記で記載したログが1回の実行で、2回連続して出力されるイメージです）
この場合、1回の実行で2回バックアップが取得される形となりますが、1回だけとしたい場合は以下のように対応します。
① postgresql.confの設定を以下のように変更します。(変更部分のみ記載)
# %fを追記。%fはバックアップ対象のDBクラスタのtarファイルを指す。(主ディレクトリはbase.tarという名前となる)
basebackup_to_shell.command = '/tmp/backcp.sh %f %d '
② /tmp/backup.sh を以下のように変更します。(スクリプト内全記載。追記や変更がある点のみコメントで補足。）
#!/bin/bash
cat - > /dev/null
echo "Shell Backup Start"
# スクリプト実行時に受け取った値(%f)の確認。
# 元々は%dを受け取っていたが、postgresql.confの指定変更により、${1}で受け取る値が%fに変わる。
echo "${1}"
# if文を追記。
# DBクラスタの主ディレクトリはbase.tarという名前で渡される為、
# その時のみバックアップを指定した方法で取る形とする。
if [ "${1}" = "base.tar" ]; then
backup_dire=`date "+%Y%m%d%H%M%S"`
mkdir /tmp/$backup_dire
pg_basebackup -U postgres -D /tmp/$backup_dire
# バックアップを実行した事を知らせる出力。
echo "Backup OK"
# base.tar以外の(バックアップを取得しない)時のルートを記載。なくても良い。
else
echo "Backup Skipped"
# if文完了。
fi
echo "Shell Backup Done"
exit 0
③ PostgreSQLを起動、または再起動します。
これで準備完了となります。
実行方法は変わらずの為、省略しますが、以下に結果ログ（抜粋）を記載します。
Shell Backup Start
base.tar
2022-11-29 09:47:01.689 UTC [24642] LOG: checkpoint starting: force wait
2022-11-29 09:47:01.769 UTC [24642] LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.002 s, sync=0.001 s, total=0.081 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16384
kB, estimate=40271 kB
Backup OK
Shell Backup Done
Shell Backup Start
backup_manifest
Backup skipped
Shell Backup Done
1回目(%fで渡された値がbase.tar)の時はバックアップを取得し、2回目(%fで渡された値がbackup_manifest）の場合はバックアップ未実施となっていることがわかります。
言い換えると、この例はpg_basebackupで渡されたbase.tarを使わずに独自のバックアップを実行している形とも言えます。
[参考] basebackup_to_shellに関する補足 ～スクリプトの中身について～
スクリプトに「cat - > /dev/null」を入れないと以下のように失敗します。(バックアップ自体はできていますが、、、）
pg_basebackup: error: backup failed: ERROR: could not write to shell backup program: Broken pipe
basic_archive
これまでpostgresql.confの「archive_command」で指定したコマンドでWALアーカイブを取得していましたが、本モジュールを使用してアーカイブ取得が可能となりました。
本モジュールを使用することで、処理が軽減されるようです。
（本モジュールはコピーと同じ動作をします）
※ なお、このモジュールは開発者が独自実装するアーカイブライブラリのサンプルでもあり、これを参考に独自アーカイブライブラリ作成を行うこともできます。
まずはこのモジュールを使う際の設定について記載します。
① postgresql.confに設定を行います。
archive_mode = on
# basic_archiveを使用する為の設定。
archive_library = 'basic_archive'
# アーカイブの退避先ディレクトリを記載。今回は動確の為、tmpフォルダ配下とする
basic_archive.archive_directory = '/tmp/archive_wal'
# 以下は以前は設定していたかもしれないが記載不要となる
archive_command = ''
② 設定完了後、PostgreSQLの起動、あるいは再起動を実行します。
これにて設定完了です。
以下で動確を行います。
① アーカイブ取得ディレクトリに何もない事を確認します
ls /tmp/archive_wal/
② psqlでDBに接続してSQLを実行します(WALを吐き出させて、アーカイブログを作る）
# 試験用のDB作成
create database testdb001 ;
### psqlでtestdb001につなぎ直す
# 試験用のテーブル作成
create table ex1( id int , memo text ) ;
# 1000レコード追加。
insert into ex1 select round( random() * 100), 'test' || int_num from generate_series(1, 1000) as int_num;
# データの確認。
select * from ex1 limit 10 ;
id | memo
----+--------
21 | test1
51 | test2
24 | test3
77 | test4
14 | test5
21 | test6
30 | test7
98 | test8
82 | test9
57 | test10
(10 rows)
# アーカイブ掃き出し。
select pg_switch_wal() ;
③ 再度アーカイブログディレクトリを確認する
ls /tmp/archive_wal/
# 以下、出力
000000010000000000000042
000000010000000000000043
000000010000000000000044
問題なく、アーカイブが配置されていることが確認できます。
[参考] basic_archiveに関する補足 ～本モジュールと旧方式の同時指定～
以下のようにpostgresql.confで、basic_archiveを使用する為の指定と、以前の方式だったarchive_commandを両方セットした場合は、basic_archiveの方が優先されます(postgreSQL15時点では)。
archive_mode = on
archive_library = 'basic_archive'
basic_archive.archive_directory = '/tmp/archive_wal2'
archive_command = 'cp %p /tmp/archive_wal3/%f
上記の例でいえば、/tmp/archive_wal2 ディレクトリにWALアーカイブが保持され、archicve_commandで指定した/tmp/archive_wal3 ディレクトリには情報が格納されません。
[参考] pg_walinspect
これはSQLでWAL情報を確認することができるもので、コマンドで実行していたpg_waldumpと似たような事が可能となります。
準備として、以下を実行する必要があります。
create extension pg_walinspect ;
実行例としては以下のようになります。
こういったものが表示できるのか、という参考程度に確認ください。
# 現時点のwal書き込み位置を取得する。(lsnとはwal内のアドレスを指す）
select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/97060180
# データ更新。（説明の都合上、basic_archiveの動作確認の際に使ったテーブルを活用）
update ex1 set memo = 'EFG' ;
# lsn値が変更される
select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/0/9708BC80
以下でpg_walinspect で使用できる関数の使用例を記載します。
※ 出力に（省略）とあるものは一部抜粋となる事、ご留意ください。
# pg_get_wal_record_info関数の実行例。
select * from pg_get_wal_record_info('0/97060180');
start_lsn | end_lsn | prev_lsn | xid | resource_manager | record_type | record_length | main_data_length | fpi_length | description | block_ref
------------+------------+------------+-----+------------------+-------------+---------------+------------------+------------+------------------------------------------+----------------------------------------------------------------------------------------
0/97060180 | 0/97061D98 | 0/97060148 | 0 | Heap2 | PRUNE | 7187 | 8 | 7128 | latestRemovedXid 0 nredirected 0 ndead 1 | blkref #0: rel 1663/16388/1255 fork main blk 44 (FPW); hole: offset: 176, length: 1064
(1 row)
# pg_get_wal_records_info関数の実行例。
select * from pg_get_wal_records_info('0/97060180', '0/9708BC80');
start_lsn | end_lsn | prev_lsn | xid | resource_manager | record_type | record_length | main_data_length | fpi_length | description | block_ref
------------+------------+------------+-----+------------------+---------------+---------------+------------------+------------+---------------------------------------------------------+-------------------------------------------------------------------------------------------------
0/97060180 | 0/97061D98 | 0/97060148 | 0 | Heap2 | PRUNE | 7187 | 8 | 7128 | latestRemovedXid 0 nredirected 0 ndead 1 | blkref #0: rel 1663/16388/1255 fork main blk 44 (FPW); hole: offset: 176, length: 1064
0/97061D98 | 0/97061DD0 | 0/97060180 | 0 | Standby | RUNNING_XACTS | 50 | 24 | 0 | nextXid 765 latestCompletedXid 764 oldestRunningXid 765 |
0/97061DD0 | 0/97063290 | 0/97061D98 | 765 | Heap | HOT_UPDATE | 5281 | 14 | 5216 | off 76 xmax 765 flags 0x01 ; new off 1 xmax 0 | blkref #0: rel 1663/16388/16405 fork main blk 5 (FPW); hole: offset: 864, length: 2976
0/97063290 | 0/970632D8 | 0/97061DD0 | 765 | Heap | HOT_UPDATE | 71 | 14 | 0 | off 77 xmax 765 flags 0x20 ; new off 2 xmax 0 | blkref #0: rel 1663/16388/16405 fork main blk 5
（省略）
# pg_get_wal_records_info_till_end_of_wal関数の実行例。
select * from pg_get_wal_records_info_till_end_of_wal('0/9708BC80');
start_lsn | end_lsn | prev_lsn | xid | resource_manager | record_type | record_length | main_data_length | fpi_length | description | block_ref
------------+------------+------------+-----+------------------+-------------------+---------------+------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------
0/9708F6D0 | 0/97090C40 | 0/9708F670 | 0 | Heap2 | PRUNE | 5463 | 8 | 5404 | latestRemovedXid 763 nredirected 1 ndead 1 | blkref #0: rel 1663/16388/2619 fork main blk 1 (FPW); hole: offset: 148, length: 2788
0/97090C40 | 0/97091BB8 | 0/9708F6D0 | 766 | Heap | HOT_UPDATE | 3957 | 14 | 3892 | off 1 xmax 766 flags 0x00 ; new off 2 xmax 0 | blkref #0: rel 1663/16388/2619 fork main blk 2 (FPW); hole: offset: 140, length: 4300
（省略）
# pg_get_wal_stats関数の実行例。
select * from pg_get_wal_stats('0/97060180', '0/9708BC80', true) ;
resource_manager/record_type | count | count_percentage | record_size | record_size_percentage | fpi_size | fpi_size_percentage | combined_size | combined_size_percentage
------------------------------+-------+----------------------+-------------+------------------------+----------+---------------------+---------------+--------------------------
Transaction/COMMIT | 1 | 0.053390282968499736 | 34 | 0.02662573612329282 | 0 | 0 | 34 | 0.01984682917717381
Standby/RUNNING_XACTS | 1 | 0.053390282968499736 | 50 | 0.03915549429896003 | 0 | 0 | 50 | 0.02918651349584384
Heap2/PRUNE | 1 | 0.053390282968499736 | 59 | 0.046203483272772836 | 7128 | 16.342626559060896 | 7187 | 4.195269449892594
Heap/UPDATE | 866 | 46.23598505072077 | 71012 | 55.61019922315499 | 0 | 0 | 71012 | 41.451853927337254
Heap/HOT_UPDATE | 130 | 6.940736785904965 | 9218 | 7.218706928956271 | 12008 | 27.531181217901686 | 21226 | 12.390258709255628
Heap/LOCK | 870 | 46.449546182594766 | 46995 | 36.80224909159253 | 24480 | 56.126192223037414 | 71475 | 41.72212104230877
Heap/UPDATE+INIT | 4 | 0.21356113187399894 | 328 | 0.2568600426011778 | 0 | 0 | 328 | 0.1914635285327356
(7 rows)
# pg_get_wal_stats_till_end_of_wal関数の実行例。
select * from pg_get_wal_stats_till_end_of_wal('0/9708BC80', true);
resource_manager/record_type | count | count_percentage | record_size | record_size_percentage | fpi_size | fpi_size_percentage | combined_size | combined_size_percentage
------------------------------+-------+--------------------+-------------+------------------------+----------+---------------------+---------------+--------------------------
XLOG/CHECKPOINT_ONLINE | 2 | 5.714285714285714 | 228 | 3.730366492146597 | 0 | 0 | 228 | 0.7085146053449347
Transaction/COMMIT | 1 | 2.857142857142857 | 82 | 1.3416230366492146 | 0 | 0 | 82 | 0.254816656308266
Standby/RUNNING_XACTS | 7 | 20 | 350 | 5.726439790575916 | 0 | 0 | 350 | 1.0876320696084525
Standby/INVALIDATIONS | 1 | 2.857142857142857 | 90 | 1.4725130890052356 | 0 | 0 | 90 | 0.27967681789931637
Heap2/PRUNE | 7 | 20 | 2648 | 43.324607329842934 | 12636 | 48.47322387601657 | 15284 | 47.49533871970168
Heap2/VACUUM | 5 | 14.285714285714286 | 1980 | 32.39528795811518 | 0 | 0 | 1980 | 6.152889993784959
Heap2/VISIBLE | 9 | 25.714285714285715 | 536 | 8.769633507853403 | 8192 | 31.425502531839804 | 8728 | 27.12243629583592
Heap/HOT_UPDATE | 2 | 5.714285714285714 | 145 | 2.3723821989528795 | 3892 | 14.93018259935553 | 4037 | 12.545059042883778
Heap/INPLACE | 1 | 2.857142857142857 | 53 | 0.8671465968586387 | 1348 | 5.171090992788093 | 1401 | 4.353635798632691
(9 rows)
前のlsn(prev_lsn)、トランザクションID(xid)、リソースマネージャー(resouce_manager)等、pg_waldumpの時に確認できたものが確認ができていそうですね。
HOT_UPDATEがされているか、といった傾向も確認ができます。
終わりに
この記事ではPostgreSQL15で追加されたcontribを実際に動かしながら解説しました。
追加でどのようなことができるようになったのか、なんとなく伝わりましたでしょうか。
この記事が参考になれば幸いです。
それでは、引き続きNTTテクノクロス Advent Calendar 2022 をお楽しみください。