Kalletolonen.com - Linux and coding articles

How to change a production database from SQLite to PostgreSQL

Django ships with SQLite, but PostgreSQL is a more capable DBMS. I wanted to try out the migration to a different system to learn about the process.

Kalle Tolonen
June 6, 2022
Last updated on June 13, 2022

Source(s):
Sweetcode
Digital Ocean
Tutorialspoint
Tero Karvinen 1
Tero Karvinen 2

Configuration
Debian 11
Django 3.2

Backing your old data

It's a really good idea to backup your database before doing anything with it. The reason being, if you mess things up you can still return to your starting point. I did this by dumping the data I had to json and making a copy of my database.

cp db.sqlite3 db_backup.sqlite3
./manage.py dumpdata > data.json

You should have your virtual environment activated to use manage.py.

Psycopg2-adapter

This piece of software makes it possible to convert PostgreSQL arrays to Python's lovely lists. This is not used in the scope of this article, but I chose to install it anyway.

source env/bin/activate
psycopg2 #add to your requirements
pip install -r requirements.txt

Installing PostgreSQL

It's a good idea to update your pkg's and the upgrade the system (if you haven't done that in a little while) before installing more software.

sudo apt-get update
sudo apt-get upgrade

After the update (and upgrade, if you chose to implement it) it's time to install more stuff.

sudo apt-get install libpq-dev python-dev-is-python3
sudo apt-get install postgresql postgresql-contrib

Create a database

This is easily done with 2 commands.

sudo -u postgres createdb $(whoami) #your dedicated django user
sudo -u postgres createuser $(whoami) #your dedicated django user

Django - settings.py

After the installation is done it's time to put some settings into settings.py.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'your django project user goes here',
        'USER': 'your django project user goes here',
    }
}

Run migrations

To test out the system, I executed the migrations of my project.

Operations to perform:
  Apply all migrations: admin, auth, contenttypes, mainsite, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying mainsite.0001_initial... OK
  Applying mainsite.0002_auto_20220604_0600... OK
  Applying mainsite.0003_photo... OK
  Applying mainsite.0004_article_keywords... OK
  Applying sessions.0001_initial... OK

Dumping previous data to PostreSQL

This is done with manage.py.

/manage.py loaddata data.jsos

Installed 61 object(s) from 1 fixture(s)