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 .

```
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.
/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
```
Next, get the key for these new sources. From your command prompt:
```
apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 8683D8A2
```
Now update sources one more time:
```
apt-get update
```
Now you can finally install PostgreSQL 8.4.1!
```
apt-get install postgresql-8.4
```
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
```
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
```

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

Now build and install PostGIS:

cd postgis-1.4.0
./configure
make
make install

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]'

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.