Heroku数据库从备份恢复失败

Question

我正在尝试从Heroku的备份中恢复数据库，但是失败了。

我按照他们的指示进行操作：

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

但是出现了以下错误：

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.

当我运行建议的命令时，我得到以下结果：

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';

看起来问题出在hstore扩展上，最近Heroku进行了一些更改：https://devcenter.heroku.com/changelog-items/2446

我联系了Heroku支持，但还没有收到回复。也许有人知道如何处理这个问题？

- apiljic

---

Heroku的回复：“很抱歉告知，根据我们的支持政策，我们不会代表客户执行恢复操作。” 由于他们在没有向客户发送任何警告或信息的情况下更改基础架构，最终导致数据丢失 - 而他们不会恢复数据。 - apiljic

Answer 1

以下解决方案可行：

恢复包含公共扩展安装的备份

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

更多细节请参考： https://help.heroku.com/ZOFBHJCJ/heroku-postgres-extension-changes-faq

- apiljic