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
No Comments