I have a Django app running on AWS elastic beanstalk. It was previously connected to a MySQL RDS instance. I have been through several steps to migrate the data from a MySQL instance to a PostgreSQL instance.
In brief I connected to the postgres instance and ran python manage.py migrate
I then ran python manage.py sqlflush and ran the resulting SQL commands
Then I imported a json fixture of content types from the MySQL db. I created the fixture (when connected to the MySQL db) with
python manage.py dumpdata contenttypes --indent=4 --natural-foreign > contenttype.json
and imported it into the PostgreSQL db with
python manage.py loaddata contenttype.json
The above preserves PKs and FKs
I then used AWS DMS to load the data from the MySQL instance to the PostgreSQL instance.
Using the Django app (e.g. through Django admin) all the data can be read from the Postgres instance. However, trying to post data to the database results in a 500 error.
Could this be a problem with security settings in AWS? It seems odd that I can read data from the DB but not post to it.
I've tried connecting to the DB using pgAdmin 4 with the same connection settings. I was able to use an INSERT statement to post to the database from there (but had to explicitly state the PK value)
Update
As suggested by Mark B the problem was Primary Keys. Many of the sequences in postgres had been reset to 1 as the current value. Finding the max value of the ID (PK) in each table and updating the corresponding sequence has got everything working. This question on sequences and primary keys was useful.
