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

chedli · Answer 1 · 2022-08-03T09:31:02.787

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):

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

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

score 3 · Answer 2 · answered Aug 03 '22 at 01:58

3

We've also encountered this issue attempting to use heroku pg:backups:restore and heroku pg:copy - existing backup snapshots cannot be restored if they contain installed extensions.

answered Aug 03 '22 at 01:58

Brad

31
1

marcinowski · Accepted Answer · 2022-08-03T10:45:51.047

3

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.

edited Aug 03 '22 at 10:45

answered Aug 03 '22 at 10:30

marcinowski

349
2
4

Are you sure that works for you? I have tried the same, deployment was possible after that, but I was not able to access any postgis data. Every time I access a model in the admin with a PointField, i get: `AttributeError: 'DatabaseOperations' object has no attribute 'select'` That can possible increase your trouble, because you are not able to release a version without that workaround, because it fails in the release phase of the deployment. – Simon Aug 03 '22 at 11:51
If you don't have any migrations related to PointField then this is the best solution for now. Simply add the above, deploy & run migrations and then change the engine again to django.contrib.gis.db.backends.postgis and re-deploy – elenag Aug 04 '22 at 09:59
@Simon, no I haven't bumped into any issues when reading from the DB, have you made sure your DB actually has Postgis installed? In our case it's always there provided by Heroku which I suppose would explain why it works – marcinowski Aug 04 '22 at 10:04
@marcinowski Yes, the error was on me. Had `DATABASES['default']['ENGINE']: 'my.settings.patched_postgis'` instead of `DATABASES['default']['ENGINE'] = 'my.settings.patched_postgis'`. (Copied the assignment from a dict elsewhere). Great workaround, Thanks! – Simon Aug 04 '22 at 11:50

MrMaz · Answer 4 · 2022-08-08T16:56:07.417

3

We followed this workaround provide by @chedli https://stackoverflow.com/a/73219273/840568

But in our case, postgis was giving an error of not being allowed to relocate schemas when trying the ALTER EXTENSION postgis SET SCHEMA heroku_ext workaround, so we ended up having to do this additional step.

UPDATE pg_extension
  SET extrelocatable = true
    WHERE extname = 'postgis';

ALTER EXTENSION "postgis" SET SCHEMA "heroku_ext";

UPDATE pg_extension
  SET extrelocatable = false
    WHERE extname = 'postgis';

edited Aug 08 '22 at 16:56

answered Aug 03 '22 at 16:50

MrMaz

31
4

1

I tried this and got `permission denied for table pg_extension` – Doug Harris Aug 04 '22 at 17:59
The only usable way for now is described by @chedli - https://stackoverflow.com/a/73219273/840568 – sfate Aug 04 '22 at 18:47
@sfate can you up vote so it has more visibility, thanks! – chedli Aug 07 '22 at 15:36
@DougHarris you can't run this command directly on the heroku database instance, you have to clean up your schema on a server where you have permission – MrMaz Aug 08 '22 at 16:53
@chedli i updated my answer to reference your original workaround and clarify that my answer is an additional step. – MrMaz Aug 08 '22 at 16:57

score 2 · Answer 5 · answered Aug 03 '22 at 00:46

My team was experiencing this as well. Couldn't wait around for Heroku to fix, so we did a bit of sketchy surgery. Won't be applicable for everyone, but since our usage of PostGIS is pretty minor, it wasn't so bad.

This was my checklist for each affected database:

back up database
turn on maintenance mode
create new primitive float columns on all tables that used geography types to store raw lat / lng data
write values from geography fields to the new columns
drop postgis extension: DROP EXTENSION postgis CASCADE;
recreate extension: CREATE EXTENSION IF NOT EXISTS postgis;
recreate our geography fields and populate them from the new columns
drop the new temporary columns
recreate appropriate indexes

So far so good.

score 2 · Answer 6 · answered Oct 26 '22 at 17:16

I have a solution that requires no codebase changes and can be done entirely via Heroku CLI

