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?

Please show the errors you get when you do try dropping public manually then restoring. DROP EXTENSION hstore; DROP SCHEMA public CASCADE;. — Craig Ringer
– Craig Ringer, Commented Apr 25, 2013 at 23:39

Craig Ringer · Accepted Answer · 2013-04-25 23:48:50Z

4

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.

answered Apr 25, 2013 at 23:48

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Connor Over a year ago

Had a feeling the hstore was casuing trouble, thanks for the fix.

Collectives™ on Stack Overflow

Trouble importing Heroku PostgreSQL dump to local database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related