PostgreSQL pg_log Disk Full & Database Lockfile Error on WSL2 Ubuntu

Resolve critical PostgreSQL database lock and unavailability caused by `pg_log` disk space exhaustion on Windows WSL2 Ubuntu. Learn to free space and restore service.

When running PostgreSQL within a Windows Subsystem for Linux 2 (WSL2) Ubuntu environment, one of the most debilitating issues you can encounter is a complete database outage due to disk space exhaustion. This often manifests as the database failing to start, or existing connections dropping, accompanied by errors related to pg_log and lock files. As a DevOps engineer, understanding the intricate relationship between WSL2's virtual disk, PostgreSQL's logging mechanisms, and system resources is crucial for rapid recovery.

Symptom & Error Signature

Users will typically experience applications failing to connect to the PostgreSQL database, or the database server itself refusing to start. Attempts to connect via psql will often result in connection refused errors.

Common error messages found in PostgreSQL logs (often /var/log/postgresql/postgresql-*.log) or when trying to start PostgreSQL manually with pg_ctl or systemctl:

2023-10-27 10:30:05.123 UTC [12345] LOG:  database system was shut down at 2023-10-27 10:30:04 UTC
2023-10-27 10:30:05.124 UTC [12345] FATAL:  could not open relation mapping file "global/pg_filenode.map": No space left on device
2023-10-27 10:30:05.124 UTC [12345] LOG:  startup process (PID 12345) exited with exit code 1
2023-10-27 10:30:05.124 UTC [12346] LOG:  aborting startup due to startup process failure
2023-10-27 10:30:05.124 UTC [12346] LOG:  database system is shut down

Or, when trying to start:

pg_ctl: could not start server
Examine the log output.

And within the logs, you might see:

2023-10-27 10:35:01.567 UTC [12347] FATAL:  could not open lock file "/var/lib/postgresql/14/main/postmaster.pid": No space left on device

When checking system status:

sudo systemctl status postgresql.service

× postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Mon 2023-10-27 10:35:01 UTC; 1min 2s ago
    Process: 12347 ExecStart=/usr/bin/pg_ctlcluster 14 main start (code=exited, status=1/FAILURE)
        CPU: 12ms

Oct 27 10:35:01 ubuntu-wsl systemd[1]: Starting PostgreSQL RDBMS...
Oct 27 10:35:01 ubuntu-wsl pg_ctlcluster[12347]: FATAL:  could not open lock file "/var/lib/postgresql/14/main/postmaster.pid": No space left on device
Oct 27 10:35:01 ubuntu-wsl systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
Oct 27 10:35:01 ubuntu-wsl systemd[1]: postgresql.service: Failed with result 'exit-code'.
Oct 27 10:35:01 ubuntu-wsl systemd[1]: Failed to start PostgreSQL RDBMS.

Root Cause Analysis

The core of this problem lies in the No space left on device error, which prevents PostgreSQL from performing critical operations like writing to its transaction log, creating temporary files, or even opening essential lock files like postmaster.pid.

Here's a breakdown of the underlying reasons:

1. pg_log Directory Overgrowth: PostgreSQL's logging can be verbose. If not properly configured with log rotation (e.g., logrotate or PostgreSQL's internal logging_collector settings), the log files in /var/lib/postgresql/<version>/main/log (or /var/log/postgresql/ depending on configuration) can consume all available disk space. This is especially prevalent in development or staging environments where default logging levels are often high and monitoring/maintenance is less rigorous.
2. WSL2 Virtual Disk Exhaustion: WSL2 distributions store their entire filesystem in a virtual hard disk (VHDX file) on your Windows host machine. While WSL2 automatically expands this VHDX as needed, it has a default maximum size (often 256GB or more, but configurable). If the accumulated data, including large pg_log files, reaches this limit or exhausts the underlying Windows drive space, you'll hit a No space left on device error inside WSL2.
3. Stale Lock Files: When disk space is exhausted, PostgreSQL might fail to gracefully shut down or clean up its temporary files and lock files. Specifically, postmaster.pid (located in the data directory, e.g., /var/lib/postgresql/14/main/) indicates that a PostgreSQL server instance is running. If this file exists but no process is actually running (due to a crash or failed startup), or if the system cannot write to it due to full disk, PostgreSQL will refuse to start to prevent data corruption.

