Domain 10Linux & SREINTERMEDIATE

(Manhattan) Troubleshooting PostgreSQL Write Failures: Disk Exhaustion & Systemd Unit Hierarchy

A technical dive into diagnosing PostgreSQL database write failures caused by disk volume exhaustion, navigating misleading systemd unit statuses, and applying storage remediation.

Domain: Domain 10: Linux Internals, SRE & Troubleshooting•Updated: 2026-11-09
#PostgreSQL#Disk-Space#Systemd#Troubleshooting#SadServers#SRE

1. Introduction

In modern production infrastructure, database availability is critical to application stability. When a database engine becomes incapable of performing write operations, downstream applications immediately experience cascading failures, degraded API endpoints, and potential data loss.

This article explores a real-world Linux troubleshooting scenario based on the “Manhattan” exercise from SadServers. In this challenge, PostgreSQL fails to insert data into a target database.

By reading this guide, you will learn how to:

  • Unmask misleading systemd service statuses caused by meta-wrapper units.
  • Differentiate between system-level storage exhaustion (block space vs. inode capacity) and database software bugs.
  • Trace unit failure causes using systemd journal outputs (journalctl) and distribution-specific PostgreSQL utilities (pg_lsclusters).
  • Execute safe storage remediation and verify system health.
  • Implement production SRE practices to prevent storage-induced database downtime.

2. Problem Description and Environment

Scenario Background

The objective of the exercise is to insert a single record into an existing PostgreSQL database named dt.

Environment Specifications

  • Operating System: Ubuntu / Debian-based Linux environment.
  • Database Management System: PostgreSQL 14.
  • Service Manager: systemd.
  • Listening Port: TCP 5432.
  • Database Data Directory: Configured via data_directory in /etc/postgresql/14/main/postgresql.conf.

Target Test Command & Expected Outcome

From the default administrative context, running the SQL command should insert a row into the persons table:

sudo -u postgres psql -c "insert into persons(name) values ('jane smith');" -d dt

Expected Output: INSERT 0 1

Actual Symptom

Running the test command fails immediately with a socket connection error:

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?

3. Initial Investigation and Hypotheses

To approach this problem systematically, we first separate confirmed symptoms from potential root causes.

Initial Observations

  1. Unreachable Unix Domain Socket: psql cannot locate /var/run/postgresql/.s.PGSQL.5432. This indicates that the PostgreSQL database daemon is either completely stopped, hung during initialization, or configured to listen on a non-standard socket path.
  2. Database Unavailable: Because the daemon is not listening, database write queries fail instantly.

Formulated Hypotheses

Hypothesis Supporting Evidence Evidence Against Diagnostic Step
H1: PostgreSQL service is stopped or failed Connection socket missing systemctl status postgresql reports active (exited) Inspect unit status (systemctl) and cluster state (pg_lsclusters)
H2: Volume disk space or inode exhaustion Database services fail to write PID/lock files when disk is full Root filesystem / has 6.1 GB free Check mount points (df -h) and inode table usage (df -i)
H3: File ownership or permission restriction postgres user might lack write permissions on the data directory Directory ownership defaults to postgres during setup Check directory permissions with ls -ld

4. Troubleshooting Process

Step 1: Connection & Query Test

Objective

Reproduce the failure directly and observe the exact error message returned by the client utility.

Command

sudo -u postgres psql -c "insert into persons(name) values ('jane smith');" -d dt

Command Explanation

  • sudo -u postgres: Switches execution context to the postgres administrative user to bypass PostgreSQL authentication hurdles.
  • psql: The standard interactive/non-interactive command-line terminal for PostgreSQL.
  • -c "insert ...": Specifies a single SQL command string to execute non-interactively.
  • -d dt: Connects to target database dt.

Output and Analysis

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?

Analysis: The local socket file .s.PGSQL.5432 is absent. The database service is not accepting connections.


Step 2: Inspect Master Systemd Service Status

Objective

Check whether systemd views the postgresql service as running.

Command

systemctl status postgresql

Command Explanation

  • systemctl status: Queries systemd-journald and systemd control interface for unit runtime state, process ID, and recent log buffers.

Output and Analysis

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Mon 2026-11-09 05:30:39 UTC; 50s ago
  Process: 674 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 674 (code=exited, status=0/SUCCESS)

Nov 09 05:30:39 ip-10-1-13-250 systemd[1]: Starting PostgreSQL RDBMS...
Nov 09 05:30:39 ip-10-1-13-250 systemd[1]: Started PostgreSQL RDBMS.

