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
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
$ 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)
$ 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)
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.
This will take a few minutes.
$ pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydatabase > mydb.dump
This will take quite a bit of time, depending on the size of your dump.
$ s3cmd put mydb.dump s3://my-s3-bucket
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.
$ 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.
This is really helpful, thank you. How large was your database, how long did it take? Did Heroku offer any other approach to upgrade and migration?