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

by

15👍

✅

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.

👤solarissmoke

8👍

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:

👤Erwin Brandstetter

0👍

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

👤salafi

Leave a comment