|Version 7 (modified by 12 years ago) ( diff ),|
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
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.
vim /home/haiti/web2py/maintenance.htm This site is down for maintenance - back soon! vim /etc/apache2/sites-enabled/prod RewriteRule ^/$ /maintenance.htm [R]
Better to avoid locks & do without SSH login
- Currently needs to be done resource by resource (JOINed tables can be done together as Components of main resource)
- need to do referenced tables before the tables which refer to them
- migrate the locations first (using the xml interface), you can later migrate the orgs and offices together (through Component JOIN)
- Ideally we want to develop a way of having all tables loaded together into RAM, refs fixed-up & then imported to db together
Best longer term as we want to be able to have this functionality exposed to UI.
- BluePrintSynchronisation - which is to be migrated to standard S3XRC Web Services anyway