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
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 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.
Shut down the server
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 fileexport PGCLIENTENCODING=UTF8
Import the compressed file into the PostgreSQL database using the
psql
client toolgunzip -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.
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.