Latticegrid server migration

PostgreSQL import and export (pg_restore and pg_dump)

database dump and restore

  pg_dump -i -Fc fsmpublications_development -U fsmpublications -h pdev > fsmpublications_dev_201012315.dmp
  pg_restore -d fsmpublications_production -U fsmpublications -h pprod fsmpublications_dev_201012315.dmp

data dump from my laptop after a new build:

  psql --version
  #psql (PostgreSQL) 8.4.5
  #contains support for command-line editing
  pg_dump -i -Fc fsm_latticegrid_production -U fsm_latticegrid -h pprod > fsmpublications_prod_201103306.dmp

  pg_dump -i -Fc fsmpublications_development -U fsmpublications > fsmpublications_dev_201103306.dmp

set up a tunnel from my laptop to rails2-prod routing localhost port 5433 to pprod port 5432 (standard postgres port)

  ssh -f [email protected] -L 5433:pprod.bioinformatics.northwestern.edu:5432 -N

run pg_restore to get the data from my laptop into production. -O tells it to use the connection postgres user, not the one I extracted from

  pg_restore -c -O -d fsm_latticegrid_production -U fsm_latticegrid -h localhost -p 5433 fsmpublications_dev_201103306.dmp

copy it using scp

  scp fsmpublications_dev_201103306.dmp [email protected]:/home/wakibbe

now restore from pg_dump file using pg_restore

  pg_restore -O -d fsm_latticegrid_production -U fsm_latticegrid -h pprod fsmpublications_dev_201103306.dmp

Alternatively, you can do this more directly using ssh tunneling. For instance, set up a tunnel from a laptop to rails-dev by routing localhost port 5433 to pdev port 5432 (standard postgres port)

  ssh -f [email protected] -L 5433:pdev.bioinformatics.northwestern.edu:5432 -N

Now run pg_dump to get the data out of Postgres into a file

  pg_dump -i -Fc fsmpublications_development -U fsmpublications -h localhost -p 5433 > fsmpublications_dev_201012315.dmp

Set up a tunnel from my laptop to rails-prodv routing localhost port 5433 to pprod port 5432 (standard postgres port)

  ssh -f [email protected] -L 5433:pprod.bioinformatics.northwestern.edu:5432 -N

run pg_restore to get the data from my laptop into production. -O tells it to use the connection postgres user, not the one I extracted from

  pg_restore -c -O -d fsm_latticegrid_production -U fsm_latticegrid -h localhost -p 5433 fsmpublications_dev_201012315.dmp