Migrate PostGIS version 1.5 to version 2.0 using PostgresApp on Mac OSX

gistfile1.md

Migrate PostGIS version 1.5 to version 2.0 using PostgresApp on Mac OSX

Version

Source DB (data to be migrated)

OS Ubuntu 12.04 (on vagrant/virtualbox vm)
PostgreSQL 9.1
PostGIS 1.5.3

Target DB (data after migration).

OS Mac OSX 10.8
PostgreSQL 9.2.4.1 (13) using PostgresApp (postgresapp.com)
PostGIS 2.0.1 (included in PostgresApp)

Preparation

Dump Source DB

On my linux server

pg_dump -h localhost -p 5432 -U mydbusername -W -Fc -b -v -f "/var/tmp/mydb.backup" mydb

Create Database

Before continue, please makesure command line utility coming from postgresapp is in your path. Edit your .bashrc, or .zshrc like this

PATH="/Applications/Postgres.app/Contents/MacOS/bin:$PATH"

On Mac OSX, connect to database using command line, psql

psql postgres

After connected

postgres# CREATE USER mydbusername PASSWORD 'mypassword';
postgres# CREATE DATABASE mydb OWNER mydbusername ENCODING 'utf8';

Now connect to new database we have created before, you can switch to other databas using \c otherdatabase

\c mydb
mydb# 

After connected to new database, create postgis extension

mydb# CREATE EXTENSION postgis;
mydb# CREATE EXTENSION postgis_topology;

If you have an error like on this page PostgresApp/PostgresApp#111, you need install libtiff using brew

brew install libtiff

Import Database

Copy or download backup file created by pg_dump. Then import using this command

cd /Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0
perl postgis_restore.pl mydb.backup | psql -h localhost -p 5432 mydb 2> /tmp/errors.txt

Check /tmp/errors.txt to see if any error occured during import.

Troubleshoot

Check Extension installed on your database

mydb# \dx

Check ownership and alter it when necessary

mydb# \d
mydb# ALTER TABLE tablename OWNER TO newtargetuser;