CODEWITHSUNDEEP
postgresqlherokuheroku-postgres
user18904391
user18904391

Reputation: 139

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?

Upvotes: 10

Views: 13394

Answers (5)

Juan Delgado
Juan Delgado

Reputation: 81

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

Upvotes: 8

Simon
Simon

Reputation: 423

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.

Upvotes: 0

snepote
snepote

Reputation: 21

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

Upvotes: 2

user18904391
user18904391

Reputation: 139

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.

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 247445

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.

Upvotes: -1

Related Questions