28

I'm trying to import my production Heroku database into my development machine.

My local db is PostgreSQL.

First, I'm exporting the dump from Heroku to my machine

curl -o latest.dump `heroku pgbackups:url`

Then, I try to drop the local db with rake db:drop and then I create the empty database again by using rake db:create.

The problem I'm getting is when actually trying to import the dump to the database

psql -d app_development -U myusername -f mydumpfile.sql

I begin seeing errors like this

psql:latest.dump:24: ERROR:  syntax error at or near "PGDMP"
LINE 1: PGDMP
        ^
psql:latest.dump:28: ERROR:  syntax error at or near ""
LINE 1:     INCREMENT BY 1
        ^
psql:latest.dump:36: ERROR:  syntax error at or near ""
LINE 1:     id integer NOT NULL,
        ^
psql:latest.dump:40: ERROR:  syntax error at or near ""
LINE 1:     INCREMENT BY 1
        ^
psql:latest.dump:45: ERROR:  syntax error at or near ""
LINE 1:     id integer NOT NULL,
        ^
psql:latest.dump:49: ERROR:  syntax error at or near ""
LINE 1:     INCREMENT BY 1

... 

psql:latest.dump:1601: invalid command \S4???(?̭?A?|c?e0<00K?A?}FϚ?????A(??~?t?I?????G(?    K???l??k"?H?ȁ?ͲS?,N*?[(@??a5J??j}
psql:latest.dump:1602: invalid command \??k???|??w???h?
psql:latest.dump:1603: invalid command \=??????o?h?
psql:latest.dump:1609: invalid command \????^.?????????E???/-???+??>#?ؚE?.2)Ȯ&????    g????"7},_??]?:?f?Tr|o???)?p????h?KO?08[Rqu???|3?cW?ڮ?ahbm??H?H8??$???2?a?-أ
psql:latest.dump:1613: invalid command \D!qVS???L??*??׬R??I!???
psql:latest.dump:1614: invalid command \??-?}Q
psql:latest.dump:12565: ERROR:  invalid byte sequence for encoding "UTF8": 0xb0

Any idea what is happening this and how to solve it?

noctarius
  • 5,979
  • 19
  • 20
Martin
  • 11,216
  • 23
  • 83
  • 140
  • 1
    Is there a specific reason you aren't using Taps, http://rubydoc.info/gems/taps/0.3.24/frames? It allows you to run a simple `heroku db:pull` to populate a local database. I'm not sure if this would eliminate your problem, but it is a bit more convenient :) – theIV Jun 01 '12 at 15:02
  • Actually you're right. Installed taps gem and now it works perfectly. For some reason taps gem still requires to declare both taps and sqlite3 on gem file even if i'm using a postgresql. Wanna try yours as answer? – Martin Jun 01 '12 at 15:10
  • Done and done. About the gem requirements, yes, it's a bit annoying the need to declare those requirements even if you are using PG. :/ – theIV Jun 01 '12 at 15:33

5 Answers5

56

You see errors because psql tries to interpret SQL queries when you're actually giving him a compressed dump (that's what heroku uses).

While you can't read the dump, pg_restore -O latest.dump gives you valid SQL you could pipe to psql but the easy solution is the following one :

pg_restore -O -d app_development latest.dump

Notes :

  • Use -O because you probably don't use the random username of your remote heroku postgres db.
  • Heroku doesn't recommend to use taps but I don't know how really risky it is.
Maxime R.
  • 9,621
  • 7
  • 53
  • 59
40

Follow these 4 simple steps in your terminal
(Heroku Dev Center):

  1. Create a backup copy of your database:

    $ heroku pg:backups capture DATABASE_NAME

  2. Download the copy from Heroku (to your local machine) using curl:

    $ curl -o latest.dump `heroku pg:backups public-url`

  3. Load it*:

    $ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U YOUR_USERNAME -d DATABASE_NAME latest.dump
    • get YOUR_USERNAME and choose the desired database from your config/database.yml file.
    • DATABASE_NAME can be your development/test/production db (Ex. mydb_development)

That's it!

yairchu
  • 23,680
  • 7
  • 69
  • 109
Lior Elrom
  • 19,660
  • 16
  • 80
  • 92
  • 1
    new interface is: `heroku pgbackups:capture` and then `curl -o latest.dump \`heroku pg:backups public-url\`` – grosser Aug 07 '15 at 04:16
  • WARNING: `heroku addons:add` has been deprecated. Please use `heroku addons:create` instead. Please update your post! – arniotaki Oct 29 '15 at 09:51
  • @arniotaki, can you please add a reference to the change? – Lior Elrom Oct 30 '15 at 00:39
  • I do not have a reference it is just a message that came to me when I tried to run the command – arniotaki Nov 02 '15 at 08:10
  • You could check that link: http://stackoverflow.com/questions/30143739/uplaod-local-database-data-to-heroku – arniotaki Nov 02 '15 at 08:12
  • 1
    Thanks for this! The pg_restore worked fantastically. Heroku also lets you click a button and download the database dump. I did it that way, renamed it 'latest.dump' an dit seems to have gone swimmingly. – Ryan Jan 03 '17 at 20:01
  • Step 2 gave me the following error: `Couldn't resolve host 'heroku pg'`. Replacing the single-quotes with backticks fixes this problem: `curl -o latest.dump \`heroku pg:backups public-url\`` – Andreas Jun 26 '17 at 02:56
  • 1
    If you're not sure what to put in for USERNAME, try 'postgres' or your computers login name (which worked for me on my Mac). – quicklikerabbit Apr 02 '18 at 20:46
  • Now it's possible to download the backup directly from command line: `heroku pg:backups:download` https://devcenter.heroku.com/articles/heroku-postgres-backups#downloading-your-backups – Samuli Asmala Jun 15 '21 at 06:32
0

I wanted to avoid having to set up Postgres on my local machine (blowing away and recreating the database is a pain if you're just looking for quick instructions). I put together some exact instructions for doing this with a local Postgres database running Docker. I'm adding a link here, since Google kept bringing me to this question (and it's a possible solution, though probably not what you're looking for): https://gist.github.com/locofocos/badd43131f14b3e40c760741d5a26471

Patrick
  • 1,227
  • 14
  • 17
0

Heroku export the .dump extension file of the db to import in any of the relational DB by having its own norms and conditions. While importing it to the local postgres DB, first you download the file latest.dump into your local machine and then run 

pg_restore -h localhost -p 5432 -U postgres_username -d db_name -v latest.dump

and restart the rails server.

Piyush Chaudhary
  • 183
  • 2
  • 12
0

Late 2021 update for the highest voted answer to date (works great):

$ rails db:drop db:create db:migrate

$ heroku pg:backups capture DATABASE_URL

$ curl -o latest.dump heroku pg:backups public-url

$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U YOUR_USERNAME -d DATABASE_NAME latest.dump

  • get YOUR_USERNAME on your local machine

  • DATABASE_NAME can be your development/test/production db (Ex. rails_react_bootstrap_development) from your config/database.yml file.

  • DATABASE_URL is not a variable or example code you need to set. It is a valid heroku option

Anthony Maseda
  • 21
  • 1
  • 4