WAL archiving stalls and primary reports 'could not flush file' during checkpoint under nightly bulk load

Environment

PostgreSQL 13.7 on Debian 11 VM with LVM thin-provisioned ext4 data volume hosted on shared SAN; wal_level = replica, archive_mode = on with archive_command copying WAL to a remote NFS archive server; nightly bulk import job inserts ~200M rows over several hours.

Symptoms

During nightly bulk import the primary occasionally blocks at checkpoints and reports repeated errors of the form: 'could not flush file

Observations

pg_wal contains many files awaiting archiving; archive_command processes show repeated 'No space left on device' on the NFS mount for the archive server at the time of failure. Kernel logs (/var/log/kern.log) show 'NFS: server not responding' and writeback timeouts on the VM's block device around checkpoint times. iostat shows write latency spikes on the local SAN LUN preceding the PostgreSQL errors. After freeing space on the NFS archive target, archive_command resumes but PostgreSQL still experiences long checkpoint durations and subsequent WAL file growth.

Already attempted

Manually cleared old archives on the NFS server to restore space, retried archive_command which succeeded; adjusted archive_command to use rsync with --partial to reduce retries; increased max_wal_size temporarily from 2GB to 8GB to delay checkpoints; monitored IO with iostat and vmstat; rebooted the VM when writeback timeouts persisted. Restoring space allowed archive to finish, but intermittent SAN writeback latency still correlated with checkpoints.

Question

What combination of reversible configuration changes and diagnostics will reliably prevent WAL archiving stalls and checkpoint 'could not flush file' errors during heavy bulk loads, and how can we determine whether the root cause is NFS archive target running out of space, SAN write latency under heavy writeback, or a misconfiguration of archive_command/concurrency?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely causes

Archive target (NFS) ran out of space or became unresponsive, causing archive_command to fail and WAL files to pile up, leading to long checkpoint operations. 90%

Shared SAN LUN experienced transient high write latency or writeback stalls under heavy sequential/random writes from the bulk import, causing local fsyncs to time out. 85%

archive_command or its wrapper doesn't handle transient NFS failures or space errors gracefully (no retry/backoff), allowing WAL accumulation to reach critical size. 60%

Summary

The immediate cause of the 'could not flush file' errors is inability to complete WAL write/flush operations within the database because the underlying storage or archiving path is slow or failing. Two separate storage layers are implicated: (1) the NFS archive target reporting 'No space left' prevented archive_command from moving WAL files, causing WAL accumulation; (2) SAN I/O writeback timeouts indicate the primary's local storage experienced high write latency, which during heavy checkpoints causes database fsync/flush operations to block and error. Both layers must be addressed: free, reliable archive target capacity and mitigate SAN latency under bulk writes (or throttle the PostgreSQL checkpoints/commit behavior).

Suggested Fixes

Diagnostic step 1

Immediately ensure WAL archives have space: free space on NFS archive target, verify remote disk usage (df -h) and available inodes (df -i); do not delete WAL files on primary—only remove safely archived files from the archive server after verifying backups.

Diagnostic step 2

Switch archive_command to a robust wrapper that logs failures and retries with exponential backoff, and consider archiving to a local fast staging disk before pushing to NFS to decouple database I/O from network storage.

Diagnostic step 3

Temporarily increase max_wal_size and checkpoint_timeout to reduce checkpoint frequency during heavy imports; additionally set wal_compression = on to reduce WAL volume if CPU permits.