I am following this set of instructions provided by Heroku to export a copy of my local postgis database and restore it to my Heroku postgres instance. I further followed this set of instructions to provision the postgis extension for my database on Heroku.
The provisioning worked as expected.
climbville::DATABASE=> CREATE EXTENSION postgis;
CREATE EXTENSION
climbville::DATABASE=> \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.2.3 | heroku_ext | PostGIS geometry and geography spatial types and functions
I then ran the restore using the following command:
heroku pg:backups:restore '<presigned url>' DATABASE_URL --app climbville
However, the restore failed with the following error message:
pg_restore: creating EXTENSION "postgis"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 2; 3079 18546 EXTENSION postgis (no owner)
pg_restore: error: could not execute query: ERROR: Extensions can only be created on heroku_ext schema
The restore seems to be failing when it checks for and tries to install the postgis extension. I noticed that when I logged into the Heroku postgres instance after the attenmpted resotre, the postgis extension was gone. This leads me to believe that the pg:restore deleted the extension and then tried to reinstall it but failed.
Any ideas how I can install the postgis extension on my Heroku postgres instance at the time of restore?
Ok, I have discovered how to fix the above issue. Instead of using the above restore command, I added --extensions 'postgis' to the command. That is:
heroku pg:backups:restore '<presigned url>' DATABASE_URL --extensions 'postgis' --app climbville
According to this support post from Heroku, they changed how the postgis extension is stored in the database, and it seems the --extensions flag correctly adds the extension prior to the restore.