Victor's room

Overview & Root Cause Summary: The error psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory occurs when the psql client attempts a local Unix domain socket connection, but the server’s socket file is missing because the PostgreSQL daemon is stopped, the service is listening in an alternate directory (such as /tmp), or port mismatch occurred after a version upgrade.

Understanding the Root Causes

• PostgreSQL Service Inactive or Crashed: The PostgreSQL server daemon (postgres / postmaster) is not running due to a system restart, configuration syntax error, or unhandled crash.
• Unix Domain Socket Directory Mismatch: Debian/Ubuntu defaults to placing the socket in /var/run/postgresql/, whereas macOS Homebrew, Fedora, and Arch Linux default to /tmp/.
• Port Collision / Version Cluster Shift: Upgrading PostgreSQL major versions (e.g., from v15 to v16) can leave the active cluster running on port 5433 while the client targets port 5432.
• Stale Lock File (postmaster.pid): A hard system reboot leaves an orphaned lock file in the data directory, preventing the daemon from creating the socket.

Step 1: Quick Fix (Verify & Start PostgreSQL Service)

Check the status of your PostgreSQL service and start the daemon if inactive.

# On Linux (Ubuntu / Debian / RHEL):
sudo systemctl status postgresql

# Start and enable PostgreSQL on system boot:
sudo systemctl enable --now postgresql

# On macOS (Homebrew):
brew services list
brew services start postgresql@16

# Check active listening sockets and clusters (Debian/Ubuntu):
pg_lsclusters

Step 2: Socket Path Redirection & TCP Host Override

If the client looks in the wrong socket directory or if the server is bound to TCP/IP, override the connection target.

# 1. Connect via TCP loopback (bypasses Unix domain socket file):
psql -h 127.0.0.1 -U postgres

# 2. Connect explicitly pointing to the macOS /tmp socket path:
psql -h /tmp -U postgres

# 3. If running on a non-default cluster port (e.g., 5433 after upgrade):
psql -p 5433 -U postgres

# 4. Set persistent socket directory in postgresql.conf:
# unix_socket_directories = '/var/run/postgresql, /tmp'

Step 3: Fix Directory Permissions & Remove Orphaned postmaster.pid

Ensure the socket directory exists with proper permissions and remove stale lock files if the service fails to start.

# 1. Recreate the socket directory and set ownership:
sudo mkdir -p /var/run/postgresql
sudo chown -R postgres:postgres /var/run/postgresql
sudo chmod 775 /var/run/postgresql

# 2. If PostgreSQL fails to start due to stale lock:
# Locate your data directory (e.g., /var/lib/postgresql/16/main or /usr/local/var/postgres)
# Check if postmaster.pid exists:
ls -la /var/lib/postgresql/16/main/postmaster.pid

# If postgres process is NOT running, remove the stale PID file:
sudo rm /var/lib/postgresql/16/main/postmaster.pid
sudo systemctl start postgresql

Verification & Testing Steps

Verify that the PostgreSQL server is accepting connections cleanly across local and TCP sockets.

# 1. Test database availability using pg_isready:
pg_isready -h localhost -p 5432

# 2. Verify Unix domain socket status:
pg_isready -d postgres

# 3. Test local authentication:
sudo -u postgres psql -c "SELECT version();"

Summary Comparison Table