2

I have a postgres database with 40 tables residing on Heroku right now. It's a Django app, and overall rows are roughly 5 million.

I want to migrate all that to an exact replica in an Azure VM (which already has postgres correctly installed and set up).

What's the safest, cleanest way to make this migration?

Here are the steps in my head:

1) Prepare the Azure VM, by:

  • switching to user postgres and going to psql client via typing psql

  • creating a database called mydatabase via the command CREATE DATABASE mydatabase;

  • no need to run syncdb to create tables at this stage.

2) Log onto the Heroku commandline, and run the command heroku maintenance:on

3) Next, run the command heroku run pg_dump -C dbname | bzip2 | ssh myusername@hostname "bunzip2 | psql mydatabase" (source: here)

That's it. No syncdb to create table structures or anything at any point.

Can anyone comment on those steps, and rectify wherever needed? Thanks!

Note: ask for more information in case you need it

Community
  • 1
  • 1
Hassan Baig
  • 15,055
  • 27
  • 102
  • 205
  • you should 'practice' the migration on your PC using eg Docker or Vagrant, to determine exactly the steps that are necessary to get your Azure instance in a ready state... there are bound to be some unexpected steps! – Anentropic Dec 20 '15 at 05:40
  • Possible duplicate http://stackoverflow.com/questions/33912608/migrating-from-heroku-to-azure-getting-the-database-migration-right/33913512#33913512 – Brij Raj Singh - MSFT Dec 20 '15 at 12:06
  • @BrijRajSingh-MSFT: Yea we talked on that question I remember. But isn't your answer there `curl -o latest.dump heroku pg:backups public-url --app ` just a way to take a DB backup? – Hassan Baig Dec 20 '15 at 14:26
  • That's correct, while the OP had most of the steps figured out quite correctly. – Brij Raj Singh - MSFT Dec 21 '15 at 06:36
  • Well myself being OP of that quesion, I tried pg_restore after that in this way: `pg_restore --verbose --clean --no-acl --no-owner -h example.cloudapp.net -U postgres -d mydb /home/mhb11/e38862b4-32bd-41b8-9b1c-fcaa9734b1f8?dl=0` But I got a ton of errors and it didn't work. The DB is still sitting unmigrated. What do you think about the steps I've outlined in THIS question? See any anomalies? – Hassan Baig Dec 21 '15 at 11:46
  • For instance, I get 100s of errors like: `pg_restore: [archiver (db)] could not execute query: ERROR: relation "user_sessions_session_expire_date" already exists Command was: CREATE INDEX user_sessions_session_expire_date ON user_sessions_session USING btree (expire_date);` or `pg_restore: [archiver (db)] Error from TOC entry 2355; 2606 16916 FK CONSTRAINT user_id_refs_id_4d uv4 pg_restore: [archiver (db)] could not execute query: ERROR: constraint "user_id_refs_id_43c39" for relation "auth_user_user_permissions" already exists Command was: ALTER TABLE ONLY ath_user_user_permissions` – Hassan Baig Dec 21 '15 at 15:07

1 Answers1

4

heroku pg:backups capture (to capture the pg backup)

curl -o latest.dump heroku pg:backups public-url (to download the pg backup to your local machine)

ls -lh latest.dump (to check the size of the downloaded file, so you're certain you downloaded the complete file)

scp -P <your port e.g. 2222> latest.dump user@example.cloudapp.net:/home/user (this will transfer the file to your remote machine)

Then on your remote machine: pg_restore --verbose --clean --no-acl --no-owner -U myuser -d mydb latest.dump

Hopefully this gets the job done.