Can't migrate from OrientDB to PG - Sonatype Nexus Repository - Sonatype Community

Can’t migrate from OrientDB to PG

post by rafal.kedziorski on Aug 25, 2024

Hi,

I try to migrate my Nexus t3.70.1.x o 3.71.x. But first I have to migrate my OrienDB to PG16.

I did all the stuff described in Migrating to a New Database

And when I start the migration

java -Xmx10G -Xms10G -XX:+UseG1GC -XX:MaxDirectMemorySize=12G
–add-exports java.base/sun.nio.ch=ALL-UNNAMED -jar nexus-db-migrator-*.jar
–migration_type=postgres
–db_url="jdbc:postgresql://pg16.db.server:5432/nexus?user=nexus&password=xxx"

I get this

12:02:11 [main] WARN c.s.n.d.migrator.MigratorApplication - Please ensure any Nexus Repository instance has been gracefully shut down before proceeding.
12:02:11 [main] INFO c.s.n.d.migrator.MigratorApplication - Do you want to continue [y/n]?
y
12:02:13 [main] INFO c.s.n.d.migrator.MigratorApplication - --content_migration parameter is absent. Setting it to true.
12:02:13 [main] INFO c.s.n.d.migrator.MigratorApplication - Force parameter wasn’t found. Setting it to false by default.
...
12:02:17 [main] ERROR o.s.boot.SpringApplication - Application run failed
java.lang.IllegalStateException: Failed to execute ApplicationRunner
Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]; nested exception is org.postgresql.util.PSQLException: FEHLER: Relation »batch_job_instance« existiert nicht

Error starting ApplicationContext. To display the conditions report re-run your application with ‘debug’ enabled.

Looks like the tables are not created.

Best Regards,

Rafal

post by rafal.kedziorski on Aug 25, 2024

I used the wrong migrator version. But with nexus-db-migrator-3.70.1-03.jar I get the same error.

Error starting ApplicationContext. To display the conditions report re-run your application with ‘debug’ enabled.

12:11:37 [main] ERROR o.s.boot.SpringApplication - Application run failed
java.lang.IllegalStateException: Failed to execute ApplicationRunner
Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]; nested exception is org.postgresql.util.PSQLException: FEHLER: Relation »batch_job_instance« existiert nicht

Best Regards,

Rafal

post by rafal.kedziorski on Aug 25, 2024

Now I try migrato to H2.

12:33:11 [main] INFO c.s.n.d.m.validator.PathValidator - Validating Orient directory contains .bak files: /nexus
Error starting ApplicationContext. To display the conditions report re-run your application with ‘debug’ enabled.
12:33:11 [main] ERROR o.s.boot.SpringApplication - Application run failed
java.lang.IllegalStateException: Failed to execute ApplicationRunner
Caused by: com.sonatype.nexus.db.migrator.exception.InvalidMigrationFilesException: The migration files in /nexus do not match the expected requirements: No backup files (*.bak) found.

The error message is not specific. Which backup files?

I did this:

The utility will use the backup files (e.g., component-.bak, config-.bak, security-.bak) for migration.

I follow all the rules for PG or H2 migration. Sorry, but somethig is here wrong.

post by rafal.kedziorski on Aug 25, 2024

Finally I could migrate to H2. But the documentation is very very bad. It looks like no one tested it before.

I give up for the PG migration.

post by s.schmidt on Aug 26, 2024

Hey Rafal, this “only” works if you dont have 3rd Party-Plugins from the community installed. Because if you move to PG, all 3rd Party is disabled sadly, thats why we can move right now.

post by rafal.kedziorski on Aug 26, 2024

I have only pure Nexus running without any special 3rd Party.

But now I migrated to H2. And it’s ok for me.

Regards,

Rafal

post by chrkuznos on Aug 26, 2024

@rafal.kedziorski how did you overcome the missing bak files? did you execute any command to produce bak files? if yes could you please explain

thank you

post by rafal.kedziorski on Aug 26, 2024

I added the OrientDB backup task in my Nexus. I started it manualy and than I made a copy into the folder with the migration jar. And than the migration to H2 works.

I didn’t find any info in the migrations page how the BAK files should be created. Why?

post by rafal.kedziorski on Aug 26, 2024

Is this

Caused by: org.postgresql.util.PSQLException: FEHLER: Relation »batch_job_instance« existiert nicht

the reason for 3rd party? I do’t think so.

post by john.burrows on Aug 26, 2024

@rafal.kedziorski

We ran into issues with migration as well from OrientDB to PostgreSQL.

What we found was that the DB Migrator does not support Java 17, so we had to migrate the OrientDB to PostgreSQL using Java 11, then upgrade to Java 17 afterwards. So, you have to ensure that you are on Java 11 (or earlier) when migrating.

If this does not pertain to you, I apologize

John

post by rafal.kedziorski on Aug 28, 2024

Thx for the Info. I made a backup of my VM nbefore the Upgrade. I cat try this again.

But this shows how broken the procedure is.

post by d.remke on Apr 4, 2025

I have the same problem. When I start the migration from h2 to Postgresql with Java 11, I get the message:

Java Runtime only recognises class file versions up to 55.0

With java 17:

Caused by: org.postgresql.util.PSQLException: ERROR: Relation ‘batch_job_instance’ does not exist What can I do?

post by rseddon on Apr 4, 2025

The ‘batch_job_instance” error generally indicates the database user does not have the correct permissions. As documented in Migrating to a New Database, the DB user must be the owner of the DB:

CREATE USER <username> WITH PASSWORD '<password>';
CREATE DATABASE <db-name> WITH OWNER <username> ENCODING 'UTF8';

post by Martin_Tovmassian on Nov 10, 2025

Faced the same kind of error - i.e. Caused by: org.postgresql.util.PSQLException: ERROR: relation "batch_job_instance" does not exist - while migrating a Nexus v3.86.0-08 database from H2 to Postgresql with the utility nexus-db-migrator-3.86.0-08.jar.

This stackoverflow post gave me a really good hint. I was able to troubleshoot the migration by adding spring.batch.initialize-schema=ALWAYS into an application.properties placed in the same directory as the utility.