Heroku pgbackups: Syntax errors on localhost restore

heroku, postgresql, ruby-on-rails-3

Solution

I found the answer directly from the manual:

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

$ pg_restore --verbose --clean --no-acl --no-owner -h myhost -U myuser -d mydb latest.dump

Problem

Trying to restore database backup made with heroku pgbackups -tool. I download backup by exposing url: ``` $ heroku pgbackups:url 'backup-name' ``` Created db with: ``` $ createdb 'dbname' -U postgres ``` And tried to restore from *.dump file: ``` $ psql -U postgres -d 'dbname' -f *.dump ``` I end up with following kind of syntax errors: ``` ERROR: syntax error at or near "PGDMP" ... ERROR: invalid byte sequence for encoding "UTF8": 0x9d HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding" ``` Ok, so this has something to do with encoding - but how I solve it? Both config/application.rb and my postgres server has encoding set to UTF-8. database.yml has sqlite configured to it(haven't touched on production config). Gemfile has simply: ``` gem 'pg' ```

Original source