Due to this change by heroku, I am not able to restore my Heroku Postgres backup.

Following errors are thrown while restoring:

pg_restore: error: could not execute query: ERROR: extension "pg_stat_statements" must be installed in schema "heroku_ext" Command was: CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "public"; pg_restore: error: could not execute query: ERROR: extension "pg_stat_statements" does not exist Command was: COMMENT ON EXTENSION "pg_stat_statements" IS 'track planning and execution statistics of all SQL statements executed'; Command was: CREATE EXTENSION IF NOT EXISTS "postgis" WITH SCHEMA "public";

Any many such errors.

I contacted Heroku support, and they have provided the following step to try fix the issue manually.

Here are the steps in detail:

1. Download a backup of your database.
2. Convert the dump file to a .sql file with: pg_restore -f
3. Modify the CREATE EXTENSION commands to use CREATE EXTENSION IF NOT EXISTS extension_name WITH SCHEMA heroku_ext. You can do this by using sed or a text editor of your choice.
4. Restore the backup using pg_restore to Heroku Postgres from your app. The extensions in your production database will now be owned by the correct schema; future migrations, backups, and restores should proceed without needing to repeat this process.

I have modified the .sql file as per step3, but I am not able to figure hot how to convert this .sql file to a .dump file to be restored on the Heroku app.

I have tried heroku pg:psql --app <app> <sql_file> but it does not work and throws errors.

Can someone help me in importing this .sql file to Heroku?

CodePudding user response：

You can ignore the error and manually create the extension in the required schema. If you want to avoid the error message, drop the extension in the database before dumping it. That should not be a problem, because nothing else in your database should depend on that extension.

CodePudding user response：

Steps followed to solve the issue:

1. Download the DB dump file from Heroku.
2. convert the dump to a .sql file using pg_restore.
3. Reset the Heroku DB using pg:reset
4. log in to the DB using pg:psql and install the extensions in the "heroku_ext" schema
5. Now execute the .sql file on the DB using pg:psql and check the errors
6. modify the .sql file lines that are causing the errors. Most of the errors can be solved by replacing "public" with "heroku_ext" at that lines.
7. After modifying the sql, again follow steps 3 to step 7 until all errors are fixed.