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:
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
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.