wiki:UserGuidelines/Admin/DataMigration/Haiti

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

--

Haiti


Data Migration

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

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

CSV

Beware DB locks!

  • Don't keep Web2Py shell open longer than necessary
python web2py.py -S prod -M
db.export_to_csv_file(open('db.csv','wb'))
Ctrl+D

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

python web2py.py -S dev -M
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.