Can't migrate from OrientDB to PG

Sonatype Nexus Repository
1

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: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
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:776)
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:763)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:314)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1317)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1306)
at com.sonatype.nexus.db.migrator.MigratorApplication.main(MigratorApplication.java:117)
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
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:744)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:757)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:815)
at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.getJobInstance(JdbcJobInstanceDao.java:151)
at org.springframework.batch.core.repository.support.SimpleJobRepository.isJobInstanceExists(SimpleJobRepository.java:93)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:220)
at jdk.proxy2/jdk.proxy2.$Proxy55.isJobInstanceExists(Unknown Source)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:128)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:220)
at jdk.proxy2/jdk.proxy2.$Proxy55.isJobInstanceExists(Unknown Source)
at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.getNextJobParameters(JobLauncherApplicationRunner.java:206)
at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.execute(JobLauncherApplicationRunner.java:198)
at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.executeLocalJobs(JobLauncherApplicationRunner.java:173)
at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.launchJobFromProperties(JobLauncherApplicationRunner.java:160)
at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.run(JobLauncherApplicationRunner.java:155)
at com.sonatype.nexus.db.migrator.config.MigratorJobLauncherApplicationRunner.run(MigratorJobLauncherApplicationRunner.java:38)
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:773)
… 5 common frames omitted
Caused by: org.postgresql.util.PSQLException: FEHLER: Relation »batch_job_instance« existiert nicht
Position: 39
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:722)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)

error.

Looks like the tables are not created.

Best Regards,
Rafal

2

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

…

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

Best Regards,
Rafal

3

Now I try migrato 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
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:776)
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:763)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:314)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1317)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1306)
at com.sonatype.nexus.db.migrator.MigratorApplication.main(MigratorApplication.java:117)
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.

4

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.

5

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.

6

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

7

@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

8

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?

9

Is this

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

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

10

@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

11

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.