Postgres and PostGIS on a mac

Installation.md

With brew

brew install postgres
brew install PostGIS

Set up.md

Startup [from brew info postgres]:

To have launchd start postgresql at login:

ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents

To load postgresql now:

launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Or, if you don't want/need launchctl, you can just run:

postgres -D /usr/local/var/postgres

Create a db with your username:

  createdb

Login:

  psql -h localhost -p 5432 or just psql

Create a template database with PostGIS enabled

When PostgreSQL creates a new database it is actually copying over a database named template1. What I'm doing here is creating a database which is a copy of template1 and which includes the PostGIS extension. I then set the is_template flag. Then, when I create new databases which need to hold spatial data, I can use this as a template

CREATE DATABASE template_postgis;
update pg_database set datistemplate = true where datname ='template_postgis';
\connect template_postgis;
CREATE EXTENSION postgis;

To test it's working:

SELECT postgis_full_version();

[Thanks to this link: http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS20Ubuntu1204]

Create database from the PostGIS template and connect:

CREATE DATABASE galactic_empire TEMPLATE template_postgis;
\connect galactic_empire

####Go nuts!

User creation.md

###Create the user (From your Ubuntu(postgres) shell, not psql):

createuser --interactive
Enter name of role to add: anakin
Shall the new role be a superuser? (y/n)
Shall the new role be allowed to create databases? (y/n)
Shall the new role be allowed to create more new roles? (y/n)

###Login to psql and then:

\connect galactic_empire AS anakin;