You have to edit postgresql.conf file and change the listen_addresses line.
This file you can find in the /etc/postgresql/9.3/main directory.
Default Ubuntu config have allowed only localhost (or 127.0.0.1) interface, which is sufficient for using, when every PostgreSQL client work on the same computer, as PostgreSQL server. If you want connect PostgreSQL server from other computers, you have change this config line in this way:
listen_addresses = '*'
Then you have edit pg_hba.conf file, too. In this file you have set, from which computers you can connect to this server and what method of authentication you can use. Usually you will need similar line:
host all all 192.168.1.0/24 md5
Please, read comments in this file...
EDIT:
After the editing postgresql.conf and pg_hba.conf you have to restart postgresql server: sudo service postgresql restart
EDIT2: Highlited configuration files.
Answer from Jan Marek on Stack OverflowYou have to edit postgresql.conf file and change the listen_addresses line.
This file you can find in the /etc/postgresql/9.3/main directory.
Default Ubuntu config have allowed only localhost (or 127.0.0.1) interface, which is sufficient for using, when every PostgreSQL client work on the same computer, as PostgreSQL server. If you want connect PostgreSQL server from other computers, you have change this config line in this way:
listen_addresses = '*'
Then you have edit pg_hba.conf file, too. In this file you have set, from which computers you can connect to this server and what method of authentication you can use. Usually you will need similar line:
host all all 192.168.1.0/24 md5
Please, read comments in this file...
EDIT:
After the editing postgresql.conf and pg_hba.conf you have to restart postgresql server: sudo service postgresql restart
EDIT2: Highlited configuration files.
Uncomment the listen_addresses = '*' in the postgresql.conf
This has bitten me a second time so I thought might be worth mentioning. The line listen_addresses = '*' in the postgresql.conf is by default commented. Be sure to uncomment (remove the pound sign, # at the beginning) it after updating otherwise, remote connections will continue to be blocked.
PS: psql -U postgres -c 'SHOW config_file' - to locate the postgresql.conf file path
I face an error since today. Most likely as a reaction to an unexpected shut down last night (local dev.). I did not change anything regarding my setup (django <--> postgresql). Already checked out if a firewall blocked the path over night, but that seems not to be the case.
When I try to runserver:
django.db.utils.OperationalError: connection failed: could not receive data from server: Socket is not connected
When I try to login at pgadmin:
connection to server at "127.0.0.1", port 5432 failed: Connection refused (0x0000274D/10061) Is the server running on that host and accepting TCP/IP connections?
When I try to check the db status (windows powershell):
SERVICE_NAME: postgresql-x64-14
TYPE : 10 WIN32_OWN_PROCESS
STATE : 1 STOPPED
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0There is no postmaster.pid that could couse the problem.
Can you guys help me with suggestions? I am really bad at this type of debugging and hope to retrieve some good inputs. The problem is that I do not know where to start here.
Thanks in advance
Problems connecting to Windows-based Postgresql - Database Administrators Stack Exchange
windows - Postgresql: Connection refused on port 5432 - Stack Overflow
postgresql - unable to connect to server for Postgres - Stack Overflow
database - PostgreSQL: could not connect to server - Connection refused error - Stack Overflow
Here are the steps of troubleshooting I would recommend:
From the local machine try the following. If they work you know the issue is not PostgreSQL. If they don't work then you know your issue is local to the machine.
- psql -h localhost -p 5433
- psql -h [external ip] -p 5433
- If the latter fails, try temporarily disabling all firewalls and trying again
Now if that works the next step is to troubleshoot the network. Traceroute may be helpful here. Also try connecting to any other open ports using other software and make sure that works. From there, you may be able to figure out exactly where the packets are being dropped.
Make sure PostgreSQL Server is allowed through the firewall:
On the server machine, go to
Control Panel > System and Security > Windows Firewall > Allow an app through Windows Firewall
Scroll through Allowed apps and features, make sure 'PostgreSQL Server' is checked.
On windows, Just go to the 'Services'. Start/Restart the postgresql-X64 service. It worked for me as my service was in stopped state somehow.
There are two items to configure if your server isn't on localhost:
- find your
postgresql.confand add your server's public IP address to the end of the settinglisten_addresses(separate multiple entries by commas); uncomment the line if it is commented out (e.g. with '#') - add a line to
pg_hba.confcontaining your client's IP address - you may copy the line containing 127.0.0.1 and change only the IP address
On Ubuntu, these files are in /etc/postgresql/<version>/main/.
Apparently I had forgotten to remove the # from listen_address="*". It was a comment the entire time. I am dumb.
Ensure that the postgresql.conf file has an entry forlisten_addresses='*'. It looks like you are using the wrong configuration parameter name, and might have a space surrounding the asterisk.
Lastly, ensure you restart postgres after making these changes.
cd /etc/postgresql/9.x/main/
open file named postgresql.conf
sudo vi postgresql.conf
{Note:-(You can use sudo "nano") If facing issues with editing through vi(Vim)]
add this line to that file
listen_addresses = '*'
then open file named pg_hba.conf
sudo vi pg_hba.conf
and add this line to that file
host all all 0.0.0.0/0 md5
It allows access to all databases for all users with an encrypted password
restart your server
sudo /etc/init.d/postgresql restart
Check the port defined in postgresql.conf. My installation of postgres 9.4 uses port 5433 instead of 5432