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

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.