PostgreSQL WAL growth on a 500 GB LVM volume
A PostgreSQL 15 primary runs on a RHEL 9 virtual machine. The database cluster, including pg_wal and the archive directory, sits on one 500 GB XFS logical volume mounted at /var/lib/pgsql; the volume is 92% full (about 40 GB free). For the past six hours pg_stat_archiver shows repeated failures because the archive_command cannot write to an NFS mount; as a result pg_wal has grown to 380 GB. Normal database growth is about 3 GB per day, and the business requires 20% free space at all times plus capacity for 90 days of growth. iostat shows the data disks at 70% utilization with 18 ms average latency; free RAM is 6 GB and the server has 32 GB. The monitoring team only alerts on filesystem usage above 95%, and no one has checked inode usage or WAL archive age. A maintenance window is available tonight.
1. What is the immediate cause of the space pressure, and what is the safe order of remediation? Include the commands or views you would use.
Read the worked answer
Start with evidence, not deletion. Query pg_stat_archiver for failed_count, last_failed_time, and last_archived_wal; count .ready files in pg_wal/archive_status; check pg_wal size and the NFS mount permissions from the postgres user. The 380 GB pg_wal is almost certainly retained WAL, not normal WAL, because archive_command has been failing for six hours. The safe order is: (1) stop application writes if free space falls below the emergency threshold; (2) fix the NFS export or permission and the archive destination; (3) run the exact archive_command manually as postgres on one .ready file; (4) confirm pg_stat_archiver advances; (5) force a checkpoint or switch WAL only after archiving works, so recycled segments are removed by PostgreSQL. Never rm files under pg_wal; that breaks recovery and can corrupt the cluster. Extending the LV is a parallel safety measure if free space is under 10%, but it does not fix the archive failure. The immediate cause is archiving failure, with database growth as a secondary trend.
2. Calculate whether the current volume can meet the 90-day requirement and propose a concrete capacity change.
Read the worked answer
Current used is 460 GB. At 3 GB/day, 90 days adds 270 GB, so base data reaches 730 GB. The 20% free requirement means usable capacity must be at least 730 / 0.8 = 912.5 GB. The existing 500 GB volume cannot meet this; extend by at least 413 GB. Choose a 500 GB extension to leave operational slack and round to array or LUN granularity, giving 1000 GB total. Verify the underlying datastore has free capacity and that the LVM PV can grow; for XFS, run lvextend then xfs_growfs while mounted. Do not size only to 730 GB, because that leaves 0% headroom and ignores WAL spikes and future index growth. If thin provisioning is used, check pool overcommit. Also note the 3 GB/day figure is an average; use peak weekly growth or a 95th percentile for the final decision. Schedule the extension before the next backup window and set a review date at 60 days.
3. Which metrics and alert thresholds should be added so this does not recur silently?
Read the worked answer
Alert at 75% and 85%, not 95%. Add pg_wal size, archive lag (now - last_archived_time), failed archive count, .ready file count, filesystem free bytes and inodes, and disk await/utilization. A 95% threshold on a 500 GB volume leaves only 25 GB, which is under two weeks of growth and smaller than a WAL spike. Inode alerts matter if the workload creates many small files; XFS inode exhaustion is independent of byte usage. Use trend-based alerts: projected days to 85% from a 7-day slope. Add a check that archive_command succeeds after every WAL switch, and a synthetic restore test. Capacity review should include database bloat and autovacuum effectiveness, because 3 GB/day may be bloat rather than real data growth.