Resolving PostgreSQL ‘Disk Space Exhausted’ and Database Lock Errors on macOS

Troubleshoot and fix PostgreSQL 'pg_log disk space exhausted' errors and database lockfiles on macOS local environments, preventing database startup failures.

Troubleshoot and fix PostgreSQL 'pg_log disk space exhausted' errors and database lockfiles on macOS local environments, preventing database startup failures.

Introduction

Encountering a PostgreSQL database that refuses to start or accept connections, often accompanied by cryptic log messages pointing to disk space issues and orphaned lock files, can be a frustrating experience, especially in a local development environment. This guide addresses a common scenario on macOS where the pg_log directory consumes all available disk space, leading to PostgreSQL startup failures and database lock-related errors. We'll walk through a highly technical and accurate resolution process, ensuring your development database is back online and preventing future recurrences.

Symptom & Error Signature

When disk space on your macOS machine (particularly the partition where PostgreSQL stores its data and logs) becomes exhausted, PostgreSQL will fail to perform critical operations, including writing to its log files, creating temporary files, or even updating its postmaster.pid lockfile.

You might observe the following symptoms:

  • PostgreSQL service fails to start via brew services start postgresql.
  • Applications unable to connect to the database.
  • Error messages in your terminal when attempting to start PostgreSQL or in the database's log files.

Typical error signatures in the PostgreSQL logs (located, for example, at /usr/local/var/postgres/pg_log if installed via Homebrew) might include:

2024-07-08 10:30:05.123 PDT [12345] FATAL:  could not write to file "pg_wal/xlog/000000010000000000000001": No space left on device
2024-07-08 10:30:05.123 PDT [12345] HINT:  Check free disk space.
2024-07-08 10:30:05.123 PDT [12345] LOG:  terminating any other active server processes
2024-07-08 10:30:05.123 PDT [12345] WARNING:  could not remove old temporary file "base/pgsql_tmp/pgsql_tmp_12345.0": No space left on device
2024-07-08 10:30:05.123 PDT [12345] LOG:  could not write to log file: No space left on device
2024-07-08 10:30:05.123 PDT [12345] LOG:  database system is shut down
2024-07-08 10:30:05.123 PDT [12345] FATAL:  could not create lock file "/tmp/.s.PGSQL.5432.lock": No space left on device

Or, if a previous crash due to disk exhaustion left a stale lock file:

2024-07-08 10:35:10.456 PDT [67890] FATAL:  lock file "postmaster.pid" already exists
2024-07-08 10:35:10.456 PDT [67890] HINT:  Is another postmaster (PID 67889) already running in data directory "/usr/local/var/postgres"?

Root Cause Analysis

The primary root cause is the exhaustion of disk space, specifically on the volume hosting your PostgreSQL data directory and its pg_log subdirectory. Modern PostgreSQL installations, especially in development environments, often default to verbose logging levels. Over time, these logs can grow significantly, consuming gigabytes or even terabytes of disk space if not managed properly.

When disk space runs out:

  1. Log File Write Failures: PostgreSQL cannot write new entries to its log files, leading to errors and potential shutdown.
  2. WAL Segment Failures: Write-Ahead Log (WAL) segments cannot be created, which is critical for transaction integrity and recovery. This directly leads to database crashes.
  3. Temporary File Failures: Operations requiring temporary disk space (e.g., large sorts, hash joins) will fail.
  4. Lock File Issues:
    • The postmaster.pid file, which serves as a lock to prevent multiple PostgreSQL instances from starting on the same data directory, might fail to be created or deleted correctly during a crash, leaving a stale lock.
    • Socket lock files (e.g., in /tmp) may also fail to be created, preventing client connections.

On macOS, PostgreSQL is commonly installed via Homebrew. The default data directory for a Homebrew installation is typically /usr/local/var/postgres/, and the logs are usually found within pg_log inside this directory.

Step-by-Step Resolution

Follow these steps carefully to resolve the disk space issue, remove stale lock files, and get your PostgreSQL database back online.

1. Verify Disk Space Usage

First, confirm that your disk is indeed full.

df -h

This command will display disk space usage for all mounted file systems. Look for the percentage used in the Use% column. If your primary drive (often / or /System/Volumes/Data) is at 95% or higher, disk space is likely the culprit.

2. Identify Large Files and PostgreSQL Log Directory

Next, locate where the disk space is being consumed. We'll specifically check the PostgreSQL data directory. For Homebrew installations, this is commonly /usr/local/var/postgres.

# Check overall size of PostgreSQL data directory
du -sh /usr/local/var/postgres

# Check the size of the pg_log directory
du -sh /usr/local/var/postgres/pg_log

# List largest files within pg_log to identify culprits (e.g., sorted by size, largest first)
find /usr/local/var/postgres/pg_log -type f -print0 | xargs -0 du -h | sort -rh | head -n 10

You will likely see that pg_log accounts for a significant portion of the disk usage.

