PG::NotNul/Violation: ERROR: null value in column "id" of relation "posts" violates not-null constraint DETAIL:

Sorry, guys. Me again.

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)

Is this something easily remedied or does it indicate my migrate from MySQL to PostgreSQL not go as well as it seemed?

Crap! it’s worse than I thought. I logged out again to see if that would solve anything and I can’t log back in now.

I guess there’s more to migrating from MySQL to PostgreSQL than I thought.

Will keep digging.

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.

Oh, you can bet i have backups of everything this time! :grin:

I’m completely new to PgSQL and I missed out the step between creating the new database and importing the data.

I’ll give the rake commands a try later.

Thank you!

Progress! I’m able to log in okay now. :+1:t2:

When I try to create a test post to an unused aspect however, I now see

I’m not sure how to get past this. Anyone help, please?

I’ve decided to cut my losses again and start from a fresh database build.

Far less stressful. :slight_smile:

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.

Done. Thanks for the reminder! :+1:t2: