Задать вопрос

Тел: +7 965 3737 888





Database migration and dump/load script

<p>I once needed to convert a Django project from PostgreSQL to SQLite. At that time I was either unaware of manage.py dumpdata/loaddata or it they didn't yet exist. I asked for advice on the #django IRC channel where ubernostrum came up with this plan:
simple process:
1) Select everything.
2) Pickle it.
3) Save to file.
4) Read file.
5) Unpickle.
6) Save to db.
Or something like that.
<p>First I thought it was funny, but then started to think about it and it made perfect sense. And so dbpickle.py was born.
<p>I've used this script also for migrating schema changes to production databases.
<p>For migration you can write plugins to hook on dbpickle.py's object retrieval and saving. This way you can add/remove/rename fields of objects on the fly when loading a dumped database. It's also possible to populate new fields with default values or even values computed based on the object's other properties.
<p>A good way to use this is to create a database migration plugin for each schema change and use it with dbpickle.py to migrate the project.
<p>See also <a href="http://akaihola.blogspot.com/2006/11/database-conversion-django-style.html">original blog posting</a> and <a href="http://groups.google.com/group/django-users/browse_thread/thread/6a4e9781d08ae815/c5c063a288483e07#c5c063a288483e07">my usenet posting</a>  wondering about the feasibility of this functionality with manage.py dumpdata/loaddata.
<p>See <a href="http://trac.ambitone.com/ambidjangolib/browser/trunk/dbpickle/dbpickle.py">trac site</a> for version history.

Вопрос полезен? Да0/Нет0

Ответы (4):

Ответ полезен? Да0/Нет0


I had transaction errors too but they were caused by strings too big for the data model. Apparently neither django nor sqlite3 checked for those but postgresql did. So I updated the data model to accommodate the data and the load operation succeeded. Then I had to update the sequences in the database as the script did not do it. I did : alter sequence xxx_id_seq restart with yyy; Where I obtain yyy like this: select max(id) from xxx;

After that, my application runs fine with postgresql.

Ответ полезен? Да0/Нет0


I haven't had a need for dbpickle.py for a long time now, so I really can't tell right away what could be the reason for the aborted transaction on PostgreSQL. I did load data on PostgreSQL when I created dbpickle, and had no issues back then.

It might be that something has changed in the Django ORM, or you might have a data structure which didn't exist in my data.

I got another private message with a similar report. I'll look into this. If you have a smallish database which causes this problem and which you could share with me, please contact me so I can take a look. My email is akaihol plus-sign django at-sign ambitone dot com.

Ответ полезен? Да0/Нет0

I was able to migrate my data from MySQL -> SQLite using this method, no problem. But I'm getting all kinds of transaction errors when I try to load into "postgresql_psycopg2". Any ideas what I'm screwing up?

Ответ полезен? Да0/Нет0

This is excellent! Thanks for the help! I migrated in reverse, from SQLite3 to Postgres, and this worked like a charm!