Database integrity (missing constraints)

[153957]

Since the transition from MySQL to Postgres the database constraints have not been properly set.
This seems to be caused by the mysql to postgres tool which does not properly set the required constraints. See the two screenshots of the table structure (in Postico) of the inforecords.Cluster table.

The unique and foreign key constraints are not properly set in the live database. Although Django should take care of most of these automatically it would be wiser to have the database constraints properly set.

This will require some careful work, probably restoring from a dump. However, I had some trouble loading a dumped database into my local database with proper constraints... If I loaded the dumped data into a bare database (without tables) it went without issues.

Live database

Local newly migrated database

( @davidfokkema @tomkooij @kaspervd )

[153957]

To restore the database locally (vagrant) without errors/warnings I had to remove it and let postgres create the tables.

dropdb --host=localhost --username=postgres publicdb
createdb --host=localhost --username=postgres publicdb
pg_restore --host=localhost --username=postgres  --create --dbname=publicdb /vagrant/publicdb_dump.sql

[153957]

Ran into an issues because of this with the migration to remove pulseheight fits:
https://github.com/HiSPARC/publicdb/blob/master/publicdb/histograms/migrations/0019_remove_pulseheightfit.py
I had to disable the 'AlterUniqueTogether' step of the migration to make it work.