Version: 8.2411.x.x RR

Migration from MariaDB database 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 nevisMeta database schema on PostgreSQL with the same schema version as the mariaDB. The contents of the schema will be removed during migration. For installation steps visit Database Preparing
An installed pgloader tool. You can install it by running the following command:

sudo apt-get install pgloader

Migration

Steps

Stop the nevisMeta service.
Create meta_tables.lisp configuration files (as described below in the Configuration section).
Configuration
Change the database connection string in the meta_tables.lisp files to point to the MariaDB database. Change the schema name in the ALTER SCHEMA command to match the schema name of the nevisMeta database.
Run the pgloader tool with the meta_tables.lisp configuration file to migrate the non-historical tables.

pgloader meta_tables.lisp

Change the database connection string in the nevismeta.properties file to point to the PostgreSQL database.
Restart the nevisMeta service.

Configuration

meta_tables.lisp

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

LOAD DATABASE
FROM mysql://root:root@localhost/nevismeta
INTO postgresql://umet01:umet01@localhost/nevismeta
cast
    type datetime to timestamp,
    type bigint when (= 20 precision) to bigint drop typemod,
    type bigint when unsigned to numeric drop typemod,
    column tmetc_client.ctl_tcn to integer,
    column tmetc_client_state.ctl_tcn to integer,
    column tmetc_client_state_meta_info.ctl_tcn to integer,
    column tmetc_client_state_response_types.ctl_tcn to integer,
    column tmetc_client_state_scope.ctl_tcn to integer,
    column tmetc_meta_info.ctl_tcn to integer,
    column tmetc_persisted_consent.ctl_tcn to integer,
    column tmetc_persisted_consent_scope.ctl_tcn to integer,
    column tmetc_refresh_token.ctl_tcn to integer,
    column tmetc_refresh_token_audience.ctl_tcn to integer,
    column tmetc_refresh_token_meta_info.ctl_tcn to integer,
    column tmetc_refresh_token_scope.ctl_tcn to integer,
    column tmetc_resource_server.ctl_tcn to integer,
    column tmetc_resource_server_state.ctl_tcn to integer,
    column tmetc_resource_server_state_meta_info.ctl_tcn to integer,
    column tmetc_scope.ctl_tcn to integer,
    column tmetc_scope_state.ctl_tcn to integer,
    column tmetc_scope_state_meta_info.ctl_tcn to integer,
    column tmetc_setup.ctl_tcn to integer,
    column tmetc_setup_state.ctl_tcn to integer,
    column tmetc_setup_state_meta_info.ctl_tcn to integer,
    column tmetc_setup_state.jwt_bearer_grant_allowed to boolean,
    column tmetc_setup_state.tmetc_setup_state to boolean,
    column tmetc_setup_state.force_authentication_scope_policy to boolean,
    column tmetc_setup_state.implicit_grant_allowed to boolean,
    column tmetc_setup_state.authorization_grant_allowed to boolean,
    column tmetc_setup_state.client_credential_grant_allowed to boolean,
    column tmetc_setup_state.refresh_token_allowed to boolean,
    column tmetc_setup_state.force_authentication to boolean,
    column tmetc_resource_server_state.deleted to boolean,
    column tmetc_client_state.deleted to boolean,
    column tmetc_client_state.require_auth_time to boolean,
    column tmetc_client_state.require_pushed_authorization_requests to boolean,
    column tmetc_client.id with extra auto_increment to serial
WITH
    data only,
    truncate, 
    create no tables,
    no foreign keys,
    include no drop

excluding table names matching 
    ~<flyway_schema_history>

