Trouble importing Heroku PostgreSQL dump to local database
heroku, postgresql
Solution
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.
Problem
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?