Skip to main content

Data Migration to PostgreSQL

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

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 for details on connecting to PostgreSQL.

Prepare for the migration

A PostgreSQL server can host multiple databases. The specific user and database need to be prepared by a database administrator ahead of the migration.

The following example creates a user and database in PostgreSQL.

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

Perform a test run of the migration

The migration is an I/O intensive task where the export may take a notable amount of time depending on the size of the database and performance of the storage.

We recommend performing a test run of the migration in a staging environment 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 5 - 15 minutes to export and another 5 - 15 minutes to import into PostgreSQL.

Ensure Sufficient Free Disk Space

The sonatypeWork directory temporary stores files generated during the data export, doubling the required disk spaced needed. Check the size of the database and available space before attempting to migrate.

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

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

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.

  1. Shut down the server

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

  5. Update the config.yml to use the external database and restart the server

Note

You may restart the server using the embedded database if you encounter an error with the import.