Resolving PostgreSQL Connection Error: Could Not Connect to Server - Unix Domain Socket Issue Analysis and Repair

Problem Background and Error Analysis

When deploying PostgreSQL in Vagrant virtual machine environments, developers frequently encounter connection errors: psql: could not connect to server: No such file or directory. This error typically points to the Unix domain socket path /var/run/postgresql/.s.PGSQL.5432, indicating that the PostgreSQL server failed to start properly or has configuration issues.

Core Problem Diagnosis Process

To resolve this issue, a systematic examination of various PostgreSQL service components is required. Below is a diagnostic procedure summarized from actual cases:

Service Status Verification

First, confirm whether the PostgreSQL service is running. Use the following command to check service status:

sudo service postgresql status

If the service is not running, use the start command:

sudo service postgresql start

Cluster Information Check

PostgreSQL uses clusters to manage multiple database instances. Check cluster status using the pg_lsclusters command:

pg_lsclusters

The output example shows critical information including version, cluster name, port, status, etc.:

Version - Cluster   Port Status Owner    Data directory 
9.6     - main     5432 online postgres /var/lib/postgresql/9.6/main

The status column showing "online" indicates normal cluster operation, while other statuses require further investigation.

Manual Cluster Startup

If service startup fails, attempt to manually start a specific cluster using the pg_ctlcluster command:

pg_ctlcluster <version> <cluster> start

For example, for PostgreSQL version 9.6, main cluster:

pg_ctlcluster 9.6 main start

Configuration Error Analysis and Repair

The root cause of PostgreSQL connection issues is often configuration file errors, particularly in the pg_hba.conf file.

Log File Analysis

When PostgreSQL fails to start, the system generates detailed error logs. The log file path is typically:

/var/log/postgresql/postgresql-<version>-main.log

Use a text editor to examine log contents:

sudo nano /var/log/postgresql/postgresql-9.6-main.log

Typical configuration error log example:

2017-07-13 16:53:04 BRT [32176-1] LOG:  invalid authentication method "all"
2017-07-13 16:53:04 BRT [32176-2] CONTEXT:  line 90 of configuration file "/etc/postgresql/9.5/main/pg_hba.conf"
2017-07-13 16:53:04 BRT [32176-3] FATAL:  could not load pg_hba.conf

pg_hba.conf File Repair

The pg_hba.conf file controls client authentication methods. Common errors include using invalid authentication methods or syntax errors. Correct configuration example:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

After fixing configuration errors, restart the PostgreSQL service:

sudo service postgresql restart

Supplementary Solutions

Beyond primary configuration issues, other environmental factors can also cause connection errors.

Directory Permission Issues

In some cases, missing directories cause startup failures. Check and create necessary directory structures:

sudo mkdir -p /usr/local/var/postgres/pg_tblspc
sudo chown -R postgres:postgres /var/lib/postgresql/<version>/main

Process Lock File Handling

If previous PostgreSQL processes terminated abnormally, leftover lock files might prevent new processes from starting:

sudo rm /usr/local/var/postgres/postmaster.pid

Then restart the service.

Preventive Measures and Best Practices

To avoid similar connection issues, follow these best practices:

Configuration File Validation

Always backup original files before modifying PostgreSQL configurations, and use pg_ctlcluster's syntax checking functionality:

pg_ctlcluster <version> <cluster> reload

Service Monitoring

Set up system service monitoring to ensure PostgreSQL starts automatically after system reboots:

sudo systemctl enable postgresql

Environment Consistency

In development environments, ensure all team members use the same PostgreSQL versions and configurations to avoid connection issues due to environmental differences.

Conclusion

The PostgreSQL connection error "could not connect to server: No such file or directory" typically stems from service configuration issues or improper permission settings. Through systematic diagnostic procedures—checking service status, analyzing cluster information, reviewing log files, repairing configuration files—developers can effectively resolve such issues. Understanding PostgreSQL's authentication mechanisms and configuration file structures is crucial for preventing similar problems in the future.