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  lt tgl sss pgh pa us gt  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

User · Answer

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.

User · Answer

Quick answer on OSX  set your environment variables    gt export PGHOST localhost   gt export PGPORT 5432   Or whatever you need

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[postgresql] postgresql port confusion 5433 or 5432?

The answer is

Examples related to postgresql

Examples related to port

Examples related to psql

Tags