Troubleshooting: PostgreSQL pg_log Disk Space Exhaustion & Database Lockfile Issues on Ubuntu 20.04 LTS

Resolve critical PostgreSQL database outages caused by pg_log disk space exhaustion leading to lockfile errors on Ubuntu 20.04 LTS servers.

When a PostgreSQL server runs out of disk space, it can lead to a cascade of critical failures, including an inability to write new data, create temporary files, or even manage its own internal process and lock files. A common culprit for disk space exhaustion is the unchecked growth of the pg_log directory. This guide will walk you through diagnosing and resolving disk space issues caused by verbose PostgreSQL logging, which can manifest as "database lock lockfile" errors, specifically on Ubuntu 20.04 LTS environments.

Symptom & Error Signature

Users attempting to connect to the PostgreSQL database or applications relying on it will experience connection failures or timeouts. In the server's logs, you'll observe errors indicating disk exhaustion and I/O failures.

Typical User-facing Errors:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  the database system is starting up

Or, if the database is completely unresponsive:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

System & PostgreSQL Log Entries (e.g., /var/log/syslog, journalctl -xe, /var/log/postgresql/*.log):

systemd[1]: [email protected]: Failed to start PostgreSQL Cluster 12-main.
systemd[1]: [email protected]: A start job for unit [email protected] has finished with a failure.
postgres[12345]: [2-1] LOG:  could not write to file "pg_wal/xlog/000000010000000A0000000E": No space left on device
postgres[12345]: [3-1] PANIC:  could not write to file "pg_wal/000000010000000A0000000E" at offset 16384: No space left on device
postgres[12345]: [4-1] LOG:  terminating because of crash or configuration limit
postgres[12345]: [5-1] LOG:  database system is shut down
postgres[12345]: [6-1] FATAL:  could not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": No space left on device

Disk Usage Check (df -h output):

Filesystem      Size  Used Avail Use% Mounted on
udev            7.8G     0  7.8G   0% /dev
tmpfs           1.6G  1.2M  1.6G   1% /run
/dev/sda1       499G  499G     0 100% /
tmpfs           7.8G     0  7.8G   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           7.8G     0  7.8G   0% /sys/fs/cgroup
/dev/loop0       56M   56M     0 100% /snap/core18/1705
tmpfs           1.6G     0  1.6G   0% /run/user/1000

Notice the /dev/sda1 (root partition) showing 100% usage and 0 Avail space.

Root Cause Analysis

The primary root cause for this issue is a lack of available disk space, almost always due to the uncontrolled growth of log files.

1. Unmanaged pg_log Directory: PostgreSQL, by default on Ubuntu, directs its logs to /var/log/postgresql/. If log rotation (e.g., via logrotate) is not properly configured or is failing, these log files can grow indefinitely, eventually consuming all available disk space on the root partition.
2. Verbose Logging Settings: Default PostgreSQL configurations (e.g., log_destination = 'stderr', logging_collector = on, log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log') are designed to capture extensive information. While valuable for debugging, without proper retention policies, this verbosity quickly fills disks.
3. "No space left on device": Once the disk is 100% full, the operating system cannot write any new data. This prevents PostgreSQL from:
   • Writing new log entries.
   • Creating or updating its Process ID (PID) file (postmaster.pid).
   • Managing its internal control and lock files (like .s.PGSQL.5432.lock or those within pg_wal), leading to the "could not create lock file" errors.
   • Performing any I/O operations required for normal database function or even graceful shutdown/startup.

The "database lock lockfile" error is a consequence of the "No space left on device" condition, not typically an independent locking issue. PostgreSQL simply cannot create or manage its necessary state and communication files on a full disk.

Step-by-Step Resolution

Follow these steps to diagnose, temporarily resolve, and permanently prevent the issue.

1. Verify Disk Space and Identify the Culprit

First, confirm that your disk is indeed full and locate the directory consuming the most space.

# Check overall disk usage
df -h

# Check inode usage (less common, but good to rule out)
df -i

# Identify top-level directories consuming space
sudo du -sh /* | sort -rh | head -n 10

# Drill down into /var (common location for logs)
sudo du -sh /var/* | sort -rh | head -n 10

# Specifically check PostgreSQL log directory
sudo du -sh /var/log/postgresql/

The du -sh /* command can take a long time on large file systems. Be patient. Focus on /var, /opt, and /usr/local as common locations for application data and logs.

2. Free Up Disk Space (Temporary Fix)

Once you've confirmed pg_log (or /var/log/postgresql/) is the culprit, you need to quickly free up enough space for the database to start.

Deleting log files directly can remove valuable diagnostic information. Only do this if you are confident you understand the impact or if the server is completely down and you need immediate recovery. If possible, archive logs before deleting.

Option A: Delete Old Log Files (Recommended for quick recovery)

# Navigate to the PostgreSQL log directory
cd /var/log/postgresql/

# List files by size to identify the largest ones
sudo ls -lhS

# Delete log files older than, for example, 7 days.
# Adjust the '-mtime +7' to a value that frees enough space.
# ALWAYS use -exec rm {} ; or -delete with caution!
sudo find . -name "*.log" -type f -mtime +7 -exec rm {} ;

# If you need more space, consider deleting ALL but the very newest logs.
# This command deletes all files ending in .log
# CAREFUL: This deletes without confirmation.
# sudo find . -name "*.log" -type f -delete

# Alternatively, if you only need to delete the very largest files:
# sudo rm <largest_log_file_name_1> <largest_log_file_name_2>

Option B: Truncate Large Log Files (If deletion is not an option)

Truncating a file sets its size to zero without deleting the file itself. This is sometimes safer if processes have open file handles to the logs, but rm followed by a restart is generally fine.

# Example: Truncate a specific large log file
sudo truncate -s 0 /var/log/postgresql/postgresql-12-main.log

After freeing space, verify with df -h again. You need at least a few hundred MBs, preferably a few GBs, free to allow PostgreSQL to start and operate.

3. Restart PostgreSQL Service

Once sufficient disk space is available, attempt to restart PostgreSQL.

sudo systemctl start postgresql
sudo systemctl status postgresql

If it starts successfully, you should see "active (exited)" or "active (running)" depending on the PostgreSQL service setup on your system, and your applications should be able to connect again.

4. Configure Log Rotation (Permanent Fix)

To prevent this issue from recurring, implement or correct logrotate configuration for your PostgreSQL logs. Ubuntu's PostgreSQL packages usually include a basic logrotate configuration, but it might need adjustment.

# Inspect the existing logrotate configuration for PostgreSQL
sudo cat /etc/logrotate.d/postgresql-common

A typical logrotate configuration for PostgreSQL might look like this:

/var/log/postgresql/*.log {
    daily
    missingok
    rotate 7
    compress
    delaycompress
    notifempty
    create 0640 postgres adm
    su postgres adm
    postrotate
        /usr/bin/pg_ctlcluster --skip-systemctl-redirect 12 main reload > /dev/null 2>&1 || true
    endscript
}

Explanation of common directives:

• daily: Rotate logs daily. Other options include weekly or monthly.
• rotate 7: Keep 7 rotated log files. Oldest files beyond this count are deleted.
• compress: Compress old log files.
• delaycompress: Delay compression until the next rotation cycle.
• notifempty: Don't rotate the log if it's empty.
• create 0640 postgres adm: Create new log files with specified permissions and ownership.
• su postgres adm: Run postrotate script as the postgres user.
• postrotate/endscript: Commands to run after log rotation. The pg_ctlcluster reload command tells PostgreSQL to reopen its log files, so new entries go to the newly created, empty log file.

Adjustments you might consider:

• rotate count: Increase or decrease based on disk space and retention needs.
• size directive: If you want to rotate logs based on size instead of (or in addition to) time, add size 100M (e.g., rotate if log reaches 100MB). If size is specified, rotation happens when either the time or size condition is met.
• maxsize directive: Similar to size, but specifically ensures rotation even if the time condition isn't met. If the log grows larger than maxsize, it's rotated immediately.

After modifying /etc/logrotate.d/postgresql-common, you can test the configuration without actually performing a rotation using logrotate -d /etc/logrotate.d/postgresql-common. To force a rotation (e.g., after cleanup), run sudo logrotate -f /etc/logrotate.d/postgresql-common.

5. Optimize PostgreSQL Logging Configuration (Optional, but Recommended)

For finer control over log file sizes and content, you can adjust settings directly in postgresql.conf. This complements logrotate by controlling how PostgreSQL itself manages log files before logrotate takes over.

# Find your postgresql.conf file
sudo find / -name postgresql.conf 2>/dev/null
# Typical location for Ubuntu 20.04 with PostgreSQL 12:
# /etc/postgresql/12/main/postgresql.conf

# Open the configuration file for editing
sudo nano /etc/postgresql/12/main/postgresql.conf

Look for the "Error Reporting and Logging" section and consider adjusting these parameters:

# LOGGING COLLECTOR
#-------------------------------------------------------------------------
logging_collector = on         # Enable capturing of stderr and csvlog
                               # output into log files.
                               # Recommended to be 'on' in production.

# log_directory = 'log'        # directory where log files are written,
                               # relative to PGDATA. (default is pg_log)
                               # We're using the system default /var/log/postgresql

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
                               # log file name pattern. Can include:
                               # %Y = year, %m = month, %d = day, %H = hour,
                               # %M = minute, %S = second, %a = weekday, %j = day of year,
                               # %p = AM/PM, %r = 12-hour time, %t = tab, %P = process ID
                               # (This pattern results in frequent file creation which is good for logrotate)

log_truncate_on_rotation = off # If on, log files with the same name as existing files
                               # will be truncated rather than appended to.
                               # This should be 'off' when logrotate is used,
                               # as logrotate handles moving and creating new files.

log_rotation_age = 1d          # Automatic log file rotation will occur after this much time.
                               # 'd' for days, 'h' for hours, 'm' for minutes.
                               # Can be useful if logrotate is set to weekly/monthly,
                               # but you want more frequent internal rotation.

log_rotation_size = 0          # Automatic log file rotation will occur after this size.
                               # 'kB', 'MB', 'GB' can be used. (0 disables)
                               # Setting this to a value like '100MB' ensures that
                               # logs don't grow excessively large even between logrotate runs.
                               # e.g., log_rotation_size = 100MB

# What to log? (adjust based on needs, less verbosity means less disk space)
# For production, consider:
# log_min_duration_statement = 1000 # Log statements taking longer than 1 second (1000ms)
# log_checkpoints = on
# log_connections = on
# log_disconnections = on
# log_lock_waits = on
# log_temp_files = 1024 # Log temp files larger than 1MB
# log_autovacuum_min_duration = 0 # Log all autovacuum actions for monitoring

After modifying postgresql.conf, save the changes and restart PostgreSQL for them to take effect.

sudo systemctl restart postgresql

6. Clean Up Other Potential Disk Hogs (Optional)

While pg_log is the primary suspect here, it's good practice to check for other common disk space consumers.

# Clean apt cache
sudo apt clean

# Remove unused packages
sudo apt autoremove

# Check for old Docker images, containers, volumes (if Docker is used)
sudo docker system prune -a

# Clear old journalctl logs (can sometimes consume a lot)
sudo journalctl --vacuum-size=100M
sudo journalctl --vacuum-time=7d

By following these steps, you'll not only recover your PostgreSQL database from a disk space emergency but also establish robust log management practices to prevent future occurrences, ensuring high availability and stability for your services.