migrate.sh

#!/bin/sh
# Need to copy data from your SQLite development environment
# to your Postgres production environment? No problem.

set -e 

SOURCE="db/development.sqlite3"
DESTINATION="mashd_production"

echo Starting migration from $SOURCE to $DESTINATION

echo Using rake to sync the database schemas...
RAILS_ENV=production rake db:drop
RAILS_ENV=production rake db:create
RAILS_ENV=development rake db:schema:dump
RAILS_ENV=production rake db:schema:load
echo done.  Now moving on to table data.

for TABLE in `sqlite3 -line $SOURCE '.tables'`; do
  rm -f $TABLE.csv
  echo Dumping $TABLE from $SOURCE
  sqlite3 $SOURCE <<EOF
.mode csv
.header off
.output $TABLE.csv
SELECT * FROM $TABLE;
EOF
  COUNT=`wc -l $TABLE.csv`
  echo Okay, $COUNT rows.
  echo Flushing $TABLE on $DESTINATION
  psql -d $DESTINATION -c "DELETE FROM $TABLE;"
  echo Loading into $DESTINATION
  psql -d $DESTINATION -c "COPY $TABLE FROM '`pwd`/$TABLE.csv' delimiter ',' csv;"
  rm -f $TABLE.csv
  if [ `psql -d $DESTINATION -c "\d $TABLE" -t | grep ' id ' | wc -l` != "0" ]; then 
    echo Resetting $TABLE sequence on $DESTINATION
    ID=`psql -d $DESTINATION -t -c "SELECT COALESCE(max(id)+1, 1) FROM $TABLE;" | head -1`
    psql -d $DESTINATION -c "ALTER SEQUENCE ${TABLE}_id_seq RESTART WITH ${ID};"
    echo "... set to $ID"
  fi
done