Manually adding data causes IntegrityError

Question

Hello,

I have two Django sites: one for development and one for production. Every once in a while, the data from the development database needs to be transferred to the production database or the other way around. I use postgresql.

This works fine: I empty the tables from the database I want to copy to, I generate sql from the applicable tables, and insert the data in the emptied tables. So far, so good.

But when I enter data into the database via the admin interface, Django raises IntegrityErrors, because appname_modelname_pkey already exists.

I think this is because the admin interface wants to add data with id 1, but that's already an imported record. Django isn't aware that id '1' is already taken.

How do I fix this problem? I want Django to increment the id (like SQL auto_increment would do), no matter what data is already stored.

Any help is appreciated!

Answer 1

If you're using postgres on both sides, then the sequence associated with the primary keys are going to be different.

For example, suppose you're moving production data to development. Also, suppose the sequence value in production is 20 and the sequence in development is 10. Then the first new item you add in development will have an id of 11. That id (probably) already exists in the production data, so you get an integrity error.

When you restore tables from a dump, you can reset the sequences by dropping and recreating the existing tables before you restore.

(Or, you can probably use the ALTER SEQUENCE command to sync up the sequences. However, I'm not familiar enough with postgres to say whether that's the right way to do it.)