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 to 3.71.x. But first, I have to migrate my OrientDB 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:13 [main] INFO c.s.n.d.migrator.MigratorApplication - ------------------------------------------------------------
12:02:13 [main] INFO c.s.n.d.migrator.MigratorApplication - Java version: Eclipse Adoptium 17.0.12
12:02:13 [main] INFO c.s.n.d.migrator.MigratorApplication - JVM arguments: -Xmx10G -Xms10G -XX:+UseG1GC -XX:MaxDirectMemorySize=12G --add-exports=java.base/sun.nio.ch=ALL-UNNAMED
12:02:13 [main] INFO c.s.n.d.migrator.MigratorApplication - Migrator arguments: --logging.level.com.sonatype=DEBUG --migration_type=postgres --db_url=jdbc:postgresql://xxx:5432/nexus?user=nexus&password=**** --content_migration=true --export_json=false --force=false
12:02:13 [main] INFO c.s.n.d.migrator.MigratorApplication - ------------------------------------------------------------
12:02:14 [main] INFO c.s.n.d.migrator.MigratorApplication - Starting MigratorApplication v3.71.0-01 using Java 17.0.12 on nexus with PID 2524 (/nexus/nexus-db-migrator-3.71.0-01.jar started by nexus in /nexus)
12:02:14 [main] DEBUG c.s.n.d.migrator.MigratorApplication - Running with Spring Boot v3.71.0-01, Spring v3.71.0-01
12:02:14 [main] INFO c.s.n.d.migrator.MigratorApplication - No active profile set, falling back to 1 default profile: "default"
12:02:17 [main] INFO o.s.b.c.r.s.JobRepositoryFactoryBean - No database type set, using meta data indicating: POSTGRES
12:02:17 [main] INFO c.s.n.d.migrator.MigratorApplication - Started MigratorApplication in 3.939 seconds (JVM running for 6.676)
12:02:17 [main] INFO o.s.b.a.b.JobLauncherApplicationRunner - Running default command line with: [--logging.level.com.sonatype=DEBUG, --migration_type=postgres, --db_url=jdbc:postgresql://192.168.178.30:5432/nexus?user=nexus&password=nexus, --content_migration=true, --export_json=false, --force=false]
12:02:17 [main] INFO o.s.b.a.l.ConditionEvaluationReportLoggingListener -
Error starting ApplicationContext. To display the conditions report re-run your application with ‘debug’ enabled.
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
Position: 39

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
Position: 39

Best Regards,

Rafal

post by rafal.kedziorski on Aug 25, 2024

Now I try to migrate to H2.

And I get this error:

12:33:11 [main] INFO c.s.n.d.m.validator.PathValidator - Validating Orient directory contains .bak files: /nexus
12:33:11 [main] INFO o.s.b.a.l.ConditionEvaluationReportLoggingListener -
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 something 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 don’t have 3rd Party-Plugins from the community installed. Because if you move to PG, all 3rd Party is disabled sadly, that’s 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 manually and then I made a copy into the folder with the migration jar. And then the migration to H2 works.

I didn’t find any info on the migrations page about 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 Position: 39 the reason for 3rd party? I don’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 before the Upgrade. I can 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 recognizes 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.