Resolving PostgreSQL pg_log Disk Space Exhaustion & Database Lock on Debian 12 Bookworm

Fix PostgreSQL database locks and service failures on Debian 12 caused by full pg_log disk space. Learn to clear logs, free space, and prevent recurrence.

When your PostgreSQL database suddenly becomes unresponsive, applications fail to connect, or the entire web application ecosystem grinds to a halt, one of the most insidious culprits can be a full disk. Specifically, an overgrowth of PostgreSQL's transaction logs or pg_log files can consume all available space, leading to critical database failures, service unresponsiveness, and "lockfile" errors as PostgreSQL struggles to write essential operational data. This guide provides a highly technical, step-by-step resolution for this common issue on Debian 12 (Bookworm) systems.

Symptom & Error Signature

Users typically experience one or more of the following symptoms:

• Web applications reporting database connection errors (e.g., "could not connect to server: Connection refused", "FATAL: remaining connection slots are reserved for non-replication superuser connections").
• The PostgreSQL service (often [email protected] or similar) is reported as failed or inactive by systemctl.
• Attempts to start or restart PostgreSQL fail immediately.
• System logs (journalctl) show errors related to disk I/O or inability to write.

Here are typical error messages you might encounter:

From journalctl -u postgresql (or journalctl -u [email protected] for specific versions):

systemd[1]: [email protected]: Start request repeated too quickly.
systemd[1]: [email protected]: Failed with result 'exit-code'.
systemd[1]: Failed to start PostgreSQL Cluster 15-main.
...
postgresql[12345]: 2024-06-25 10:30:00.123 UTC [12345] LOG: could not write lock file "/var/run/postgresql/.s.PGSQL.5432.lock": No space left on device
postgresql[12345]: 2024-06-25 10:30:00.123 UTC [12345] FATAL: could not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": No space left on device
postgresql[12345]: 2024-06-25 10:30:00.123 UTC [12345] LOG: database system is shut down

From dmesg (kernel messages, indicating disk I/O issues):

[123456.789012] EXT4-fs (sda1): write access unavailable, fs is full
[123456.789013] EXT4-fs (sda1): Remounting filesystem read-only

From df -h (showing disk usage):

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        50G   50G     0 100% /
udev             10M    0   10M   0% /dev
tmpfs           7.8G  1.2M  7.8G   1% /run

Root Cause Analysis

The root cause of this issue is typically a filesystem reaching 100% capacity on the partition where PostgreSQL stores its data and/or log files. This commonly happens on the root partition (/) if /var is not a separate mount, or on a dedicated /var partition.

Here's a breakdown of why this leads to the observed symptoms:

1. Excessive Log Growth: PostgreSQL, by default, writes detailed logs to files within a directory like /var/log/postgresql/ or within the data directory itself (/var/lib/postgresql/<version>/main/log/). Without proper log rotation (e.g., via logrotate), these log files can grow indefinitely, consuming gigabytes or even terabytes of disk space over time.
2. Lack of Free Space: Once the filesystem reaches 100% utilization, the operating system can no longer write any new data to that disk.
3. PostgreSQL's Inability to Operate: PostgreSQL requires free disk space for several critical operations:
4. * Writing Log Files: It cannot log new events, leading to internal errors.
5. * Writing Transaction Logs (WAL): Crucial for data integrity and recovery. While WAL segments are usually pre-allocated, operational temporary files and checkpoints might require space.
6. * Creating Lock Files: When PostgreSQL starts, it creates a lock file (e.g., /var/run/postgresql/.s.PGSQL.5432.lock) to prevent multiple instances from running on the same port. If it cannot create this file, it fails to start.
7. * Temporary Files and Caching: Queries that require large sorts or joins might create temporary files on disk. If these cannot be written, queries will fail.
8. Service Failure: The inability to write lock files or perform other critical disk operations prevents the PostgreSQL server from starting or causes it to shut down gracefully (or crash) to prevent data corruption. systemd will then report the service as failed.

Step-by-Step Resolution

Follow these steps carefully to recover your PostgreSQL service and prevent future occurrences.

1. Verify Disk Space and Identify Culprit

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

1. Check overall disk usage:
2. `bash
3. df -h
4. `
5. Look for a partition (often / or /var) showing 100% Use%.

