Create a PostGIS database template

Follow these steps:

• Execute the following commands for creating a template for your spatially-enabled database. In this example, our template will be named template_postgis.

    $ createdb template_postgis
  
    # 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 template_postgis -c "create extension postgis"
    $ psql template_postgis -c "create extension postgis_topology"
    $ psql template_postgis -f <installdir>/postgresql/share/contrib/postgis-2.0/legacy.sql
  
    # Enable 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;"
  

  Although the official documentation recommends loading legacy.sql only “if really necessary”, we have found that it is indeed required for installing Tiger Geocoder and running the OGC tests suite. It is also necessary to restore data from an old database. Run uninstall_legacy.sql later to remove the deprecated functions.

• Create a new database with PostGIS support with:

    $ createdb -T template_postgis geocoder
  

  If you prefer to use the psql console:

    CREATE DATABASE geocoder TEMPLATE template_postgis;