PostGIS

Add PostgreSQL users

A superuser:

sudo -u postgres createuser -s -P SUPERUSER

A user:

sudo -u postgres createuser -P USER

Create PostGIS template

Based on the spatial_adapter gem's documentation.

sudo -u postgres createdb -E UTF-8 template_postgis

If you are using PostgreSQL 8.4, the default template is template1, which raises the error:

createdb: database creation failed: ERROR:  new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
HINT:  Use the same encoding as in the template database, or use template0 as template.

As mentioned in the hint, use template0 instead:

sudo -u postgres createdb -E UTF-8 -T template0 template_postgis

You may have to provide a username and password for the psql command.

sudo -u postgres psql postgres

In the PostgreSQL console (change the paths to the SQL files as necessary, e.g. on Homebrew installs this is "\i /usr/local/share/postgis/postgis.sql"):

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis';

-- Connect to new database
\c template_postgis

CREATE LANGUAGE plpgsql; -- may already exist
\i /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
\i /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON geography_columns TO PUBLIC;
GRANT ALL ON spatial_ref_sys TO PUBLIC;
VACUUM FREEZE;

Create databases

sudo -u postgres createdb -T template_postgis twfy_local_production
sudo -u postgres createdb -T template_postgis twfy_local_staging
sudo -u postgres createdb -T template_postgis openlylocal_development
sudo -u postgres createdb -T template_postgis twfy_local_test