1. Identify large directories:
2. Navigate to the suspected full partition and use du to find the largest directories. Start broad and narrow down.
3. `bash
4. sudo du -h /var/log | sort -rh | head -n 10
5. sudo du -h /var/lib/postgresql | sort -rh | head -n 10
6. `
7. You're likely to see /var/log/postgresql/ or a subdirectory within /var/lib/postgresql/ consuming significant space. For example:
8. `
9. 50G /var/log/postgresql
10. 48G /var/log/postgresql/postgresql-15-main.log.1
11. `

2. Stop PostgreSQL Service

Before manipulating PostgreSQL's log files or data directory, it is critical to stop the service to prevent data corruption.

[!WARNING] Do NOT proceed to delete files if PostgreSQL is still running, especially not within its data directory (/var/lib/postgresql). This can lead to irreversible data loss or corruption.

sudo systemctl stop postgresql
# Or for a specific version:
sudo systemctl stop [email protected]

Verify the service is stopped: `bash sudo systemctl status postgresql ` It should show Active: inactive (dead).

3. Clear Excessive pg_log Files

Once the service is stopped, you can safely remove old log files.

1. Navigate to the PostgreSQL log directory:
2. For Debian, this is typically /var/log/postgresql/.
3. `bash
4. cd /var/log/postgresql/
5. `

1. List files and confirm their size:
2. `bash
3. ls -lh
4. `
5. This will show you the massive log files, usually named postgresql-<version>-main.log or similar, possibly with suffixes like .1, .gz, etc.

1. Delete old log files:
2. Start by deleting the oldest and largest log files to free up space. You might need to delete iteratively until you have enough free space to restart the database.

[!IMPORTANT] > Do not delete the current log file (postgresql-<version>-main.log) or files still being written to (though this is less likely if the service is stopped). Focus on .1, .2, .gz files. > > Be cautious with rm -rf. Always verify the directory and files before execution.

To delete log files older than, say, 7 days: `bash sudo find . -name "postgresql--main.log." -mtime +7 -exec rm {} ; ` To delete all but the most recent 2-3 log files (be careful with this, adjust head -n -3 to suit): `bash # List files sorted by modification time (oldest first) ls -t postgresql--main.log. | tail -n +4 | xargs sudo rm -v # The above command keeps the 3 newest logs. Adjust +4 to keep N-1 logs. # E.g., tail -n +2 keeps 1 newest log. # To delete all but the current one and the most recent rotated one: # sudo rm postgresql--main.log.2 postgresql--main.log.3 ` If you're desperate for space and can afford to lose older logs for recovery: `bash sudo rm postgresql--main.log. `

1. Verify free space:
2. `bash
3. df -h
4. `
5. You should now see some available space. Aim for at least 1-2GB free to allow the database to start and operate normally.

4. Restart PostgreSQL Service

With disk space freed, attempt to restart the database service.

sudo systemctl start postgresql
# Or for a specific version:
sudo systemctl start [email protected]

5. Verify Database Operation

Check the status of the service and confirm your applications can connect.

1. Check service status:
2. `bash
3. sudo systemctl status postgresql
4. `
5. It should now show Active: active (running).

1. Check logs for errors:
2. `bash
3. sudo journalctl -u postgresql -f
4. `
5. Look for LOG: database system is ready to accept connections.

1. Test application connectivity:
2. Access your web application or connect via psql:
3. `bash
4. sudo -u postgres psql
5. `
6. If you get a psql prompt, the database is running. Type q to exit.

6. Implement Log Rotation (Long-Term Fix)

To prevent this issue from recurring, configure logrotate for PostgreSQL. Debian's postgresql-common package usually sets up a basic logrotate configuration, but it might be overridden or misconfigured.

1. Inspect existing logrotate configuration:
2. Check /etc/logrotate.d/postgresql-common or /etc/logrotate.d/postgresql.
3. `bash
4. sudo cat /etc/logrotate.d/postgresql-common
5. `
6. A typical configuration might look like this:
7. `
8. /var/log/postgresql/*.log {
9. daily
10. missingok
11. rotate 7
12. compress
13. delaycompress
14. notifempty
15. create 0640 postgres adm
16. su postgres adm
17. postrotate
18. # Find the PIDs of running postgresql clusters and send them a SIGHUP
19. if [ -e /etc/postgresql/15/main/postgresql.conf ] ; then
20. pg_ctlcluster 15 main reload > /dev/null || true
21. fi
22. endscript
23. }
24. `

