PostgreSQL Connection Troubleshooting: Comprehensive Analysis of psql Server Connection Failures

Problem Background and Error Analysis

When users encounter the "could not connect to server: No such file or directory" error while executing the psql command, this indicates that the PostgreSQL client cannot locate the Unix domain socket file for server communication. The error message explicitly states that the system cannot find the expected socket file at /var/run/postgresql/.s.PGSQL.5432, typically indicating that the database service is not running, socket path configuration is incorrect, or permission issues exist.

Service Status Verification

The first step is to confirm whether the PostgreSQL service is running. On Unix-like systems, use the following command to check PostgreSQL processes:

ps -ef | grep postgres

This command lists all processes containing the "postgres" keyword. If output shows PostgreSQL processes running, the last column typically displays the startup command and its parameters, for example:

501   408     1   0  2Jul15 ??         0:21.63 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.log

Here, the -D parameter specifies the data directory, and -r specifies the log file path. If no PostgreSQL processes appear, the service is not started and requires initiation using system service management commands.

Socket File Location

If the service is running but connections still fail, locate the actual socket file. PostgreSQL may create socket files in the /tmp directory by default:

sudo find /tmp/ -name .s.PGSQL.5432

This command searches for files named .s.PGSQL.5432 in the /tmp directory and its subdirectories. If the socket file is found, connect using the explicit path:

psql -h /tmp/ dbname

Replace dbname with the target database name. This approach bypasses the default socket path and directly uses the located actual path.

Configuration File Inspection and Modification

When the service runs but no socket is found, inspection of the pg_hba.conf configuration file may be necessary. This file resides in the PostgreSQL data directory, typically locatable through running process command parameters. Ensure the configuration file contains the following line to permit local socket connections:

# "local" is for Unix domain socket connections only
local       all       all       trust

If this line is absent or commented out, uncomment or add the appropriate configuration, then restart the PostgreSQL service for changes to take effect. The "trust" permission setting allows local connections without password authentication, though this should be adjusted based on security requirements in production environments.

Advanced Troubleshooting Techniques

For more complex scenarios, use the pg_lsclusters command to list all PostgreSQL cluster statuses on the system:

pg_lsclusters

This command output displays each cluster's version, name, port, status, owner, and data directory information. If status shows "down", use cluster management commands for startup:

sudo pg_ctlcluster 9.6 main start

If startup fails, examine error messages in log files. Common permission issues include incorrect data directory permissions:

sudo chmod -R 0700 /var/lib/postgresql/9.6/main

Or SSL certificate file permission problems:

sudo chown root:ssl-cert /etc/ssl/private/ssl-cert-snakeoil.key
sudo chmod 740 /etc/ssl/private/ssl-cert-snakeoil.key

Ensure the PostgreSQL user belongs to appropriate user groups:

sudo gpasswd -a postgres ssl-cert

Docker Environment Special Considerations

When running PostgreSQL in Docker environments, connection issues may have different causes. When using "localhost" as the hostname, clients might attempt to use local socket files rather than network connections. The solution is specifying IP addresses instead of hostnames:

psql -h 127.0.0.1 -p 5432 -U username -d database

This forces the client to use TCP/IP connections instead of Unix domain sockets. Simultaneously ensure Docker container port mappings are correctly configured, and application connection strings use proper formatting, particularly URL encoding passwords containing special characters.

Systematic Troubleshooting Process Summary

For PostgreSQL connection issues, follow this systematic troubleshooting process: first verify service status, confirming PostgreSQL processes are running; second locate actual socket file positions, attempting connections using explicit paths; then inspect configuration file permissions and settings, ensuring local connections are permitted; finally consider environment-specific factors like Docker configurations or network settings. This hierarchical approach efficiently identifies and resolves most connection problems.