ConnectALL : Step By Step guide to migrate ConnectALL database to Postgres from other database flavours(MySQL, MSSQL, Oracle)

Products

• ConnectAll On-Prem
• ConnectALL

Issue/Introduction

This document covers the steps to run the ConnectALL DB migration utility to migrate from MySQL/Oracle/MSSQL database to Postgres database.

Environment

3.7+

Resolution

Scope:

The scope of the document is considering following assumptions.

 

1. ConnectALL is installed on a LINUX/Windows server
2. ConnectALL is running with Source database under /opt/ConnectALL
3. ConnectALL version is greater than or equal to 3.7.0

 

 

Glossary:

 

Server1 - This is referred to the ConnectALL application running with MySQL/Oracle/MSSQL database.

Server2 - This is referred to the ConnectALL application running with Postgres.

Source Database - Any of the database(MySQL, Oracle or MSSQL) flavour being used in existing ConnectALL installation.

 

Run Pre-Requisites:

1. Upgrade the existing instance(say, Server1) of ConnectALL to the latest release.
2. Prepare a new machine(say, Server2) and install same latest release of ConnectALL pointing to Postgres database.
3. Install Java21 or later on Server1 from https://adoptium.net/en-GB/ (From ConnectALL 3.8 version JDK is upgrade to Java 21 so you can use that as well).
4. Make sure CONNECTALL_HOME environment variable is set on Server1. Eg - export CONNECTALL_HOME=/opt/ConnectALL/Core
5. Download and extract Migrator.zip on Server1.
6. Make sure you have database username/passwords for both Source and Postgres database.
7. Source database and Postgres JDBC jars will be needed. 
8. Drop any backup tables from Source database schema.

 

Run Steps:

 

1. Stop ConnectALL services on both Server1 and Server2. Disable if needed.
2. Update config file in Migrator utility ..\migrator\config\connectall.properties with both source and Postgres database details. See sample below.

spring.datasource.srcdb.url=jdbc:mysql://localhost:3306/<source_databasename>?allowPublicKeyRetrieval=true&useSSL=false

spring.datasource.srcdb.username=<username>

spring.datasource.srcdb.password=<Password>

spring.datasource.postgres.url=jdbc:postgresql://localhost:5432/<postgres_databasename>

spring.datasource.postgres.username=<postgres_username>

spring.datasource.postgres.password=<postgresDB_password>

spring.datasource.jpa.postgres.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

1. Copy JDBC jars for both MySQL and Postgres databases to ..\migrator\lib\
2. Verify CONNECTALL_HOME is set.
3. Open Command Prompt and run below Command from ..\migrator\ directory. Please note that if you have spaces in path, run the command in double quotes.

cd ..\migrator\

C:\Adoptium\jdk-21.0.6.7-hotspot\bin\java -Dupdate.connectall.properties=Y -cp migrator-xxxx.jar org.springframework.boot.loader.launch.PropertiesLauncher

6. Check if migration_report.html is generated in ..\migrator\  directory which can be used for validation.
7. Check if the postgres.jar is copied to lib directory if each module.

..\ConnectALL\Core\lib\

..\ConnectALL\UI\tomcat\lib\

..\ConnectALL\Scheduler\lib\

..\ConnectALL\Orphan\lib\

6. Start UI service and verify Database Settings screen.
7. Run scripts below to advance sequences.

SELECT setval('adapter_field_type_mapping_seq', COALESCE((SELECT MAX(id) FROM adapter_field_type_mapping), 0) + 1, false);

SELECT setval('api_key_id_seq', COALESCE((SELECT MAX(id) FROM api_keys), 0) + 1, false);

SELECT setval('business_script_associations_seq', COALESCE((SELECT MAX(id) FROM business_script_associations), 0) + 1, false);

SELECT setval('business_scripts_seq', COALESCE((SELECT MAX(id) FROM business_scripts), 0) + 1, false);

SELECT setval('business_script_versions_seq', COALESCE((SELECT MAX(id) FROM business_script_versions), 0) + 1, false);

SELECT setval('ca_upgrade_history_seq', COALESCE((SELECT MAX(upgrade_history) FROM ca_upgrade_history), 0) + 1, false);

SELECT setval('fav_conn_advanced_properties_seq', COALESCE((SELECT MAX(adv_property_id) FROM fav_conn_advanced_properties), 0) + 1, false);

SELECT setval('field_types_seq', COALESCE((SELECT MAX(type_id) FROM field_types), 0) + 1, false);

SELECT setval('team_associations_seq', COALESCE((SELECT MAX(id) FROM team_associations), 0) + 1, false);

SELECT setval('teams_seq', COALESCE((SELECT MAX(id) FROM teams), 0) + 1, false);

 

 

Additional Checks:

1. Check if existing connections work.
2. Check if you can save the modifications in your automations.
3. Check if the sync works fine.
4. Check and update id needed ..\ConnectALL\.install4jesponse.varfile which may cause issues with future upgrades since it is pointing to MYSQL database.

Additional Information

It is recommended to carry out the migration activity under supervision of ConnectALL Support Engineering. Please reach out to Support for planning migration and downloading latest Migration Utility.