0

Having trouble restoring my Heroku database with my local backup. Keep in mind I am using the PostGIS extension. After doing the pg_restore command from the terminal I get the "'spacial_ref_sys' does not exist" error

pg_restore: creating EXTENSION "postgis"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 2; 3079 426371 EXTENSION postgis (no owner)
pg_restore: error: could not execute query: ERROR:  relation "spatial_ref_sys" does not exist
Command was: CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;

But "spatial_ref_sys" DOES EXIST since I made sure to create the extension. To verify that this table exists:

![![enter image description here

Why?

anolan23
  • 443
  • 3
  • 12
  • 1
    as per the screenshot you shared, spatial_ref_sys table is in heroku_ext schema. Perhaps that table should bein public schema instead? Check which schema it is in locally – Pavel Bely Aug 05 '22 at 18:50
  • Yes, locally it is in public schema. Can I change the heroku_ext to public? How would I do that? The other option would be to locally change the postgis extension to be under the heroku_ext schema, but that doesn't like good practice having to alter your local db just to satisfy the remote – anolan23 Aug 05 '22 at 19:08
  • 1
    1 option - try renaming this schema locally (yes, that is a bit of hack, but first we need to make it work). 2 - try some of the workaround listed here https://stackoverflow.com/questions/73206939/heroku-postgres-postgis-django-releases-fail-with-relation-spatial-ref-sys – Pavel Bely Aug 05 '22 at 19:43
  • @PavelBely Nice, I decided to do what you said even though it feels weird to do this way. It worked. If you want to add it as an answer I will give points. Also, that link was helpful. – anolan23 Aug 05 '22 at 19:59

0 Answers0