Migrating IQ Server from H2 to PostgreSQL

The H2 database embedded within the IQ Server is single-threaded suitable for small instances. For production instances with databases over 10GB, we recommend migrating to an external PostgreSQL database.

These steps are applicable for all IQ server deployments covering: Lifecycle, Repository Firewall, Developer, and SBOM Manager instances.

The migration consists of the following steps:

Export the data from the embedded database into an SQL dump
Import the SQL dump into the PostgreSQL database

See External Database Configuration for details on connecting to PostgreSQL

Prepare for the migration

A PostgreSQL server may host multiple databases. For example, you may use the same PostgreSQL server for both your IQ server as well as your Nexus Repository instance.

Ensure sufficient free disk space

The migration process involves exporting the data from the H2 database as SQL dump files and importing these files into your PostgreSQL instance. The sonatypeWork directory is used to temporarily store the files generated during the data export, tripling the required disk space. Check the size of the database and available space before attempting to migrate.

You will find the IQ server's H2 database in the following location:

sonatype-work/clm-server/data/ods.h2.db

Plan for the SQL dump to be roughly half the size of your current database.

Adding the PostgreSQL server user and database for the IQ server

The PostgreSQL server administrator should prepare the specific user and database used by the IQ server in the PostgreSQL server before beginning the migration.

The following example creates a user and database in PostgreSQL. These properties are used for the external database configuration as detailed in the table below.

CREATE USER sonatypeiq WITH PASSWORD 'super-secret-password';
CREATE DATABASE sonatypeiq WITH OWNER sonatypeiq ENCODING 'UTF8';

Postgres Property	IQ Server Variables
`USER`	`DATABASE_USERNAME`
`PASSWORD`	`DATABASE_PASSWORD`
`DATABASE`	`DATABASE_NAME`
`OWNER`	`DATABASE_USERNAME`

See External Database Configuration for details on connecting to PostgreSQL

Perform a test run of the migration

Migrating the IQ server database from H2 to PostgreSQL is an I/O-intensive task where the export may take a notable amount of time depending on the database's size and storage performance.

We recommend performing a test run of the migration using a backup. The I/O performance of the storage holding the server's embedded database is a key factor for the duration of the data export.

sonatype-work/clm-server/data

A dataset of ~10 GB roughly takes about ~15 minutes to export and another ~15 minutes to import into PostgreSQL.

Performing the migration

The commands below assume that the java and psql executables are included in your shell's search path. The java executable must meet the system requirements. You may restart the server using the embedded database if you encounter an error with the import.

Shut down the IQ server
Note
For Docker or Kubernetes deployments, use the IQ Server installer .tgz package to access the server JAR for the export command. After extracting the archive, run the command with the versioned JAR name, for example nexus-iq-server-*.jar.
Generate the migration file of the embedded database
```
java -jar nexus-iq-server-*.jar export-embedded-db --dump-file iq-db-dump.sql.gz config.yml
```
The ".gz" filename uses gzip compression as a plain text dump require significantly more storage.
Set the following environment variable to ensure psql can read the file
```
export PGCLIENTENCODING=UTF8
```
Make sure the Postgres user account has session_replication_role before proceeding to the Postgres import.
```
GRANT SET ON PARAMETER "session_replication_role" TO --username; 
```
Import the compressed file into the PostgreSQL database using the psql client tool
```
gunzip -c iq-db-dump.sql.gz | psql --set ON_ERROR_STOP=1 --host [db-server-host] --port [db-server-port] --username [db-owner-user] --dbname [db-name]
```
The import will be aborted in case of an error. The psql tool will display progress notices and log messages.
Note
On Windows systems where gunzip is not available, first unzip the iq-db-dump.sql.gz file. Then import the extracted .sql file using psql:
```
psql -U sonatypeiq -d sonatypeiq \i 'C:/Nexus/iq-db-dump.sql'
```
After successful import, revoke the Postgres user's session_replication_role permission.
```
REVOKE SET ON PARAMETER "session_replication_role" FROM --username;
```
Update the configuration to use the external database and restart the IQ server
See External Database Configuration for details on connecting to PostgreSQL