Can not connect to PostgreSQL listening on port 5432

You can use psql -U postgres -h localhost to lớn force the connection to lớn happen over TCP instead of UNIX domain name 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 lớn are configured in postgresql.conf.

I found uninstalling Postgres sounds unconvincing. This helps to lớn 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.

I failed to lớn solve this problem with my postgres-9.5 server. After 3 days of zero progress trying every permutation of fix on this and other sites I decided to lớn re-install the server and lose 5 days worth of work. But, I did replicate the issue on the new instance. This might provide some perspective on how to lớn fix it before you take the catastrophic approach I did.

First, disable all logging settings in postgresql.conf. This is the section:

# ERROR REPORTING AND LOGGING

Comment out everything in that section. Then restart the service.

When restarting, use /etc/init.d/postgresql start or restart I found it helpful to lớn be in superuser mode while restarting. I had a x-window open just for that operation. You can establish that superuser mode with sudo -i.

Verify that the server can be reached with this simple command: psql -l -U postgres

If that doesn't fix it, then consider this:

I was changing the ownership on many folders while trying to lớn find a solution. I knew that I'd probably be trying to lớn revert those thư mục ownerships and chmods for 2 more days. If you have already messed with those thư mục ownerships and don't want to lớn completely purge your server, then start tracking the settings for all impacted folders to lớn bring them back to lớn the original state. You might want to lớn try to lớn tự a parallel install on another system and systematically kiểm tra the ownership and settings of all folders. Tedious, but you may be able to lớn get access to lớn your data.

Once you tự gain access, systematically change each relevant line in the # ERROR REPORTING AND LOGGING section of the postgresql.conf tệp tin. Restart and test. I found that the mặc định thư mục for the logs was causing a failure. I specifically commented out log_directory. The mặc định thư mục the system drops the logs into is then /var/log/postgresql.

Possibly it could have happened because you changed the permissions of the /var/lib/postgresql/9.3/main thư mục.

Try changing it to lớn 700 using the command below:

sudo chmod 700 main

This is not exactly related to lớn the question since I'm using Flask, but this was the exact error I was getting and this was the most relevant thread to lớn get ideas.

My setup: Windows Subsystem for Linux, Docker-compose w/ makefile w/ dockerfile, Flask, Postgresql (using a schema consisting of tables)

To connect to lớn postgres, setup your connection string lượt thích this:

from flask import Flask
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql+psycopg2://:@/"

NOTE: I never got any IP (e.g. localhost, 127.0.0.1) to lớn work using any method in this thread. Idea for using the container name instead of localhost came from here: https://github.com/docker-library/postgres/issues/297

Set your schema:

from sqlalchemy import MetaData
db = SQLAlchemy(app, metadata=MetaData(schema=""))

Set your tìm kiếm path for your functions when you setup your session:

db.session.execute("SET search_path TO ")

The most upvoted answer isn't even remotely correct because you can see in the question the server is running on the expected port (he shows this with netstat).

While the OP did not mark the other answer as chosen, they commented that the other answer (which makes sense and works) was sufficient,

But for these reasons that solution is poor and insecure even if it the server wasn't running on port 5432:

