PostgreSQL Failure: Disk Space Exhausted (pg_log) & Database Lock Issues on CentOS Stream / Rocky Linux

Troubleshoot and resolve PostgreSQL service failures caused by exhausted disk space in pg_log and associated database lockfile issues on RHEL-based systems.

Troubleshoot and resolve PostgreSQL service failures caused by exhausted disk space in pg_log and associated database lockfile issues on RHEL-based systems.

When your PostgreSQL database suddenly becomes unresponsive, or applications report connection errors, one of the most common culprits, especially in high-traffic or poorly managed environments, is an exhausted disk partition. Specifically, the pg_log directory, responsible for storing database logs, can grow unchecked, consuming all available disk space. This not only prevents PostgreSQL from writing new data but can also lead to critical failures in creating necessary lockfiles (postmaster.pid), rendering the database unable to start or function correctly. This guide provides a comprehensive, technical walkthrough to diagnose and resolve such issues on CentOS Stream and Rocky Linux distributions.

Symptom & Error Signature

Users will typically experience application outages, slow response times, or complete database unavailability. Attempting to restart or check the PostgreSQL service will reveal critical errors.

Common symptoms and error messages observed in application logs, PostgreSQL logs (/var/lib/pgsql/data/log/postgresql-*.log), or via journalctl -u postgresql.service include:

# Application/Client Error Examples
psycopg2.OperationalError: could not connect to server: No space left on device
        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 5432?
        
FATAL: remaining connection slots are reserved for non-replication superuser connections

# PostgreSQL Server Log Examples
2023-10-27 10:30:05.123 UTC [12345] LOG:  terminating any other active server processes
2023-10-27 10:30:05.123 UTC [12345] WARNING:  terminating connection because of crash of another server process
2023-10-27 10:30:05.123 UTC [12345] DETAIL: The postmaster has exited abnormally.
2023-10-27 10:30:05.123 UTC [12345] FATAL:  could not create lock file "postmaster.pid": No space left on device
2023-10-27 10:30:05.123 UTC [12345] LOG:  database system is shut down
2023-10-27 10:30:05.123 UTC [12345] LOG:  could not write to file "pg_wal/000000010000000000000001" (target size 16777216): No space left on device
2023-10-27 10:30:05.123 UTC [12345] PANIC:  could not write to file "pg_xact/0000" (target size 2048): No space left on device

# systemctl status postgresql.service output
$ sudo systemctl status postgresql.service
● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
     Active: failed (Result: exit-code) since Fri 2023-10-27 10:30:05 UTC; 5s ago
    Process: 12345 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=1/FAILURE)
   Main PID: 12345 (code=exited, status=1/FAILURE)
        CPU: 1.234s

The key indicator is No space left on device messages, particularly when PostgreSQL attempts to create files like postmaster.pid or write to its WAL segments or logs.

Root Cause Analysis

The root cause for this error is invariably an exhausted disk partition where PostgreSQL stores its data, primarily driven by:

  1. Excessive Logging:

    • Verbose postgresql.conf settings: log_min_duration_statement set to 0, log_statement = 'all', or log_duration = on can generate massive log files, especially under high load.
    • Debugging left enabled: Temporary debugging configurations that increase logging verbosity were not reverted.
    • Lack of log rotation: PostgreSQL's built-in log rotation (log_rotation_age, log_rotation_size) or system-level logrotate configuration is either disabled, misconfigured, or not functioning, allowing logs to accumulate indefinitely.

  2. Unmanaged Transaction Logs (WAL):

    • If archive_mode is enabled without a proper archiving and cleanup strategy, WAL segments can accumulate rapidly in pg_wal, consuming significant disk space. While pg_log is the primary focus of this issue, pg_wal can contribute to disk exhaustion and similar symptoms.

  3. Under-provisioned Disk Space:

    • The disk partition hosting /var/lib/pgsql (or wherever PGDATA is located) was initially provisioned with insufficient space for the database's growth and operational logging needs.

  4. Stale Lockfiles (Secondary):

    • While No space left on device is the direct cause preventing the creation of postmaster.pid, a previous crash might have left a stale postmaster.pid behind. If disk space is then cleared but PostgreSQL still refuses to start, it might be due to detecting this old lockfile, believing another instance is running. However, for the specific error signature (No space left on device when creating postmaster.pid), clearing space is paramount.

