How to move PostGIS extension out of the schema heroku_ext in a Heroku Postgres database?

Question

I have encountered the following problem with the Postgresql database on Heroku and the PostGIS extension with a Python/Django Rest Framework project. When trying to release the application, I get the following error:

Traceback (most recent call last):
  File "/app/.heroku/python/lib/python3.11/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.UndefinedTable: relation "public.spatial_ref_sys" does not exist
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "/app/manage.py", line 28, in <module>
    execute_from_command_line(sys.argv)
  File "/app/.heroku/python/lib/python3.11/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/app/.heroku/python/lib/python3.11/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)

(…)

  File "/app/.heroku/python/lib/python3.11/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: relation "public.spatial_ref_sys" does not exist

As far as I understand, the problem is due to the fact that the PostGIS extension (and its table spatial_ref_sys) is expected to be in schema public, meanwhile, according to previous standards, it is in heroku_ext. Currently, however, the extensions are installed in schema public, according to this article and the release process requires that they are installed in schema public.

However, my attempts to move the PostGIS extension from heroku_ext to public were unsuccessful. Calling the command:

ALTER extension postgis SET SCHEMA public;

I get an error:

ERROR: must be owner of extension postgis

In this article the Heroku support states:

You can reinstall or relocate your extension to any schema you want after enabling the Heroku Data Labs feature. Once enabled, extensions going forward will have their types and functions go to their appropriate schemas (usually public) and nothing new will be added to heroku_ext.

This solution, however, does not work, as the command:

heroku data:labs:enable extensions-on-any-schema --addon {OUR_DATABASE_URL} -a {OUR_APP_NAME}

(completed with database URL and application name, of course) returns an error:

 ' Error: Couldn't find that add on.
 ' Error ID: not_found

How can the PostGIS extension can be transferred to the public schema? Without this we are not able to do the release of the next version of the app.

There were some workarounds described on Stack Overflow, however, they require emptying the database, which is not possible in this case as this is a production app and importing all data again is virtually impossible.

Answer 1

After reaching out to the Heroku Support team regarding this issue, it turned out that the root cause was a bug within Heroku itself. The support team addressed the bug and performed maintenance on my database. As I was using an Essential tier database, which doesn't allow for manual maintenance, their intervention was necessary. Following these actions, the problem was resolved.

For those experiencing similar issues, I recommend performing maintenance on your database as a potential solution. However, if your database is on the Essential tier and manual maintenance isn't feasible, the best way is to contact Heroku Support for assistance and ask them to perform maintenance.