Migrating from MariaDB to PostgreSQL

danger

This guide contains a possible free migration solution. It can be used to be the base of your migration script, but it is not guaranteed to work in all cases, since we only tested in our test environment. Nevis can not guarantee that it does not cause data loss or corruption. Verification of all data's successful migration is the responsibility of the customer.

Preparation

To migrate the MariaDB database to PostgreSQL, you need to prepare the following:

• A freshly deployed nevisAdapt database schema on PostgreSQL with the same schema version as the mariaDB. The contents of the schema will be removed during migration.
• Install the PostgreSQL repository RPM to be able to install the pgloader tool.
• An installed pgloader tool. You can install it by running the following command:

Install pgloader

sudo yum install pgloader

Migration

Steps

1. Stop the nevisAdapt service.
2. Create detect_tables.lisp configuration files (as described below in the Configuration section).

Adapt the configuration file

• Change the database connection string in the detect_tables.lisp to point to the MariaDB database.
• Change the schema name in the ALTER SCHEMA command to match the schema name of the nevisAdapt database.

3. Run the pgloader tool with the detect_tables.lisp configuration file to migrate the tables.

pgloader detect_tables.lisp

4. Change the database connection string in the nevisdetect.properties file to point to the PostgreSQL database.
5. Restart the nevisAdapt service.

Configuration

detect_tables.lisp

The following configuration file is used to map the nevisAdapt tables from MariaDB to PostgreSQL. The file is used by the pgloader tool to migrate the data. Changing values of the LOAD DATABASE and INTO commands is required to match the MariaDB and PostgreSQL connection strings respectively. Additionally, changing of ALTER SCHEMA is required to match the schema name of the nevisAdapt database.

Table migration file

LOAD DATABASE
FROM mysql://schema-user:schema-secret@mysqlhost:3306/namespace
INTO postgresql://schema-user:schema-secret@postgrehost:5432/namespace
cast
    type integer to integer,
    type bigint to bigint,
    type datetime to timestamp,
    type timestamp to timestamp,
    type decimal when (and (= 65 precision) (= 6 scale)) to "double precision" drop typemod,
    type double to "double precision",
    -- by default smallints are cast to booleans in postgre
    -- hibernate auditing tables use smallint as enum for revtype: 0-add, 1-modify, 2-delete
    column trdfc_action_aud.revtype to smallint,
    column trdfc_actionplugin_aud.revtype to smallint,
    column trdfc_case_aud.revtype to smallint,
    column trdfc_cleanup_statistics_aud.revtype to smallint,
    column trdfc_core_aud.revtype to smallint,
    column trdfc_featurecorrelator_aud.revtype to smallint,
    column trdfc_framework_aud.revtype to smallint,
    column trdfc_model_aud.revtype to smallint,
    column trdfc_model_cfg_aud.revtype to smallint,
    column trdfc_model_dataset_aud.revtype to smallint,
    column trdfc_model_riskscore_aud.revtype to smallint,
    column trdfc_normalize_cond_aud.revtype to smallint,
    column trdfc_persistency_aud.revtype to smallint,
    column trdfc_plugin_aud.revtype to smallint,
    column trdfc_pluginriskscore_aud.revtype to smallint,
    column trdfc_policy_aud.revtype to smallint,
    column trdfc_policyrange_aud.revtype to smallint,
    column trdfc_reqatrrcond_aud.revtype to smallint,
    column trdfc_reqprocesscfg_aud.revtype to smallint,
    column trdfc_rule_aud.revtype to smallint,
    column trdfc_tag_aud.revtype to smallint,
    column trdfc_user_aud.revtype to smallint,
    -- some integers became bigints to increase consistency across tables
    column trdfc_cleanup_statistics.generatedid to bigint,
    column trdfc_cleanup_statistics_aud.generatedid to bigint,
    column trdfc_cleanup_statistics_aud.rev to bigint
WITH
    data only,
    truncate,
    disable triggers

excluding table names matching
    -- flyway schema history table name
    ~<schema_version>

BEFORE LOAD DO
    -- this renaming is required because pgloader cannot make a difference of schema and database name
    -- default values:
    -- schema-user: public
    -- database-name: nevisdetect
    $$ ALTER SCHEMA "schema-user" RENAME TO "database-name";$$
AFTER LOAD DO
    $$ ALTER SCHEMA "database-name" RENAME TO "schema-user";$$
;