error: could not execute query: ERROR: schema "_heroku" does not exist

Question

I use Heroku to deploy my app and changed my plan from Basic to Essential 1 on the Postgresql add-on this morning. Since then, I cannot manage to download my database in a local environment. Here is the script I run, that was working until now :

 task :download_staging_db do
  puts %x{echo "Setting environment to development"}
  puts %x{rails db:environment:set RAILS_ENV=development}
  puts %x{echo "Dropping existing DB"}
  puts %x{rake db:drop}
  puts %x{echo "Downloading staging DB"}
  puts %x{heroku pg:pull DATABASE_URL <my_db_name> --exclude-table-data="versions" --app evening-meadow-24924}
end

And the error message I get from Heroku when running it :

[...]
pg_restore: creating EVENT TRIGGER "log_create_ext"
pg_restore: error: could not execute query: ERROR:  schema "_heroku" does not exist
Command was: CREATE EVENT TRIGGER "log_create_ext" ON "ddl_command_end"
   EXECUTE FUNCTION "_heroku"."create_ext"();


pg_restore: creating EVENT TRIGGER "log_drop_ext"
pg_restore: from TOC entry 4450; 3466 2315888 EVENT TRIGGER log_drop_ext heroku_admin
pg_restore: error: could not execute query: ERROR:  schema "_heroku" does not exist
Command was: CREATE EVENT TRIGGER "log_drop_ext" ON "sql_drop"
   EXECUTE FUNCTION "_heroku"."drop_ext"();


pg_restore: creating EVENT TRIGGER "validate_extension"
pg_restore: from TOC entry 4451; 3466 2315890 EVENT TRIGGER validate_extension heroku_admin
pg_restore: error: could not execute query: ERROR:  schema "_heroku" does not exist
Command was: CREATE EVENT TRIGGER "validate_extension" ON "ddl_command_end"
   EXECUTE FUNCTION "_heroku"."validate_extension"();


pg_restore: warning: errors ignored on restore: 6

Any idea on what could be going wrong here ? I found no help on StackOverflow, and opened a ticket on Heroku support, but haven’t had a response so far.

Answer 1

I also have this problem. I tried updating the Heroku CLI, but no luck.

If you have this problem and just need to restore one backup: you can use a hex editor to null out the offending lines in the .dump file, ie EXECUTE FUNCTION "_heroku"."create_ext"();. Specifically, search the offending "_heroku" text, and null out all the text around it, beginning with the first character starting after the 0x00, up to the terminating 0x00.

Sorry, it's not a great solution, but when I needed to restore a single backup, it was a life saver.