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

Related Community Groups
CSW Debug Blog | 17 Posts | Join
A group blog to discuss metadata Catalog Service for the Web (CSW) implementation experiences
Building a GeoSciML WFS Server | 11 Posts | Join
Development, testing and implementation of a WFS service that returns GeoSciML documents
ETL Debug Blog | 12 Posts | Join
A group blog on implementing and debugging Extract-Transform-Load (ETL) efforts.
Presentations and Posters | 12 Posts | Join
Post your posters and presentations related to USGIN topics.
Metadata interest group | 13 Posts | Join
group for general posting on metadata content, standards, tools
USGIN Amazon Virtual Server Development | 18 Posts | Invite only
Documenting the process of development of a Web Server in the Amazon EC2 environment. Software installations tailored to the requirements for USGIN
GeoNetwork configuration and development | 7 Posts | Join
Discussion on GeoNetwork setup, configuration, and development.
Student Projects | 0 Posts | Join
Discussion of student projects related to USGIN
Drupal Development | 6 Posts | Join
All about bending Drupal to your needs
Geoportal on an Amazon Virtual Machine | 3 Posts | Closed
Installation, configuration, etc.
Using Django for USGIN | 7 Posts | Request membership
Thought and ideas about using Django to accomplish USGIN-related... things.
ArcGIS Server and OGC Services | 3 Posts | Join
Tips on using ArcGIS Server to provide OGC web services
Content model discussion | 0 Posts | Request membership
Community site for comments on development of content models and encoding for information intechange
Making Web Maps | 2 Posts | Request membership
For information about the myriad of mechanisms for showing service data on a web page.
Troubleshooting Web Service Deployment - Blog | 5 Posts | Join
This blog is for documenting our group's experiences with web service deployment.
Best Practices for USGIN Web Service Hosting | 10 Posts | Join
Tips, techniques, and frequently asked questions for hosting AASG Geothermal Data Web Map Services and Web Feature Services
Hub Disaster Recovery | 0 Posts | Request membership
Discussions around how to harden a distributed federated system against disaster; setting up a system to mirror hub VMs at other hubs.