I am trying to connect to a postgres database installed in a remote server using the following command:
psql -h host_ip
-U db_username
-d db_name
This the error that occurs:
psql: could not connect to server: Connection refused Is the server running on host "" and accepting TCP/IP connections on port 5432?
I already tried the following but the issue remains unresolved:
pg_hba.conf
file to include host all all 0.0.0.0/0 md5
listen_addresses='*'
nmap
gave me the following output:Starting Nmap 6.47 ( http://nmap.org ) at 2015-09-07 18:08 IST
Nmap scan report for 10.17.250.250
Host is up (0.0000040s latency).
Not shown: 997 closed ports
PORT STATE SERVICE
22/tcp open ssh
25/tcp open smtp
80/tcp open http
Am I missing something. Hope someone can help.
This question is related to
postgresql
ubuntu
psql
centos7
In my case I had removed a locale and generated another locale. Database failed to open because of fatal errors in the postgresql.conf file, on 'lc_messages', 'lc_monetary', 'lc_numberic', and 'lc_time'.
Restoring the locale sorted it out for me.
Try to migrate your database. For instance, if you are using Heroku to host your project and with Django, then try heroku run python manage.py migrate
command; the error should go away.
I think you are using the machine-name instead of the ip of the host.
I got the same error when i tried with machine's name. Because, It is allowed only when both the client and host are under same network and they have the same Operating system installed.
See the port and make a port change in postgresql.conf. My installation of postgres 9.4 uses port 5431 or 5434 instead of 5432. If it say the port is in use so change the port. And check if you give password in psql installation so give the password in file and save it.
Check the port defined in postgresql.conf
. My installation of postgres 9.4 uses port 5433
instead of 5432
In my case, I did not change azure default security policy in management portal. The original is port 22 allowed and the rest are all denied. As long as I add 5432 port, everything becomes good.
Following configuration, you need to set:
To open the port 5432 edit your /etc/postgresql/9.1/main/postgresql.conf
and change
# Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
In /etc/postgresql/10/main/pg_hba.conf
# IPv4 local connections:
host all all 0.0.0.0/0 md5
Now restart your DBMS
sudo service postgresql restart
Now you can connect with
psql -h hostname(IP) -p port -U username -d database
I had the exact same problem, with my configuration files correct. In my case the issue comes from the Eduroam wifi I used : when I connect via another wifi everything works. It seems that Eduroam blocks port 5432, at least in my university.
Make sure the settings are applied correctly in the config file.
vim /etc/postgresql/x.x/main/postgresql.conf
Try the following to see the logs and find your problem.
tail /var/log/postgresql/postgresql-x.x-main.log
I have struggled with this when trying to remotely connect to a new PostgreSQL installation on my Raspberry Pi. Here's the full breakdown of what I did to resolve this issue:
First, open the PostgreSQL configuration file and make sure that the service is going to listen outside of localhost.
sudo [editor] /etc/postgresql/[version]/main/postgresql.conf
I used nano
, but you can use the editor of your choice, and while I have version 9.1
installed, that directory will be for whichever version you have installed.
Search down to the section titled 'Connections and Authentication'. The first setting should be 'listen_addresses'
, and might look like this:
#listen_addresses = 'localhost' # what IP address(es) to listen on;
The comments to the right give good instructions on how to change this field, and using the suggested '*'
for all will work well.
Please note that this field is commented out with #. Per the comments, it will default to 'localhost', so just changing the value to '*'
isn't enough, you also need to uncomment the setting by removing the leading #
.
It should now look like this:
listen_addresses = '*' # what IP address(es) to listen on;
You can also check the next setting, 'port', to make sure that you're connecting correctly. 5432 is the default, and is the port that psql will try to connect to if you don't specify one.
Save and close the file, then open the Client Authentication config file, which is in the same directory:
sudo [editor] /etc/postgresql/[version]/main/pg_hba.conf
I recommend reading the file if you want to restrict access, but for basic open connections you'll jump to the bottom of the file and add a line like this:
host all all all md5
You can press tab instead of space to line the fields up with the existing columns if you like.
Personally, I instead added a row that looked like this:
host [database_name] pi 192.168.1.0/24 md5
This restricts the connection to just the one user and just the one database on the local area network subnet.
Once you've saved changes to the file you will need to restart the service to implement the changes.
sudo service postgresql restart
Now you can check to make sure that the service is openly listening on the correct port by using the following command:
sudo netstat -ltpn
If you don't run it as elevated (using sudo
) it doesn't tell you the names of the processes listening on those ports.
One of the processes should be Postgres, and the Local Address should be open (0.0.0.0) and not restricted to local traffic only (127.0.0.1). If it isn't open, then you'll need to double check your config files and restart the service. You can again confirm that the service is listening on the correct port (default is 5432
, but your configuration could be different).
Finally you'll be able to successfully connect from a remote computer using the command:
psql -h [server ip address] -p [port number, optional if 5432] -U [postgres user name] [database name]
The following helped me on macos Mojave:
$sudo mv /usr/local/var/postgres /usr/local/var/postgres.save
$brew uninstall postgres
$brew install postgres
Mine was quite straightforward if you are on a Mac try:
brew install postgres
This will tell you if you have it already install and what version or install the latest version for you if not then run
brew upgrade postgresql
This will make sure you have the latest version installed then finally
brew services start postgresql
This will start the service again. I hope this helps someone.
Source: Stackoverflow.com