How to Change Your Django Database to PostgreSQL

For most people, when developing a Django web application, they use the default SQLite3 database. SQLite3 allows you to quickly get started coding without complexities. However when its time to move the application to production, you may need to change to a more robust database such as MySQL or PostgreSQL

In this guide, we will learn how to do exactly that. We will learn how to move from SQLite3 to PostgreSQL.

Assumptions

• You are using Ubuntu 20.04
• You do not intend to carry any data from the development database to the PostreSQL, just the structure of the database

Procedure

1.First, install Postgresql on your target server.

sudo apt update

sudo apt install postgresql postgresql-contrib

2.Once installed, create a database and user for your database, giving them the correct permissions

CREATE DATABASE my_database;<br>
CREATE USER my_user WITH PASSWORD 'password';<br>
GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

3.Next, grant permissions to the public schema of your PostgreSQL database to your user

GRANT ALL ON SCHEMA public TO my_user;
GRANT ALL ON SCHEMA public TO public;

4.Next, access the Postgres pg_hba.conf file and change the line below:

local                    all                     all                           peer

to

local                    all                     all                           md5

NB: On Ubuntu, the pg_hba.conf file is located under /etc/postgresql/{{postgres_version}}/main/pg_hba.conf.

You can however determine the exact location by running this query on your postgres console

postgres=# SHOW hba_file;

Once done, we now need to change the settings in Django so it stops using SQLite3

5. Access settings.py file in your Django project.

Look for the DATABASES settings and comment out these lines by putting the # symbol infront of each line.

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

6. Beneath that, add the lines below

"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": POSTGRES_DATABASE,
"USER": POSTGRES_USERNAME,
"PASSWORD": POSTGRES_PASSWORD,
"HOST": POSTGRES_HOST,
"PORT": POSTGRES_PORT
}

NB: Replace the values POSTGRES_** with actual values. Refer to the relevant Django documentation on how to configure a Postgres database on Django.

Your final DATABASES section should look like this

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

    "default": {
       "ENGINE": "django.db.backends.postgresql",
	"NAME": POSTGRES_DATABASE,
	"USER": POSTGRES_USERNAME,
	"PASSWORD": POSTGRES_PASSWORD,
	"HOST": POSTGRES_HOST,
	"PORT": POSTGRES_PORT
    }
}

7. Once done, now access your Django application folder and find all migrations folders inside your app. Generally, all Django apps have a migration folder provided you created models for the apps and had run migration commands

Now, delete all files inside migration folder except __init__.py. Make sure to do this on all your migration folders. Each Django app has a migration folder provided you have run the migrations command at least once.

8.Once deleted, run the following commands to create new migrations

(venv) python manage.py makemigrations
(venv) python manage.py migrate

Congratulations! Your database tables should all be created on postgres. You can now proceed to create a new super user and some demo data as needed.

Troubleshooting

You may encounter the errors below while working trying to use Postgres from SQLite3

1. No changes detected when running python manage.py makemigrations command

Fix:

This means the migration folders still have previous migrations when the command was run. Double check the folders do not have anything except __init__.py

2. You get the error ‘on LINE 1: CREATE TABLE “django_migrations” (“id” bigint NOT NULL PRIMA…’ as shown below when you run python manage.py migrate command

During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "manage.py", line 22, in
main()
File "manage.py", line 18, in main
execute_from_command_line(sys.argv)
File "/home/mwabini/DjangoProjects/rwitter/venv/lib/python3.8/site-packages/django/core/management/init.py", line 442, in execute_from_command_line
utility.execute()
File "/home/mwabini/DjangoProjects/rwitter/venv/lib/python3.8/site-packages/django/core/management/init.py", line 436, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/home/mwabini/DjangoProjects/rwitter/venv/lib/python3.8/site-packages/django/core/management/base.py", line 412, in run_from_argv
self.execute(*args, *cmd_options) File "/home/mwabini/DjangoProjects/rwitter/venv/lib/python3.8/site-packages/django/core/management/base.py", line 458, in execute output = self.handle(args, *options) File "/home/mwabini/DjangoProjects/rwitter/venv/lib/python3.8/site-packages/django/core/management/base.py", line 106, in wrapper res = handle_func(args, **kwargs)
File "/home/mwabini/DjangoProjects/rwitter/venv/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 356, in handle
post_migrate_state = executor.migrate(
File "/home/mwabini/DjangoProjects/rwitter/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 107, in migrate
self.recorder.ensure_schema()
File "/home/mwabini/DjangoProjects/rwitter/venv/lib/python3.8/site-packages/django/db/migrations/recorder.py", line 72, in ensure_schema
raise MigrationSchemaMissing(
django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table (permission denied for schema public
LINE 1: CREATE TABLE "django_migrations" ("id" bigint NOT NULL PRIMA…
^
)

Fix:

To fix this, first make sure the database user has necessary permissions to access the database. Refer to step 2 in the guide above. Next make sure the database user has permissions to access the public SCHEMA. As seen on the error message, permission denied for schema public, insufficient permissions to access that schema results in migrations not running successfully. Refer to step 3 in the guide above