Question 115021 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

5

Answers


  1. Chosen as BEST ANSWER
    0

    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.

  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
  4. 0

    I had the same issue and found the answer in this question.
    https://stackoverflow.com/a/73773443/17517157

    Just add --extensions 'postgis' to your restore command.

    Login or Signup to reply.
  5. 0

    Ran across the same issue when trying to restore a Heroku db locally, solved it by running these two commands:

    psql <db> -c "CREATE SCHEMA IF NOT EXISTS heroku_ext"
psql <db> -c "ALTER database <db> SET search_path TO heroku_ext,public"

    then proceed to restore:

    pg_restore --verbose --clean --no-acl --no-owner -h localhost -d <db> latest.dump
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search