Can not connect to PostgreSQL listening on port 5432

This issue comes from installing the postgres package without a version number. Although postgres will be installed and it will be the correct version, the script to setup the cluster will not run correctly; it's a packaging issue.

If you're comfortable with postgres there is a script you can run to create this cluster and get postgres running. However, there's an easier way.

First purge the old postgres install, which will remove everything of the old installation, including databases, so back up your databases first.. The issue currently lies with 9.1 so I will assume that's what you have installed

sudo apt-get remove --purge postgresql-9.1

Now simply reinstall

sudo apt-get install postgresql-9.1

Note the package name with the version number. HTH.

The error message refers to a Unix-domain socket, so you need to tweak your netstat invocation to not exclude them. So try it without the option -t:

netstat -nlp | grep 5432

I would guess that the server is actually listening on the socket /tmp/.s.PGSQL.5432 rather than the /var/run/postgresql/.s.PGSQL.5432 that your client is attempting to connect to. This is a typical problem when using hand-compiled or third-party PostgreSQL packages on Debian or Ubuntu, because the source default for the Unix-domain socket directory is /tmp but the Debian packaging changes it to /var/run/postgresql.

Possible workarounds:

• Use the clients supplied by your third-party package (call /opt/djangostack-1.3-0/postgresql/bin/psql). Possibly uninstall the Ubuntu-supplied packages altogether (might be difficult because of other reverse dependencies).
• Fix the socket directory of the third-party package to be compatible with Debian/Ubuntu.
• Use -H localhost to connect via TCP/IP instead.
• Use -h /tmp or equivalent PGHOST setting to point to the right directory.
• Don't use third-party packages.

This works for me:

Edit: postgresql.conf

sudo nano /etc/postgresql/9.3/main/postgresql.conf

Enable or add:

listen_addresses = '*'

Restart the database engine:

sudo service postgresql restart

Also, you can check the file pg_hba.conf

sudo nano /etc/postgresql/9.3/main/pg_hba.conf

And add your network or host address:

host    all             all             192.168.1.0/24          md5

Just create a softlink like this :

ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

You can use psql -U postgres -h localhost to force the connection to happen over TCP instead of UNIX domain sockets; your netstat output shows that the PostgreSQL server is listening on localhost's port 5432.

You can find out which local UNIX socket is used by the PostgrSQL server by using a different invocavtion of netstat:

netstat -lp --protocol=unix | grep postgres

At any rate, the interfaces on which the PostgreSQL server listens to are configured in postgresql.conf.

I make it work by doing this:

dpkg-reconfigure locales

Choose your preferred locales then run

pg_createcluster 9.5 main --start

(9.5 is my version of postgresql)

/etc/init.d/postgresql start

and then it works!

sudo su - postgres
psql

I had to compile PostgreSQL 8.1 on Debian Squeeze because I am using Project Open, which is based on OpenACS and will not run on more recent versions of PostgreSQL.

The default compile configuration puts the unix_socket in /tmp, but Project Open, which relies on PostgreSQL, would not work because it looks for the unix_socket at /var/run/postgresql.

There is a setting in postgresql.conf to set the location of the socket. My problem was that either I could set for /tmp and psql worked, but not project open, or I could set it for /var/run/postgresql and psql would not work but project open did.

One resolution to the issue is to set the socket for /var/run/postgresql and then run psql, based on Peter's suggestion, as:

psql -h /var/run/postgresql

This runs locally using local permissions. The only drawback is that it is more typing than simply "psql".

The other suggestion that someone made was to create a symbolic link between the two locations. This also worked, but, the link disappeared upon reboot. It maybe easier to just use the -h argument, however, I created the symbolic link from within the PostgreSQL script in /etc/init.d. I placed the symbolic link create command in the "start" section. Of course, when I issue a stop and start or restart command, it will try to recreate an existing symbolic link, but other than warning message, there is probably no harm in that.

In my case, instead of:

ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

I have

ln -s /var/run/postgresql/.s.PGSQL.5432 /tmp/.s.PGSQL.5432

and have explicitly set the unix_socket to /var/run/postgresql/.s.PGSQL.5432 in postgresql.conf.

If your Postgres service is up and running without any error or there is no error in starting the Postgres service and still you are getting the mentioned error, follow these steps

Step1: Running pg_lsclusters will list all the postgres clusters running on your device

eg:

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

most probably the status will be down in your case and postgres service

Step 2: Restart the pg_ctlcluster

#format is pg_ctlcluster <version> <cluster> <action>
sudo pg_ctlcluster 9.6 main start

#restart postgresql service
sudo service postgresql restart

Step 3: Step 2 failed and threw error

If this process is not successful it will throw an error. You can see the error log on /var/log/postgresql/postgresql-9.6-main.log

My error was:

FATAL: could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
Try adding `postgres` user to the group `ssl-cert`

Step 4: check ownership of postgres

Make sure that postgres is the owner of /var/lib/postgresql/version_no/main

If not, run

sudo chown postgres -R /var/lib/postgresql/9.6/main/

Step 5: Check postgres user belongs to ssl-cert user group

It turned out that I had erroneously removed the Postgres user from the ssl-cert group. Run the below code to fix the user group issue and fix the permissions

#set user to group back with
sudo gpasswd -a postgres ssl-cert

# Fix ownership and mode
sudo chown root:ssl-cert  /etc/ssl/private/ssl-cert-snakeoil.key
sudo chmod 740 /etc/ssl/private/ssl-cert-snakeoil.key

# now postgresql starts! (and install command doesn't fail anymore)
sudo service postgresql restart

I found uninstalling Postgres sounds unconvincing. This helps to solve my problem:

1. Start the postgres server:

   sudo systemctl start postgresql
   

2. Make sure that the server starts on boot:

   sudo systemctl enable postgresql
   

Detail information can be found on DigitalOcean site Here.

Solution:

Do this

export LC_ALL="en_US.UTF-8"

and this. (9.3 is my current PostgreSQL version. Write your version!)

sudo pg_createcluster 9.3 main --start