[Django]-Heroku postgres postgis – django releases fail with: relation "spatial_ref_sys" does not exist

by

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.

👤marcinowski

4👍

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.

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

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

👤chedli

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.

👤Brad

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';
👤MrMaz

2👍

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.

👤eff

2👍

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

  1. Use the Heroku datastore durability tool to create a backup on the
    source database or heroku pg:backups:capture -a <SOURCE_APP>.
  2. Determine which pg extensions the database uses (can check from psql
    with \dx)
  3. create a comma-separated string of the extensions (e.g.:fuzzystrmatch,pg_stat_statements,pg_trgm,pgcrypto,plpgsql,unaccent,uuid-ossp')
  4. Make sure your Heroku CLI is updated to at least version 7.63.0 (use
    heroku update to update)
  5. Run this: 
    heroku pg:backups:restore $(heroku pg:backups public-url -a <SOURCE_APP>) DATABASE_URL --extensions '<EXTENSIONS>' -a <TARGET_APP>
  6. Reset dynos on the TARGET_APP
👤OneNeptune

1👍

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
👤Rory-R-Reyes

1👍

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.

👤marcinowski

Leave a comment