1. Adjust logrotate settings if necessary:
2. * rotate 7: Keeps 7 rotated logs. Adjust this number (e.g., rotate 14 for two weeks) based on your disk space and retention needs.
3. * daily, weekly, monthly: How often logs are rotated. daily is generally good for busy systems.
4. * compress: Compresses old log files. Highly recommended.
5. * size 100M: You can add size <bytes> (e.g., size 100M) to force rotation when a log file reaches a certain size, regardless of the daily/weekly setting. This is very effective for high-volume logs.
7. > [!IMPORTANT]
8. > If you make changes, ensure they are compatible with existing logrotate options. A common issue is a missing postrotate script to signal PostgreSQL to reopen log files. Without it, PostgreSQL might continue writing to the old (renamed) log file. The pg_ctlcluster ... reload command correctly handles this.

1. Manually test logrotate:
2. `bash
3. sudo logrotate -f /etc/logrotate.d/postgresql-common
4. `
5. This forces a rotation immediately. Check /var/log/postgresql/ afterwards to confirm new log files are created and old ones are rotated/compressed.

7. Configure PostgreSQL Logging (Proactive)

Fine-tuning PostgreSQL's logging behavior in postgresql.conf can also help manage disk space.

1. Edit postgresql.conf:
2. `bash
3. sudo nano /etc/postgresql/15/main/postgresql.conf
4. `

1. Review and adjust these parameters:

• log_destination: Set to stderr or csvlog (if you want structured logs). stderr is standard for syslog/journal.
• * logging_collector = on: (Default) This sends log messages to files managed by PostgreSQL itself. If off, logs go to syslog, which then needs syslog's own rotation. For /var/log/postgresql management, keep it on.
• * logdirectory = 'pglog': (Relative to data directory) or /var/log/postgresql (absolute path).
• * logfilename = 'postgresql-%Y-%m-%d%H%M%S.log': This creates unique log files per start time, which logrotate can then manage.
• * logrotationage = 1d: Rotates logs daily. This works in conjunction with logrotate.
• logrotationsize = 0: Disables size-based rotation by PostgreSQL itself if logrotate is handling it. Set a value (e.g., 100MB) if you want PostgreSQL to rotate before* logrotate. It's often better to let logrotate handle it consistently.
• * logminduration_statement = 1000: Logs all statements taking longer than 1 second (1000ms). Adjust this value to 0 to log all statements (VERY VERBOSE, use with caution on production), or increase it to 5000 (5 seconds) to reduce log volume.
• * log_statement = 'none': Logs no statements. Can be ddl, mod, all. none is recommended for most production environments unless debugging.
• * log_checkpoints = on: Logs each checkpoint. Useful for performance monitoring.
• * log_connections = on: Logs successful client connections.
• * log_disconnections = on: Logs end of client sessions.

[!IMPORTANT] > Setting logrotationage or logrotationsize directly in postgresql.conf provides a fallback or additional rotation layer. However, logrotate is generally preferred for system-wide log management. Ensure your postgresql.conf settings don't conflict or create redundant files. If logrotate is robustly configured, you can set logrotationage = 0 and logrotationsize = 0 in postgresql.conf to let logrotate handle it exclusively.

1. Restart PostgreSQL after postgresql.conf changes:
2. `bash
3. sudo systemctl restart postgresql
4. `

8. Consider Dedicated Disk/Partition (Advanced)

For high-traffic production databases, it's often prudent to separate PostgreSQL's data and log directories onto dedicated partitions or even separate physical disks. This isolates I/O and prevents a full log partition from impacting the entire system or vice-versa.

1. Mount a new disk/partition:
2. Create a new filesystem and mount it, e.g., to /mnt/pgdata or /mnt/pglogs.
3. Edit /etc/fstab to ensure it mounts automatically on boot.

1. Move PostgreSQL data or logs:
2. * Moving logs: Change logdirectory in postgresql.conf to point to the new location (e.g., /mnt/pglogs). Remember to create the directory and set correct permissions (chown postgres:postgres /mnt/pg_logs).
3. * Moving data directory: This is more involved. You would stop PostgreSQL, copy /var/lib/postgresql/ to the new location (e.g., /mnt/pgdata), update datadirectory in postgresql.conf, and potentially update the systemd service file or pg_ctlcluster configuration.

This comprehensive approach will not only resolve the immediate disk space and lockfile issues but also establish robust logging and maintenance practices to prevent recurrence.