3. Stop PostgreSQL Service

It's crucial to stop PostgreSQL gracefully before manipulating its data or log files to prevent data corruption.

brew services stop postgresql

Ensure the service is fully stopped. You can verify this by running ps aux | grep -i postgres and checking that no PostgreSQL processes are running. If processes persist, you might need to use kill -9 <PID> for each process, though this should be a last resort.

4. Clean Up pg_log Files

Now that PostgreSQL is stopped, you can safely remove or archive old log files. Do not delete the pg_log directory itself, only its contents.

Before deleting, consider archiving log files to an external drive or cloud storage if you need them for auditing or debugging purposes. Deleting them permanently makes them unrecoverable.

Option A: Archive (Recommended for forensic analysis)

# Create a temporary backup directory
mkdir -p ~/pg_log_backup

# Move all log files older than, for example, 7 days into the backup directory
# Adjust '-mtime +7' to your desired retention period
find /usr/local/var/postgres/pg_log -name "*.log" -type f -mtime +7 -exec mv {} ~/pg_log_backup/ ;

# Or, if you need to free up space immediately and have a large number of recent logs,
# move ALL current log files (excluding the directory itself) to a backup.
# Be careful not to move the directory itself or hidden files like .DS_Store.
mv /usr/local/var/postgres/pg_log/*.log ~/pg_log_backup/

Option B: Delete (Use with caution!)

# Delete all log files older than, for example, 7 days
# Adjust '-mtime +7' to your desired retention period
find /usr/local/var/postgres/pg_log -name "*.log" -type f -mtime +7 -delete

# Or, to delete ALL current log files (excluding the directory itself)
rm /usr/local/var/postgres/pg_log/*.log

After cleaning, verify that disk space has been freed up:

df -h

5. Check and Remove Stale postmaster.pid Lockfile

If PostgreSQL crashed, it might have left behind a postmaster.pid file in its data directory, which prevents a new instance from starting.

# Navigate to the PostgreSQL data directory
cd /usr/local/var/postgres

# Check if postmaster.pid exists
ls postmaster.pid

# If it exists, remove it
rm postmaster.pid

Only remove postmaster.pid after you are absolutely certain no PostgreSQL process is running. Removing it while PostgreSQL is active can lead to severe data corruption.

6. Start PostgreSQL Service

With disk space cleared and any stale lock files removed, attempt to start PostgreSQL again.

brew services start postgresql

Verify its status:

brew services list

You should see postgresql listed as started.

7. Verify Database Access

Once PostgreSQL is running, test connectivity.

psql postgres

This should connect you to the default postgres database. Type q to exit. Your applications should now also be able to connect.

8. Configure Log Retention and Rotation

To prevent this issue from recurring, implement a strategy for log retention.

For macOS Local Environments (Homebrew):

A simple cron job can periodically clean up old logs.

  1. Open your crontab for editing:

    crontab -e

  2. Add a line like the following to delete logs older than 30 days (adjust -mtime +30 as needed) every day at 2 AM:

    0 2 * * * find /usr/local/var/postgres/pg_log -name "*.log" -type f -mtime +30 -delete > /dev/null 2>&1

    Ensure that the user whose crontab you're editing has the necessary permissions to delete files in the pg_log directory.

  3. Save and exit (:wq in vi/vim).

For Production Linux Environments (Ubuntu/Debian, Systemd):

While this guide focuses on macOS, it's crucial for DevOps engineers to be aware of production-grade solutions. On Linux, logrotate is the standard tool for managing log files.

  1. Check existing configuration: PostgreSQL often ships with a logrotate configuration file.

    cat /etc/logrotate.d/postgresql-common

  2. Example logrotate configuration (/etc/logrotate.d/your-postgresql):

    /var/log/postgresql/*.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 0640 postgres adm
    su postgres adm
    postrotate
        # Reload postgresql service to signal log rotation
        # This depends on your PostgreSQL version and how it handles log rotation signals.
        # Some versions might require pg_ctl reload or a SIGHUP to the postmaster.
        # For systemd-managed services, a simple reload is often sufficient.
        test -e /usr/share/postgresql/9.X/pg_ctlcluster && 
        /usr/bin/pg_ctlcluster 9.X main reload > /dev/null || true
        # Or for simpler setups:
        # /usr/bin/find /var/log/postgresql/ -type f -name "*.log" -exec kill -HUP `cat /var/run/postgresql/9.X-main.pid` ; || true
    endscript
}

    Adjust paths (/var/log/postgresql), PostgreSQL version (9.X), and the postrotate command to match your specific setup. The postrotate command ensures PostgreSQL releases its old log file handle and starts writing to a new one after rotation.

9. Monitor Disk Usage

Regularly monitor your disk space, especially in development environments where log settings might be more verbose. Tools like df -h and du -sh are your first line of defense. Consider setting up monitoring alerts if this is a recurring issue or in a shared development server context.