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"),
}
}