# 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:

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

See [External Database Configuration](https://help.sonatype.com/en/external-database-configuration.html "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` |

## 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.

1. 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`.

2. 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.

3. Set the following environment variable to ensure `psql` can read the file

```
export PGCLIENTENCODING=UTF8
```

4. 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;
```

5. 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'
```

6. After successful import, revoke the Postgres user's `session_replication_role` permission.

```
REVOKE SET ON PARAMETER "session_replication_role" FROM --username;
```

7. Update the configuration to use the external database and restart the IQ server

See [External Database Configuration](https://help.sonatype.com/en/external-database-configuration.html "External Database Configuration") for details on connecting to PostgreSQL.