Use the Heroku datastore durability tool to create a backup on the source database or heroku pg:backups:capture -a <SOURCE_APP>.
Determine which pg extensions the database uses (can check from psql with \dx)
create a comma-separated string of the extensions (e.g.:fuzzystrmatch,pg_stat_statements,pg_trgm,pgcrypto,plpgsql,unaccent,uuid-ossp')
Make sure your Heroku CLI is updated to at least version 7.63.0 (use heroku update to update)

Run this:

heroku pg:backups:restore $(heroku pg:backups public-url -a <SOURCE_APP>) DATABASE_URL --extensions '<EXTENSIONS>' -a <TARGET_APP>

Reset dynos on the TARGET_APP

score 1 · Answer 7 · answered Aug 02 '22 at 16:46

I am tracking a very similar issue. I have been in contact with heroku. My previous error mentioned the pgaudit extension, now I am seeing the same error as you. I will update here if I learn of a solution.

System check identified no issues (5 silenced).
Traceback (most recent call last):
File "/app/.heroku/python/lib/python3.10/site-packages/django/db/backends/utils.py", line 87, in _execute
return self.cursor.execute(sql)
psycopg2.errors.UndefinedTable: relation "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 40, in
main()
File "/app/manage.py", line 36, in main
execute_from_command_line(sys.argv)
File "/app/.heroku/python/lib/python3.10/site-packages/django/core/management/init.py", line 446, in execute_from_command_line
utility.execute()
File "/app/.heroku/python/lib/python3.10/site-packages/django/core/management/init.py", line 440, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/app/.heroku/python/lib/python3.10/site-packages/django/core/management/base.py", line 414, in run_from_argv
self.execute(*args, **cmd_options)
File "/app/.heroku/python/lib/python3.10/site-packages/django/core/management/base.py", line 460, in execute
output = self.handle(*args, **options)
File "/app/.heroku/python/lib/python3.10/site-packages/django/core/management/base.py", line 98, in wrapped
res = handle_func(*args, **kwargs)
File "/app/.heroku/python/lib/python3.10/site-packages/django/core/management/commands/migrate.py", line 106, in handle
connection.prepare_database()
File "/app/.heroku/python/lib/python3.10/site-packages/psqlextra/backend/base.py", line 32, in prepare_database
super().prepare_database()
File "/app/.heroku/python/lib/python3.10/site-packages/django/contrib/gis/db/backends/postgis/base.py", line 26, in prepare_database
cursor.execute("CREATE EXTENSION IF NOT EXISTS postgis")
File "/app/.heroku/python/lib/python3.10/site-packages/sentry_sdk/integrations/django/init.py", line 544, in execute
return real_execute(self, sql, params)
File "/app/.heroku/python/lib/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute
return self._execute_with_wrappers(
File "/app/.heroku/python/lib/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/app/.heroku/python/lib/python3.10/site-packages/django_read_only/init.py", line 74, in blocker
return execute(sql, params, many, context)
File "/app/.heroku/python/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
with self.db.wrap_database_errors:
File "/app/.heroku/python/lib/python3.10/site-packages/django/db/utils.py", line 91, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/app/.heroku/python/lib/python3.10/site-packages/django/db/backends/utils.py", line 87, in _execute
return self.cursor.execute(sql)
django.db.utils.ProgrammingError: relation "spatial_ref_sys" does not exist
Sentry is attempting to send 2 pending error messages
Waiting up to 2 seconds
Press Ctrl-C to quit

UPDATE: here is the latest response I have gotten from a heroku rep -- Thanks for your reply. It appears this new error is a known issue that our data engineering team is actively working on; they expect a fix to be rolled out as soon as later today. I'll keep an eye on their progress and notify you once the fix is implemented. — Rory-R-Reyes, Aug 02 '22 at 17:52
Thanks @Rory-R-Reyes, good to here I am not alone with that one. — Simon, Aug 02 '22 at 18:45
Thanks @Rory-R-Reyes, we also just encountered this exact same issue with our application(s) today — Chris B., Aug 02 '22 at 19:58
Broke a lot of things, including our feature environments. New environments built are also not recognizing the extension, even after successfully being installed. — John Cartwright, Aug 03 '22 at 00:24
I received this update around 7:30 CST. I have not heard any updates since -- I wanted to get back to you to confirm that our team is still working on this issue. We've found the cause of the issue and are working on deploying a fix ASAP. Please rest assured this is our highest priority at the moment and we continue working to mitigate this impact and find solutions as soon as possible. — Rory-R-Reyes, Aug 03 '22 at 17:34

score 1 · Answer 8 · answered Aug 02 '22 at 22:37

We've also got an error before this one which said pgaudit stack is not empty which required me to manually put our databases in the maintenance mode in order to rotate them. https://devcenter.heroku.com/articles/data-maintenance-cli-commands#heroku-data-maintenances-schedule This article helped me get around the first issue but now I'm on the same boat with spatial_ref_sys error.

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

8 Answers8

Linked