{{tag>Brouillon DB SQL CSV MySQL Postgres}}

= Notes migration MySQL vers Postgres en passant par CSV

Avant migration, suppression de caractère problématiques
<code sql>
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;
</code>

Export données en CSV
<code sql>
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;
</code>

Modif fichiers CSV
<code bash>
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
</code>

Export du schéma de la base 
<code bash>
mysqldump prospect -u root -p --compatible=postgresql --compact --skip-opt |grep -v '^/' >/tmp/schema.sql
</code>

== Autres

Export Postgres vers CSV
<code bash> 
psql -q --csv -P csv_fieldsep=';' -c "SELECT * FROM pg_stat_activity;"
</code>