PostgreSQL: could not connect to server: Connection refused

Question

I have a Django project, I want to switch from SQLite to PostgreSQL.

After installation, I can't run the command psql

Here is the traceback:

psql: could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?

OS: Windows

Solution

TCP/IP connections are not enabled by default, so you probably have to edit a file called postgres.conf:

  vi /etc/postgresql/9.4/main/postgresql.conf

For you it may reside in a different location. Look for a line saying:

  #listen_addresses = ''        # what IP address(es) to listen on;

Change it to this:

  listen_addresses = '*'        # what IP address(es) to listen on;

Right under this there's the port setting. For me it reads:

  port = 5432               # (change requires restart)

Higher up in the same file there is a reference to another config file:

  hba_file = '/etc/postgresql/9.4/main/pg_hba.conf' # host-based authentication file

Go ahead and edit that file. You should insert a line like this:

  host all all 192.168.1.0 255.255.255.0 trust

Your IP may be different. (Once you ensure this is working, you can change "trust" to "md5" for better security.) After doing this, you need to restart the postgres server.

  /usr/lib/postgresql/9.4/bin/pg_ctl restart

Comments

[Lemayzeur]

Ronnie, i'm on windows, not linux.. i did it well on my virtual ubuntu. But i can't on windows, and i want to do that on windows. The project is on win10

[Ronnie Barabas]

Hi, can you connect to the server locally? Run psql from the same Win10 machine. If you can, then you could try to create an inbound rule in Firewall to allow access to port 5432.

[Ronnie Barabas]

I dug a little more and it seems the trouble comes from the server not able to start. If you go to "Services" and look for postgresql, it is probably not running. Starting manually will fail. I will try some more tomorrow. In the mean time, if you know you got some errors during installation, I would suggest uninstalling and reinstalling, this time make a new data folder directly in the root of C:\ or other drive.

[Lemayzeur]

ok i will do it ! if the error persist, i think i'm gonna use linux instead

[Ronnie Barabas]

Yesterday, when I was trying to start the postgresql service I got the error message "The PostgreSQL Server 9.5 service on local computer started and then stopped. some service stop automatically if then are not in use by other service or programs". So I went to the Log On tab of the properties and switched to Local System Account and enabled "Allow service to interact with desktop". After that I was able to start it. Back to pgAdmin, right click on PostgreSQL 9.5, selected "Connect" an the rest was easy.

[Ronnie Barabas]

If you are curious what caused your problem, go to the Event Viewer and look for an error message related to postgresql. Mine was this: "FATAL: data directory C:/Users/Dell/Documents/db does not exist." The folder actually existed, so it was the permissions.

[mahoriR]

For postgres 9.6, the port is 5433, by default. I faced the same problem but issue turned out be using wrong port (5432)