Notes migration MySQL vers Postgres en passant par CSV

Avant migration, suppression de caractère problématiques

UPDATE adresse SET Comments = REPLACE (Comments,";",",");
UPDATE entreprise SET Comments = REPLACE (Comments,"\"","-");
UPDATE entreprise SET Comments = REPLACE (Comments,"\'","");
UPDATE entreprise SET Comments = REPLACE (Comments,"\\","");
UPDATE adresse SET Surnom = NULL;

Export données en CSV

SELECT * INTO
OUTFILE '/tmp/zadresse.csv'
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM adresse;
 
SELECT * INTO 
OUTFILE '/tmp/zentreprise.csv' 
FIELDS TERMINATED BY ';' 
ENCLOSED BY '"' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' 
FROM entreprise;

Modif fichiers CSV

cat zadresse.csv |sed -e 's/\\N//g'|sed -e 's|\\||g'|sed -e 's|\/||g'>zadresse2.csv
cat zentreprise.csv |sed -e 's/\\N//g'|sed -e 's|\\||g'|sed -e 's|\/||g'>zentreprise2.csv

Export du schéma de la base

mysqldump prospect -u root -p --compatible=postgresql --compact --skip-opt |grep -v '^/' >/tmp/schema.sql

Export Postgres vers CSV

psql -q --csv -P csv_fieldsep=';' -c "SELECT * FROM pg_stat_activity;"