pg_restore: error: could not execute query: ERROR: extension "pg_stat_statements" must be installed in schema "heroku_ext"

user_18904391 0
Viewed 269

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?

heroku-postgres heroku postgresql
3 Answers

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.
user_189043910

How to restore a Heroku database dump in a non-Heroku environment

  1. Download the latest db dump from heroku
heroku pg:backups:download --app <app>
  1. Drop local db if exists and create a new one
psql <db_url> -c "DROP DATABASE IF EXISTS <db_name>"
psql <db_url> -c "CREATE DATABASE <db_name> WITH ENCODING 'UTF8' TEMPLATE template0"

Here comes the important part

  1. Create the heroku_ext schema inside the newly created database
psql <db_url><db_name> -c "CREATE SCHEMA IF NOT EXISTS heroku_ext AUTHORIZATION <db_user>"
  1. Create the extention in the heroku_ext schema
psql <db_url><db_name> -c "CREATE extension IF NOT EXISTS <extension> WITH schema heroku_ext"
  1. Grant the necessary permissions, eg:
psql <db_url><db_name> -c "GRANT ALL ON SCHEMA heroku_ext TO public"
psql <db_url><db_name> -c "GRANT USAGE ON SCHEMA heroku_ext TO public"
  1. Add schema to search_path
psql <db_url><db_name> -c "SET search_path TO heroku_ext,public"
  1. Grant permissions to the schema to the relevant user
psql <db_url><db_name> -c "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA heroku_ext TO <db_user>"

Finally...

  1. Restore the database dump
pg_restore --no-acl --no-owner -h <db_host> -U <db_user> -d <db_name> ./latest.dump
user_50026600

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.

user_64643080
Related