How to install the hstore extension on the template1 database using a Django migration?

Question

According to http://djangonauts.github.io/django-hstore/#_limitations, in order to install PostgreSQL's hstore extension on the template1 database one must run

psql -d template1 -c 'create extension hstore;'

as an initial setup step. I'd prefer to have this automatically done by Django, however; it seems I can use the CreateExtension operation for this. However, if I try to do this by modifying the 0001_initial.py migration as follows,

from __future__ import unicode_literals

from django.db import migrations, models
import django.db.models.deletion
from django.contrib.postgres.operations import CreateExtension


class Migration(migrations.Migration):

    initial = True

    dependencies = [
    ]

    operations = [
        CreateExtension(name='hstore'),
        ...
    ]

I still run into a type "hstore" does not exist error when I try to python manage.py migrate. This particular stack trace is from the Aptible PaaS, which is provisioned with a 'default' PostgreSQL database without hstore installed:

remote: INFO -- : WAITING FOR: Run before_release commands from .aptible.yml: python3 manage.py migrate
remote: INFO -- : (0.001) SELECT typarray FROM pg_type WHERE typname = 'citext'; args=None
remote: INFO -- : (0.002) 
remote: INFO -- :             SELECT c.relname, c.relkind
remote: INFO -- :             FROM pg_catalog.pg_class c
remote: INFO -- :             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
remote: INFO -- :             WHERE c.relkind IN ('r', 'v')
remote: INFO -- :                 AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
remote: INFO -- :                 AND pg_catalog.pg_table_is_visible(c.oid); args=None
remote: INFO -- : (0.001) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=()
remote: INFO -- : (0.001) 
remote: INFO -- :             SELECT c.relname, c.relkind
remote: INFO -- :             FROM pg_catalog.pg_class c
remote: INFO -- :             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
remote: INFO -- :             WHERE c.relkind IN ('r', 'v')
remote: INFO -- :                 AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
remote: INFO -- :                 AND pg_catalog.pg_table_is_visible(c.oid); args=None
remote: INFO -- : (0.001) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=()
remote: INFO -- : Operations to perform:
remote: INFO -- :   Apply all migrations: admin, auth, contenttypes, lucy_web, oauth2_provider, sessions
remote: INFO -- : Running migrations:
remote: INFO -- : CREATE TABLE "lucy_web_question" ("id" serial NOT NULL PRIMARY KEY, "created_at" timestamp with time zone NOT NULL, "updated_at" timestamp with time zone NOT NULL, "question_type" varchar(255) NOT NULL, "number_in_category" integer NOT NULL CHECK ("number_in_category" >= 0), "options" varchar(255)[] NOT NULL, "conditions" hstore NOT NULL, "info" hstore NOT NULL, "has_conditional_text_field" boolean NOT NULL); (params None)
remote: INFO -- : (0.002) CREATE TABLE "lucy_web_question" ("id" serial NOT NULL PRIMARY KEY, "created_at" timestamp with time zone NOT NULL, "updated_at" timestamp with time zone NOT NULL, "question_type" varchar(255) NOT NULL, "number_in_category" integer NOT NULL CHECK ("number_in_category" >= 0), "options" varchar(255)[] NOT NULL, "conditions" hstore NOT NULL, "info" hstore NOT NULL, "has_conditional_text_field" boolean NOT NULL); args=None
remote: INFO -- : Traceback (most recent call last):
remote: INFO -- :   File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 62, in execute
remote: INFO -- :     return self.cursor.execute(sql)
remote: INFO -- : psycopg2.ProgrammingError: type "hstore" does not exist
remote: INFO -- : LINE 1: ..., "options" varchar(255)[] NOT NULL, "conditions" hstore NOT...
remote: INFO -- :                                                              ^

As I understand it, template1 serves as the template for all databases when creating them, and since I am running into this error at a CREATE TABLE command it must meant that the 0001_migration.py did not create this extension in template1, but in some other database. How can I create a migration which does create the extension in template1?

Is there a reason you're trying to use django-hstore? It's not maintaned and only supports versions 1.7 - 1.9. You have linked Django 2.0. documentation. Anyway, if you scroll down a bit in the Django documentation you have linked, you'll see there is `HStoreExtension`. Import it and add `HStoreExtension()` to migration instead of `CreateExtension()`. — Borut, Feb 07 '18 at 20:20

score 0 · Answer 1 · answered Feb 07 '18 at 21:12

I finally added the HStoreExtension() operation not to 0001_initial.py, but to all migrations with 'hstore' in the file (which I found by searching). For example, one called 0071_question_conditions.py I modified to be as follows:

from __future__ import unicode_literals

import django.contrib.postgres.fields
import django.contrib.postgres.fields.hstore
from django.db import migrations
from django.contrib.postgres.operations import HStoreExtension


class Migration(migrations.Migration):

    dependencies = [
        ('lucy_web', '0070_auto_20171204_1217'),
    ]

    operations = [
        HStoreExtension(),
        migrations.AddField(
            model_name='question',
            name='conditions',
            field=django.contrib.postgres.fields.ArrayField(base_field=django.contrib.postgres.fields.hstore.HStoreField(blank=True), blank=True, null=True, size=None),
        ),
    ]

With these operations, I'm able to deploy successfully to Aptible (i.e., with a database without hstore pre-installed).

How to install the hstore extension on the template1 database using a Django migration?

1 Answers1