Export and Import database from local to heroku

27 Mar Export and Import database from local to heroku

Export and import from local to heroku

From local run the following command replacing with your own values

pg_dump --no-owner --no-acl -d db/padroncolima_development > yourdatabasedump

Upload this file “yourdatabasedump” into a public repository like S3( if that is the case dont forget to set public permissions to the file).

Now backups are part of heroku.com and they stop using this pg backup as an add-on the new command you have to run look something similar to this:

heroku pg:backups restore 'https://s3.amazonaws.com/spreedemostore/yourdatabasedump' DATABASE -a pco.

If you have problems with your sql import you have an alternative:
First thing: generate your sql in local:

pg_dump -d db/your_db_name_development > test2.sql
# or without owner and acl
pg_dump --no-owner --no-acl -d db/your_db_name_development > test2.sql

And then import the sql into heroku

cat test2.sql | heroku pg:psql --app your_app_name_goes_here

Generate a downloadable dump from Heroku

# generate a new dump of your database
heroku pg:backups capture --app=yourappname
# this will download the last generated dump
heroku pg:backups public-url --app=yourappname
# and then just visit the generated url

Some commands you might find useful:
If you are using s3cmd to upload your file into amazon aws:

s3cmd put yourdatabasedump s3://yourbucket/yourdatabasedump --acl-public

If you want to import that in local development:

psql -d db/yourdb_goes_here < your-dump-file

Check which posgress plan your application has

heroku addons | grep POSTGRES

If you are using the free postgress plain and you want to pay for example the basic $9 per month you can upgrade your plan like so:

heroku addons:add heroku-postgresql:hobby-basic

Set a different database url for your heroku app

# Create a new database
heroku addons:add heroku-postgresql:hobby-basic
# see the current databse used
heroku config
# you are going to see something like:
# DATABASE_URL:               postgres://dfl3IwE6hHj2qsqQNGVaackrK0@ec2-54-163-227-57.compute-1.amazonaws.com:5432/xxxxfd
# HEROKU_POSTGRESQL_ROSE_URL: postgres://dfl3IwE6hHj2qsqQNGVaackrK0@ec2-54-163-227-57.compute-1.amazonaws.com:5432/xxxxfd
# HEROKU_POSTGRESQL_TEAL_URL: postgres://dfl3IwE6hH2qsqQNGVaackrK0@ec2-54-163-227-57.compute-1.amazonaws.com:5432/xxxxxxxfde
# LANG:                       en_US.UTF-8
# now you can set a new url using the command:
heroku set VARIABLE=value
# in my case
heroku config:set DATABASE_URL=postgres://dfl3IwE6hH2qsqQNGVaackrK0@ec2-54-163-227-57.compute-1.amazonaws.com:5432/xxxxxxxfde

Override your current database in heroku with a new dump or sql file

# reset and empty your database in heroku
heroku pg:reset DATABASE
# Now import your sql see the commands above
# migrate your database
heroku run rake db:migrate

Import dump downloaded from heroku into local

pg_dump --no-owner --no-acl -d db/yourdb_development < TEAL_553ed6ff69702d796a160000.dump
# or try
pg_restore --verbose --clean --no-acl --no-owner -d db/yourdb_development TEAL_553ed6ff69702d796a160000.dump