What you're doing here when you say --purge is you're deleting the configuration tệp tin for PostgreSQL ((as well as all of the data with the database. You or may not even see a warning about this, but here is the warning just to lớn show you now,

Removing the PostgreSQL server package will leave existing database clusters intact, i.e. their configuration, data, and log directories will not be removed. On purging the package, the directories can optionally be removed. Remove PostgreSQL directories when package is purged? [prompt for yes or no]

When you add it again PostgreSQL is reinstalling it to lớn a port number that's not taken (which may be the port number you expect). Before you even try this solution, you need to lớn answer a few questions along the same line:

• Do I want multiple versions of PostgreSQL on my machine?
• Do I want an older version of PostgreSQL?
• What tự I want to lớn happen when I dist-upgrade and there is a newer version?

Currently when you dist-upgrade on Ubuntu (and any Debian variant), the new version of PostgreSQL is installed alongside the old copy and the port number on the new copy is the port number of the old copy + 1. So you can just start it up, increment the port number in your client and you've got a new install! And you have the old install to lớn fall back on -- it's safe!

However, if you only one want version of PostgreSQL purging to lớn change the configuration is still not the right option because it will destroy your database. The only time this could even be acceptable is you want to lớn destroy everything related to lớn PostgreSQL. You're better off ensuring your database is correct and then merely editing the configuration tệp tin so sánh the new install runs on the old port number

#!/bin/bash

# We can find the version number of the newest PostgreSQL with this
VERSION=$(dpkg-query -W -f "\${Version}" 'postgresql' | sed -e's/+.*//')
PGCONF="/etc/postgresql/${VERSION}/main/postgresql.conf"

# Then we can update the port.
sudo sed -ie '/port = /s/.*/port = 5432/' "$PGCONF"

sudo systemctl restart postgresql

Do not install a specific version of PostgreSQL. Only ever install postgresql. If you install a specific version then when you dist-upgrade your version will simply remain on your computer forever without upgrades. The repo will no longer have the security patches for the old version (which they don't support). This must always be suboptimal to lớn getting a newer version that they tự tư vấn, running on a different port number.

I had the exact same problem Peter Eisentraut described. Using the netstat -nlp | grep 5432 command, I could see the server was listening on socket /tmp/.s.PGSQL.5432.

To fix this, just edit your postgresql.conf tệp tin and change the following lines:

listen_addresses = '*'
unix_socket_directories = '/var/run/postgresql'

Now lập cập service postgresql-9.4 restart (Replace 9-4 with your version), and remote connections should be working now.

Now to lớn allow local connections, simply create a symbolic links to lớn the /var/run/postgresql directory.

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

Don't forget to lớn make sure your pg_hba.conf is correctly configured too.

In my case, all i had to lớn tự was this:

sudo service postgresql restart

and then

sudo -u postgres psql

This worked just fine. Hope it helps. Cheers :) .

Find your file:

sudo find /tmp/ -name .s.PGSQL.5432

Result:

/tmp/.s.PGSQL.5432

Login as postgres user:

su postgres
psql -h /tmp/ yourdatabase

I had the same problem (on Ubuntu 15.10 (wily)). sudo find / -name 'pg_hba.conf' -print or sudo find / -name 'postgresql.conf' -print turned up empty. Before that it seemed that multiple instances of postgresql were installed.

You might have similar when you see as installed, or dependency problems listing

.../postgresql
.../postgresql-9.x 

and so sánh on.

In that case you must sudo apt-get autoremove each package 1 by 1.

Then following this to lớn the letter and you will be fine. Especially when it comes to lớn key importing and adding to lớn source list FIRST

sudo apt-get update && sudo apt-get -y install python-software-properties && wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

If not using wily, replace wily with your release, i.e with the output of lsb_release -cs

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'
sudo apt-get update && sudo apt-get install postgresql-9.3 pgadmin3

And then you should be fine and be able to lớn connect and create users.

Expected output:

Creating new cluster 9.3/main ...
config /etc/postgresql/9.3/main
data   /var/lib/postgresql/9.3/main
locale en_US.UTF-8
socket /var/run/postgresql
port   5432

Source of my solutions (credits)

While having the same issue I tried something different:

Starting the postgresql daemon manually I got:

FATAL:  could not create shared memory segment ...
   To reduce the request size (currently 57237504 bytes), reduce PostgreSQL's
   shared memory usage, perhaps by reducing shared_buffers or max_connections.

So what I did was to lớn mix a lower limit for shared_buffers and max_connections into postgresql.conf and restart the service.

This fixed the problem!

Here's the full error log:

$ sudo service postgresql start
 * Starting PostgreSQL 9.1 database server                                                                                                                                                               * The PostgreSQL server failed to lớn start. Please kiểm tra the log output:
2013-06-26 15:05:11 CEST FATAL:  could not create shared memory segment: Invalid argument
2013-06-26 15:05:11 CEST DETAIL:  Failed system Call was shmget(key=5432001, size=57237504, 03600).
2013-06-26 15:05:11 CEST HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.  You can either reduce the request size or reconfigure the kernel with larger SHMMAX.  To reduce the request size (currently 57237504 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
    If the request size is already small, it's possible that it is less than vãn your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
    The PostgreSQL documentation contains more information about shared memory configuration.

1. Check the status of postgresql:

   service postgresql status
   

   If it shows online, proceed to lớn step no 3 else execute step no 2.

2. To make postgresql online, execute the following command:

   sudo service postgresql start
   

   Now kiểm tra the status by running the command of the previous step. It should show online.

3. To start psql session, execute the following command:

   sudo su postreg
   

4. Finally, kiểm tra if it's working or not by executing:

   psql
   

Restart postgresql by using the command

sudo /opt/bitnami/ctlscript.sh restart postgresql

This error could mean a lot of different things.

In my case, I was running PostgreSQL 12 on a virtual machine.

I had changed the shared_buffer config and apparently, the system administrator edited the memory config for the virtual machine reducing the RAM allocation from where it was to lớn below what I had mix for the shared_buffer.

I figured that out by looking at the log in

/var/log/postgresql/postgresql-12-main.log

and after that I restarted the service using

sudo systemctl restart postgresql.service

that's how it worked