I have some Django sites, and I’ve often wondered about how to make my dev database faithfully mirror the production database (at a point in time), i.e. copy all the info from my prod database back to my dev environment.
Approach 1: Export and import the data
You would think it is simple: just export the data from one and import it into the other. If you use the same type of database (e.g. MySQL) in both environments, this should work fine. But I use sqlite in dev and MySQL in prod. Using sqlite on the development server is quick and easy, with no need to set up MySQL (see my earlier post on some of the hoops you may need to jump through for that to work). But then there are subtle differences between the import and export formats, and you can waste a lot of time mucking around with the files trying to get it to work.
Approach 2: Fixtures
So, the second idea is: use fixtures, e.g. on the production machine:
./manage.py dumpdata --indent 2 > all.json
and load it in again on the dev server (I use git to transfer it but there’s probably a cleaner approach):
./manage.py loaddata all.json
Ah – another problem. The
dumpdata command, with no apps supplied, dumps everything including Users, User profiles, Sites, etc. Your dev database, even if you only just set it up a second ago, will have some content already. So you will get error messages like this:
IntegrityError: Could not load myapp.UserProfile(pk=18): column user_id is not unique
You could go back and only list the apps you want to transfer the data of after the
dumpdata command, but this is painful and you may not know them all if you’re using lots of third party apps (South, Django-CMS, etc).
The solution: Fixtures + some delete statements
But I can give you good news: there is a way to do it by deleting all that initial content from your dev database, and potentially making one small deletion from the above
Start with a newly created database, with the tables installed (e.g. using
./manage.py syncdb). These are the tables you need to wipe clean:
- south_migrationhistory if you’re using South. Note: I am using South but did not delete the contents of this table, and it all worked. But I suspect you should delete the contents.
- any user profiles you define (at least in Django 1.4, I’m not sure how they work in Django 1.5 yet)
In sqlite you’d do this by typing (assuming your database file is named
sqlite3 sqlite.db delete from django_site; delete from django_content_type; delete from auth_permission; delete from auth_user; delete from south_migrationhistory; delete from ...;
Looks scary, but this is a brand new database anyway, right? You could easily recreate this content by starting over with
The last line is where you delete your user profiles. If you do define a user profile, the act of creating a user will also create a profile. So when the users get loaded from the fixture file, user profiles will be automatically created; when the user profiles’ turn comes to be loaded from the fixture, there can be a potential clash. If you are OK to lose all your user’s profile data on your dev machine, there is a simple solution: just delete these entries from the json file.
That’s it! I hope that helps someone out there.