Heroku postgres postgis - django releases fail with: relation "spatial_ref_sys" does not exist

Question

Heroku changed their PostgreSQL extension schema management on 01 August 2022. (https://devcenter.heroku.com/changelog-items/2446)

Since then every deployment to Heroku of our existing django 4.0 application fails during the release phase, the build succeeds.

Has anyone experienced the same issue? Is there a workaround to push new release to Heroku except reinstalling the postgis extension?

If I understand the changes right, Heroku added a schema called "heroku_ext" for newly created extensions. As the extension is existing in our case, it should not be affected.

All currently installed extensions will continue to work as intended.

Following the full logs of an release via git push:

git push staging develop:master
Gesamt 0 (Delta 0), Wiederverwendet 0 (Delta 0), Pack wiederverwendet 0
remote: Compressing source files... done.
remote: Building source:
remote: 
remote: -----> Building on the Heroku-20 stack
remote: -----> Using buildpacks:
remote:        1. https://github.com/heroku/heroku-geo-buildpack.git
remote:        2. heroku/python
remote: -----> Geo Packages (GDAL/GEOS/PROJ) app detected
remote: -----> Installing GDAL-2.4.0
remote: -----> Installing GEOS-3.7.2
remote: -----> Installing PROJ-5.2.0
remote: -----> Python app detected
remote: -----> Using Python version specified in runtime.txt
remote: -----> No change in requirements detected, installing from cache
remote: -----> Using cached install of python-3.9.13
remote: -----> Installing pip 22.1.2, setuptools 60.10.0 and wheel 0.37.1
remote: -----> Installing SQLite3
remote: -----> Installing requirements with pip
remote: -----> Skipping Django collectstatic since the env var DISABLE_COLLECTSTATIC is set.
remote: -----> Discovering process types
remote:        Procfile declares types -> release, web, worker
remote: 
remote: -----> Compressing...
remote:        Done: 156.1M
remote: -----> Launching...
remote:  !     Release command declared: this new release will not be available until the command succeeds.
remote:        Released v123
remote:        https://myherokuapp.herokuapp.com/ deployed to Heroku
remote: 
remote: This app is using the Heroku-20 stack, however a newer stack is available.
remote: To upgrade to Heroku-22, see:
remote: https://devcenter.heroku.com/articles/upgrading-to-the-latest-stack
remote: 
remote: Verifying deploy... done.
remote: Running release command...
remote: 
remote: Traceback (most recent call last):
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute
remote:     return self.cursor.execute(sql)
remote: psycopg2.errors.UndefinedTable: relation "spatial_ref_sys" does not exist
remote: 
remote: 
remote: The above exception was the direct cause of the following exception:
remote: 
remote: Traceback (most recent call last):
remote:   File "/app/manage.py", line 22, in <module>
remote:     main()
remote:   File "/app/manage.py", line 18, in main
remote:     execute_from_command_line(sys.argv)
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
remote:     utility.execute()
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/django/core/management/__init__.py", line 440, in execute
remote:     self.fetch_command(subcommand).run_from_argv(self.argv)
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/django/core/management/base.py", line 414, in run_from_argv
remote:     self.execute(*args, **cmd_options)
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/django/core/management/base.py", line 460, in execute
remote:     output = self.handle(*args, **options)
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/django/core/management/base.py", line 98, in wrapped
remote:     res = handle_func(*args, **kwargs)
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 106, in handle
remote:     connection.prepare_database()
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/django/contrib/gis/db/backends/postgis/base.py", line 26, in prepare_database
remote:     cursor.execute("CREATE EXTENSION IF NOT EXISTS postgis")
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/sentry_sdk/integrations/django/__init__.py", line 544, in execute
remote:     return real_execute(self, sql, params)
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute
remote:     return self._execute_with_wrappers(
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
remote:     return executor(sql, params, many, context)
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
remote:     return self.cursor.execute(sql, params)
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/django/db/utils.py", line 91, in __exit__
remote:     raise dj_exc_value.with_traceback(traceback) from exc_value
remote:   File "/app/.heroku/python/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute
remote:     return self.cursor.execute(sql)
remote: django.db.utils.ProgrammingError: relation "spatial_ref_sys" does not exist
remote: 
remote: Sentry is attempting to send 2 pending error messages
remote: Waiting up to 2 seconds
remote: Press Ctrl-C to quit
remote: Waiting for release.... failed.
To https://git.heroku.com/myherokuapp

Answer 1

Here is the workaround that I've come up with for our review apps that use a database backup, through pg:backups:restore ( might want to enable meaintenance if you're manipulating a production database):

1. Copy your review app database locally (one that was recently restored through pg:backups:restore so you get all the data): heroku pg:pull [Database URL] localdb -a [app-name]

2. Set your application database config to use localdb then connect to psql and execute : ALTER EXTENSION "hstore" SET SCHEMA heroku_ext; . Run this command for all your existent extensions.

To list all the available extensions that you've pulled run \dx. You don't have to change plpgsql it's native to PostgreSQL

This will work because locally you have all privileges.

3. push this version back to your review app: heroku pg:push mylocaldb [Database URL] -a [app-name] ==> Your database needs to be empty to perform this. You can try this operation on a fresh review app with empty data. this way it can become your new base for all review apps

4. Make sure everything works as expected (data properly restored). Then what you can do is generate a new database dump through pg:backups:capture, and use that as your new go-to database backup for all your new review apps.

Source: https://devcenter.heroku.com/articles/managing-heroku-postgres-using-cli

I also had to do this because for example DROPPING the hstore extension and reenabling it was not a viable option for our case.

Answer 2

I've worked around it by overwriting the postgis/base.py engine, I've put the following in my app under db/base.py

from django.contrib.gis.db.backends.postgis.base import (
     DatabaseWrapper as PostGISDatabaseWrapper,
)

class DatabaseWrapper(PostGISDatabaseWrapper):
    def prepare_database(self):
        # This is the overwrite - we don't want to call the
        # super() because of a faulty extension creation
     pass

Then in my settings I've just pointed the DATABASES["engine"] = "app.db"

It won't help with backups but at least I can release again.