How to install an extension in PostgreSQL before creating the models in django?

Question

Using django 1.8 + Postgres 9+, I have models with custom PG datatypes (like ltree). Creating de database from zero fail because

CREATE EXTENSION ltree;

is not executed. I try with a empty migration, but that run after the models creation. Exist a way to run sql before the models creation?

Brigittebriley

You can manually edit the initial migration to add this custom sql. BTW why is this tagged postgresql 8.4? – Blunge

I do that (in the initial migration) but is not run before the model creations. I delete the PG database for a full recreation before run python manage.py migrate – Brigittebriley

how about clearing out the migrations for that app, creating a new migration that only contains the create extension and then ./manage.py makemigrations to create a new migration for table creation (will have to clear up the entry in django_migrations though) – Blunge

Answer 1

I know this is unanswered for long, and maybe now you already have figured out the answer. But I'm posting just in case someone gets a little bit of help from this.

For extensions that are available in Postgres

If the extension is one of the defaults that are available with Postgres, then you can simply create first migration as this, and then load other migrations.

# -*- coding: utf-8 -*-
from __future__ import unicode_literals
from django.contrib.postgres.operations import HStoreExtension

from django.db import migrations


class Migration(migrations.Migration):

    run_before = [
        ('some_app_that_requires_hstore', '0001_initial'),
    ]

    operations = [
        HStoreExtension(),
    ]

Notice the use of run_before. It is the exact opposite of dependencies. Alternatively, you can make this migration as say 1st one and load all others after this using dependencies.

In case this migration fails to create an extension because of privileges issues, then you can simply use the superuser and nosuperuser in Postgres to temporarily provide privileges for the current user to run migrations like:

ALTER ROLE user_name superuser;
# create the extension and then remove the superuser privileges.
ALTER ROLE user_name nosuperuser;

For third-party extensions that are not available in Postgres

For third-party extensions, you can use run_python, to load the extension for you like:

from django.db import migrations


def create_third_party_extension(apps, schema_editor):
    schema_editor.execute("CREATE EXTENSION my_custom_extension;")


def drop_third_party_extension(apps, schema_editor):
    schema_editor.execute("DROP EXTENSION IF EXISTS my_custom_extension;")


class Migration(migrations.Migration):

    dependencies = [
        ('venues', '0001_auto_20180607_1014'),
    ]

    operations = [
        migrations.RunPython(create_third_party_extension, reverse_code=drop_third_party_extension, atomic=True)
]

Alternatively, you can just have them as a part of your deployment scripts instead of migrations.

I hope this helps.

Arrears

Hi, I am facing the same issue. How can I add this to the existing schema? Do I just create a random migration file? I am getting this error when I added custom file. django.db.migrations.exceptions.InconsistentMigrationHistory: Migration main.0001_initial is applied before its dependency main.0000_extenstion on database 'default'. @sanyam-khurana – Rondure

Whatever new migration file you create should have dependency according to you previous migration which seems the 0001_initial migration in your case. – Arrears

Also for 3rd party extensions not available, I found it simpler to add another Class for your extension in the django.contrib.postgres.operations file that mimics out-of-the-box extensions available. Then you can easily treat this as out-of-the-box. – Pelion

Answer 2

You can also install the extension on the template database, and when new databases are created (like when running tests), the new database will copy that template and include the extension.

psql -d template1 -c 'create extension if not exists hstore;'

Curtiscurtiss

Answer 3

For django 3+ you can use migrations.RunSQL:

class Migration(migrations.Migration):

    dependencies = [
        ('some_app_that_requires_hstore', '0001_initial'),
    ]

    operations = [
        migrations.RunSQL('CREATE EXTENSION ltree;'),
        migrations.AddConstraint(
            ...
        ),
    ]

Cheremkhovo