Can't connect to Postgresql on port 5432
PostgresqlSshPortRemote AccessPostgresql Problem Overview
I have PostgreSQL 9.3 installed on a server running Ubuntu Server 14.04.
If I ssh into the server via terminal, I'm able to connect with psql. But when I try to configure pgAdmin III to do the remote connection, I get:
> Server doesn't listen The server doesn't accept connections: the > connection library reports could not connect to server: Connection > refused Is the server running on host "172.24.3.147" and accepting > TCP/IP connections on port 5432?
When I run on the server service postgresql status it gives me:
9.3/main (port 5432): online
So of course I'm missing something important here.
EDIT
When running netstat -na on the server, I get (relevant portion, I guess):
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
tcp 0 0 172.24.3.147:22 172.24.3.240:61950 ESTABLISHED
tcp 0 0 172.24.3.147:22 172.24.3.240:60214 ESTABLISHED
Postgresql Solutions
Solution 1 - Postgresql
You have to edit postgresql.conf file and change line with 'listen_addresses'.
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.
EDIT2: Highlited configuration files.
Solution 2 - Postgresql
> 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
Solution 3 - Postgresql
Had same problem with psql via command line connecting and pgAdmin not connecting on RDS with AWS. I did have my RDS set to Publicly Accessible. I made sure my ACL and security groups were wide open and still problem so, I did the following:
sudo find . -name *.conf
then sudo nano ./data/pg_hba.conf
then added to top of directives in pg_hba.conf file host all all 0.0.0.0/0 md5
and pgAdmin automatically logged me in.
This also worked in pg_hba.conf file
host all all md5 without any IP address and this also worked with my IP address host all all <myip>/32 md5
As a side note, my RDS was in my default VPC. I had an identical RDS instance in my non-default VPC with identical security group, ACL and security group settings to my default VPC and I could not get it to work. Not sure why but, that's for another day.
Solution 4 - Postgresql
Remember to check firewall settings as well. after checking and double-checking my pg_hba.conf and postgres.conf files I finally found out that my firewall was overriding everything and therefore blocking connections
Solution 5 - Postgresql
You probably need to either open up the port to access it in your LAN (or outside of it) or bind the network address to the port (make PostgreSQL listen on your LAN instead of just on localhost)
Solution 6 - Postgresql
just summary from the accepted answer
if maybe someone like me overlook the filename that must be edit
in my case the conf file located at
/etc/postgresql/14/main'
if you type cd /etc/postgresql/14/main
so just sudo nano add these 2 filenames
- postgresql.conf
listen_addresses = '*'
uncomment this line and change localhost to *
- pg_hba.conf
host all all 0.0.0.0/0
change the 127.0.0.1/32 change to 0.0.0.0/0
last don't forget to restart using sudo service postgresql restart
I hope this one clears the notification error
Solution 7 - Postgresql
I had the same problem after a MacOS system upgrade. Solved it by upgrading the postgres with brew. Details: it looks like the system was trying to access Postgres 11 using older Postgres 10 settings. I'm sure it was my mistake somewhere in the past, but luckily it all got sorted out with the upgrade above.
Solution 8 - Postgresql
I had this same issue. I originally installed version 10 because that was the default install with Ubuntu 18.04. I later upgraded to 13.2 because I wanted the latest version. I made all the config modifications, but it was still just binging to 1207.0.0.1 and then I thought - maybe it is looking at the config files for version 10. I modified those and restarted the postgres service. Bingo! It was binding to 0.0.0.0
I will need to completely remove 10 and ensure that I am forcing the service to run under version 13.2, so if you upgraded from another version, try updating the other config files in that older directory.
Solution 9 - Postgresql
You have to edit pg_hba.conf to accept all requests within the network
#TYPE DATABASE USER ADDRESS METHOD
host all all 0.0.0.0/0 md5
host all all ::1/128 md5
From your application, you can connect using this IP address like below example: -
> postgresql://postgres:******@192.168.1.101:5432/app
Solution 10 - Postgresql
If error occurred first thing first you have to read the log.
- This only applicable for postgresql installation through brew on your mac *
First, you have to check the postgresql status by running
brew services on your terminal
If its stopped, try to started it and then run the brew services again
And if the status show error, you can easily find the log location through the *.plist file.
Example, on my configuration
cat /Users/drosanda/Library/LaunchAgents/[email protected]
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>KeepAlive</key>
<true/>
<key>Label</key>
<string>homebrew.mxcl.postgresql@12</string>
<key>ProgramArguments</key>
<array>
<string>/usr/local/opt/postgresql@12/bin/postgres</string>
<string>-D</string>
<string>/usr/local/var/postgresql@12</string>
</array>
<key>RunAtLoad</key>
<true/>
<key>StandardErrorPath</key>
<string>/usr/local/var/log/[email protected]</string>
<key>StandardOutPath</key>
<string>/usr/local/var/log/[email protected]</string>
<key>WorkingDirectory</key>
<string>/usr/local</string>
</dict>
</plist>
Here is the log file content:
2021-11-30 09:07:20.154 WIB [3891] FATAL: lock file "postmaster.pid" already exists
2021-11-30 09:07:20.154 WIB [3891] HINT: Is another postmaster (PID 422) running in data directory "/usr/local/var/postgresql@12"?
For my case, the postgresql won't start caused by the PID file is still exists.
It's easy to fix, by removed the postmaster.pid file.
Just add an answer that might useful for someone later.
Solution 11 - Postgresql
> windows === press WIN + R //open services
> type - services.msc
> find postgres - double click on it.
>
> properties box open then click on start
> enjoy.
Solution 12 - Postgresql
adding an updated answer for another possibility. running postgres 10 on AWS EC2 I had to add a custom inbound rule through Windows Defender Firewall to allow port 5432.
Solution 13 - Postgresql
For anyone else coming from docker and other applications issues like node js or Django or anything
i faced this problem for 2 days or more watched videos tutorials nothing seem to work although ill say i have missed some but here is the solution that worked for me
instead of local host for PGHOST
you must specify your service name
e.g
image: postgres
container_name: postgresdb
restart: always
env_file:
- ./env/.env
volumes:
then your host must be PGHOST:nodeapp-db
Solution 14 - Postgresql
I use home-brew to start my server.
1)stop the server (but it is not working. it Is the problem)
brew services stop postgresql
2)make update if it is needed
brew postgresql-upgrade-database
3)below comment solve my problem it start the server. Thats it
brew services start postgresql