Step-by-Step Resolution

Follow these steps carefully to resolve the disk space issue and restore your PostgreSQL service.

1. Verify Disk Space Utilization

First, determine which partition is full and identify the largest directories within your PostgreSQL data directory.

# Check overall disk space utilization
df -h

# Check inode utilization (less common for this specific error, but good to check)
df -i

# Identify the PostgreSQL data directory (PGDATA)
# Common locations: /var/lib/pgsql/data, /var/lib/pgsql/1X/data
# You can find PGDATA by checking postgresql.service unit file:
sudo systemctl show -p Environment postgresql.service

# Assuming PGDATA is /var/lib/pgsql/data, find largest directories within it
sudo du -sh /var/lib/pgsql/data/* | sort -rh
sudo du -sh /var/lib/pgsql/data/log
sudo du -sh /var/lib/pgsql/data/pg_wal # Check WAL too, just in case

The df -h output will clearly show which mounted filesystem is at 100% usage. The du -sh commands will help pinpoint pg_log (or potentially pg_wal) as the primary consumer of space.

2. Stop PostgreSQL Service

It's critical to stop the PostgreSQL service before attempting to clear files to prevent data corruption or further issues.

sudo systemctl stop postgresql.service
sudo systemctl status postgresql.service

Ensure the service shows Active: inactive (dead) or failed before proceeding. If it's failed, that's expected.

3. Clear Excessive Log Files

This is the most crucial step to free up disk space. You need to be careful not to delete essential database files.

Extreme Caution Advised: Only delete files identified as excessive logs (.log files, old WAL segments if truly problematic and archived). NEVER delete base, global, pg_wal (unless specifically managing old WAL segments), pg_xact, PG_VERSION, postgresql.conf, postmaster.opts unless you are absolutely certain of their purpose and have a full backup. Deleting critical data files will lead to irrecoverable data loss.

# Navigate to the log directory
cd /var/lib/pgsql/data/log

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

# Option 1: Safely move old logs to a temporary location outside the full partition
# (Requires space on another partition, or a temporary drive)
# Assuming you have /mnt/tmp for temporary storage
# sudo mkdir -p /mnt/tmp/pg_log_backup
# sudo mv /var/lib/pgsql/data/log/*.log /mnt/tmp/pg_log_backup/

# Option 2: Delete old log files (recommended for immediate space relief)
# This command deletes log files older than 7 days. Adjust -mtime as needed.
sudo find /var/lib/pgsql/data/log -type f -name "*.log" -mtime +7 -delete

# This command identifies and deletes very large log files. INSPECT output BEFORE executing.
# First, list large files for review:
sudo find /var/lib/pgsql/data/log -type f -size +500M -print
# To delete them (use with extreme caution after review):
# sudo find /var/lib/pgsql/data/log -type f -size +500M -print0 | xargs -0 sudo rm -f

# If pg_wal was also problematic due to unarchived WAL segments,
# consult PostgreSQL documentation for safe WAL cleanup methods.
# For immediate crisis, and IF archive_mode is not critical at this moment:
# sudo pg_archivecleanup -d /var/lib/pgsql/data/pg_wal 000000000000000000000000

# After clearing, verify disk space again
df -h

If, after clearing space, you still encounter FATAL: could not create lock file "postmaster.pid" (and not No space left on device), it might be due to a stale postmaster.pid file.

Delete it only if PostgreSQL is confirmed to be stopped:

sudo rm -f /var/lib/pgsql/data/postmaster.pid

This file acts as a lock; if it's present from a previous crashed instance, PostgreSQL won't start.

4. Adjust PostgreSQL Logging Configuration

To prevent future recurrences, optimize your postgresql.conf for logging.

# Open your postgresql.conf for editing
# Common path: /var/lib/pgsql/data/postgresql.conf
sudo vi /var/lib/pgsql/data/postgresql.conf

Locate the "Error Reporting and Logging" section and adjust the following parameters:

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# Where to send logging output; 'stderr' is typical for Systemd
log_destination = 'stderr'

# Enable logging collector, which redirects stderr to log files
logging_collector = on

# Directory where log files will be written
log_directory = 'log'

# Log file name pattern (e.g., postgresql-YYYY-MM-DD_HHMMSS.log)
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# When to truncate existing log files (on rotation or when new instance starts)
log_truncate_on_rotation = on

# Rotate log files based on age (e.g., daily)
log_rotation_age = 1d

# Rotate log files based on size (e.g., 100MB)
log_rotation_size = 100MB

# Log all statements taking at least X milliseconds. -1 disables.
# Set to -1 to avoid logging all statements, only enable for specific debugging
log_min_duration_statement = -1

# Do not log all SQL statements unless strictly necessary for debugging.
# If needed, set to 'ddl' or 'mod' for less verbose logging.
# log_statement = 'none'

# Log connection and disconnection events (can be useful, but adds to logs)
# log_connections = off
# log_disconnections = off

# Adjust verbosity of messages
log_error_verbosity = default # or 'terse', 'verbose'

5. Configure logrotate for PostgreSQL Logs

While PostgreSQL's internal rotation is good, system-level logrotate provides more robust management, especially for logs generated to stderr and handled by systemd-journald or other system log aggregators. However, if logging_collector = on (as recommended above), PostgreSQL handles its own files in PGDATA/log. If you prefer to have journalctl manage logs (by setting log_destination = 'stderr' and logging_collector = off), then journald's rotation handles it, and logrotate specifically for PGDATA/log files might not be needed.

For PGDATA/log management outside of logging_collector or if log_destination points directly to files that logrotate should handle:

# Create or edit a logrotate configuration file for PostgreSQL
sudo vi /etc/logrotate.d/postgresql

Add the following content (adjust path as needed):

/var/lib/pgsql/data/log/*.log {
    daily
    missingok
    rotate 7
    compress
    delaycompress
    notifempty
    create 0600 postgres postgres
    sharedscripts
    postrotate
        # For systems where PostgreSQL is managed by systemd and logging_collector is ON
        # This reloads the service without restarting it, forcing log file re-opening
        /usr/bin/systemctl reload postgresql.service > /dev/null 2>&1 || true
        # If logging_collector is OFF and logs go to stderr, this might not be needed.
    endscript
}

Test your logrotate configuration without actually rotating logs using the dry-run option:

sudo logrotate -d /etc/logrotate.d/postgresql

To force a rotation immediately (useful for testing after configuration):

sudo logrotate -f /etc/logrotate.d/postgresql

6. Increase Disk Space (If Permanent Solution Needed)

If disk space exhaustion is a recurring issue, simply clearing logs is a temporary fix. You will need to permanently increase the available storage.

  • Resize Partition/LVM: If using LVM (Logical Volume Management), you can extend the logical volume and filesystem.
  • Move PGDATA: Migrate the entire /var/lib/pgsql/data directory to a larger disk or partition. This involves stopping PostgreSQL, moving the directory, updating systemd service unit file (ExecStart's -D flag or Environment=PGDATA=/new/path) or postgresql.conf, and setting correct permissions.
  • Cloud Provider Options: If on a cloud platform, resize the attached disk volume and then extend the filesystem within the OS.

7. Start PostgreSQL Service

Once sufficient disk space is freed and logging configurations are adjusted, attempt to start the database service.

sudo systemctl daemon-reload # Important if you changed systemd unit file (e.g., PGDATA path)
sudo systemctl start postgresql.service
sudo systemctl status postgresql.service

Monitor the service status and its logs for any new errors:

sudo journalctl -u postgresql.service -f

If PostgreSQL starts successfully, your applications should regain connectivity.

8. Monitor Disk Usage and Logs

Implement proactive monitoring for disk space on your PostgreSQL partition. Tools like Prometheus/Grafana, Zabbix, Nagios, or simple cron jobs with df -h can alert you before the disk fills up again. Regularly review PostgreSQL logs to identify any abnormal growth or verbose output that needs tuning.