CODEWITHSUNDEEP
postgresqlherokupg-restore
apiljic
apiljic

Reputation: 523

Heroku database restore from backup failing

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?

Upvotes: 3

Views: 766

Answers (1)

apiljic
apiljic

Reputation: 523

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

Upvotes: 4

Related Questions