Analysis: postgresql.service reports active (exited) with ExecStart=/bin/true. On Debian/Ubuntu systems, postgresql.service is a meta-wrapper service. It executes /bin/true and delegates actual process management to instantiated systemd template units (e.g., postgresql@14-main.service). The wrapper reports active even when the underlying database cluster daemon crashes.


Step 3: Filesystem Block & Inode Audit

Objective

Verify if the operating system filesystems hosting database data or log files are out of storage space or inode allocations.

Commands

df -h
df -i

Command Explanation

  • df -h: Displays total, used, and available disk block space in human-readable units (MB, GB).
  • df -i: Displays total, used, and available inode metadata slots.

Output and Analysis

Filesystem        Size  Used Avail Use% Mounted on
udev              223M     0  223M   0% /dev
tmpfs              47M  1.5M   45M   4% /run
/dev/nvme1n1p1    7.7G  1.2G  6.1G  17% /
tmpfs             232M     0  232M   0% /dev/shm
tmpfs             5.0M     0  5.0M   0% /run/lock
tmpfs             232M     0  232M   0% /sys/fs/cgroup
/dev/nvme1n1p15   124M  278K  124M   1% /boot/efi
/dev/nvme0n1      8.0G  8.0G   28K 100% /opt/pgdata
Filesystem       Inodes IUsed  IFree IUse% Mounted on
udev              57043   296  56747    1% /dev
tmpfs             59318   397  58921    1% /run
/dev/nvme1n1p1  516096 39802 476294    8% /
tmpfs             59318     1  59317    1% /dev/shm
tmpfs             59318     2  59316    1% /run/lock
tmpfs             59318    17  59301    1% /sys/fs/cgroup
/dev/nvme1n1p15       0     0      0     - /boot/efi
/dev/nvme0n1       1464  1281    183   88% /opt/pgdata

Analysis: The volume /dev/nvme0n1 mounted on /opt/pgdata is 100% full (8.0 GB used out of 8.0 GB, with only 28 KB remaining). Inode allocation is fine (88% used, 183 available inodes). Storage block space exhaustion on /opt/pgdata is confirmed.


Step 4: Inspect PostgreSQL Cluster State and Template Unit Logs

Objective

Confirm the database cluster state and retrieve detailed service logs for the template unit.

Commands

pg_lsclusters
journalctl -u postgresql@14-main -n 30 --no-pager

Command Explanation

  • pg_lsclusters: Debian/Ubuntu utility that enumerates all configured PostgreSQL clusters, listing version, cluster name, status, data directory, and log paths.
  • journalctl -u postgresql@14-main: Queries journald logs specifically for the systemd template unit handling the cluster 14-main.
  • -n 30 --no-pager: Displays the last 30 log lines directly without invoking less.

Output and Analysis

Ver Cluster Port Status Owner    Data directory   Log file
14  main    5432 down   postgres /opt/pgdata/main /var/log/postgresql/postgresql-14-main.log
-- Logs begin at Mon 2026-11-09 05:29:43 UTC, end at Mon 2026-11-09 05:32:35 UTC. --
Nov 09 05:30:38 ip-10-1-13-250 systemd[1]: Starting PostgreSQL Cluster 14-main...
Nov 09 05:30:39 ip-10-1-13-250 postgresql@14-main[580]: Error: /usr/lib/postgresql/14/bin/pg_ctl /usr/lib/postgresql/14/bin/pg_ctl start -D /opt/pgdata/main -l /var/log/postgresql/postgresql-14-main.log -s -o  -c config_file="/etc/postgresql/14/main/postgresql.conf"  exited with status 1:
Nov 09 05:30:39 ip-10-1-13-250 postgresql@14-main[580]: 2026-11-09 05:30:39.353 UTC [665] FATAL:  could not create lock file "postmaster.pid": No space left on device
Nov 09 05:30:39 ip-10-1-13-250 postgresql@14-main[580]: pg_ctl: could not start server
Nov 09 05:30:39 ip-10-1-13-250 postgresql@14-main[580]: Examine the log output.
Nov 09 05:30:39 ip-10-1-13-250 systemd[1]: postgresql@14-main.service: Can't open PID file /run/postgresql/14-main.pid (yet?) after start: No such file or directory
Nov 09 05:30:39 ip-10-1-13-250 systemd[1]: postgresql@14-main.service: Failed with result 'protocol'.
Nov 09 05:30:39 ip-10-1-13-250 systemd[1]: Failed to start PostgreSQL Cluster 14-main.

