Django: What are the best practices to migrate a project from sqlite to PostgreSQL

Question

I need to migrate a complex project from sqlite to PostgreSQL. A lot of people seems to have problem with foreign keys, data truncature and so on...

• Is there a full automated utility ?
• Do I need to check some data or schema before the migration ?

Edit : I tried django-command-extensions DumpScript but it doesn't run on my 2GB RAM PC with my current DataSet.

Answer 1

In my experience, dumping & restoring from SQL doesn't work properly.

You should follow this sequence instead:

1. Dump db contents to json

$ ./manage.py dumpdata > dump.json

2. Switch the backend in settings.py

DATABASES = {
    # COMMENT OUT:
    # 'default': dj_database_url.config(default='sqlite:////full/path/to/your/database/file.sqlite'),
    # ADD THIS INSTEAD:
    'default': dj_database_url.config(default='postgres://localhost:5432/postgres_db_name'),
}

3. Syncdb and migrate the new DB to the same table structure

$ ./manage.py syncdb
$ ./manage.py migrate

4. Load the json to the new db.

$ ./manage.py loaddata dump.json

5. Congrats! Now the new data is in your postgres db.

Answer 2

The following is a refinement of Nimo's answer and Stephen's answer for Django 1.7+:

1. ./manage.py dumpdata --natural-primary --natural-foreign > dump.json
2. Change DATABASES in settings.py to point to the new (PostgreSQL) db.
3. ./manage.py migrate
4. ./manage.py loaddata dump.json

One problem I encountered is that SQLite doesn't seem to actually enforce the maximum length for CharFields. In my case, this made the loaddata step fail. I was able to find (and delete) model instances with too long CharField values via:

MyModel.objects.extra(where=["LENGTH(text) > 20"]).delete()

Once I did this before step 1. above, everything worked.

Answer 3

I've never had to do it but what I would try is.

1. Stop running servers
2. python manage.py dumpdata
3. Alter settings.py to point to the newly created postgres database
4. python manage.py loaddata

Answer 4

According to the @Nimo answer, using from "syncdb", doesn't work in Django 1.9 and later (that works on Django 1.7)

Instead, use the command below:

python manage.py migrate

---

And here is Postgres setting configuration:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'myproject',
        'USER': 'myprojectuser',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '',
    }
}

Answer 5

Another way of doing this may be to use multiple databases.

http://docs.djangoproject.com/en/dev/topics/db/multi-db/

It is important you read this section.

http://docs.djangoproject.com/en/dev/topics/db/multi-db/#moving-an-object-from-one-database-to-another

From what I understand that means that provided there is no data in your new DB, from fixtures for example you could do

queryset = MyModel.objects.using("old-db").all()
for obj in queryset:
    obj.save(using="new-db")

Because that should preserve the primary keys I don't think there'd be any foreign key issues.

Answer 6

What worked for me was to run sequel from ruby. Just run the command:

gem install sequel

You will need to have installed on your system the devel packages for postgres , sqlite and ruby Run the command:

gem install pg sqlite3

Create an empty database on postgresql, let's say testDB and assign a grant permission to a user From the command prompt run:

sequel -C sqlite:///path/to/sqlitedb.db postgres://user:password@host/testDB

This will run with no errors.

Change the settings in your django project to work with the postgres database Run

./manage migrate (not necessary)

Run the server

Answer 7

First things first I'd just try a simple:

sqlite3 sqllitedb .dump | psql postgresdb

At that point, just test it out. Write some test scripts in Django to output a set of sample records for each application, and then do a diff to ensure they're identical. If they are, then your conversion is probably fine.

If that doesn't work...

I'd recommend against using Django to dump and load the data, since I'm guessing it's not optimized to do so.

Instead, I'd create a second version of your app with the correct PostgreSQL database settings, run syncdb to create all the tables, then copy the data over from mysqllite to PostgreSQL using some other tool.

The thing is, most of the problems when converting over data is in the table definitions, etc. Those seem to be the most idiosyncratic. If you can generate a SQL script that is a dump of just the table contents, that should be pretty standard SQL INSERT INTO commands.

Honestly, I can't see why there would be foreign key problems. Assuming that sqlite is creating accurate foreign keys (and why wouldn't it?) then there's no way that wouldn't copy over correctly. Really, foreign keys aren't special forms of data. It's no more likely that the UserProfile.user_id field will contain an incorrect value than the UserProfile.photo field would. If the foreign key concern is that the fields themselves aren't correctly identified as foreign key fields (i.e. no constraints), then the option of first creating the database using syncdb will address that problem.

As per truncation: as I understand it, PostgreSQL throws a hard error if data is about to be truncated. I don't know if that is the case with sqlite or if it just truncates silently. Either way, again assuming sqlite isn't somehow munging the data on export, the fields should contain data that is the proper length for the field it's going in. The only thing I can think of that might affect this is character encoding, so make sure that the PostgreSQL fields have the same encoding as the sqlite tables do, at least during the import.