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?
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:
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
host all all 0.0.0.0/0 md5
Then restart PostgreSQL server:
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