Migrate PostgreSQL local db to Heroku on Windows 7

Question

Noob to Heroku and Postgres, and using Windows 7 and git bash to try to port my local Django postgres 9.4 database to Heroku. At the moment I'm stuck with a server 500 error on my heroku app. But the Django site and database work fine on my local environment (ie. my laptop).

I've tried multiple permutations of what appear to be the main methods to to migrate the local database, such as the following:

1. Reset followed by pg:push

$ heroku pg:reset HEROKU_POSTGRESQL_BLACK
$ PGUSER=MyUsername PGPASSWORD=MyPassword heroku pg:push localdb HEROKU_POSTGRESQL_BLACK -a MyAppname

Errors:

pg_dump: blah blah blah

:

pg_restore: [archiver] did not find magic string in header

pg_dump: [custom archiver] could not write to output file: Invalid argument

And $ heroku pg:info showed 0 tables

2. Dump db to SQL and try to push that

$ pg_dump -U MyUsername -d localdb --no-owner --no-acl -f backup.sql
$ heroku pg:psql --app MyAppname < backup.sql

(btw, the backup.sql file was populated and looked fine)

Errors:

Nothing happened, just a new line in bash.

And $ heroku pg:info showed 0 tables

3. Dump db, copy to Dropbox and try to restore that

$ PGPASSWORD=mypassword pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > database.dump
$ heroku pg:backups restore 'https://www.dropbox.com/s/bunch_of_chars/database.dump?dl=0' HEROKU_POSTGRESQL_BLACK_URL -a MyAppname --confirm MyAppname

(I, of course, copied database.dump to Dropbox)

Errors:

Bash just hangs, showing:

r004 --restore--> HEROKU_POSTGRESQL_BLACK

<-[0KRunning... 0.00B

After several hours I cancel out of it.

And $ heroku pg:info showed 0 tables

4. Connect pgAdmin to Heroku

I use pg:config to get the details of my Heroku database. I add ssl, and enter the heroku 'dbname' in database restriction I then try to connect.

Errors:

pgAdmin just hangs, so a couple hours later I kill the process.

All advice I've seen has been to try pushing, or restoring the database. But this doesn't seem to be working for my configuration.

Any other advice/solutions please?

Many thanks in advance.

"blah blah" isn't very useful. Please include full, exact errors, arguments, etc. Wild guess: you used pg_dump without -Fc then tried to feed the output to pg_restore. (I don't use Heroku so I can't really comment on the Heroku-specific bits). — Craig Ringer
– Craig Ringer, Commented Apr 12, 2015 at 12:18
Ah no, I did use -Fc, as in: PGPASSWORD=MyPassword pg_dump -Fc --no-acl --no-owner -h localhost -U Myusername crosstech > database.dump — christo
– christo, Commented Apr 12, 2015 at 12:46
And the blah blah was abbreviation for about 5,000 characters, which seemed too long for the post, and in which there didn't seem any problems (until the end bit). It was something like this: pg_dump: reading schemas pg_dump: reading user-defined tables pg_dump: reading extensions ......... pg_dump: saving database definition pg_restore: [archiver] did not find magic string in file header pg_dump: [custom archiver] could not write to output file: Invalid argument — christo
– christo, Commented Apr 12, 2015 at 12:48
aah, on Windows there are possible issues with stdio redirection not being 8-bit clean. Try using pg_dump -f database.dump .... . I'm hoping to have the time to investigate this and see if I can patch pg_dump to detect that and error out. — Craig Ringer
– Craig Ringer, Commented Apr 12, 2015 at 12:58
this worked for me "pg_dump -Fc --no-acl --no-owner -h localhost -d rails_db_development > mydb.dump" — dsmithco
– dsmithco, Commented May 14, 2015 at 23:00

Smita Ahinave · Accepted Answer · 2016-03-21 05:57:19Z

0

I changed the link of Dropbox a little bit and then it works. What I changed is dl=0 into dl=1, so that heroku could download from Dropbox instead of open the downloading page.

edited Mar 21, 2016 at 5:57

Smita Ahinave

1,8867 gold badges26 silver badges42 bronze badges

answered Mar 21, 2016 at 5:13

hope yao

11 bronze badge

Sign up to request clarification or add additional context in comments.

6 Comments

elssar Over a year ago

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

hope yao Over a year ago

Sounds silly. Good answers are rejected because of so called reputation

elssar Over a year ago

The problem wasn't that the dropbox link wasn't working, rather that importing a dump on heroku wasn't working. So you did not actually answer the question. It is not a matter of reputation, rather quality of answer. I believe this should be a comment and not an answer

hope yao Over a year ago

@elssar Importing a dump on heroku wasn't working is caused by the wrong dropbox link. Heroku should download the dump directly from the link provided.

elssar Over a year ago

the OP tried 4 different ways to import the dump, and none of them worked. So its unlikely that fixing the link will help.

|

Collectives™ on Stack Overflow

Migrate PostgreSQL local db to Heroku on Windows 7

1. Reset followed by pg:push

2. Dump db to SQL and try to push that

3. Dump db, copy to Dropbox and try to restore that

4. Connect pgAdmin to Heroku

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1. Reset followed by pg:push

2. Dump db to SQL and try to push that

3. Dump db, copy to Dropbox and try to restore that

4. Connect pgAdmin to Heroku

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related