Skip to main content

Troubleshooting common errors for Database Migration from H2 to PostgreSQL

Updated

.

Problem 

This knowledge-base article outlines some common errors generated during the migration of H2 to PostgreSQL databases.

Symptoms 

ERROR: relation "batch_job_instance" does not exist

Executing the DB migrator command almost immediately fails with the following ERROR:

17:54:14 [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: ERROR: relation "batch_job_instance" does not exist
...

Possible cause:

The DB schema specified in the "currentSchema=" does not exist, so that the database user specified in the DB migrator Java command couldn't access or create this table.

Solution:

Please make sure the schema used in "currentSchema=" (or the public schema if the currentSchema is not used) is owned by the DB user specified in "user=". For example, if the following command is used:

java -jar nexus-db-migrator-*.jar --migration_type=h2_to_postgres --db_url="jdbc:postgresql://127.0.0.1:5432/nexus?user=nexus&password=nexus123&currentSchema=nexus"

As the DB username in the above example is "nexus", please make sure the schema "nexus" exists and also owned by "nexus". For example "\dn+" in the psql command shows this information:

nexus=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+--------+-------------------+-------------
nexus | nexus  | |
public | nexus | |
(2 rows)

If the schema does not exist, an example command to create the schema "nexus" for the DB user "nexus" is

CREATE SCHEMA nexus AUTHORIZATION nexus;

 

0 records were migrated

The DB migrator command completed without any error, but reporting 0 records were migrated

06:23:39 [main] INFO c.s.n.d.m.l.ProvidingJobInfoListener - Migration job finished at Wed Mar 05 06:23:39 GMT 2025
06:23:39 [main] INFO c.s.n.d.m.l.ProvidingJobInfoListener - Migration job took 1 seconds to execute
06:23:39 [main] INFO c.s.n.d.m.l.ProvidingJobInfoListener - 0 records were processed
06:23:39 [main] INFO c.s.n.d.m.l.ProvidingJobInfoListener - 0 records were filtered
06:23:39 [main] INFO c.s.n.d.m.l.ProvidingJobInfoListener - 0 records were skipped
06:23:39 [main] INFO c.s.n.d.m.l.ProvidingJobInfoListener - 0 records were migrated
06:23:39 [main] INFO c.s.n.d.m.l.ProvidingJobInfoListener - 114 tables are missing from the source database
06:23:39 [main] INFO c.s.n.d.m.l.ProvidingJobInfoListener - List of missing config tables:[GO_COMPONENT_TAG, NUGET_COMPONENT, GO_CONTENT_REPOSITORY, CONDA_COMPONENT, P_COMPONENT_TAG, API_KEY_V, COMPOSER_ASSET_BLOBASSET_BLOB_ID, DEPLOYMENT_ID, R_COMPONENT, MAVEN_COMPONENT_TAG, HELM_COMPONENT_TAG, HUGGINGFACE_COMPONENT_TAG, QRTZ_SIMPLE_TRIGGERSTRIGGER_GROUP, CONAN_ASSET_BLOBASSET_BLOB_ID, YUM_ASSET_BLOBASSET_BLOB_ID, MAVEN_ASSET_BLOBASSET_BLOB_ID, SAML_CONFIGURATION, COMPOSER_CONTENT_REPOSITORY, HELM_COMPONENT, GO_ASSET_BLOBASSET_BLOB_ID, soft_deleted_blobs, APT_CONTENT_REPOSITORY, RUBYGEMS_CONTENT_REPOSITORY, P_ASSET_BLOBASSET_BLOB_ID, NPM_COMPONENT, CAPABILITY_STORAGE_ITEM, ROLE, QRTZ_CALENDARS, API_KEY, ANONYMOUS_CONFIGURATION, HTTP_CLIENT_CONFIGURATION, CHANGE_BLOBSTORE, USER_ROLE_MAPPING, HUGGINGFACE_CONTENT_REPOSITORY, YUM_COMPONENT_TAG, APT_ASSET_BLOBASSET_BLOB_ID, DOCKER_ASSET_BLOBASSET_BLOB_ID, GITLFS_CONTENT_REPOSITORY, HUGGINGFACE_ASSET_BLOBASSET_BLOB_ID, COCOAPODS_CONTENT_REPOSITORY, NPM_CONTENT_REPOSITORY, NUGET_COMPONENT_TAG, COCOAPODS_ASSET_BLOBASSET_BLOB_ID, SELECTOR_CONFIGURATION, LDAP_CONFIGURATION, QRTZ_JOB_DETAILS, YUM_CONTENT_REPOSITORY, REPOSITORY, RAW_CONTENT_REPOSITORY, TAG, COMPOSER_COMPONENT, FIREWALL_IGNORE_PATTERNS, CLEANUP_POLICY, PRIVILEGE, CARGO_ASSET_BLOBASSET_BLOB_ID, USER_TOKEN, GO_COMPONENT, RAW_ASSET_BLOBASSET_BLOB_ID, NUGET_CONTENT_REPOSITORY, CARGO_COMPONENT, NPM_COMPONENT_TAG, DOCKER_COMPONENT, CONDA_COMPONENT_TAG, MAVEN_CONTENT_REPOSITORY, COCOAPODS_COMPONENT, REALM_CONFIGURATION, GITLFS_COMPONENT, APT_COMPONENT, HUGGINGFACE_COMPONENT, SECRETS, COMPOSER_COMPONENT_TAG, GITLFS_COMPONENT_TAG, COCOAPODS_COMPONENT_TAG, PYPI_ASSET_BLOBASSET_BLOB_ID, HELM_ASSET_BLOBASSET_BLOB_ID, NPM_ASSET_BLOBASSET_BLOB_ID, CONDA_CONTENT_REPOSITORY, BLOB_STORE_CONFIGURATION, SCRIPT, AZURE_DELETED_BLOB, DOCKER_CONTENT_REPOSITORY, CONDA_ASSET_BLOBASSET_BLOB_ID, REPOSITORY_ROUTING_RULE, EMAIL_CONFIGURATION, CARGO_COMPONENT_TAG, MAVEN_COMPONENT, DOCKER_COMPONENT_TAG, CONAN_CONTENT_REPOSITORY, PYPI_CONTENT_REPOSITORY, P_COMPONENT, GITLFS_ASSET_BLOBASSET_BLOB_ID, RAW_COMPONENT, APT_COMPONENT_TAG, CONAN_COMPONENT, R_CONTENT_REPOSITORY, P_CONTENT_REPOSITORY, RUBYGEMS_COMPONENT_TAG, PYPI_COMPONENT, R_ASSET_BLOBASSET_BLOB_ID, SECURITY_USER, RUBYGEMS_COMPONENT, KEY_STORE_DATA, SAML_USER, HELM_CONTENT_REPOSITORY, YUM_COMPONENT, NUGET_ASSET_BLOBASSET_BLOB_ID, R_COMPONENT_TAG, DOCKER_FOREIGN_LAYERS, CARGO_CONTENT_REPOSITORY, RAW_COMPONENT_TAG, PYPI_COMPONENT_TAG, RUBYGEMS_ASSET_BLOBASSET_BLOB_ID, RHC_CONFIGURATION, CONAN_COMPONENT_TAG]
...

Possible cause:

The DB migrator with "--migration_type=h2_to_postgres" reads the nexus.mv.db file from the current location. If this file does not exist, H2 database silently creates the nexus.mv.db file with 0 records, so that Nexus DB migrator says "0 records were migrated".

Solution:

Please review https://help.sonatype.com/en/migrating-to-a-new-database.html#migrating-from-h2-to-postgresql, which instructs the reader to execute the Database backup task (in case of some unexpected issue) and says "running this command from the $data-dir/db directory". The "$data-dir" directory is usually something like "[installed_dir]/sonatype-work/nexus3" (more details)

Related articles