How should I upgrade from bradjasper's django-jsonfield to Django's built-in jsonfield?

Question

I have a Postgres 9.4 / Django 1.8 database that uses bradjasper's django-jsonfield package. (See https://github.com/bradjasper/django-jsonfield ) It works well, but I would like to upgrade the existing data to use Postgres 9.6 and Django 1.9's built-in JSONField. (See https://docs.djangoproject.com/en/1.10/ref/contrib/postgres/fields/#jsonfield ) This will allow more robust searches of JSON content.

How do I upgrade the old database to the new one?

What I've tried: I tried inserting a pair of schema migrations to

• Convert the bradjasper JSONField to a TextField, including running the migration. (This shouldn't change the database, since bradjasper stores data as a string.)
• Run Django's dumpdata command.
• Updated Postgres & Django versions.
• Run the migration to convert the TextField to Django's JSONField. (This should be a change in the database, from a text or json to jsonb.)
• Run Django's loaddata command. This gives an error like: u"[] (type <type 'unicode'>) is not a valid list for field url_methods"]: (myapp.mytable:pk=1) field_value was '[]' I'm looking at postgresql migrating JSON to JSONB and Upgrade PostgreSQL JSON column to JSONB? but am hoping to minimize the custom SQL.

Answer 1

Upgrade Postgres first. If all works upgrade Django.

Only if everything works as expected you can start writing your field migration.

You want to go from:

from jsonfield import JSONField

class MyModel(models.Model):
  json = JSONField()

To:

from django.contrib.postgres.fields import JSONField

class MyModel(models.Model):
  json = JSONField()

Steps:

1. Add the new Postgres JSON field named json_new.
2. Make migrations. Do NOT migrate yet.
3. Dive into the migration file and write a data migration (RunPython) to populate the new json field.
4. Make migrations.
5. Delete the old field. Delete the old import.
6. Make migrations, migrate.
7. Rename new field to old field name. json_new > json.
8. Make migrations, migrate.
9. Done.

Step 1:

Use import ... as ... to prevent collisions. Your model will look like:

from jsonfield import JSONField as OldJSONField
from django.contrib.postgres.fields import JSONField

class MyModel(models.Model):
  json = OldJSONField()
  json_new = JSONField()

Step 3:

You need to RunPython in your migration see https://docs.djangoproject.com/en/1.10/ref/migration-operations/#runpython Also note how to import your model.

The actual data migration will be something like:

for obj in MyModel.objects.all()
    obj.json_new = obj.json  
    obj.save()

Step 4 - 7:

Make sure you create separate migrations for deleting and renaming. If you do all code changes and create a single migration, then Django will think you dropped json_new. But you want to drop json and rename json_new to json. Small, but important difference.

It isn't to hard to reduce the migration steps. But that requires to write some code by hand. I'm lazy and like Django to write this code for me.

Answer 2

The ALTER COLUMN command from @tometzky via Upgrade PostgreSQL JSON column to JSONB? does this with surprisingly little hassle:

• sudo -u postgres psql -c 'ALTER TABLE mytable ALTER COLUMN "myfield" TYPE jsonb USING "myfield"::text::jsonb;' mydatabase

I didn't need Django's loaddata / dumpdata commands or a custom migration.

I did have some issues getting pg_upgrade to work the way I wanted, since it wasn't on the default path and wanted to change the port used by Postgres during the upgrade. To get around that, I did the following:

• pg_ctl -D /etc/postgresql/9.4/main/ stop
• use sed on postgresql.conf to change which port it used
• install Postgres 9.6
• pg_ctl -D /etc/postgresql/9.6/main/ stop
• cd /var/log/postgresql
• run pg_upgrade
• cd back to the original working directory
• apt-get -y remove postgresql-9.4 postgresql-client-9.4 postgresql-server-dev-9.4
• service postgresql start