jakeonrails/migrating.md

## migrating.md

      
    Raw
  

              migrating.md
            
          
    Migrating from Postgres 9.0/Postgis 1.5 to Postgres 9.2/Postgis 2.0 on Heroku

This may not be relevant to many, but it's a process that I just had to go through and it was a bit tricky to figure a smooth way to make it work.
The gist of it is that you must do the following:

Export your production database from heroku to your local machine
Create a new, blank database with Postgres 9.2 and PostGIS 2.0
Import your heroku database into the new local database running 9.2/2.0
Dump the new database and upload it to S3
Restore your heroku database using the S3 file as the source

Export your Heroku database to your local machine

Dump your Heroku Postgres 9.0/Postgis 1.5 database

You need to get the connection settings for $DATABASE, $HOST, $PORT, and $USERNAME from http://postgres.heroku.com.
$ pg_dump -Fc -b -v -f "heroku.backup" $DATABASE -h $HOST -p $PORT -U $USERNAME 
Create a blank Postgres 9.2 database

$ psql
postgres=# create database mydatabase;
CREATE DATABASE
You can confirm the correct 9.2 version is being used like this:
mydatabase=# select version();
                                                                    version
-----------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.3 on x86_64-apple-darwin11.4.2, compiled by Apple clang version 3.1 (tags/Apple/clang-318.0.58) (based on LLVM 3.1svn), 64-bit
(1 row)
Add the PostGIS extension

$ psql mydatabase
mydatabase=# create extension postgis;
CREATE EXTENSION
Confirm the PostGIS version by doing this:
mydatabase=# SELECT PostGIS_full_version();
NOTICE:  Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?
                                                                     postgis_full_version
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.0.3 r11128" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.3" LIBJSON="UNKNOWN" RASTER
(1 row)
Restore the Heroku backup to your local Postgres 9.2 database

postgis_restore.pl needs to be the one from Postgres 9.2. It is found in the utils folder. If you used homebrew to install postgres 9.2, you will find it in /usr/local/bin
$ perl postgis_restore.pl "heroku.backup"| psql -h localhost -p 5432 -U postgres mydatabase
You'll see output like:
Converting heroku.backup to ASCII on stdout...
  Reading list of functions to ignore...
  Writing manifest of things to read from dump file...
  Writing ASCII to stdout...
ALTER TABLE
ALTER TABLE
SELECT 3911
DELETE 3911
SET
...
SET
ALTER OPERATOR
...
ALTER OPERATOR
SET
SET
CREATE TABLE
CREATE SEQUENCE
CREATE TABLE
CREATE SEQUENCE
CREATE TABLE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
 setval
--------
 111218
(1 row)

CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
 setval
--------
 111218
(1 row)

CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
  setval
----------
 87781197
(1 row)

ALTER TABLE
...
ALTER TABLE
Done.
ALTER TABLE
...
ALTER TABLE
CREATE INDEX
...
CREATE INDEX
REVOKE
REVOKE
GRANT
GRANT
UPDATE 3749
INSERT 0 162
DROP TABLE
ALTER TABLE
If you get an error about no role named postgres, you can create it using:
$ createuser -srd
Now your local database should be running Postgres 9.2/PostGIS 2.0
Test it on your local development environment. You may run into permissions errors, in which case you can use
REASSIGN OWNED BY old_role [, ...] TO new_role

in PSQL to set the owner on the tables to the role your application uses to connect to the local database.
Import your fresh database into Heroku

Dump your Postgres 9.2/Postgis 2.0 database in compressed format

This will take a few minutes.
$ pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydatabase > mydb.dump
Push your local database dump to an Amazon S3 bucket

This will take quite a bit of time, depending on the size of your dump.
$ s3cmd put mydb.dump s3://my-s3-bucket
Create a Postgres 9.2 database on Heroku

In another shell, while waiting for your upload to S3 to finish:
$ heroku addons:add heroku-postgresql:fugu --version=9.2 --app production-app-name

Adding heroku-postgresql:fugu on vb-realty-production... done, v196 ($400/mo)
Attached as HEROKU_POSTGRESQL_BROWN_URL
The database should be available in 3-5 minutes.
 ! The database will be empty. If upgrading, you can transfer
 ! data from another database with pgbackups:restore.
Use `heroku pg:wait` to track status..
Use `heroku addons:docs heroku-postgresql:fugu` to view documentation.
Restore your local database (hosted on S3) into Heroku

$ heroku pgbackups:restore DATABASE 'https://s3.amazonaws.com/my-s3-bucket/mydb.dump'
To get your file onto S3 you can use the s3cmd CLI utility installable through homebrew. The URL has to be publically accessible so you may need to log into your s3 console and make it public, manually.