postgresql port confusion 5433 or 5432?

Question

I have installed postgresql on OSX. When I run psql, I get

$ psql
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5433"?

However, from /etc/services

postgresql      5432/udp    # PostgreSQL Database
postgresql      5432/tcp    # PostgreSQL Database
#                          Tom Lane <[e-mail]>
pyrrho          5433/tcp    # Pyrrho DBMS
pyrrho          5433/udp    # Pyrrho DBMS

5433 is occupied by pyrrho, 5432 is assigned to pg. I can connect with

psql -p 5432

but why does psql think it is 5433 and how do I make psql look in the right place by default?

Answer 1

It seems that one of the most common reasons this happens is if you install a new version of PostgreSQL without stopping the service of an existing installation. This was a particular headache of mine, too. Before installing or upgrading, particularly on OS X and using the one click installer from Enterprise DB, make sure you check the status of the old installation before proceeding.

Answer 2

The default port of Postgres is commonly configured in:

sudo vi /<path to your installation>/data/postgresql.conf

On Ubuntu this might be:

sudo vi /<path to your installation>/main/postgresql.conf

Search for port in this file.

Answer 3

Thanks to @a_horse_with_no_name's comment, I changed my PGPORT definition to 5432 in pg_env.sh. That fixed the problem for me. I don't know why postgres set it as 5433 initially when it was hosting the service at 5432.

Answer 4

For me in PgAdmin 4 on Mac OS High Sierra, Clicking the PostrgreSQL10 database under Servers in the left column, then the Properties tab, showed 5433 as the port under Connection. (I don't know why, because I chose 5432 during install). Anyway, I clicked the Edit icon under the Properties tab, change that to 5432, saved, and that solved the problem. Go figure.

Answer 5

I ran into this problem as well, it ended up that I had two postgres servers running at the same time. I uninstalled one of them and changed the port back to 5432 and works fine now.

Answer 6

/etc/services is only advisory, it's a listing of well-known ports. It doesn't mean that anything is actually running on that port or that the named service will run on that port.

In PostgreSQL's case it's typical to use port 5432 if it is available. If it isn't, most installers will choose the next free port, usually 5433.

You can see what is actually running using the netstat tool (available on OS X, Windows, and Linux, with command line syntax varying across all three).

This is further complicated on Mac OS X systems by the horrible mess of different PostgreSQL packages - Apple's ancient version of PostgreSQL built in to the OS, Postgres.app, Homebrew, Macports, the EnterpriseDB installer, etc etc.

What ends up happening is that the user installs Pg and starts a server from one packaging, but uses the psql and libpq client from a different packaging. Typically this occurs when they're running Postgres.app or homebrew Pg and connecting with the psql that shipped with the OS. Not only do these sometimes have different default ports, but the Pg that shipped with Mac OS X has a different default unix socket path, so even if the server is running on the same port it won't be listening to the same unix socket.

Most Mac users work around this by just using tcp/ip with psql -h localhost. You can also specify a port if required, eg psql -h localhost -p 5433. You might have multiple PostgreSQL instances running so make sure you're connecting to the right one by using select version() and SHOW data_directory;.

You can also specify a unix socket directory; check the unix_socket_directories setting of the PostgreSQL instance you wish to connect to and specify that with psql -h, e.g.psql -h /tmp.

A cleaner solution is to correct your system PATH so that the psql and libpq associated with the PostgreSQL you are actually running is what's found first on the PATH. The details of that depend on your Mac OS X version and which Pg packages you have installed. I don't use Mac and can't offer much more detail on that side without spending more time than is currently available.