CODEWITHSUNDEEP
djangopostgresqlsqlitedatabase-migration
Amanur Rahman
Amanur Rahman

Reputation: 113

Django sqlite to postgres database migration

I have a quite large db of 750MB which I need to migrate from sqlite to postgres. As, the db is quite large I am facing some issues that some of the previous questions on the same topic did not had.

Few days ago, I have migrated one sqlite db of size 30MB without any issues with loaddata and dumpdata commands. But for this large db, one of my app throws Database image is malformed error when running command dumpdata. Another of my app dumps successfully but does not load. I have seen the with -v 3 verbose flag that the objects are not even processed. To be precise, while running the loaddata command data from json file is processed first to check duplicate primary key and other model constraints then those data are used to create model objects. But for this app, data are not processed in the first place.

Apart from this two commands there are some other methods that does the migration. But, the schema is completely changed in those way which I don't desire to do. Moreover I have faced issue DurationField becomes a string after migration and I couldn't typecast those fields. Bigint becomes int and varchar becomes text etc are some of the issues. I cannot afford to have this kind of issues.

Upvotes: 1

Views: 287

Answers (2)

Amanur Rahman
Amanur Rahman

Reputation: 113

I found the issues for these problems.

  1. Database image is malformed occurred because I was copying entire db file directly. Just as, angardi suggested, I compressed the db file using gzip package and then copied it. Which solved this issue.

  2. There were two issues for which loaddata was not loading a certain app. That app contained a model which had a foreign key to itself. And one of the entry of the model had the foreign key set to the entry. This resulted in an infinite loop and the app wasn't loading. Even after fixing the entry I was facing the same issue. This happened because I dumped data of a specific app. One of the model of the app had a ManyToMany field set to a model of another app. Though, loaddata throws error when ForeignKey field reference is not found, I think it kept searching for the ManyToMany fields without throwing an error which turned into an infinite loop type of situation. All, I had to do here is, dump the entire database or the related app on the same file.

Upvotes: 2

angardi
angardi

Reputation: 375

Read this for the image malformed part.

When migrating from one database to another, you should first dump all data, then run django migrations on the new database and the load data. That should retain all column types.

When running loaddata, the tables that have no dependencies should be loaded first and then the ones that depend on those already loaded.

Upvotes: 1

Related Questions