BEFORE LOAD DO
    -- drop fk
    $$ ALTER TABLE umet01.tmetc_client DROP CONSTRAINT fk_client_setup_id; $$,
    $$ ALTER TABLE umet01.tmetc_client_state DROP CONSTRAINT fk_client_state_client_id; $$,
    $$ ALTER TABLE umet01.tmetc_client_state_meta_info DROP CONSTRAINT fk_client_state_meta_info_client_state_id; $$,
    $$ ALTER TABLE umet01.tmetc_client_state_meta_info DROP CONSTRAINT fk_client_state_meta_info_meta_info_id; $$,
    $$ ALTER TABLE umet01.tmetc_client_state_scope DROP CONSTRAINT fk_client_state_scope_client_state_id; $$,
    $$ ALTER TABLE umet01.tmetc_client_state_scope DROP CONSTRAINT fk_client_state_scope_scope_id; $$,
    $$ ALTER TABLE umet01.tmetc_client_state_response_types DROP CONSTRAINT fk_cs_response_types_client_state_id; $$,
    $$ ALTER TABLE umet01.tmetc_persisted_consent DROP CONSTRAINT fk_persisted_consent_client_id; $$,
    $$ ALTER TABLE umet01.tmetc_persisted_consent_scope DROP CONSTRAINT fk_ps_scope_persisted_consent_id; $$,
    $$ ALTER TABLE umet01.tmetc_persisted_consent_scope DROP CONSTRAINT fk_ps_scope_scope_id; $$,
    $$ ALTER TABLE umet01.tmetc_refresh_token DROP CONSTRAINT fk_refresh_token_client_id; $$,
    $$ ALTER TABLE umet01.tmetc_refresh_token_meta_info DROP CONSTRAINT fk_refresh_token_meta_info_meta_info_id; $$,
    $$ ALTER TABLE umet01.tmetc_refresh_token_meta_info DROP CONSTRAINT fk_refresh_token_meta_info_refresh_token_id; $$,
    $$ ALTER TABLE umet01.tmetc_resource_server DROP CONSTRAINT fk_resource_server_setup_id; $$,
    $$ ALTER TABLE umet01.tmetc_resource_server_state_meta_info DROP CONSTRAINT fk_resource_server_state_meta_info_meta_info_id; $$,
    $$ ALTER TABLE umet01.tmetc_resource_server_state_meta_info DROP CONSTRAINT fk_resource_server_state_meta_info_resource_server_state_id; $$,
    $$ ALTER TABLE umet01.tmetc_resource_server_state DROP CONSTRAINT fk_resource_server_state_resource_server_id; $$,
    $$ ALTER TABLE umet01.tmetc_refresh_token_audience DROP CONSTRAINT fk_rf_audience_refresh_token_id; $$,
    $$ ALTER TABLE umet01.tmetc_refresh_token_scope DROP CONSTRAINT fk_rf_scope_refresh_token_id; $$,
    $$ ALTER TABLE umet01.tmetc_refresh_token_scope DROP CONSTRAINT fk_rf_scope_scope_id; $$,
    $$ ALTER TABLE umet01.tmetc_scope DROP CONSTRAINT fk_scope_resource_server_id; $$,
    $$ ALTER TABLE umet01.tmetc_scope_state_meta_info DROP CONSTRAINT fk_scope_state_meta_info_meta_info_id; $$,
    $$ ALTER TABLE umet01.tmetc_scope_state_meta_info DROP CONSTRAINT fk_scope_state_meta_info_scope_id; $$,
    $$ ALTER TABLE umet01.tmetc_scope_state DROP CONSTRAINT fk_scope_state_resource_server_state_id; $$,
    $$ ALTER TABLE umet01.tmetc_scope_state DROP CONSTRAINT fk_scope_state_scope_id; $$,
    $$ ALTER TABLE umet01.tmetc_setup_state_meta_info DROP CONSTRAINT fk_setup_state_meta_info_meta_info_id; $$,
    $$ ALTER TABLE umet01.tmetc_setup_state_meta_info DROP CONSTRAINT fk_setup_state_meta_info_setup_state_id; $$,
    $$ ALTER TABLE umet01.tmetc_setup_state DROP CONSTRAINT fk_setup_state_setup_id; $$,
    -- renaming schema to the temporary name
    $$ ALTER SCHEMA "umet01" RENAME TO "nevismeta"; $$

