#MySQL to PostgreSQL Migration Guide

This guide outlines the process for migrating data from a MySQL database to PostgreSQL for the 5CRSE Payload CMS application.

#Prerequisites

• Node.js 18+ installed
• Access to the source MySQL database
• Running PostgreSQL database (using Docker)
• .env file configured with appropriate connection details

#Environment Setup

1. Copy the example environment file:

cp .env.example .env

2. Update the .env file with your actual MySQL connection details:

# MySQL Connection Details
MYSQL_HOST=your_mysql_host
MYSQL_USER=your_mysql_username
MYSQL_PASSWORD=your_mysql_password
MYSQL_DATABASE=aystate

#Installation

Install the required dependencies for the migration process:

node src/scripts/install-migration-deps.js

This will add the necessary packages:

• mysql2: MySQL client for Node.js
• pg: PostgreSQL client for Node.js
• dotenv: Environment variable management

#Migration Process

The migration is handled by the migrate-mysql-to-postgres.js script, which performs the following steps:

1. Connects to both MySQL and PostgreSQL databases
2. Extracts data from MySQL tables
3. Transforms the data to match the Payload CMS schema
4. Imports the transformed data into PostgreSQL
5. Closes all database connections

#Running the Migration

To run the migration process:

node src/scripts/migrate-mysql-to-postgres.js

The script will display detailed logs of the migration process, including:

• Connection status for both databases
• Records being migrated for each table
• Successful insertions or skipped duplicates
• Any errors encountered during the process

#Migration Order

The migration occurs in the following order to respect dependencies:

1. User Roles
2. Vehicles (including vehicle images)
3. Dining (including location and images)
4. Events (including location, images, and categories)

#Troubleshooting

#Connection Issues

If you encounter connection issues:

• Verify your MySQL credentials in the .env file
• Ensure the MySQL server is accessible from your environment
• Check that the PostgreSQL Docker container is running:

  docker ps | grep postgres
  

#Data Mapping Issues

If you encounter issues with data mapping:

• Check the table structure in MySQL to ensure it matches the expected schema
• Review the schema in PostgreSQL to ensure it's compatible with the mapped data
• Adjust the mapping functions in the migration script if necessary

#Running Out of Memory

For large databases, you might encounter memory issues. In this case:

• Consider running the migration in smaller batches
• Use pagination in your MySQL queries to limit the amount of data processed at once
• Increase the available memory for Node.js:

  export NODE_OPTIONS=--max-old-space-size=4096
  

#Post-Migration Verification

After completing the migration, verify that all data has been successfully migrated:

1. Check that all user roles are present in PostgreSQL
2. Verify vehicle data, including images
3. Confirm dining records are accessible in the Payload CMS admin interface
4. Test event data, ensuring relationships and images are correctly linked

#Support

If you encounter any issues during the migration process, please refer to:

• Payload CMS documentation: https://payloadcms.com/docs
• PostgreSQL documentation: https://www.postgresql.org/docs/
• MySQL documentation: https://dev.mysql.com/doc/

#Need Additional Help?

If you require additional assistance with the migration process, please contact the development team.

#License

This migration tool is part of the 5CRSE application and is subject to the same licensing terms as the main application.