Heroku pg:backups:restore恢复PostGIS扩展失败问题求助

I've run into this exact issue before—here's what's happening and how to resolve it:

Your local database backup includes a TOC (Table of Contents) entry for creating the PostGIS extension, but Heroku requires PostGIS to be installed exclusively in the heroku_ext schema. When you run pg:backups:restore, the tool first drops any existing PostGIS extension, then tries to recreate it using the default schema from your backup, which triggers the error. That's why your previously installed extension disappears after the failed restore.

Solution 1: Skip Extension Restoration During Backup Restore #

The simplest fix is to tell pg_restore to ignore extension-related entries entirely. This way, it won't touch your already installed PostGIS extension in heroku_ext, and will just restore your data.

Run this modified restore command:

heroku pg:backups:restore '<presigned url>' DATABASE_URL --app climbville --no-restore-extensions

For more granular control (e.g., only skip PostGIS specifically), use:

heroku pg:backups:restore '<presigned url>' DATABASE_URL --app climbville --extension=postgis --exclude-schema=heroku_ext

Important: Ensure you've already installed PostGIS in heroku_ext (like you did initially) before running this command.

Solution 2: Exclude PostGIS From Your Local Backup #

If you created the backup yourself using pg_dump, exclude the PostGIS extension from the export process entirely. This prevents the problematic TOC entry from being included in the backup in the first place:

pg_dump -Fc --exclude-extension=postgis your_local_database_name > backup.dump

Upload this modified backup to your storage provider (e.g., S3) and run the standard restore command—no extra flags needed, since the extension isn't present in the backup.

Solution 3: Emergency Fix If You Already Lost the Extension #

If you already ran the failed restore and PostGIS is gone, follow these steps to recover:

1. Log into your Heroku Postgres instance:

   heroku pg:psql --app climbville
   

2. Reinstall PostGIS in the required schema:

   CREATE EXTENSION postgis SCHEMA heroku_ext;
   

3. Run the restore command again with the --no-restore-extensions flag to bring back your data without touching the extension.

Key Takeaways #

• Heroku PostGIS has a strict requirement: it must live in the heroku_ext schema—never use the default public schema or any other custom schema.
• Don't rely on pg_restore to handle PostGIS installation on Heroku; manage the extension manually before or after restoring your data.

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