0

The problem has already been approached here and here but without any outcome for me to work with.

I am backing up using as required in the Heroku documentation.

pg_dump -Fc --no-acl --no-owner --no-privileges  -h [HOST] -U [DB_USER] [DB_NAME] > backup.dump

When I restore like so (the file's size is ~100MB):

heroku pg:backups --app [APP_NAME] restore 'https://[DUMP_URL]' [HEROKU_DATABASE_URL]

I receive an error message after the progress of uploading stops at 19.4MB and logs state:

$ heroku pg:backups --app [APP_NAME] info r081
=== Backup info: r081
Database:    BACKUP
Started:     2016-04-29 10:10:37 +0000
Finished:    2016-04-29 10:11:31 +0000
Status:      Failed
Type:        Manual
Backup Size: 19.4MB
=== Backup Logs
2016-04-29 10:10:39 +0000: pg_restore: connecting to database for restore
2016-04-29 10:10:41 +0000: pg_restore: creating SCHEMA public
2016-04-29 10:10:41 +0000: pg_restore: creating EXTENSION plpgsql
2016-04-29 10:10:41 +0000: pg_restore: creating COMMENT EXTENSION plpgsql
2016-04-29 10:10:41 +0000: pg_restore: [archiver (db)] Error while PROCESSING TOC:
2016-04-29 10:10:41 +0000: pg_restore: [archiver (db)] Error from TOC entry 2474; 0 0 COMMENT EXTENSION plpgsql
2016-04-29 10:10:41 +0000: pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
2016-04-29 10:10:41 +0000: Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
2016-04-29 10:10:41 +0000: 
2016-04-29 10:10:41 +0000: 
2016-04-29 10:10:41 +0000: 
2016-04-29 10:10:41 +0000: pg_restore: creating SEQUENCE answer_seq
[...]
2016-04-29 10:10:48 +0000: pg_restore: executing SEQUENCE SET category_seq
2016-04-29 10:10:49 +0000: pg_restore: processing data for table "file"
2016-04-29 10:11:31 +0000: out of memory
2016-04-29 10:11:31 +0000: waiting for restore to complete
2016-04-29 10:11:31 +0000: restore done
2016-04-29 10:11:31 +0000: waiting for download to complete
2016-04-29 10:11:31 +0000: download done

I first thought it could be due to the table file which contains BLOBS. But I can't seem to understand why it should be a problem. The largest file is 4MB. The logs also state that only 19.4 of the 100MB have been imported.

I use the following version of the heroku toolbelt:

heroku-toolbelt/3.43.0 (x86_64-linux-gnu) ruby/2.1.5
heroku-cli/4.30.0-2dfc0f4 (amd64-linux) go1.6.2
=== Installed Plugins
heroku-apps@2.0.3
heroku-cli-addons@0.3.0
heroku-fork@4.1.3
heroku-git@2.5.1
heroku-local@5.0.2
heroku-orgs@1.1.0
heroku-pg-extras
heroku-pipelines@1.1.5
heroku-run@3.2.3
heroku-spaces@2.1.2
heroku-status@2.1.4
Community
  • 1
  • 1
Steven
  • 1,218
  • 3
  • 18
  • 38
  • Have you tried using `pg:push` before going the manual dump, restore way? If that didn't work either, the only workaround I can think of at the moment would be to dump in the default plaintext mode and manually removing the offending statements before restoring. – janfoeh Apr 29 '16 at 11:56
  • The dump is local but the original database is in an environment I can't execute the Heroku toolbelt on. And as far as I understood, pg:push relies on the fact, that the database is local and I can't specify a remote host. – Steven Apr 29 '16 at 12:29
  • That looks about right to me, too. How about restoring the dump locally, then running `pg:push`? If you encounter the same issue during the local restore, [this answer](http://stackoverflow.com/questions/10169203/postgresql-9-1-pg-restore-error-regarding-plpgsql/11776053#11776053) should have you covered. – janfoeh Apr 29 '16 at 12:38
  • I was actually more into understanding than just solving the problem. But thank you anyways. – Steven Apr 29 '16 at 12:56
  • The pg:push approached worked but I still don't know why restoring from dump did not work. I've done this successfully quite a few times before. – Steven Apr 29 '16 at 13:11

1 Answers1

0

I suppose my database plan was not big enough to support the operation. I can exclude the other issues statet in the heroku FAQ

Steven
  • 1,218
  • 3
  • 18
  • 38