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 adapt_tables.lisp configuration files (as described below in the Configuration section).

Adapt the configuration file

• Change the database connection string in the adapt_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 adapt_tables.lisp configuration file to migrate the tables.

pgloader adapt_tables.lisp

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

Configuration

adapt_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 bigint to bigint,
    type smallint to boolean,
    type integer to integer,
    type timestamp to timestamp,
    column tacac_rememberme_token.valid_until to timestamp
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: nevisadapt
    $$ ALTER SCHEMA "<schema-user>" RENAME TO "<database-name>";$$
AFTER LOAD DO
    $$ ALTER SCHEMA "<database-name>" RENAME TO "<schema-user>";$$
;