Analysis: pg_lsclusters confirms cluster status is down, with data_directory set to /opt/pgdata/main. The journal log reveals the fatal failure: FATAL: could not create lock file "postmaster.pid": No space left on device. PostgreSQL crashed during startup because it could not write its initial lock file into /opt/pgdata/main.


Step 5: Verify Data Directory Configuration & Locate Space Hogs

Objective

Confirm the data directory setting in configuration and identify which files are consuming space inside /opt/pgdata.

Commands

grep "^data_directory" /etc/postgresql/14/main/postgresql.conf
du -sh /opt/pgdata/*

Command Explanation

  • grep "^data_directory": Filters postgresql.conf for un-commented lines beginning with data_directory.
  • du -sh /opt/pgdata/*: Calculates summary (-s) disk usage in human-readable (-h) format for all items in /opt/pgdata/.

Output and Analysis

data_directory = '/opt/pgdata/main'             # use data in another directory
4.0K    /opt/pgdata/deleteme
7.0G    /opt/pgdata/file1.bk
923M    /opt/pgdata/file2.bk
488K    /opt/pgdata/file3.bk
50M     /opt/pgdata/main

Analysis: The PostgreSQL database instance (/opt/pgdata/main) only uses 50 MB. The 8.0 GB partition was completely filled by leftover non-database backup files placed directly inside /opt/pgdata/: file1.bk (7.0 GB) and file2.bk (923 MB).


5. Root Cause Analysis

Failure Mechanism

flowchart TD
    A["Volume /dev/nvme0n1 (/opt/pgdata) reaches 100% usage"] --> B["Systemd attempts to start postgresql@14-main"]
    B --> C["pg_ctl attempts to create lock file /opt/pgdata/main/postmaster.pid"]
    C --> D["Linux VFS returns ENOSPC (No space left on device)"]
    D --> E["PostgreSQL emits FATAL error and terminates"]
    E --> F["Unix domain socket /var/run/postgresql/.s.PGSQL.5432 is not created"]
    F --> G["psql client queries fail with socket missing error"]
  1. Storage Exhaustion (ENOSPC): Arbitrary backup files (file1.bk and file2.bk) consumed nearly all available space on /opt/pgdata.
  2. Lock File Failure: When PostgreSQL starts, pg_ctl attempts to create postmaster.pid inside the cluster data directory (/opt/pgdata/main) to enforce single-process memory access. Kernel call open() returned ENOSPC (No space left on device).
  3. Database Termination: PostgreSQL immediately aborted startup, preventing initialization of shared memory and Unix domain sockets.
  4. Misleading Systemd Status: The meta-wrapper postgresql.service reported active (exited) because /bin/true completed cleanly. The actual template unit postgresql@14-main.service had failed.

6. Solution and Remediation

Remediation Steps

  1. Delete the unneeded backup files from /opt/pgdata/ to free storage capacity.
  2. Verify disk space availability on /opt/pgdata.
  3. Restart PostgreSQL via systemd.

Commands Executed

rm -f /opt/pgdata/*.bk /opt/pgdata/deleteme
df -h /opt/pgdata
systemctl start postgresql

Command Explanation

  • rm -f /opt/pgdata/*.bk /opt/pgdata/deleteme: Unlinks pattern-matched files forcefully without prompting.
  • systemctl start postgresql: Triggers systemd to start the master wrapper service and instantiate postgresql@14-main.service.

Output of Remediation Commands

Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme0n1    8.0G   91M  8.0G   2% /opt/pgdata

Storage utilization on /opt/pgdata dropped from 100% to 2% ($91\text{ MB}$ used out of $8.0\text{ GB}$).


7. Verification and Validation

Step 1: Execute SQL Insert Query

Command

sudo -u postgres psql -c "insert into persons(name) values ('jane smith');" -d dt

Output

INSERT 0 1

Result: Success. The database process connected to socket .s.PGSQL.5432 and executed the write operation.

Step 2: Verify Cluster Status

Command

pg_lsclusters

Output

Ver Cluster Port Status Owner    Data directory   Log file
14  main    5432 online postgres /opt/pgdata/main /var/log/postgresql/postgresql-14-main.log

Result: Cluster 14-main is confirmed online.


8. Alternative Troubleshooting Approaches

Approach Mechanics Advantages Disadvantages / Trade-offs
Option A: Delete Orphan Files (Applied) Run rm -f /opt/pgdata/*.bk to free block space directly. Instant space recovery, zero system configuration changes. Potential data loss if files are actual unarchived backups.
Option B: Move Files to Root Partition Run mv /opt/pgdata/*.bk /root/ to preserve backups. Preserves files while freeing target volume. Target partition must have sufficient capacity (in our case, root / had 6.1 GB free, insufficient for the 7.0 GB file1.bk).
Option C: Volume / Filesystem Resizing Expand underlying block device (AWS EBS / LVM) and execute resize2fs /dev/nvme0n1. Production-safe; preserves all data and files. Requires cloud infrastructure privileges or LVM volume group space.
Option D: Relocate Data Directory Edit data_directory in postgresql.conf to point to /var/lib/postgresql/14/main. Bypasses damaged volume. Requires copying existing DB state, adjusting permissions (chown -R postgres), and restarting service.

9. Linux Command Deep Dive

df (Disk Free)

  • Purpose: Queries virtual file system metadata (statvfs) to report available block space and inode stats across mounted filesystems.

  • Syntax: df [OPTION]... [FILE]...

  • Common Options:

  • df -h: Human-readable format (MB, GB).

  • df -i: Displays inode usage instead of block usage.

  • df -T: Prints filesystem type (ext4, xfs, tmpfs).

  • df -x tmpfs: Excludes temporary memory filesystems from view.

  • Practical SRE Example: Find all local ext4/xfs filesystems with over 90% usage:

df -hT -x tmpfs -x devtmpfs | awk '0+$6 > 90 {print $0}'

du (Disk Usage)

  • Purpose: Summarizes disk space allocated per file or directory recursively.

  • Syntax: du [OPTION]... [FILE]...

  • Common Options:

  • du -sh *: Summarize (-s) top-level contents in human-readable (-h) format.

  • du -d 1 -h /path: Restricts recursion depth to 1 level (--max-depth=1).

  • du -ah: Lists individual files alongside directories.

  • Practical SRE Example: Find the top 10 largest items in /var/log:

du -ah /var/log 2>/dev/null | sort -rh | head -n 10

journalctl (Systemd Journal Engine)

  • Purpose: Fetches system and service logs captured by systemd-journald.

  • Syntax: journalctl [OPTIONS]... [MATCHES]...

  • Common Options:

  • journalctl -u unit_name: Filters logs for a specific systemd unit.

  • journalctl -f: Real-time tailing of incoming log streams.

  • journalctl -p err..emerg: Filters logs by priority (errors and critical warnings).

  • journalctl --since "1 hour ago": Time-window filtering.

  • Practical SRE Example: Inspect failed unit activations from the current boot:

journalctl -b -p err --no-pager

pg_lsclusters (Debian/Ubuntu Postgres Utility)

  • Purpose: Enumerates PostgreSQL clusters managed under /etc/postgresql/.
  • Common Options:
  • pg_lsclusters: Default overview listing version, cluster name, port, status, owner, data path, and log file.
  • pg_lsclusters --json: Returns cluster status in structured JSON format for automation scripts.

10. Underlying Linux Concepts and Architecture

Block Exhaustion vs. Inode Exhaustion

Filesystems allocate storage using two distinct structures:

  1. Data Blocks: Store actual file contents.
  2. Inodes (Index Nodes): Store metadata (file size, permissions, owner, timestamps, block pointers).
+-----------------------------------------------------------------------+
|                             Filesystem                                |
+------------------------------------+----------------------------------+
|           Inode Table              |           Data Blocks            |
| [Inode 1: main/ ]                  | [Block 101: SQL Tables          ] |
| [Inode 2: postmaster.pid (FAIL)]   | [Block 102: file1.bk (7.0 GB)   ] |
+------------------------------------+----------------------------------+
  • Block Exhaustion (df -h at 100%): Occurs when large files consume all available data blocks. New files cannot be created even if empty inodes exist.
  • Inode Exhaustion (df -i at 100%): Occurs when millions of zero-byte or tiny files consume all inode slots. No new files can be created even if gigabytes of block space remain.

Systemd Meta-Services vs. Instantiated Template Units

Debian and Ubuntu packaging uses systemd Template Units (postgresql@.service) managed by a parent wrapper service (postgresql.service).

  • postgresql.service: A static dummy unit (ExecStart=/bin/true). It acts as a target for legacy sysvinit compatibility (service postgresql start). Running systemctl status postgresql often returns active (exited) because /bin/true succeeds instantly.
  • postgresql@14-main.service: The actual instance unit dynamically generated for version 14 and cluster main. It manages the real /usr/lib/postgresql/14/bin/postgres daemon process.

Process PID & Lock Files (postmaster.pid)

PostgreSQL enforces process exclusivity using a lock file named postmaster.pid in its data directory. This file contains:

  • Process ID (PID) of the main postgres process.
  • Cluster data directory path.
  • Shared memory segment ID.
  • Listening port and socket location.

If PostgreSQL cannot write or acquire this lock file during startup, it immediately aborts to prevent memory corruption.


11. Real-World Sysadmin and SRE Applications

Production Monitoring & Alerting

To catch storage issues before they cause database downtime, implement alerts using Prometheus node_exporter:

# Prometheus Alerting Rule Definition
groups:
  - name: storage_alerts
    rules:
      - alert: DatabaseVolumeSpaceFillingUp
        expr: (node_filesystem_free_bytes{mountpoint="/opt/pgdata"} / node_filesystem_size_bytes{mountpoint="/opt/pgdata"}) * 100 < 10
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "Storage volume {{ $labels.mountpoint }} has under 10% space remaining."

Architectural Best Practices

  1. Mount Point Isolation: Database data directories (e.g., /var/lib/postgresql) must be mounted on dedicated block devices, separate from OS root (/), application logs (/var/log), and manual backup staging areas.
  2. Systemd Failure Handlers: Configure systemd template units with OnFailure= directives to automatically trigger alerting scripts or PagerDuty webhooks when an instance crashes.

12. Further Learning and Experiments

Safe Hands-on Experiment: Simulating Block Space Exhaustion

You can safely replicate storage exhaustion in a test VM using a virtual loopback filesystem:

# 1. Create a 100MB dummy image file
dd if=/dev/zero of=/tmp/testdisk.img bs=1M count=100

# 2. Format as ext4
mkfs.ext4 /tmp/testdisk.img

# 3. Create mount point and mount via loop device
sudo mkdir -p /mnt/fulltest
sudo mount -o loop /tmp/testdisk.img /mnt/fulltest

# 4. Fill 100% of the block space
sudo dd if=/dev/zero of=/mnt/fulltest/space_hog.dat bs=1M count=100

# 5. Observe kernel ENOSPC behavior on file creation
sudo touch /mnt/fulltest/newfile.txt
# Output: touch: cannot touch '/mnt/fulltest/newfile.txt': No space left on device

# 6. Cleanup
sudo umount /mnt/fulltest
rm -f /tmp/testdisk.img

Official References & Documentation


13. Key Takeaways

  • Do not trust wrapper systemd units blindly: Always inspect instantiated template units (systemctl status postgresql@14-main) or cluster utilities (pg_lsclusters) when master wrapper services show active (exited).
  • Distinguish space usage types: Use df -h for data block exhaustion and df -i for inode exhaustion.
  • Understand application fatal errors: Database daemons require disk writes for lock/PID files during startup. A full disk prevents daemon initialization completely.
  • Enforce mount point segregation: Never store backup files, log exports, or temporary dumps on the same volume partition as active database data directories.

14. Quick Troubleshooting Reference / Runbook

+---------------------------------------------------------------------------------------+
|                      PostgreSQL Service Failure Runbook                              |
+---------------------------------------------------------------------------------------+
|                                                                                       |
|  1. Verify Connection & Error                                                         |
|     $ sudo -u postgres psql -c "SELECT 1;"                                            |
|                                                                                       |
|  2. Check True Cluster Status                                                         |
|     $ pg_lsclusters                                                                   |
|     $ systemctl status postgresql@14-main                                             |
|                                                                                       |
|  3. Inspect Service Logs                                                              |
|     $ journalctl -u postgresql@14-main -n 50 --no-pager                               |
|                                                                                       |
|  4. Check Storage Capacity (Blocks & Inodes)                                          |
|     $ df -h /path/to/data_directory                                                   |
|     $ df -i /path/to/data_directory                                                   |
|                                                                                       |
|  5. Locate Space Consuming Files                                                      |
|     $ du -sh /path/to/data_directory/* | sort -rh                                     |
|                                                                                       |
|  6. Remediate & Restart                                                               |
|     $ rm -f /path/to/unwanted_backups                                                 |
|     $ systemctl restart postgresql@14-main                                            |
|                                                                                       |
|  7. Validate Write Functionality                                                      |
|     $ sudo -u postgres psql -c "INSERT INTO ..." -d target_db                         |
|                                                                                       |
+---------------------------------------------------------------------------------------+