wiki:UserGuidelines/Admin/DataMigration/Haiti

Version 18 (modified by Fran Boon, 15 years ago) ( diff )

--

Haiti

Data Migration & Consistency

We want to be able to migrate data from Prod to Dev so that Testers / Documenters have real data to play with.

Easy as a 1-off: 

/etc/init.d/apache2 stop
rm -rf /home/haiti/dev/databases
cp -ar /home/haiti/prod/databases /home/haiti/dev
/etc/init.d/apache2 start

Maintaining this should be possible via DataMigration#WebServices Web Services

Also want to be able to migrate from the current sqlite backend to MySQL

We often need to use this same tricks to deal with database consistency issues.

CSV

Beware DB locks!

  • Don't keep Web2Py shell open longer than necessary
  • Consider goign into Maintenance Mode on the Webserver & disabling the Web2Py cron in /etc/crontab 
cd /home/haiti/web2py
python web2py.py -S prod -M -N
db.export_to_csv_file(open('db.csv','wb'))
Ctrl+D

Remove reference Tables from CSV file (don't want to import duplicates!)

NB Do CSV file edits in a text editor, not MS Excel (MS Excel makes the dumps unusable!)

NB Be careful of file permissions!

  • All files in the databases folder should be chown www-data:www-data
  • If the 1st run of the model happens in the Python shell, then they will be owned by root... 
cd /home/haiti/web2py
python web2py.py -S dev -M -N
db.import_from_csv_file(open('db.csv','rb'))
db.commit()
Ctrl+D

Changing a Column type in Sqlite live

sqlite handles live migrations pretty well, however if there is existing data then a conversion from string to integer can cause problems. For a development system, the easiest solution is simply to remove all databases/* & restart, however this isn't possible for a live system: 

vim /home/haiti/prod/models/00_db.py
    migrate = True

cd /home/haiti/prod
bzr pull

cd /home/haiti/web2py
python web2py.py -S prod -M

db(db.or_organisation.id > 0).select().export_to_csv_file(open('orgs.csv','wb'))
db.or_organisation.drop()
db.commit()
Ctrl+D

python web2py.py -S prod -M

db.or_organisation.import_from_csv_file(open('orgs.csv','rb'))
db.commit()
Ctrl+D

/etc/init.d/apache2 force-reload

vim /home/haiti/prod/models/00_db.py
    migrate = False

If the system is being heavily used, need to put up a holding page during this time to prevent data entry. For switching on maintenance. 

cd /home/haiti
./maintenance.on

For switching off maintenance. 

cd /home/haiti
./maintenance.off

Changing a set of Lookup Options live

We can easily amend the module_resource_opts = {} live in the code (usually models/module.py) however we need to migrate existing data (after backing it up, of course): 

cd /home/haiti/prod
bzr pull

cd /home/haiti/web2py
python web2py.py -S prod -M

db(db.or_organisation.id > 0).select().export_to_csv_file(open('orgs.csv','wb'))
db(db.or_organisation.type == 4).update(type='')
db(db.or_organisation.type == 5).update(type='')
db.commit()
Ctrl+D

/etc/init.d/apache2 force-reload

If the system is being heavily used, need to put up a holding page during this time to prevent data entry. For switching on maintenance. 

cd /home/haiti
./maintenance.on

For switching off maintenance. 

cd /home/haiti
./maintenance.off

Web Services

Better to avoid locks & do without SSH login

Haiti

Note: See TracWiki for help on using the wiki.