Note: At Ubuntu 10.04 (Lucid), you can install PostgreSQL 8.4 and PostGIS 1.4 using

sudo apt-get install postgresql-8.4-postgis

There are two ways to go about this: The easy way and the hard way. The easy way is to install PostgreSQL 8.3 and PostGIS 1.3. Both of these are out-of-date versions. The hard way installs PostgreSQL 8.4.1 and PostGIS 1.4. I'll outline both ways here. On our machine, I did it the hard way.

PostgreSQL 8.3 and PostGIS 1.3 (The Easy Way)

apt-get install postgresql-8.3-postgis

... and you're done.

PostgreSQL 8.4.1 and PostGIS 1.4 (The Hard Way)

First of all -- this walkthrough benefits enormously from blog posts by Mark Feeney  and Javier de la Torre .

1. apt-get update
   apt-get upgrade

    These commands update your apt system with what is available in the repositories, and upgrade any packages already installed to which upgrades are available. This seems like a good thing to do on a regular basis, or at least before any software installations.

2. /etc/apt/sources.list is a listing of the repositories used by the apt system. In order to proceed, we need to access some non-standard repositories. Add the following two lines to the file:

   deb http://ppa.launchpad.net/pitti/postgresql/ubuntu jaunty main
   deb-src http://ppa.launchpad.net/pitti/postgresql/ubuntu jaunty main
   

3. Next, get the key for these new sources. From your command prompt:

   apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 8683D8A2
   

4. Now update sources one more time:

   apt-get update

5. Now you can finally install PostgreSQL 8.4.1!

   apt-get install postgresql-8.4

6. This starts the PostgreSQL service, but by default it listens on port 5433. To change it to the standard PostgreSQL port (5432), use the following command:

   sed -i.bak -e 's/port = 5433/port = 5432/' /etc/postgresql/8.4/main/postgresql.conf
   

   Now stop and restart PostgreSQL

   /etc/init.d/postgresql-8.4 stop
   /etc/init.d/postgresql-8.4 start

7. Now moving on to PostGIS - First we'll need to install the libraries that will be needed to build PostGIS from source:

   apt-get install postgresql-server-dev-8.4 libpq-dev
   apt-get install libgeos-dev
   apt-get install proj

8. Download and extract the PostGIS tarball:

   wget http://postgis.refractions.net/download/postgis-1.4.0.tar.gz
   tar xvfz postgis-1.4.0.tar.gz 

9. Now build and install PostGIS:

   cd postgis-1.4.0
   ./configure
   make
   make install
   

10. Moving on to configuration... give your postgres user a password at the OS level, and also within PostgreSQL:

    passwd postgres (enter the password at the prompt)
    su postgres
    psql -c "ALTER user postgres WITH PASSWORD '[password]'

11. Now create a template PostGIS-enabled database:

    createdb geodb
    createlang -d geodb plpgsql
    psql -d geodb -f /usr/share/postgresql/8.4/contrib/postgis.sql
    psql -d geodb -f /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql
    psql -d geodb -c "SELECT postgis_lib_version();"

    If the last command returns "1.4.0" then the template database is properly setup.

     

Allowing External TCP/IP Connections to PostgreSQL

Having troubles here right now... can get it to work with SSH tunneling though.

Put Data on the Elastic Volume

mkdir /mnt/data-store/postgresql
mkdir /mnt/data-store/postgresql/data
cp -R /var/lib/postgresql/8.4/main/* /mnt/data-store/postgresql/data
chown -R postgres:postgres /mnt/data-store/postgresql/data
chmod -R 0700 /mnt/data-store/postgresql/data

/etc/postgresql/8.4/main/postgresql.conf

data_directory = '/mnt/data-store/postgresql/data'

Logging - A lot to learn...

And I haven't done anything about it. No changes have been made to the logging configurations.