Migrate your Django SQLite DB to Postgres

Sqlite is nice and lightweight for use in development but isn't necessarily desirable to use in production. Generally a object-relational database like postgres is advised for production environments where you expect many concurrent queries. Here's how you go about switching:

Install PostgreSQL:

sudo apt update
sudo apt install postgresql postgresql-contrib

Change the default user's password and create a new superuser 'dbuser':

sudo su postgres
psql
ALTER USER postgres WITH PASSWORD 'postgrespasswordhere';
CREATE USER dbuser WITH PASSWORD 'dbuserpasswordhere';
ALTER USER dbuser WITH SUPERUSER;

Create new db, myapp:

CREATE DATABASE myapp;
\l

Now you have a database created called, cd to your app's manage.py:

cd /path/to/your/app

Configure your python virtual environment:

source venv/bin/activate
pip install psycopg2-binary

Export your sqlite database to a json file:

python manage.py dumpdata > db.json

Change your settings.py from:

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

To this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'myapp',
        'USER': 'dbuser',
        'PASSWORD': 'dbuserpasswordhere',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

Recreate database models:

python manage.py migrate

Open shell:

python manage.py shell

Fix incompatible content types related to PostgreSQL:

from django.contrib.contenttypes.models import ContentType
ContentType.objects.all().delete()
exit()

Lastly load your db json data to the new database:

python manage.py loaddata db.json

That's it, now remember to store your database credentials outside of the app itself to avoid putting them through version control. I suggest loading an external json into settings.py on your server or use environment variables.

For example create an external '/etc/mycreds.json' and load it into settings.py:

import json
import os

with open("/etc/mycreds.json") as config_file:
    config = json.load(config_file)

DATABASES = {
    "default": {
        "ENGINE": config.get("DATABASE_ENGINE"),
        "NAME": config.get("DATABASE_NAME"),
        "USER": config.get("DATABASE_USER"),
        "PASSWORD": config.get("DATABASE_PASSWORD"),
        "HOST": config.get("DATABASE_HOST"),
        "PORT": config.get("DATABASE_PORT"),
    }
}