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_directoryin/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
- Unreachable Unix Domain Socket:
psqlcannot 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. - 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 thepostgresadministrative 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 databasedt.
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: Queriessystemd-journaldandsystemdcontrol 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 cluster14-main.-n 30 --no-pager: Displays the last 30 log lines directly without invokingless.
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": Filterspostgresql.conffor un-commented lines beginning withdata_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"]
- Storage Exhaustion (
ENOSPC): Arbitrary backup files (file1.bkandfile2.bk) consumed nearly all available space on/opt/pgdata. - Lock File Failure: When PostgreSQL starts,
pg_ctlattempts to createpostmaster.pidinside the cluster data directory (/opt/pgdata/main) to enforce single-process memory access. Kernel callopen()returnedENOSPC(No space left on device). - Database Termination: PostgreSQL immediately aborted startup, preventing initialization of shared memory and Unix domain sockets.
- Misleading Systemd Status: The meta-wrapper
postgresql.servicereportedactive (exited)because/bin/truecompleted cleanly. The actual template unitpostgresql@14-main.servicehad failed.
6. Solution and Remediation
Remediation Steps
- Delete the unneeded backup files from
/opt/pgdata/to free storage capacity. - Verify disk space availability on
/opt/pgdata. - 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 instantiatepostgresql@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:
- Data Blocks: Store actual file contents.
- 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 -hat 100%): Occurs when large files consume all available data blocks. New files cannot be created even if empty inodes exist. - Inode Exhaustion (
df -iat 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). Runningsystemctl status postgresqloften returnsactive (exited)because/bin/truesucceeds instantly.postgresql@14-main.service: The actual instance unit dynamically generated for version14and clustermain. It manages the real/usr/lib/postgresql/14/bin/postgresdaemon 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
postgresprocess. - 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
- 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. - 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
- Linux Manual Pages:
man 1 df,man 1 du,man 1 journalctl,man 8 systemctl. - PostgreSQL Official Documentation: Server Setup and Operation.
- Systemd Documentation: systemd.unit — Unit configuration.
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 showactive (exited). - Distinguish space usage types: Use
df -hfor data block exhaustion anddf -ifor 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 |
| |
+---------------------------------------------------------------------------------------+