0

I've been using the same command to copy my Heroku Postgres database from my production environment to my development without a hitch, until today:

heroku pg:copy <production-app>::DATABASE DATABASE --app <development-app>

Starting copy of DATABASE to DATABASE... done
Copying... !
 ▸    An error occurred and the backup did not finish.
 ▸
 ▸
 ▸    pg_restore: warning: errors ignored on restore: 36
 ▸    pg_restore finished with errors
 ▸    waiting for pg_dump to complete
 ▸    pg_dump finished successfully
 ▸
 ▸    Run heroku pg:backups:info <redacted> for more details.

Then if I run the command for more details, I get:

▸    Not found.
FROYOGA
  • 11
  • 1
  • which plan you are - free? maybe you can find the problem in documentation - if there are some limitations listed – Stefan Aug 03 '22 at 08:10
  • If you're using any postgres extensions it's almost certainly related to a change Heroku made to how they handle postgres extensions. There's lots more discussions and possible workarounds in this post: https://stackoverflow.com/questions/73206939/heroku-postgres-postgis-django-releases-fail-with-relation-spatial-ref-sys – Doug Harris Aug 03 '22 at 14:05
  • @DougHarris Ah that might be it since I'm using `citext` – FROYOGA Aug 03 '22 at 15:45
  • I'm also using `citext` :-( – Doug Harris Aug 03 '22 at 20:59

1 Answers1

0

Probably not a solution because it didn't fully work for me, but possibly a place to start.

Rather than use pg:copy, I tried to break it into a few steps.

First, I did a dump from the source database:

pg_dump -O -f dumpfile.sql $(heroku config:get DATABASE_URL -a <my-app>

Next, I manually edited the file. I changed:

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public; to

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA heroku_ext;

and I replaced any references to public.citext with heroku_ext.citext.

Last, I loaded the edited dump file:

psql $(heroku config:get HEROKU_POSTGRESQL_COLOR_URL -a <my-app>) < dumpfile.sql

It didn't fail in the same way as pg:copy but there were other errors that prevented my app from running properly. I haven't yet had the chance to dig deeper.

Update: Heroku support suggested this workaround to me that is somewhat similar but I've not had a chance to try it yet:

The best path forward is going to be moving those extensions into the heroku_ext schema.

To fix the pg:copy upgrade method, you will need to correct the source system to be in the heroku_ext. To do this you will want to capture a backup, modify the backup file and restore using the modified backup file.

Because of the uniqueness around each customers' database, we are not able to automate these steps behind the scenes.

Steps for doing so

  1. Download Your Backup https://devcenter.heroku.com/articles/heroku-postgres-backups#downloading-your-backups
  2. Convert dump file to .sql file pg_restore -f <output-file-name> <input-file-name>
  3. Modify the CREATE EXTENSION commands to use WITH SCHEMA heroku_ext. You may need to modify the dependencies suggested above.
  4. Restore the backup using pg_restore
  5. Take a new backup pg:backup:capture on the corrected database so the next restore comes from a corrected
Doug Harris
  • 3,169
  • 5
  • 29
  • 31