使用PostGIS扩展恢复本地库至Heroku时遇spatial_ref_sys不存在错误

以下是可能的原因和对应的解决方法：

1. 备份文件重复尝试创建已存在的PostGIS扩展 #

Heroku数据库已提前安装PostGIS并创建了spatial_ref_sys表，但你的备份文件中包含了CREATE EXTENSION postgis的语句，执行时因环境差异触发异常。

解决方法：
恢复时跳过扩展的创建步骤，使用--no-extensions参数：

pg_restore --no-extensions -d YOUR_HEROKU_DB_URL your_backup.dump

2. 备份的TOC条目顺序异常 #

pg_restore的恢复顺序可能出现问题：比如在执行CREATE EXTENSION之前，备份中已有依赖spatial_ref_sys表的操作（如数据插入、表结构修改），导致提前触发表不存在的错误。

解决方法：

• 先查看备份的TOC列表，确认顺序：

  pg_restore --list your_backup.dump > backup_toc.txt
  

• 手动调整backup_toc.txt中的条目顺序，确保CREATE EXTENSION postgis的条目在所有依赖spatial_ref_sys的操作之前，然后用调整后的列表恢复：

  pg_restore --use-list=backup_toc.txt -d YOUR_HEROKU_DB_URL your_backup.dump
  

3. Schema或权限问题 #

• 确认spatial_ref_sys表确实在public schema下：
  在psql连接Heroku数据库后执行：

  SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'spatial_ref_sys';
  

• 若表不在public，恢复时需指定正确的schema；或检查执行恢复的用户是否有访问该表的权限：

  GRANT ALL ON TABLE spatial_ref_sys TO YOUR_DB_USER;
  GRANT ALL ON SCHEMA public TO YOUR_DB_USER;
  

4. PostGIS版本不兼容 #

本地备份使用的PostGIS版本与Heroku数据库的版本不一致，可能导致扩展创建逻辑冲突。

解决方法：

• 分别在本地和Heroku查询版本：

  SELECT postgis_version();
  

• 若版本差异较大，升级Heroku的PostGIS扩展（通过Heroku CLI或控制面板），或使用与Heroku版本一致的本地环境重新备份。

内容的提问来源于stack exchange，提问作者anolan23