Step-by-Step Resolution

Follow these steps to diagnose, resolve, and prevent recurrence of the PostgreSQL disk space and lock file issue on WSL2.

1. Assess Disk Space Usage within WSL2

First, confirm that disk space is indeed the culprit.

df -h

Look for 100% usage on / (root filesystem) or a very high percentage.

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda        256G  256G     0 100% /
tmpfs           7.8G     0  7.8G   0% /mnt/wsl
...

Then, identify the largest directories. pg_log is a prime suspect.

sudo du -sh /var/lib/postgresql/*/main/log/
sudo du -sh /var/log/postgresql/
sudo du -sh /var/lib/postgresql/
sudo du -sh /var/

Use sudo du -sh /* to quickly see which top-level directories are consuming the most space. You can then drill down, e.g., sudo du -sh /var/*.

2. Stop the PostgreSQL Service

It's crucial to stop PostgreSQL to safely manage its files. If it's already failed to start, this step might just confirm its inactive state.

sudo systemctl stop postgresql

If systemctl hangs or reports the service is still running after a stop attempt, you may need to manually terminate any rogue PostgreSQL processes:

sudo ps aux | grep postgres

Identify any processes still running (especially postmaster) and terminate them.

sudo kill -9 <PID_of_postmaster>

Using kill -9 should be a last resort. It can lead to data inconsistencies if the database was actively writing. Ensure no clients are connected and you've tried systemctl stop first.

3. Free Up Disk Space

This is the most critical step. You need to create enough free space for PostgreSQL to operate.

a. Clean PostgreSQL Logs (pg_log)

Navigate to your PostgreSQL log directory and prune old logs.

# Example: PostgreSQL 14
cd /var/lib/postgresql/14/main/log/
# Or if logs are in /var/log/postgresql/
# cd /var/log/postgresql/

List files by size:

sudo ls -lhS

Delete or move older, larger log files. Prioritize deleting if space is critically low.

# Delete log files older than, say, 7 days (USE WITH CAUTION)
sudo find . -name "*.log" -type f -mtime +7 -delete

# Alternatively, move them to a temporary location outside the WSL2 disk (e.g., Windows drive)
# Assuming /mnt/c is your C drive
sudo mv old_log_file.log /mnt/c/wsl_temp_logs/

Do NOT delete postgresql.conf or other essential configuration files. Stick to log files. Always double-check filenames before deleting.

b. Clear APT Cache

The package manager cache can sometimes grow large.

sudo apt clean
sudo apt autoremove --purge

c. Identify and Delete Other Large Files (Optional)

If pg_log wasn't the sole culprit, use du to find other large directories.

sudo du -h --max-depth=1 /

Drill down into large directories as needed and delete unnecessary files (e.g., old backups, temporary files, docker images if not managed).

4. Clear Stale Lock Files

With some disk space freed, you can now safely remove any stale postmaster.pid files that might be preventing PostgreSQL from starting.

# Assuming PostgreSQL 14. Adjust version as necessary.
sudo rm -f /var/lib/postgresql/14/main/postmaster.pid

Only remove postmaster.pid if you are absolutely certain that no PostgreSQL instance is running. Removing it while a database is active can lead to data corruption. This step is safe after you've stopped the service (Step 2) and verified no processes are running.

5. Reclaim WSL2 Virtual Disk Space (Optional but Recommended)

Even after deleting files within WSL2, the VHDX file on your Windows host doesn't automatically shrink. To reclaim space on your Windows drive:

a. Shut Down WSL2

Open Windows PowerShell (as Administrator) or Command Prompt (as Administrator).

wsl --shutdown

This terminates all running WSL2 distributions.

b. Compact the VHDX File

Locate your WSL2 VHDX file. For Ubuntu, it's typically: %LOCALAPPDATA%Packages<DistroName>LocalStateext4.vhdx

For example: C:Users<YourUser>AppDataLocalPackagesCanonicalGroupLimited.UbuntuonWindows_...LocalStateext4.vhdx

# Open diskpart
diskpart

# List virtual disks
# Look for the .vhdx file corresponding to your WSL2 distro
# It might appear as a "Disk" with no drive letter if not mounted by Windows.
list vdisk

# Select your vdisk by its path (replace with your actual path)
select vdisk file="C:Users<YourUser>AppDataLocalPackagesCanonicalGroupLimited.UbuntuonWindows_...LocalStateext4.vhdx"

# Attach the vdisk for compaction
attach vdisk

# Perform the compaction
compact vdisk

# Detach the vdisk
detach vdisk

# Exit diskpart
exit

Compacting the VHDX can take a long time depending on its size and fragmentation. Ensure your Windows machine has enough free space to facilitate the compaction process.

6. Restart PostgreSQL

Now, attempt to start the PostgreSQL service.

sudo systemctl start postgresql

Check its status:

sudo systemctl status postgresql

You should see an active (running) status.

7. Verify Database Operation

Connect to your database using psql or your application to confirm full functionality.

sudo -u postgres psql

psql (14.x)
Type "help" for help.

postgres=# l

If you can list databases and perform queries, your database is back online.

8. Implement Preventive Measures

To avoid future disk space related outages:

a. Configure PostgreSQL Logging & Log Rotation

Review and adjust PostgreSQL's logging parameters in postgresql.conf.

sudo nano /etc/postgresql/14/main/postgresql.conf

Key parameters:

• logging_collector = on
• log_directory = 'log' (relative to data directory) or '/var/log/postgresql' (absolute path, usually handled by OS logrotate)
• log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
• log_truncate_on_rotation = on
• log_rotation_age = 1d (rotate daily)
• log_rotation_size = 100MB (rotate if size exceeds 100MB)
• log_min_duration_statement = -1 (log all statements, useful for debugging, but set to a positive value or 0 for production to log slow queries only)
• log_connections = off
• log_disconnections = off

If using the default log_directory = 'log' (inside the data directory), PostgreSQL handles its own rotation. If log_directory points to /var/log/postgresql, then logrotate is typically used.

b. Configure logrotate for System-Managed PostgreSQL Logs

If your postgresql.conf directs logs to /var/log/postgresql/, ensure logrotate is configured. Ubuntu usually sets this up by default during PostgreSQL installation.

Check the configuration:

cat /etc/logrotate.d/postgresql-common

Ensure it's configured to rotate and compress logs, keeping a reasonable number of old files.

# Example /etc/logrotate.d/postgresql-common
/var/log/postgresql/*.log {
    daily
    missingok
    rotate 7
    compress
    delaycompress
    notifempty
    create 0640 postgres adm
    su postgres postgres
    postrotate
        # For Debian/Ubuntu, reload the service if it's running.
        # This will gently close and reopen log files.
        invoke-rc.d postgresql reload > /dev/null || true
    endscript
}

This configuration rotates logs daily, keeps 7 compressed old logs, and reloads PostgreSQL to ensure it writes to a new log file.

c. Implement Disk Space Monitoring

Set up alerts for disk space usage within your WSL2 environment. Tools like Prometheus Node Exporter paired with Prometheus and Grafana, or simple shell scripts with email notifications, can provide early warnings.

# Example: Basic disk usage check for a cron job
echo "Disk Usage Report:" | mail -s "WSL2 Disk Usage Alert" [email protected] < <(df -h)

By proactively managing logs and monitoring disk usage, you can prevent these critical outages and maintain the reliability of your PostgreSQL instances on WSL2.