PostgreSQL and PostGIS

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 jaunty main
    deb-src jaunty main
  3. Next, get the key for these new sources. From your command prompt:
    apt-key adv --keyserver --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:
    tar xvfz postgis-1.4.0.tar.gz 
  9. Now build and install PostGIS:
    cd postgis-1.4.0
    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


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.