Trouble importing Heroku PostgreSQL dump to local database

Question

I'm attempting to import a PostgreSQL dump from heroku into a local database (to mimic production data in a local environment). I'm working with postgres 9.2 locally on OSX. Here are the steps I've taken in my console:

dropdb db_dev

createdb db_dev

heroku pgbackups:capture HEROKU_POSTGRESQL_MAROON_URL

curl -o latest.dump `heroku pgbackups:url`

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U connorwarnock -d db-dev latest.dump

And the subsequent errors:

pg_restore: connecting to database for restore
pg_restore: dropping COMMENT EXTENSION plpgsql
pg_restore: dropping EXTENSION plpgsql
pg_restore: dropping COMMENT SCHEMA public
pg_restore: dropping SCHEMA public
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6; 2615 2200 SCHEMA public whgnwlkesexkyo
pg_restore: [archiver (db)] could not execute query: ERROR:  cannot drop schema public because other objects depend on it
DETAIL:  extension hstore depends on schema public
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Command was: DROP SCHEMA public;

pg_restore: creating SCHEMA public
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;



pg_restore: creating COMMENT SCHEMA public
pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for EXTENSION plpgsql
pg_restore: setting owner and privileges for COMMENT EXTENSION plpgsql
WARNING: errors ignored on restore: 2

No data is imported. It seems that the public schema is causing issues (I've dropped the schema manually and tried again, to no avail). Perhaps the hstore extension is causing trouble? Any other ideas on how to avoid these errors?

Answer 1

It looks like your template1 contains the hstore extension and possibly other changes.

I'd suggest dropping db_dev and re-creating it from template0, the read-only template that contains the basic default database.

createdb -T template0 dev_db

If that doesn't do the trick, please post updated errors and comment here.