PostgreSQL Connection Error Analysis and Solutions: A Comprehensive Guide from postmaster.pid to Port Configuration

Problem Background and Error Analysis

When using PostgreSQL on macOS systems, users frequently encounter connection errors: psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory. This error typically occurs when database connections are terminated abnormally, such as through terminal crashes or system-forced process termination.

Core Problem Diagnosis

PostgreSQL uses the postmaster.pid file to manage service process locking states. When database services shut down abnormally, this file may remain, preventing new service instances from starting. Below are systematic diagnostic steps:

Checking Service Status

First, use the pgrep -l postgres command to check if PostgreSQL processes are running. If no output appears, the service is not started.

pgrep -l postgres

Viewing Log Files

Detailed error information can be obtained by examining PostgreSQL log files:

tail /usr/local/var/log/postgres.log

Common error messages include: FATAL: lock file "postmaster.pid" already exists, indicating residual process lock files.

Solution Implementation

Solution 1: Removing Residual PID Files (Primary Solution)

For Intel-based Mac devices, execute the following commands:

rm /usr/local/var/postgres/postmaster.pid
brew services restart postgresql

For Apple Silicon (M1/M2/M3) Mac devices, the path differs:

rm /opt/homebrew/var/postgres/postmaster.pid
brew services restart postgresql

Solution 2: Port Configuration Adjustment

In some cases, PostgreSQL may be listening on the wrong port. This can be resolved by modifying the configuration file:

sudo code /usr/local/var/postgres/postgresql.conf

Locate the port parameter in the configuration file and ensure its value is 5432:

port = 5432

After modification, restart the service:

brew services restart postgresql

Solution 3: Version Downgrade Handling

If upgrades cause compatibility issues, consider downgrading to a stable version:

brew uninstall postgresql
brew install postgresql@13
brew services start postgresql@13
brew link postgresql@13 --force

In-Depth Technical Principles

PostgreSQL Process Management Mechanism

PostgreSQL uses the postmaster.pid file as a process locking mechanism, containing the main process PID and shared memory segment information. When services shut down normally, the system automatically deletes this file. However, in abnormal situations, file remnants prevent new instances from starting.

Socket Connection Mechanism

PostgreSQL defaults to using Unix domain sockets for local connections, with the path /tmp/.s.PGSQL.5432. When the service is not running, this socket file does not exist, resulting in the "No such file or directory" error.

Best Practice Recommendations

Service Management Standards

Always use the brew services stop postgresql command to stop services properly, avoiding direct terminal termination or forced application exits.

Regular Maintenance

Periodically check log files, monitor service status, and promptly address abnormal situations. Implementing log rotation strategies is recommended to prevent oversized log files.

Backup Strategies

Before performing major operations (such as version upgrades), always backup important data and configuration files. The pg_dump tool can be used for database backups.

Troubleshooting Process Summary

1. Check service status: pgrep -l postgres
2. View error logs: tail /usr/local/var/log/postgres.log
3. Remove residual PID files (select correct path based on chip architecture)
4. Restart service: brew services restart postgresql
5. If issues persist, check port configuration or consider version downgrade

Through systematic troubleshooting and appropriate solutions, most PostgreSQL connection issues can be effectively resolved. The key lies in understanding the root causes of errors and implementing targeted corrective measures.