1

I would like to install PostgreSQL and PostGIS on my Ubuntu 14.04 virtual private server that is hosted in a remote datacenter. How can I enable remote access on them?

2

To install PostgreSQL on Ubuntu 14.04 server:

sudo apt-get update
sudo apt-get install -y postgresql postgresql-contrib

Create a database and a user to access it

sudo -u postgres createuser -P USER_NAME_HERE
sudo -u postgres createdb -O USER_NAME_HERE DATABASE_NAME_HERE

Test connecting to PostgreSQL

psql -h localhost -U USER_NAME_HERE DATABASE_NAME_HERE

PostgreSQL will ask you for your password. Then you should see something like this:

psql (9.3.5)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

DATABASE_NAME_HERE=>

To exit type:

\q

Add PostGIS support to the database

sudo apt-get install -y postgis postgresql-9.3-postgis-2.1
sudo -u postgres psql -c "CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology;" DATABASE_NAME_HERE

Enabling remote access over the internet

Add the following line to /etc/postgresql/9.3/main/postgresql.conf

listen_addresses = '*'

On the file /etc/postgresql/9.3/main/pg_hba.conf in section "IPv4 local connections" replace the following line:

host    all             all             127.0.0.1/32         md5

By:

host    all             all             0.0.0.0/0            md5

Then restart PostgreSQL server:

/etc/init.d/postgresql restart

Next, open pgAdmin3 and create a new connection using these settings:

Name: (describe what this connection is for here)
Host: 
Port: 5432
Username: USERNAME_HERE
Password: PASSWORD_HERE

With the same settings, you will be able to connect using QGIS.

Obs: If you have done something wrong and want to purge and reinstall all PostgreSQL files to create a brand new installation, here are the steps:

To remove everything PostgreSQL from your system:

apt-get --purge remove postgresql\*

Just purging the postgres package isn't enough since it's just an empty meta-package.

Once all PostgreSQL packages have been removed, run:

rm -r /etc/postgresql/
rm -r /etc/postgresql-common/
rm -r /var/lib/postgresql/
userdel -r postgres
groupdel postgres

Sources:

http://www.saintsjd.com/2014/08/13/howto-install-postgis-on-ubuntu-trusty.html

https://stackoverflow.com/questions/13514128/postgresql-server-remote-connection

http://www.thegeekstuff.com/2014/02/enable-remote-postgresql-connection/

https://stackoverflow.com/questions/2748607/how-to-thoroughly-purge-and-reinstall-postgresql-on-ubuntu

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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