IntegrityError: null value in column "id" for all models/fields with ForeignKey after postgres restore from dump

Question

I'm running into issues trying to use a heroku postgres datastore from a restore of a local postgres database I have. Using the restored postgres database Django runs as normal. It retrieves all objects and uses their fields, primay key's etc without any issues.

But when it comes to writing to the database, I get the same error across the board, regardless of the model(s).

psycopg2.IntegrityError: null value in column "id" violates not-null constraint

When I reset the heroku database and create objects from a blank slate there are no problems. But if I try to create any object on a restored database, I always get this null value in column "id" violates not-null constraint

---

Here's a copy/pasted stack trace from trying to create a basic model in Django Admin. I picked this model example because there's no additional code related to creating it. No signals or anything.

Django Version: 2.0 Python Version: 3.6.3

Traceback:

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py" in _execute 85. return self.cursor.execute(sql, params)

The above exception (null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, Special Class, special-class). ) was the direct cause of the following exception:

File "/app/.heroku/python/lib/python3.6/site-packages/django/core/handlers/exception.py" in inner 35. response = get_response(request)

File "/app/.heroku/python/lib/python3.6/site-packages/django/core/handlers/base.py" in _get_response 128. response = self.process_exception_by_middleware(e, request)

File "/app/.heroku/python/lib/python3.6/site-packages/django/core/handlers/base.py" in _get_response 126. response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "/app/.heroku/python/lib/python3.6/site-packages/django/contrib/admin/options.py" in wrapper 574. return self.admin_site.admin_view(view)(*args, **kwargs)

File "/app/.heroku/python/lib/python3.6/site-packages/django/utils/decorators.py" in _wrapped_view 142. response = view_func(request, *args, **kwargs)

File "/app/.heroku/python/lib/python3.6/site-packages/django/views/decorators/cache.py" in _wrapped_view_func 44. response = view_func(request, *args, **kwargs)

File "/app/.heroku/python/lib/python3.6/site-packages/django/contrib/admin/sites.py" in inner 223. return view(request, *args, **kwargs)

File "/app/.heroku/python/lib/python3.6/site-packages/django/contrib/admin/options.py" in add_view 1553. return self.changeform_view(request, None, form_url, extra_context)

File "/app/.heroku/python/lib/python3.6/site-packages/django/utils/decorators.py" in _wrapper 62. return bound_func(*args, **kwargs)

File "/app/.heroku/python/lib/python3.6/site-packages/django/utils/decorators.py" in _wrapped_view 142. response = view_func(request, *args, **kwargs)

File "/app/.heroku/python/lib/python3.6/site-packages/django/utils/decorators.py" in bound_func 58. return func.get(self, type(self))(*args2, **kwargs2)

File "/app/.heroku/python/lib/python3.6/site-packages/django/contrib/admin/options.py" in changeform_view 1450. return self._changeform_view(request, object_id, form_url, extra_context)

File "/app/.heroku/python/lib/python3.6/site-packages/django/contrib/admin/options.py" in _changeform_view 1490. self.save_model(request, new_object, form, not add)

File "/app/.heroku/python/lib/python3.6/site-packages/django/contrib/admin/options.py" in save_model 1026. obj.save()

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/models/base.py" in save 729. force_update=force_update, update_fields=update_fields)

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/models/base.py" in save_base 759. updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/models/base.py" in _save_table 842. result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/models/base.py" in _do_insert 880. using=using, raw=raw)

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/models/manager.py" in manager_method 82. return getattr(self.get_queryset(), name)(*args, **kwargs)

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/models/query.py" in _insert 1125. return query.get_compiler(using=using).execute_sql(return_id)

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/models/sql/compiler.py" in execute_sql 1280. cursor.execute(sql, params)

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py" in execute 100. return super().execute(sql, params)

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py" in execute 68. return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py" in _execute_with_wrappers 77. return executor(sql, params, many, context)

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py" in _execute 85. return self.cursor.execute(sql, params)

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/utils.py" in exit 89. raise dj_exc_value.with_traceback(traceback) from exc_value

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py" in _execute 85. return self.cursor.execute(sql, params)

Exception Type: IntegrityError at /admin/fantasy/raceclass/add/ Exception Value: null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, Special Class, special-class).

---

The model from stack trace (keep in mind this error happens to every model, not just this [very basic] one.)

class RaceClass(models.Model):
    title = models.CharField(max_length=140)
    slug = models.SlugField(unique=True)

    def __str__(self):
        return self.title

    class Meta:
        ordering = ['title']

---

Here's how I restore(d) the local data over to heroku:

I'm dumping my local Postgres Database (Version 10.0) using command:

PGPASSWORD=mypassword pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dump

Then uploading to AWS, and restoring to a Postgres Datastore (Version 9.6.5) on Heroku using command:

heroku pg:backups:restore 'https://s3.amazonaws.com/me/items/3H0q/mydb.dump' DATABASE_URL

These are both straight from Heroku Documentation: https://devcenter.heroku.com/articles/heroku-postgres-import-export

---

Side note: I'm using Version 10.0 Postgres locally and Heroku Datastore is 9.6.5

Detrital

Please can you post the full stack trace for the error, and the code that runs (for whatever model you're trying to save) when the error happens, and the associated model class. From looking at this, it doesn't seem like the problem is with your database restore but with what you're trying to write to the database. – Schmaltzy

@Schmaltzy Added a stack trace and model code. Went with the most basic one I could find as the problem happens to all of them. Hope you can help! – Detrital

Answer 1

I'm fairly sure that this is because you are exporting from Postgres 10 and importing into 9. It isn't failing altogether but some of the schema definition (in this case the auto-incremented ID fields) are not being correctly imported.

I can think of two options:

1. Try dumping raw SQL instead of a custom format:

   PGPASSWORD=mypassword pg_dump --no-acl --no-owner -h localhost -U myuser mydb > mydb.sql
   

   You can't use pg_restore to load this - instead you have to run the query manually using psql. Something like this should work:

   heroku pg:psql < mydb.sql
   

   The caveat here is that you first need to empty the existing database.

2. If this also fails then you need to export from the same major version of Postgres that you want to import into.

Schmaltzy

This is 100% correct if you did not manually set the id = models.AutoField() attribute on the corresponding model. – Cumulostratus

Answer 2

The key error is this:

I'm using Version 10.0 Postgres locally and Heroku Datastore is 9.6.5

That's a problem waiting to happen. I would try to use the same version on both. At least the same major version.

What comes to mind with these two in particular is the introduction of standard-SQL IDENTITY columns in Postgres 10, which are meant to largely replace serial columns. You did not disclose table definitions, so I can only guess. The IDENTITY feature in Postgres 10 wouldn't translate back to Postgres 9.6, which could very well explain the violating NULL values in your error messages.

Related:

• Auto increment table column

Macmullin

Answer 3

I have recently face same problem, here is what works for me:

1. Create new database

2. Using Django manage.py to dump the data of my database and restore it to the new database after applying all migrations.

   python manage.py dumpdata --exclude auth.permission --exclude contenttypes --indent 2 > db.json

3. Restore the backup

   python manage.py loaddata db.json

Reproval