Technical Tip: CMDB Drive Filling up Due to pg_wal Taking up Space and how to remediate

aebadi · ‎09-09-2024

Description

This article describes how to determine if the CMDB disk is filling up due to pg_wal files taking up space consumption and how to remediate those files.

Scope

FortiSIEM.

Solution

pg_wal is known as a Write-Ahead Log or a Transaction log. A log is a record of all the events or changes and WAL data is just a description of changes made to the actual data. So, it is ‘data about data’ or metadata.

Troubleshoot the space consumption:

Log into the supervisor and check the current space consumption:

[root@AH-SUPER ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 32G 0 32G 0% /dev
tmpfs 32G 40K 32G 1% /dev/shm
tmpfs 32G 25M 32G 1% /run
tmpfs 32G 0 32G 0% /sys/fs/cgroup
/dev/mapper/cl-root 22G 13G 9.5G 57% /
/dev/sdb2 76G 23G 54G 30% /opt
/dev/sdd1 60G 462M 60G 1% /svn
/dev/sdc1 60G 50G 11G 84% /cmdb <---
/dev/sda1 974M 791M 116M 88% /boot
10.65.48.37:/ed 98G 15G 79G 16% /archive
10.65.48.37:/FortiSIEM 22G 11G 11G 49% /data
tmpfs 6.3G 0 6.3G 0% /run/user/500
tmpfs 6.3G 0 6.3G 0% /run/user/0

The /cmdb directory is filling up, next step will be to log into that directory and check what is consuming the space:

[root@Supervisor ~]# cd /cmdb/
sudo du --max-depth=4 -xh /cmdb | sort -hr | head -n 20
3.9G /cmdb/data
3.9G /cmdb
3.4G /cmdb/data/base/16384
3.4G /cmdb/data/base
466M /cmdb/data/pg_commit_ts
50G /cmdb/data/pg_wal <---
12M /cmdb/data/pg_xact
7.8M /cmdb/data/base/13434
7.6M /cmdb/data/base/13433
7.6M /cmdb/data/base/1
720K /cmdb/data/global
404K /cmdb/data/pg_stat_tmp
192K /cmdb/data/pg_subtrans
16K /cmdb/data/pg_multixact
8.0K /cmdb/data/pg_multixact/offsets
8.0K /cmdb/data/pg_multixact/members
8.0K /cmdb/data/pg_log
4.0K /cmdb/data/pg_replslot/cluster_10_65_48_46
4.0K /cmdb/data/pg_replslot
4.0K /cmdb/data/pg_logical
[root@AH-SUPER pg_wal]#

From the above results, the file pg_wal in the /cmdb/data folder path is taking the bulk of the space: 55G /cmdb/data/pg_wal.
The current WAL file by running the following command:

[root@Supervisor]# pg_controldata /cmdb/data | grep "REDO WAL"
Latest checkpoint's REDO WAL file: 00000001000000B2000000EB

The last checkpoint REDO WAL file obtained, use that value to delete already processed WAL files, and add the WAL file found in step 4 in the delete statement below:

find -L /cmdb/data/pg_wal \
-not -path /cmdb/data/pg_wal/archive_status/* \
-not -samefile /cmdb/data/pg_wal/archive_status \
-not -newer /cmdb/data/pg_wal/00000001000000B2000000EB \
-not -samefile /cmdb/data/pg_wal/00000001000000B2000000EB \
-exec rm {} \;

Verify that the space has been reclaimed and is no longer in critical disk space:

sudo du --max-depth=4 -xh /cmdb | sort -hr | head -n 20
3.9G /cmdb/data
3.9G /cmdb
3.4G /cmdb/data/base/16384
3.4G /cmdb/data/base
466M /cmdb/data/pg_commit_ts
33M /cmdb/data/pg_wal
12M /cmdb/data/pg_xact
7.8M /cmdb/data/base/13434
7.6M /cmdb/data/base/13433
7.6M /cmdb/data/base/1
720K /cmdb/data/global
404K /cmdb/data/pg_stat_tmp
192K /cmdb/data/pg_subtrans
16K /cmdb/data/pg_multixact
8.0K /cmdb/data/pg_multixact/offsets
8.0K /cmdb/data/pg_multixact/members
8.0K /cmdb/data/pg_log
4.0K /cmdb/data/pg_replslot/cluster_10_65_48_46
4.0K /cmdb/data/pg_replslot
4.0K /cmdb/data/pg_logical