24

What are the steps I need to take to migrate from the default SQLite database to Postgres database?

I'm doing this to get my local development environment as close to my live server (which uses postrgres).

Or is there a reason why local development uses SQLite? Is it not recommended to use Postgres for local development?

Zorgan
  • 8,227
  • 23
  • 106
  • 207
  • It just makes for a much "leaner" development environment to keep it on SQLite3, unless you have a reason not to. If you make sure you don't mix up `settings.py` between dev and release, you should be golden, That's how I do most of my projects. – robotHamster May 14 '18 at 04:17

4 Answers4

40

You can try the following steps:

1. Install psycopg2 to configure the database:

pip install psycopg2


2. Inside the default settings.py

Change original values:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}

To:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'NAME_OF_DB',
        'USER': 'DB_USER_NAME',
        'PASSWORD': 'DB_PASSWORD',
        'HOST': 'localhost',
        'PORT': 'PORT_NUMBER',
    }
}


3. Migrate the DB: 

python manage.py makemigrations
python manage.py migrate

EDIT: Thanks @robotHamster comment. Here is the method to sync the existing data:

Backup the data first:

python manage.py dumpdata > datadump.json

After changing the DB setting:

python manage.py loaddata datadump.json

Source: What's the best way to migrate a Django DB from SQLite to MySQL?
R.yan
  • 2,214
  • 1
  • 16
  • 33
  • 3
    Combines with [this gist](https://gist.github.com/sirodoht/f598d14e9644e2d3909629a41e3522ad) you should be able to keep your data too – robotHamster May 14 '18 at 04:26
7

when you are changing the database you might get a UNICODEERRO:'utf-8'

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xff in position 0: invalid start byte

After wasting more than 5 days I finally got the solution .. you will never got that accurate error in internet, I figured it out by myself .

python manage.py dumpdata > datadump.json

then change the database settings.py as per your desired database and then apply the following commands ...

python manage.py makemigrations
python manage.py migrate
python manage.py loaddata datadump.json

and then if u got the error I have mentioned earlier, please follow step by step guide :

1.Install notepad ++
2.open your datadum.json file in notepad++
3.on the bottom right corner you will get the encoding will be anything else than utf-8
4.on the top bar select encoding to UTF-8

you are good to go ..then again

python manage.py load data datadump.json

I have suffered a lot for this ...so please upvote, and shares are also appreciated. Thank you! and for more clearance, you can watch this video:https://youtu.be/RBtEr3TXNwg

Saikat Mukherjee
  • 500
  • 5
  • 11
  • You've posted multiple answers linking to this video in a short time, which makes your posts look like spam and/or that you're affiliated with the video (are you?). Linking to something you're affiliated with without disclosing that affiliation is spam on Stack Exchange/Stack Overflow. See: [**What signifies "Good" self promotion?**](//meta.stackexchange.com/q/182212), [some tips and advice about self-promotion](/help/promotion), [What is the exact definition of "spam" for Stack Overflow?](//meta.stackoverflow.com/q/260638), and [What makes something spam](//meta.stackexchange.com/a/58035). – Makyen Nov 07 '20 at 19:31
5

Here is a great tutorial on how to do this from Django Girls

It shows you the installation as well as the required changes in settings.py.

Logan
  • 316
  • 1
  • 7
1

Hope I am not late. So to my experience if you already have data in your sqlite db, you might face some challenges because some fields in sqlite don't directly match with fields in postgres. For example datetime, and boolean fields.

I found a library that helped me to do this:

https://github.com/Hitman23/pgloader

The library does any needed conversions.

Codebender
  • 195
  • 1
  • 10