AFTER LOAD DO
    -- renaming schema to the original name
    $$ ALTER SCHEMA "nevismeta" RENAME TO "umet01";$$,
    -- create fk
    $$ ALTER TABLE umet01.tmetc_client ADD CONSTRAINT fk_client_setup_id foreign key (setup_id) references umet01.tmetc_setup (id); $$,
    $$ ALTER TABLE umet01.tmetc_client_state ADD CONSTRAINT fk_client_state_client_id foreign key (client_id) references umet01.tmetc_client (id); $$,
    $$ ALTER TABLE umet01.tmetc_client_state_meta_info ADD CONSTRAINT fk_client_state_meta_info_client_state_id foreign key (client_state_id) references umet01.tmetc_client_state (id); $$,
    $$ ALTER TABLE umet01.tmetc_client_state_meta_info ADD CONSTRAINT fk_client_state_meta_info_meta_info_id foreign key (meta_info_id) references umet01.tmetc_meta_info (id); $$,
    $$ ALTER TABLE umet01.tmetc_client_state_scope ADD CONSTRAINT fk_client_state_scope_client_state_id foreign key (client_state_id) references umet01.tmetc_client_state (id); $$,
    $$ ALTER TABLE umet01.tmetc_client_state_scope ADD CONSTRAINT fk_client_state_scope_scope_id foreign key (scope_id) references umet01.tmetc_scope (id); $$,
    $$ ALTER TABLE umet01.tmetc_client_state_response_types ADD CONSTRAINT fk_cs_response_types_client_state_id foreign key (client_state_id) references umet01.tmetc_client_state (id); $$,
    $$ ALTER TABLE umet01.tmetc_persisted_consent ADD CONSTRAINT fk_persisted_consent_client_id foreign key (client_id) references umet01.tmetc_client (id); $$,
    $$ ALTER TABLE umet01.tmetc_persisted_consent_scope ADD CONSTRAINT fk_ps_scope_persisted_consent_id foreign key (persisted_consent_id) references umet01.tmetc_persisted_consent (id); $$,
    $$ ALTER TABLE umet01.tmetc_persisted_consent_scope ADD CONSTRAINT fk_ps_scope_scope_id foreign key (scope_id) references umet01.tmetc_scope (id); $$,
    $$ ALTER TABLE umet01.tmetc_refresh_token ADD CONSTRAINT fk_refresh_token_client_id foreign key (client_id) references umet01.tmetc_client (id); $$,
    $$ ALTER TABLE umet01.tmetc_refresh_token_meta_info ADD CONSTRAINT fk_refresh_token_meta_info_meta_info_id foreign key (meta_info_id) references umet01.tmetc_meta_info (id); $$,
    $$ ALTER TABLE umet01.tmetc_refresh_token_meta_info ADD CONSTRAINT fk_refresh_token_meta_info_refresh_token_id foreign key (refresh_token_id) references umet01.tmetc_refresh_token (id); $$,
    $$ ALTER TABLE umet01.tmetc_resource_server ADD CONSTRAINT fk_resource_server_setup_id foreign key (setup_id) references umet01.tmetc_setup (id); $$,
    $$ ALTER TABLE umet01.tmetc_resource_server_state_meta_info ADD CONSTRAINT fk_resource_server_state_meta_info_meta_info_id foreign key (meta_info_id) references umet01.tmetc_meta_info (id); $$,
    $$ ALTER TABLE umet01.tmetc_resource_server_state_meta_info ADD CONSTRAINT fk_resource_server_state_meta_info_resource_server_state_id foreign key (resource_server_state_id) references umet01.tmetc_resource_server_state (id); $$,
    $$ ALTER TABLE umet01.tmetc_resource_server_state ADD CONSTRAINT fk_resource_server_state_resource_server_id foreign key (resource_server_id) references umet01.tmetc_resource_server (id); $$,
    $$ ALTER TABLE umet01.tmetc_refresh_token_audience ADD CONSTRAINT fk_rf_audience_refresh_token_id foreign key (refresh_token_id) references umet01.tmetc_refresh_token (id); $$,
    $$ ALTER TABLE umet01.tmetc_refresh_token_scope ADD CONSTRAINT fk_rf_scope_refresh_token_id foreign key (refresh_token_id) references umet01.tmetc_refresh_token (id); $$,
    $$ ALTER TABLE umet01.tmetc_refresh_token_scope ADD CONSTRAINT fk_rf_scope_scope_id foreign key (scope_id) references umet01.tmetc_scope (id); $$,
    $$ ALTER TABLE umet01.tmetc_scope ADD CONSTRAINT fk_scope_resource_server_id foreign key (resource_server_id) references umet01.tmetc_resource_server (id); $$,
    $$ ALTER TABLE umet01.tmetc_scope_state_meta_info ADD CONSTRAINT fk_scope_state_meta_info_meta_info_id foreign key (meta_info_id) references umet01.tmetc_meta_info (id); $$,
    $$ ALTER TABLE umet01.tmetc_scope_state_meta_info ADD CONSTRAINT fk_scope_state_meta_info_scope_id foreign key (scope_state_id) references umet01.tmetc_scope_state (id); $$,
    $$ ALTER TABLE umet01.tmetc_scope_state ADD CONSTRAINT fk_scope_state_resource_server_state_id foreign key (resource_server_state_id) references umet01.tmetc_resource_server_state (id); $$,
    $$ ALTER TABLE umet01.tmetc_scope_state ADD CONSTRAINT fk_scope_state_scope_id foreign key (scope_id) references umet01.tmetc_scope (id); $$,
    $$ ALTER TABLE umet01.tmetc_setup_state_meta_info ADD CONSTRAINT fk_setup_state_meta_info_meta_info_id foreign key (meta_info_id) references umet01.tmetc_meta_info (id); $$,
    $$ ALTER TABLE umet01.tmetc_setup_state_meta_info ADD CONSTRAINT fk_setup_state_meta_info_setup_state_id foreign key (setup_state_id) references umet01.tmetc_setup_state (id); $$,
    $$ ALTER TABLE umet01.tmetc_setup_state ADD CONSTRAINT fk_setup_state_setup_id foreign key (setup_id) references umet01.tmetc_setup (id); $$,
    -- set sequence value to the latest version
    $$ SELECT setval(pg_get_serial_sequence('umet01.tmetc_client', 'id'), COALESCE((SELECT MAX(id)+1 FROM umet01.tmetc_client), 1), false); $$,
    $$ SELECT setval(pg_get_serial_sequence('umet01.tmetc_client_state', 'id'), COALESCE((SELECT MAX(id)+1 FROM umet01.tmetc_client_state), 1), false); $$,
    $$ SELECT setval(pg_get_serial_sequence('umet01.tmetc_meta_info', 'id'), COALESCE((SELECT MAX(id)+1 FROM umet01.tmetc_meta_info), 1), false); $$,
    $$ SELECT setval(pg_get_serial_sequence('umet01.tmetc_persisted_consent', 'id'), COALESCE((SELECT MAX(id)+1 FROM umet01.tmetc_persisted_consent), 1), false); $$,
    $$ SELECT setval(pg_get_serial_sequence('umet01.tmetc_refresh_token', 'id'), COALESCE((SELECT MAX(id)+1 FROM umet01.tmetc_refresh_token), 1), false); $$,
    $$ SELECT setval(pg_get_serial_sequence('umet01.tmetc_resource_server', 'id'), COALESCE((SELECT MAX(id)+1 FROM umet01.tmetc_resource_server), 1), false); $$,
    $$ SELECT setval(pg_get_serial_sequence('umet01.tmetc_resource_server_state', 'id'), COALESCE((SELECT MAX(id)+1 FROM umet01.tmetc_resource_server_state), 1), false); $$,
    $$ SELECT setval(pg_get_serial_sequence('umet01.tmetc_scope', 'id'), COALESCE((SELECT MAX(id)+1 FROM umet01.tmetc_scope), 1), false); $$,
    $$ SELECT setval(pg_get_serial_sequence('umet01.tmetc_scope_state', 'id'), COALESCE((SELECT MAX(id)+1 FROM umet01.tmetc_scope_state), 1), false); $$,
    $$ SELECT setval(pg_get_serial_sequence('umet01.tmetc_setup', 'id'), COALESCE((SELECT MAX(id)+1 FROM umet01.tmetc_setup), 1), false); $$,
    $$ SELECT setval(pg_get_serial_sequence('umet01.tmetc_setup_state', 'id'), COALESCE((SELECT MAX(id)+1 FROM umet01.tmetc_setup_state), 1), false); $$;

Migration from MariaDB database To PostgreSQL

Preparation​

Migration​

Steps​

Configuration​

meta_tables.lisp​

Preparation

Migration

Steps

Configuration

meta_tables.lisp