Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free.

I need to access a postgresql database from a remote machine on a VPS at DigitalOcean running 12.10 and postgresql 9.1.

How do I do this? I noticed port 5432 is closed, how do I open this?

share|improve this question
    
Follow steps mentioned in javabypatel.blogspot.in/2015/07/… and change the port number present in postgresql.conf file. after changing port restart PostgreSQL server. –  Jayesh Aug 11 at 3:49

2 Answers 2

To open the port 5432 edit your /etc/postgresql/9.1/main/postgresql.conf and change

listen_addresses='localhost'

to

listen_addresses='*'

and reload or restart you DBMS

invoke-rc.d postgresql restart

now you can connect with

$ psql -h hostname -U username -d database

if you are unable to authentify yourself, then you need to give your user access rights to your database

Edit your

/etc/postgresql/9.1/main/pg_hba.conf

and add

host all all * md5

hereafter you need also a reload

invoke-rc.d postgresql reload

I don't need to mention that this is a basic configuration, now you should think about modify your firewall and improve the security of your DBMS.

share|improve this answer
1  
In particular, you should enable SSL. –  Craig Ringer Feb 19 '14 at 14:38
    
Okey, I tried this, but when I try to connect using pgAdmin from my computer, I get "Server not listening". I added to iptables, and when I run iptables -L the following shows: ACCEPT tcp -- anywhere anywhere tcp dpt:postgresql When checking the IP and PORT on this site (yougetsignal.com/tools/open-ports), it says the port is closed –  Øyvind Feb 19 '14 at 14:46
    
is the server listening? check with netstat -nlt|grep :5432 –  user224465 Feb 19 '14 at 14:50
    
I would insert the host row in a more strict way: host <database> <user> <remote_client_IPaddress>/24 md5 –  fbrundu Feb 25 at 15:58

For the message "server not listening", that happen to me was, that i don't erase of # on the archive postgresql.conf i mean:

#listen_addresses='localhost'

to:

listen_addresses='*'

(Sorry for my english).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.