Database migration tips

Database Migration.md

Using the Rails way

# export the database
# use gem 'seed_dump'
# which writes all your data into db/seeds.rb
rake db:seed:dump

# import the database
# upload seeds.rb to server
scp db/seeds.rb user@domain:/path/to/app/db/seeds.rb

# Clear all data by rolling back migrations
RAILS_ENV=production rake db:rollback STEP=99999
RAILS_ENV=production rake db:migrate

# import db
RAILS_ENV=production rake db:seed

Postgres - PG_DUMP

• This generates pure SQL statements for migrations
• Does not work for gem devise table due to encrypted passwords
• This method is quite version-agnostic

# export out DB
# --clean : to drop all tables/etc
# --no-privileges --no-owner : to remove ownership in tables
pg_dump project_development --clean --no-privileges --no-owner > db.sql

# copy to server
scp db.sql user@domain:/path/to/app/production/current/db.sql

# import to DB
psql -d project_db -U db_user -f db.sql

Postgres - PG_RESTORE (recommended)

• This uses the PG_DUMP and PG_RESTORE for better migration
• Note that you must have the same PG_DUMP and PG_RESTORE versions
• Note that Centos may not be using the latest PG_RESTORE. Do a find / -name pg_restore and pg_restore --version to find out.

# Dump it
pg_dump --yourdatabaseandpleasefindtheconfigsyourself

# restoring it
# --clean will drop all tables before importing
# --no-acl --no-owner is very helpful in skipping ownership/permission issues
/usr/pgsql-9.2/bin/pg_restore --verbose --clean --no-acl --no-owner -U stapi_staging_user -d stapi_staging latest.dump

On Heroku

• Refer to Heroku Database.md for instructions on how to export/import database on Heroku