PostgreSQL on Django

In this post we will learn how to use PostgreSQL on Django.

Table of Contents

• Initial Setup
• Creating Postgres Database and User
• Install Django within a Virtual Environment
• Change The DATABASE setting
• Migrating existing project from sqlite to postgreSQL with data

Initial Setup

$sudo apt-get update
$sudo apt install python3-pip python3-dev libpq-dev postgresql postgresql-contrib

Creating Postgres Database and User

$sudo -u postgres psql

postgres=#CREATE DATABASE myproject;

postgres=# CREATE USER myprojectuser WITH PASSWORD 'password';

postgres=# GRANT ALL PRIVILEGES ON DATABASE myproject TO myprojectuser;

postgres=# ALTER DATABASE myproject OWNER TO myprojectuser;
postgres=# \q

Install Django within a Virtual Environment

$sudo pip3 install virtualenv
$mkdir myproject
$cd myproject
$python -m venv venv
$source venv/bin/activate

(venv)$ pip install Django psycopg2

(venv)$ django-admin startproject myproject

Change The DATABASE setting

Go to settings.py file of your Django Project and change

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

TO

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'myproject',
        'USER':'myprojectuser',
        'PASSWORD':'password',
        'HOST':'localhost',
        'PORT':'',
    }
}

(venv)$ cd myproject
(venv)$ python manage.py makemigrations
(venv)$ python manage.py migrate

(venv)$ python manage.py runserver

Migrating existing project from sqlite to postgreSQL with data

In case you already have a project and data in sqlite. You want to save the data to use with postgreSQL.

Current Database Setting

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

Save the data of your sqlite database

(venv)$ python manage.py dumpdata --indent=2 --output=mysite_data.json

New Database Setting

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'myproject',
        'USER':'myprojectuser',
        'PASSWORD':'password',
        'HOST':'localhost',
        'PORT':'',
    }
}

Migrate

(venv)$ python manage.py migrate

Load the data

(venv)$python manage.py loaddata mysite_data.json

In case you get following error in above process

Duplicate key issue when loading back.json file PostgreSQL

(venv)$ python manage.py loaddata --exclude auth.permission --exclude contenttypes mysite_data.json

(venv)$python manage.py runserver