Migrating from MariaDB to PostgreSQL

Preparation

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

• A freshly deployed nevisIDM 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 nevisAuth service.
2. Create auth_tables.lisp configuration files (as described below in the Configuration section).

Adapt the configuration file

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

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

pgloader auth_tables.lisp

4. Change the database connection string in the esauth4.xml file to point to the PostgreSQL database.
5. Restart the nevisAuth service.

Configuration

auth_tables.lisp

The following configuration file is used to map the nevisAuth 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 nevisAuth database.

Table migration file

LOAD DATABASE
FROM mysql://schema-user:schema-secret@localhost:3306/nss
INTO postgresql://schema-user:schema-secret@localhost:5432/nss
WITH
    data only,
    truncate,
    disable triggers

excluding table names matching
    ~<flyway_schema_history>

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

Different databases

In case if the Session and OOCD tables are located in different databases the script must be run for both databases separately.