Heroku database restore from backup failing-CodePudding

I am trying to restore a database from a backup at Heroku, but it fails.

I am using their instructions:

heroku pg:backups:restore b101 DATABASE_URL --app example-app

But get the following error:

Restoring... !
An error occurred and the backup did not finish.  
pg_restore: creating FK CONSTRAINT "public.user_disciplines_usertoken_tokens usertoken_id_refs_id_a11f5e9d"
pg_restore: warning: errors ignored on restore: 16
pg_restore finished with errors  
waiting for download to complete
download finished successfully
Run heroku pg:backups:info r2192 for more details.

When I run the suggested command, I get the following:

2022-08-31 15:09:55  0000 pg_restore: connecting to database for restore
2022-08-31 15:09:55  0000 pg_restore: creating EXTENSION "hstore"
2022-08-31 15:09:55  0000 pg_restore: while PROCESSING TOC:
2022-08-31 15:09:55  0000 pg_restore: from TOC entry 2; 3079 95267 EXTENSION hstore (no owner)
2022-08-31 15:09:55  0000 pg_restore: error: could not execute query: ERROR:  Extensions can only be created on heroku_ext schema
2022-08-31 15:09:55  0000 CONTEXT:  PL/pgSQL function inline_code_block line 7 at RAISE
2022-08-31 15:09:55  0000 Command was: CREATE EXTENSION IF NOT EXISTS "hstore" WITH SCHEMA "public";
2022-08-31 15:09:55  0000 
2022-08-31 15:09:55  0000 
2022-08-31 15:09:55  0000 pg_restore: creating COMMENT "EXTENSION "hstore""
2022-08-31 15:09:55  0000 pg_restore: from TOC entry 5891; 0 0 COMMENT EXTENSION "hstore"
2022-08-31 15:09:55  0000 pg_restore: error: could not execute query: ERROR:  extension "hstore" does not exist
2022-08-31 15:09:55  0000 Command was: COMMENT ON EXTENSION "hstore" IS 'data type for storing sets of (key, value) pairs';
2022-08-31 15:09:55  0000 
2022-08-31 15:09:55  0000 
2022-08-31 15:09:55  0000 pg_restore: creating EXTENSION "pg_stat_statements"
2022-08-31 15:09:55  0000 pg_restore: from TOC entry 3; 3079 95394 EXTENSION pg_stat_statements (no owner)
2022-08-31 15:09:55  0000 pg_restore: error: could not execute query: ERROR:  Extensions can only be created on heroku_ext schema
2022-08-31 15:09:55  0000 CONTEXT:  PL/pgSQL function inline_code_block line 6 at RAISE
2022-08-31 15:09:55  0000 Command was: CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "public";
2022-08-31 15:09:55  0000 
2022-08-31 15:09:55  0000 
2022-08-31 15:09:55  0000 pg_restore: creating COMMENT "EXTENSION "pg_stat_statements""
2022-08-31 15:09:55  0000 pg_restore: from TOC entry 5892; 0 0 COMMENT EXTENSION "pg_stat_statements"
2022-08-31 15:09:55  0000 pg_restore: error: could not execute query: ERROR:  extension "pg_stat_statements" does not exist
2022-08-31 15:09:55  0000 Command was: COMMENT ON EXTENSION "pg_stat_statements" IS 'track planning and execution statistics of all SQL statements executed';

So it appears the issue is with hstore extension and recently Heroku made some changes: https://devcenter.heroku.com/changelog-items/2446

I contacted Heroku support, but got no reply yet. Maybe someone has an idea how to deal with this issue?

CodePudding user response：

Heroku is aware of this issue - it's been active for a few weeks now. They have told us they expected to roll out the fix as early as yesterday, but as of yet have not yet done so.

CodePudding user response：

The following solution works:

Restoring a backup that includes extensions installed in public

heroku pg:backups:restore <BACKUP_ID> <DATABASE_NAME> --extensions '<LIST_OF_EXTENSIONS>' -a <APP_NAME>

e.g:

heroku pg:backups:restore b010 STAGING_DATABASE_URL --extensions 'pg_stat_statements,hstore,pgcrypto' -a example_app

More details here: https://help.heroku.com/ZOFBHJCJ/heroku-postgres-extension-changes-faq