Path: csiph.com!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail From: Dimitri Fontaine Newsgroups: comp.databases.postgresql Subject: Re: migrate from mysql to pgsql Date: Wed, 06 Jan 2016 21:31:22 +0100 Organization: A noiseless patient Spider Lines: 25 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit Injection-Info: mx02.eternal-september.org; posting-host="d82aa4f1883ab7155c995516303c9b46"; logging-data="8503"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19gB4qa94vlgl9B1qiTcndn" User-Agent: Gnus/5.13001 (Ma Gnus v0.10) Emacs/24.5 (darwin) Cancel-Lock: sha1:XlnsQDR+fmY9vNhVuZ+BBwcWZeM= sha1:aCLOmt6CvT7KXzyV7Ftq6rTtpkM= Xref: csiph.com comp.databases.postgresql:694 Magnus Warker writes: > I'd like to post my experiences here, for other people in the same situation > and for my own reference. :-) A quick note: have a look at pgloader, a tool that automates all the steps you're mentionning expect for the switching itself. http://pgloader.io The Foreign Data Wrapper approach is a good one too, but the MySQL driver used to bypass data validation and could fill in the PostgreSQL tables with e.g. 0000-00-00 dates, which then you can't read back. There's also the opportunity to cast bit(1) or tinyint(1) columns into proper boolean ones, and things like that. The other huge mess are is the encoding, depending on the version of MySQL you are using, basically you may have none validation of it at all. I've seen cases with more than 10 different encodings found in the same column in MySQL… Regards, -- Dimitri Fontaine PostgreSQL DBA, Architecte