Resolving PostgreSQL Port Confusion: 5432 vs 5433 Connection Issues

Problem Background and Phenomenon Analysis

After installing PostgreSQL on macOS systems, users frequently encounter connection errors when executing the psql command, with prompts indicating inability to establish connections on Unix domain socket /tmp/.s.PGSQL.5433. However, according to the definition in the /etc/services file, PostgreSQL's standard port should be 5432, while port 5433 is assigned to Pyrrho DBMS. This port inconsistency forces users to explicitly specify port parameters for successful connections: psql -p 5432.

The Nature and Limitations of /etc/services File

The /etc/services file serves only as a reference list for well-known ports and does not mandate that services must run on specified ports. This file provides standard mapping relationships between port numbers and services, but actually running services can completely choose other available ports. During PostgreSQL installation, if the standard port 5432 is already occupied, the installer typically selects the next available port 5433.

Complexity of PostgreSQL Installation in macOS Environment

Multiple PostgreSQL installation methods exist in macOS systems, including the system's built-in older version, Postgres.app, Homebrew, MacPorts, and EnterpriseDB installation packages. These different installation packages may cause client-server mismatches: users might install and start PostgreSQL service from one package but use the psql client and libpq library from another package. This mismatch manifests not only in different default ports but may also involve differences in default Unix socket paths.

Problem Diagnosis and Verification Methods

To determine the actually running PostgreSQL instance and its listening port, use the netstat tool for inspection. Execute in macOS terminal:

netstat -an | grep 543

This command displays all network connections related to 543, helping identify running PostgreSQL instances and their used ports. Simultaneously, confirm connection details by querying the running PostgreSQL instance:

psql -h localhost -p 5432 -c "SELECT version(); SHOW data_directory;"

Solutions and Configuration Methods

Several solutions are provided for port confusion issues:

Temporary Connection Solutions

Use TCP/IP connections and explicitly specify ports:

psql -h localhost -p 5432

Or specify Unix socket directory:

psql -h /tmp

Environment Variable Configuration

Specify default connection parameters by setting environment variables:

export PGHOST=localhost
export PGPORT=5432

Adding these configurations to shell configuration files (such as ~/.bashrc or ~/.zshrc) enables permanent effect.

System PATH Correction

The most fundamental solution is ensuring the system PATH environment variable preferentially points to the psql client corresponding to the actually running PostgreSQL instance. Specific operations depend on the used PostgreSQL installation package:

• For Homebrew installation: export PATH=/usr/local/opt/postgresql@13/bin:$PATH
• For Postgres.app: Add Postgres.app's bin directory to PATH

Configuration File Adjustment

For situations requiring permanent port configuration modifications, edit PostgreSQL's main configuration file:

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

Locate the port configuration item and modify it to 5432, then restart the PostgreSQL service.

Multi-Instance Management Recommendations

If multiple PostgreSQL instances exist in the system, it is recommended to:

• Use different port numbers to distinguish various instances
• Confirm currently connected instances through select version() and SHOW data_directory;
• Consider using pg_ctl command to manage startup and shutdown of different instances

Preventive Measures and Best Practices

To avoid similar port confusion issues, it is recommended to:

• Ensure stopping existing running services before installing new PostgreSQL versions
• Consistently use the same installation method to manage PostgreSQL
• Regularly check PostgreSQL instances running simultaneously in the system
• Standardize connection configuration standards in team development environments

Conclusion

PostgreSQL port confusion issues essentially stem from client-server configuration mismatches. By understanding the advisory nature of /etc/services files, identifying problems caused by multiple installation package coexistence in macOS environments, and adopting appropriate connection parameter configurations or system PATH corrections, this common issue can be effectively resolved. Proper environment variable settings and system path management are key to ensuring coordinated work between PostgreSQL clients and servers.