I’ve migrated my from Ubuntu 22.04 and MySQL to Debian 11.4.0 and PostgreSQL 13.7. The database migrate was done via Navicat local MySQL connection to local PostgreSQL connection, then dumped into a SQL file before renaming the original diaspora_production and creating a new one then restoring the DB.
All appeared to go well but when I tried out a test post, I’m seeing the following error message.
PG::NotNul/Violation: ERROR: null value in column “id” of relation “posts” violates not-null constraint DETAIL: Failing row
contains (null, 168, 0, a434dc00f66e013adcbf080027aec97d, StatusMessage, test post, 2022-08-04 21:59:31.104251, 2022-08-
04 21:59:31.104251, null, null, null, null, null, null, null, null, null, null).
… in a red box at the top of the screen (see attached)
I don’t know Navicat and I don’t know what exactly you have done. But if you migrate to postgres, the best is to create an empty database and let diaspora create the schema with RAILS_ENV=production bin/rake db:create db:migrate. That way you know you have a clean schema for postgres the way diaspora needs it. Then only import the data into the created schema.
I hope you still have the MySQL database and can copy it over again.
As long as you, again, use different usernames as they were used before, that works. If you still have your old pod with the old mysql database, you can even boot that up real quick and properly close your old again, so the old username isn’t leftover unclosed out there.
I think what happened is that you didn’t import the sequences for postgres, so when it tried to get the next value for the ID, it got a value that already existed in the database, so you need to set the sequence to the latest ID that already exists (I’m sure there are also tools which automate that part). Obviously that doesn’t help you anymore now, as you already started with a fresh database, but just in case somebody else runs into the same problem, here is a hint for what to look for.
