Migration from PostGresSQL to Mysql Fails due "largeDataFiles" folder has more than 2400 files

Products

• CA API Developer Portal

Issue/Introduction

We tried to migrate PostgreSql database to Mysql 8.0 but the process did not finish successfully.
- Export Postgres data - OK
- Connect to Mysql and create databases - OK
- Import databases apim_otk_db,analytics,portal,rbac,tenant_provisioning. OK.
- Import Druid database - Shows Error
 
The process runs for 2 hours and do not finish, even ends the container and disconnects from SSH

Verified that inside the folder "largeDataFiles" has more than 2400 files.

This is a production environment and the "Druid" base is large compared to the others.

Is there any way to force a "PURGE" of this statistics data, without having problems in the portal?

 

Environment

Release : 5.0.2

Cause

importing and reading many files from "largeDatafiles" folder. Due druid folder has too many records is taking longer than expected then the connection is terminated.

Resolution

Use the following steps to truncate druid and remove volumes before migration

1. Get the container ID of primary Postgres db

# docker ps | grep portaldb

output example :
f95982431e36        apim-portal.packages.ca.com/apim-portal/postgres:4.4    "/usr/local/bin/entr…"   14 minutes ago      Up 14 minutes (healthy)   5432/tcp       portal_portaldb-slave.1.(edited)
c972675778e1        apim-portal.packages.ca.com/apim-portal/postgres:4.4   "/usr/local/bin/entr…"   14 minutes ago      Up 14 minutes (healthy)   5432/tcp      portal_portaldb.1.(edited)

2. Access the druid container using the container ide obtained on previous step :
# docker exec -it c972675778e1 /bin/sh

3. Get PostgreSQL ID/PW

/ $ env | grep POSTGRES
POSTGRES_DATABASES=portal,tenant_provisioning,rbac,apim_otk_db,portal,druid,integration_core,integration_runscope,analytics
POSTGRES_REPLICATION_PASSWORD=<db_repl_password>
POSTGRES_REPLICATION_USER=<db_repl_user>
POSTGRES_PASSWORD=<db_password>
POSTGRES_REPLICATION_MODE=master
POSTGRES_USER=<db_user>

4. Access PostgresSQL database

/ $ psql -U <db_user>
Password for user admin: <db_password>
psql (9.6.10)
Type "help" for help.

 the select statement will display the size of each table to  help identify what we are dealing with .

admin=# SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024 AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;

5. Connect to druid Database
\c druid

to list the tables inside druid db :

\d 

6. Then run the Truncate Commands on the following tables :

druid-# truncate druid_tasklocks;
druid-# truncate druid_tasklogs;
druid-# truncate druid_tasks;
druid-# truncate druid_audit;
druid-# truncate druid_config;
druid-# truncate druid_dataSource;
druid-# truncate druid_pendingSegments;
druid-# truncate druid_rules;
druid-# truncate druid_segments;
druid-# truncate druid_supervisors; (edited)

to Close the plsql session
\q
exit

7. To clean docker volumes:
docker volume rm portal_historical-volume
docker volume rm portal_kafka-volume
docker volume rm portal_minio-volume
docker volume rm portal_zk-volume (edited)

8. When running the migration again will see there is count=0 for Druid stuff

 

because the files were reduced also to the minimum size on largeDataFiles folder too, it will allow the migration process to finish without issue.