スタンバイサーバーのpg_walディレクトリに大量のWALファイルが残ったままになる事象が発生しました。
結論から書くと、レプリケーションスロットの削除漏れでした。
参照されないレプリケーションスロットは削除しましょう。
状況
[root@erogamescape13 ap2]# cd /var/lib/pgsql/11/data/pg_wal/
[root@erogamescape13 pg_wal]# du -h
12K ./archive_status
2.4G .
[root@erogamescape13 pg_wal]# ls
00000007.history 000000090000000B000000A1 000000090000000B000000C9 000000090000000B000000F1
00000008.history 000000090000000B000000A2 000000090000000B000000CA 000000090000000B000000F2
000000080000000B0000007D 000000090000000B000000A3 000000090000000B000000CB 000000090000000B000000F3
00000009.history 000000090000000B000000A4 000000090000000B000000CC 000000090000000B000000F4
000000090000000B0000007D 000000090000000B000000A5 000000090000000B000000CD 000000090000000B000000F5
000000090000000B0000007E 000000090000000B000000A6 000000090000000B000000CE 000000090000000B000000F6
000000090000000B0000007F 000000090000000B000000A7 000000090000000B000000CF 000000090000000B000000F7
000000090000000B00000080 000000090000000B000000A8 000000090000000B000000D0 000000090000000B000000F8
000000090000000B00000081 000000090000000B000000A9 000000090000000B000000D1 000000090000000B000000F9
000000090000000B00000082 000000090000000B000000AA 000000090000000B000000D2 000000090000000B000000FA
000000090000000B00000083 000000090000000B000000AB 000000090000000B000000D3 000000090000000B000000FB
000000090000000B00000084 000000090000000B000000AC 000000090000000B000000D4 000000090000000B000000FC
000000090000000B00000085 000000090000000B000000AD 000000090000000B000000D5 000000090000000B000000FD
000000090000000B00000086 000000090000000B000000AE 000000090000000B000000D6 000000090000000B000000FE
000000090000000B00000087 000000090000000B000000AF 000000090000000B000000D7 000000090000000B000000FF
000000090000000B00000088 000000090000000B000000B0 000000090000000B000000D8 000000090000000C00000000
000000090000000B00000089 000000090000000B000000B1 000000090000000B000000D9 000000090000000C00000001
000000090000000B0000008A 000000090000000B000000B2 000000090000000B000000DA 000000090000000C00000002
000000090000000B0000008B 000000090000000B000000B3 000000090000000B000000DB 000000090000000C00000003
000000090000000B0000008C 000000090000000B000000B4 000000090000000B000000DC 000000090000000C00000004
000000090000000B0000008D 000000090000000B000000B5 000000090000000B000000DD 000000090000000C00000005
000000090000000B0000008E 000000090000000B000000B6 000000090000000B000000DE 000000090000000C00000006
000000090000000B0000008F 000000090000000B000000B7 000000090000000B000000DF 000000090000000C00000007
000000090000000B00000090 000000090000000B000000B8 000000090000000B000000E0 000000090000000C00000008
000000090000000B00000091 000000090000000B000000B9 000000090000000B000000E1 000000090000000C00000009
000000090000000B00000092 000000090000000B000000BA 000000090000000B000000E2 000000090000000C0000000A
000000090000000B00000093 000000090000000B000000BB 000000090000000B000000E3 000000090000000C0000000B
000000090000000B00000094 000000090000000B000000BC 000000090000000B000000E4 000000090000000C0000000C
000000090000000B00000095 000000090000000B000000BD 000000090000000B000000E5 000000090000000C0000000D
000000090000000B00000096 000000090000000B000000BE 000000090000000B000000E6 000000090000000C0000000E
000000090000000B00000097 000000090000000B000000BF 000000090000000B000000E7 000000090000000C0000000F
000000090000000B00000098 000000090000000B000000C0 000000090000000B000000E8 000000090000000C00000010
000000090000000B00000099 000000090000000B000000C1 000000090000000B000000E9 000000090000000C00000011
000000090000000B0000009A 000000090000000B000000C2 000000090000000B000000EA 000000090000000C00000012
000000090000000B0000009B 000000090000000B000000C3 000000090000000B000000EB 000000090000000C00000013
000000090000000B0000009C 000000090000000B000000C4 000000090000000B000000EC 000000090000000C00000014
000000090000000B0000009D 000000090000000B000000C5 000000090000000B000000ED archive_status
000000090000000B0000009E 000000090000000B000000C6 000000090000000B000000EE
000000090000000B0000009F 000000090000000B000000C7 000000090000000B000000EF
000000090000000B000000A0 000000090000000B000000C8 000000090000000B000000F0
レプリケーションスロットの確認
ap2=# SELECT * FROM pg_replication_slots; slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn -----------------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+--------------------- replication_slot_for_sakura | | physical | | | f | t | 31390 | | | C/1499CEC8 | replication_slot | | physical | | | f | f | | | | B/7D5B46A0 | (2 行)
replication_slotというの名前のレプリケーションスロットのactiveはfなので使用していません。
このサーバーがprimaryだったことがあって、その際に作成したのが、replication_slotです。
フェイルオーバーが発生して、このサーバーをstandbyとして組み込んだのですが、その際にreplication_slotを削除していませんでした。
replication_slotを削除しないと、replication_slotを参照するstandbyサーバーが出現したときのためにWALをずっと保存し続けます。
レプリケーションスロットを削除します。
ap2=# select pg_drop_replication_slot('replication_slot'); pg_drop_replication_slot -------------------------- (1 行) ap2=# SELECT * FROM pg_replication_slots; slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn -----------------------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+--------------------- replication_slot_for_sakura | | physical | | | f | t | 31390 | | | C/14B09F10 | (1 行)WALの保存設定はPostgreSQL11のデフォルト設定のももなので、無事WALファイルが自動的に削除されました。
[root@erogamescape13 pg_wal]# ls 00000007.history 00000009.history 000000090000000C00000015 000000090000000C00000017 archive_status 00000008.history 000000090000000C00000014 000000090000000C00000016 000000090000000C00000018 [root@erogamescape13 pg_wal]# du -h 12K ./archive_status 81M .
参考