Old Guy New Trick

An old guys journey to learn how to code.

Enable Remote Access - Postgres

Author: John on January 07, 2015

Recently I've been troubleshooting an issue with my pet project.  On my laptop, in a development environment, I didn't seem to have any issues. My test suite has been working fine, but when I deployed the app to a production environment, I started to discover some issues.  Issues that revolve around the database.  

I'm not a SQL guru, so from time-to-time when I want to poke around my Postgres database, I use an application called PGAdmin.  So I fired up PGAdmin and tried to connect to the Postgres database that is running on my server, over the internet.  Politely, PGAdmin told me to go pound sand - it was not able to connect.  After a little research I learned that there were two files on my server, related to postgres, that I needed to update.

On your remote server, that has Postgres running, modify the two files indicated below:
(Note: your path might be different depending on your version of Postgres and your Linux distro)

sudo vim /etc/postgresql/9.3/main/pg_hba.conf  
# Added by jfhogarty to support remote access and PGAdmin
host    all             all                  md5

sudo vim /etc/postgresql/9.3/main/postgresql.conf 
#The next line added by jfhogarty to enable remote access and PGAdmin
listen_addresses = '*'

Lastly, with the above changes added to the two .conf files, we need to restart Posgtres:

sudo service postgresql restart

With the above changes in place, and postgres restarted, I fired up PGAdmin again on my laptop.  And like *butter* I was able to connect to and work with my databases. I resumed debugging my original issue.

Learn Something New Every Day

Last Edited by: John on November 11, 2015