6

I connected to heroku postgres database with pgadmin and created a simple table that has two simple columns:

id - bigint identity 
name - text

and simple two rows:

id   name
1    name1
2    name2

When I execute the command

heroku pg:pull DATABASE_URL mylocaldb1234 --app nameless-dusk-05113

I get the error:

pg_restore: error: unrecognized data block type (0) while searching archive
 !    pg_restore errored with 1

I checked postgres local and remote versions, they are the same. Local version:

psql --version
psql (PostgreSQL) 12.5

pg_restore --version
pg_restore (PostgreSQL) 12.5

Remote version:

heroku pg:info
=== DATABASE_URL
Plan:                  Hobby-dev
Status:                Available
Connections:           5/20
PG Version:            12.5
Created:               2021-01-07 07:29 UTC
Data Size:             8.2 MB
Tables:                1
Rows:                  2/10000 (In compliance)
Fork/Follow:           Unsupported
Rollback:              Unsupported
Continuous Protection: Off
Add-on:                postgresql-spherical-97042

remote pg_restore version:

heroku run pg_restore --version
Running pg_restore --version on ⬢ nameless-dusk-05113... up, run.7775 (Free)
pg_restore (PostgreSQL) 12.5 (Ubuntu 12.5-1.pgdg18.04+1)
Alexander Tukanov
  • 503
  • 6
  • 20

1 Answers1

0

heroku pg:pull is supposed to automate a slightly more manual process described here: https://devcenter.heroku.com/articles/heroku-postgres-import-export.

Although I ran into the same error with heroku pg:pull, the more manual process worked. Specifically,

$ heroku pg:backups:capture
$ heroku pg:backups:download  # this creates a file latest.dump in your current directory
$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U postgres -d mydatabasename latest.dump

If mydatabasename doesn't already exist, you'll have to create it before running pg_restore.

$ createdb -U postgres mydatabasename
Dillon Bowen
  • 346
  • 3
  • 9