cd /etc/postgresql/9.x/main/

open file named postgresql.conf

sudo vi postgresql.conf

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

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]