How to load data from Postgres to MySQL Destination

1. Access PostgreSQL Command Line: Access the PostgreSQL command line interface (psql) by logging into the PostgreSQL server.

```bash

psql -U username -d database_name

```

2. List Tables: List the tables in your PostgreSQL database to identify which ones you want to export.

```sql

\dt

```

3. Export Table Structure: Use the `pg_dump` utility to export the table structure (schema) without data. This will help you create equivalent tables in MySQL.

```bash

pg_dump -U username -s -t table_name database_name > table_name_schema.sql

```

4. Export Table Data: Export the data from the PostgreSQL table using the `COPY` command to a CSV file.

```sql

COPY table_name TO '/path/to/output/table_name_data.csv' DELIMITER ',' CSV HEADER;

```

5. Repeat: Repeat steps 3 and 4 for each table you wish to export.

1. Edit Schema File: Open the `table_name_schema.sql` file(s) in a text editor and make necessary changes to the SQL syntax to be compatible with MySQL. This may include modifying data types, index definitions, and removing PostgreSQL-specific items.

2. Prepare Data Files: Check the CSV files for any data that might not be compatible with MySQL. This includes verifying the date formats, escaping characters, and ensuring the character encoding is supported by MySQL.

1. Access MySQL Command Line: Log into the MySQL server using the command line.

```bash

mysql -u username -p

```

2. Create Database: Create a new MySQL database to hold the imported data.

```sql

CREATE DATABASE new_database_name;

USE new_database_name;

```

3. Create Tables: Execute the modified schema SQL script(s) to create the tables in the new MySQL database.

```bash

source /path/to/table_name_schema.sql

```

4. Verify Structure: Verify that the tables were created correctly in MySQL.

```sql

SHOW TABLES;

DESCRIBE table_name;

```

1. Disable Constraints: Temporarily disable foreign key checks to avoid constraint violations during import.

```sql

SET FOREIGN_KEY_CHECKS=0;

```

2. Import Data: Use the `LOAD DATA INFILE` command to import the CSV file(s) into the corresponding MySQL tables.

```sql

LOAD DATA INFILE '/path/to/output/table_name_data.csv' INTO TABLE table_name

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 LINES;

```

3. Re-enable Constraints: Re-enable foreign key checks after the import is complete.

```sql

SET FOREIGN_KEY_CHECKS=1;

```

4. Verify Data: Verify that the data has been imported correctly by querying the tables.

```sql

SELECT * FROM table_name LIMIT 10;

```

5. Repeat: Repeat steps 1 to 4 for each table you wish to import.

1. Check for Errors: Review the import process for any errors and ensure data integrity.

2. Recreate Indexes and Constraints: If you didn't include indexes and constraints in the schema files, create them now.

3. Optimize Tables: Optimize the tables to improve performance.

```sql

OPTIMIZE TABLE table_name;

```

4. Backup: Take a backup of the MySQL database now that the data has been successfully migrated.

Things to note

• Data Types: Pay close attention to differences in data types between PostgreSQL and MySQL. You may need to map certain types from one to the other.
• Character Encoding: Ensure that the character encoding is consistent between the two databases to avoid any data corruption.
• Timestamps: PostgreSQL and MySQL may handle timestamps differently; make sure to adjust them during the conversion.
• Stored Procedures/Triggers: If you're using stored procedures or triggers, you'll need to rewrite them in MySQL's syntax.
• Performance: Large datasets may take a significant amount of time to export/import. Consider performing the migration during off-peak hours.