How to migrate from sqlite to postgres using a local sqlite database in Django?

Question

I have a 'xxxxx.db' sqlite3 database and I want to move the data to PostgreSQL. I have done some research and seen a couple of options but none of them worked (PGloader etc.). What are some other options? I am using Windows but solutions in Linux are welcome as well.

I have tried doing it in PowerShell(via Jupyter Notebook):

!pip install virtualenvwrapper-win
!mkvirtualenv [name]
!pip install django
!django-admin startproject [name]
cd [name]
!python manage.py startapp app
!python manage.py inspectdb
!python manage.py inspectdb > models.py
!python manage.py migrate
!manage.py runserver
!python manage.py dumpdata > data.json

But the dump does not include the data from my db, I have also changed the settings to

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': absolute_path_to_db/db,
    }
}

Thanks in advance!

Well you likely dumped the data of the *new* SQL file, so that means no data was there to dump... — Willem Van Onsem, Jul 11 '22 at 18:48

score 0 · Answer 1 · answered Jul 11 '22 at 18:59

The dumpdata command [Django-doc] will dump data of the activated database. Since you are pointing to the new database, that means the database is (likely) empty, and thus there is no data to dump at all.

You only need to activate the virtual environment and dump the data, so with the old database as active database in the settings.py, you activate the virtual environment, and dump the data in a JSON file:

. virtualenv/bin/activate
python3 manage.py dumpdata --all > data.json

with virtualenv the name of the directory where the virtual environment is stored.

Next in your settings, you can rewrite this to use the (new) PostgreSQL database, for example with:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': '<db-name>',
        'USER': '<db-user>',
        'PASSWORD': '<db-password>',
        'HOST': '<db-host>',
        'PORT': '5432',
    }
}

You should install the psycopg2 package [PyPi], migrate the database to create the tables, and then you can load the data from the data.json with the loaddata command [Django-doc]:

. virtualenv/bin/activate
pip3 install psycopg2
python3 manage.py migrate
python3 manage.py loaddata data.json

Would it be a bad idea to create CSVs of the tables and import them to PostgreSQL that way? Thank you for your answer but I still cannot seem to do it, I am getting errors at the loaddata part. — Anarcheese, Jul 11 '22 at 19:52
@Anarcheese is it an error related to `contenttypes` ? If so, fix it by running `ContentType.objects.all().delete()` after running migrations, then try loading data again. See also: https://stackoverflow.com/q/853796/5747944 — sytech, Jul 11 '22 at 20:02

How to migrate from sqlite to postgres using a local sqlite database in Django?

1 Answers1