By Jon Saints - 13 Aug 2014
Start with a fresh install of Ubuntu 14.04 Trusty. (AWS or vagrant might be a good way.)
On the server run the commands below.
sudo apt-get update
sudo apt-get install -y postgresql postgresql-contrib
I prefer to create one user per database for security.
Replace DATABASE_NAME_HERE and USER_NAME_HERE with the values that you want to use
# this will prompt you for a database password
sudo -u postgres createuser -P USER_NAME_HERE
sudo -u postgres createdb -O USER_NAME_HERE DATABASE_NAME_HERE
psql -h localhost -U USER_NAME_HERE DATABASE_NAME_HERE
Postgresql will ask you for your password. Then you should see:
psql (9.3.5)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
DATABASE_NAME_HERE=>
To exit type:
\q
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
Now, on your desktop computer install pgAdmin3 and connect securely to the database via an SSH tunnel.
First, open a terminal on your Desktop computer (these instructions are for Mac or Linux)
Open a tunnel using an ssh password or PEM file. You will need to know the username you use to SSH to the server and the server address or ip address.
ssh -L 127.0.0.1:5433:127.0.0.1:5432 SSH_USERNAME_HERE@SERVER_HOST_ADDRESS -N
If you are using a PEM file, then you can add -i option to the SSH command
-i PEM_FILE_PATH_HERE.pem
This will open a secure tunnel between your desktop and the database server. To exit the tunnel, just press Cntrl+C.
Next, open pgAdmin3 and create a new connection using these settings:
Name: (describe what this connection is for here)
Host: 127.0.0.1
Port: 5433
Username: USERNAME_HERE
Password: PASS_WORD_HERE
When you are done using the database close PgAdmin3 and in the terminal where the tunnel is running press Cntrl+c to exit the tunnel.