Import Heroku PostgreSQL imports into a local database

Question

I am trying to import a PostgreSQL dump from heroku into a local database (to simulate production data in a local environment). I work with postgres 9.2 locally on OSX. Here are the steps I took in the 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 following 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 

Data is not imported. It seems like an open circuit is causing problems (I reset the circuit manually and tried again, to no avail). Perhaps the hstore extension is causing problems? 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 suggest removing db_dev and re-creating it from template0 , a read-only that contains the default base database.

 createdb -T template0 dev_db 

If this does not do the trick, post updated errors and comments here.