Monday, May 2, 2011

Create a template_postgis database

#Create a template_postgis database Some might find this useful for
#creating PostGIS databases without having to be PostgreSQL super users.
#The idea is to create a template_postgis database, install plpgsql and postgis into it,
#and then use this database as a template when creating new PostGIS databases.

# Creating the template spatial database
$ createdb -E UTF8 -T template0 template_postgis
# and add PLPGSQL language support.
$ createlang -d template_postgis plpgsql



# Loading the PostGIS SQL routines.
$ psql -d template_postgis -f /usr/share/pgsql/contrib/postgis-1.5/postgis.sql
$ psql -d template_postgis -f /usr/share/pgsql/contrib/postgis-1.5/spatial_ref_sys.sql

# Enabling users to alter spatial tables.
$ psql -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;"
$ psql -d template_postgis -c "GRANT ALL ON geography_columns TO PUBLIC;"
$ psql -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"

# Garbage-collect and freeze.
$ psql -d template_postgis -c "VACUUM FULL;"
$ psql -d template_postgis -c "VACUUM FREEZE;"

# Allows non-superusers the ability to create from this template.
$ psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis';"
$ psql -d postgres -c "UPDATE pg_database SET datallowconn='false' WHERE datname='template_postgis';"

2 comments:

  1. Nicely explained. I find this article an excellent one as you have shared the complete creation of template_postgis database in detail. All the necessary steps have been mentioned that is of great help to me. Thanks.
    sap project systems

    ReplyDelete
  2. Wow, What a Excellent post. I really found this to much informatics. It is what i was searching for.I would like to suggest you that please keep sharing such type of info.Thanks PostgreSQL NULL value

    ReplyDelete

Followers