In Loki We Trust The many projects of Lokkju, Inc


Setting up PostGIS 1.5 on PostgreSQL 8.4.1 (on Debian)

I found that getting the template database for postgis set up was somewhat poorly documented - so:

First, create a role that will own the tables within the template database:
psql -c "CREATE ROLE gisgroup;"

Second, create and populate the template database:

createdb -E UNICODE template_postgis
createlang -d template_postgis plpgsql
psql -d template_postgis < /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d template_postgis < /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
psql -d template_postgis < /usr/share/postgresql/8.4/contrib/postgis_comments.sql

Third, set the ownership to the role you created:

psql -c "ALTER TABLE geometry_columns OWNER TO gisgroup;" template_postgis
psql -c "ALTER TABLE spatial_ref_sys OWNER TO gisgroup;" template_postgis

Fourth, we create the user for our database:

psql -c "CREATE USER yourgisuser WITH PASSWORD 'yourpassword';"
psql -c "GRANT gisgroup TO yourgisuser;"

Fifth, and last, we create a new postgis enable database:
createdb -T template_postgis -O yourgisuser your_new_postgis_database