Migrating Portal Data from PostgreSQL to MySQL
As part of the installation on Docker Swarm option, the internal database for the API Portal can be created using either PostgreSQL or MySQL. With the deprecation of support for PostgreSQL, the
Layer7 Portal Migration Utilityis a tool you can use to migrate data from PostgreSQL to MySQL. This topic describes the necessary steps for you to perform a successful migration.
Download the Portal Migration Utility
The Portal Migration Utility is available as a Docker image in the Layer7 Docker Hub.
The Portal Migration Utility does not cover data migration for Portal integrations with Runscope API monitoring. If your Portal implementation has a Runscope integration, recreate your integration settings for API monitoring after completing the migration. For more information, see Enable Integrations.
Supported Portal Versions
The scripts included within the Migration Utility are designed for specific releases of API Portal. The export and import scripts are intended to migrate data from and to a Portal of the same version. This Migration Utility supports the migration of API Portal version 4.5 and 5.0. You also have the option to migrate directly to MySQL 8 in Portal 5.0. PostgreSQL will not be supported as a database for versions released after 5.0.
Common Migration Scenarios
If You Are On This Version of Portal with PostgreSQL...
Upgrade to Portal 4.5 or 5.0 first, and then perform the migration to MySQL with the Migration Utility (version 4.5.cr1).
Perform the migration to MySQL with the Migration Utility (version 4.5.cr1).
Prepare for the Migration
Prior to starting the migration, ensure that you have performed and/or checked the following:
MySQL Database Setup
Prior to creating a new Docker network or working with your existing PostgreSQL database, ensure that your new MySQL database is set up:
- Create a new MySQL user name and password.
- Allow remote access to the external MySQL database by following the instructions in Configure the External Database.
- Ensure that port 3306 is open.
- Do NOT create a database schema for the database as the Migration Utility will create the required schemas.
Perform the Migration
The process of migrating your PostgreSQL data to MySQL follows these high-level steps:
We recommend that you use a backup of your production PostgreSQL database and run through the migration utility first to familiarize yourself with the process and ensure that there are no data issues.
Expose PostgreSQL Port
Expose the PostgreSQL port so that the migration utility can connect to the database.
From the Docker container where the PostgreSQL database is running, run the following command to open port 5432:
docker service update --publish-add "5432:5432" portal_portaldb;
Create and Connect to a New Docker Network
Create a new Docker Network to run the Portal Migration Utility from. Once connected in the same network, the utility can communicate with the PostgreSQL master container:
- Run the following Docker command:docker network create <new network>For example, if your new network is named 'migration-net', then enter:docker network create migration-net
- Get the PostgreSQL server’s containerID by running the following Docker command:docker ps | grep postgresDocker returns a list that should appear similar to the following example output:CONTAINER ID IMAGE COMMAND CREATED STATUS e19627125bd2 caapim/postgres:4.5 "/usr/local/bin/entr…" 2 minutes ago Up About a minute (healthy)
- From the PostgresSQL server container, connect the Postgres master to this new Docker network. This allows the PostgresSQL port to be accessible from the network:docker network connect migrationdb <new network> <Postgres master container ID>Continuing with our example, we enter:docker network connect --alias migrationdb migration-net e19627125bd2
Run the Portal Migration Utility
The Migration Utility is built as a Docker container and designed to connect to each SQL database instance (PostgreSQL and MySQL), export the data from PostgreSQL as JSON files and import them into the MySQL database. Once the Docker container is started, it will prompt for credentials for both PostgreSQL and MySQL instances and perform the necessary steps to:
- Connect to the PostgreSQL database
- Export the PostgreSQL data as JSON files
- Connect to the MySQL database and create the schema
- Read the exported JSON files and import the data into MySQL database
The Portal Migration Utility generates two main folders:
mysql. The postgres folder contains folders for all database schemas exported from the PostgreSQL server container: analytics, druid, largeFiles, otk, rbac, source (portal db) and tenant. Each folder contains JSON files with data for all tables defined in the schema.
The mysql folder contains folders for all database schemas that are imported into the MySQL server: analytics, druid, largeFiles, otk, rbac, source (portal db) and tenant. Each folder contains JSON files with data for all tables.
The JSON files in the folders are useful for troubleshooting data migration discrepancies by comparing the equivalent table files from the postgres and mysql folders using a file Diff tool.
Step 1: Run the Utility from Docker
In the following command example, <local directory> indicates a directory on the Docker host where database export files are to be stored. The Docker host should have sufficient disk space to store all the JSON files generated by the export scripts:
docker run --rm -it -v <local directory>:/opt/store --network=<new network> caapim/apim-portal-migration:<tag>
Ensure that you are pulling the right migration utility image from Docker.
Step 2: Provide PostgresSQL and MySQL Connection Details
Once the Docker container is up and running, you will be prompted for the running source PostgreSQL database server information connection credentials, followed by another prompt for destination MySQL database connection credentials. If your PostgreSQL database uses the default credentials, press the Enter key for all the PostgreSQL prompts.
The following example lists the prompts for database information:
Please enter database connection settings PostgreSQL source database PostgreSQL hostname [default: portaldb]: 10.175.244.96 PostgreSQL port [default: 5432]: 5432 PostgreSQL username [default: admin]: admin PostgreSQL password [default: 7layer]: 7layer Please enter database connection settings MySQL target database MySQL hostname [default: 127.0.0.1]: 10.175.247.97 MySQL port [default: 3306]: 3306 MySQL username [default: root]: root MySQL password [default: password]: 7layer MySQLDatabase requires SSL[y/N]:N
SSL support for MySQL is only available to and supported by the Portal from version 5.0 and onwards. If you are using the Portal 4.5 Migration Utility, enter “N” for the 'MySQLDatabase requires SSL' setting.
After receiving the database connection details, the Migration Utility exports data from the PostgreSQL database to multiple JSON files in the provided <local directory> and then imports it to the MySQL database.
Verify Migration Results
When the data migration is complete, the migration utility shows a completion prompt at the end of the migration process. Verify the
exportRecordCount.logto see if there are any errors. Run the following command to verify if there are any errors:
cd <local directory> grep -iRn “error count” *.log
The result should display error count as ‘0’ for all the schemas, as shown in the following sample log output:
importAssets.log:6: "error count": 0, importAssets.log:24: "error count": 0, importAssets.log:38: "error count": 0, importAssets.log:52: "error count": 0,
Next, using a MySQL client, connect to the MySQL Database and verify all required schemas have been created and data have been migrated, including:
- analytics (if analytics are enabled)
And finally, verify that all data have been migrated to the corresponding MySQL tables per the migration log.
If the log shows an error count greater than zero, contact Broadcom Layer7 Support.
Test and Run Your API Portal with the Migrated Data
After verifying there are no errors in the migration, proceed to change the Portal configuration to connect to the new MySQL database. To connect the API Portal to the MySQL database, follow these steps:
- Edit theportal.conffile and add the following information:PORTAL_DATABASE_TYPE=mysql PORTAL_DATABASE_HOST=<MySQL database host name> PORTAL_DATABASE_PORT=<MySQL database port> PORTAL_DATABASE_USERNAME=<username> PORTAL_DATABASE_PASSWORD=<password>It's strongly recommended that you create a backup copy of theportal.conffile prior to making changes to it.
- Stop the Portal by running the following command:
Make sure all containers have been stopped.docker stack rm portal
- Restart the Portal by running the following command:sudo ./portal.sh
- Make sure all containers are running successfully by running the following command:sudo ./status.sh
- Verify that you are able to connect and log in to your API Portal.
- To cleanup, remove the new Docker network created in “Step 1: Run the Utility from Docker” using the following command:docker network rm <new network>
If you experience difficulty connecting API Portal to the new MySQL database, revert the changes made to the portal.conf file, ensuring that the Portal is still connecting to the PostgreSQL database. Contact Broadcom Layer7 Support with a description of your issue and migration utility logs.
Migrating Your Production Environment
Data migration in your production environment could involve various steps depending on your organization’s change management & DevOps processes. Per your upgrade procedures, ensure no inbound traffic is routed to API Portal during the migration process. This could involve disabling the load balancer, disable firewall rules or DNS entry.
The following are high-level steps that are recommended for the migration:
- Disable incoming traffic to API Portal per your upgrade process.
- Backup your PostgreSQL database.
- Follow the steps outlined in the Perform the Migration section.