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

Autres

Export Postgres vers CSV

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