Path: csiph.com!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail From: Robert Klemme Newsgroups: comp.databases.postgresql Subject: Re: migrate from mysql to pgsql Date: Wed, 6 Jan 2016 13:08:19 +0100 Lines: 63 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit X-Trace: individual.net z3n6eoYNsA4Au9ACy4b2+w9PrLntVcFHPpV05Wz3+DUtPgXoo= Cancel-Lock: sha1:ttQf66K77ZVw5XVFJAjMIIiV2X8= User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.4.0 In-Reply-To: Xref: csiph.com comp.databases.postgresql:691 On 06.01.2016 06:43, Magnus Warker wrote: > I'd like to post my experiences here, for other people in the same > situation and for my own reference. :-) Thank you for posting your experience! > The migration is done on my production system and it was done without > any pain or problems! If I knew that it would go this way I wouldn't > have postponed it for years. This will be my method of choice, if I have > to do it again someday. :-) > Here is how it was done: > 3. evolve a migration script > > Evolve a SQL script that fetches the data from the migration tables and > writes it into the new tables. This script containes some SQL statements > for each of the tables in the new database, like this: > > DELETE FROM apl_table1; > INSERT INTO apl_table1 (atr1,...,atrn) > SELECT (atr1,...,atrn) FROM mig_table1; If I understand this correctly your migration script basically emptied the target PostgreSQL schema and then copied over everything from scratch. I would assume this is only practical up to a particular database size. How large was yours? > 4. switch the database > > Finally, I changed the migration instance of the application to the main > production instance. The migration script must be executed one last time > immediately before this step, so that the latest changes to the old > database make it to the new database. > > In spite of all the differences between the two DBMS and the SQL > dialects this change could be performed without any incicents. My > application (a chess system) continued to work and my users didn't > notice anything. How could that be? Didn't you require some downtime? I mean, in order to be sure to not lose data you would have to do 1. Shut down production 2. Migrate data for the last time 3. Fire up new application in production using the new database > (Besides that, I feel that it's running a bit faster now...) :-) > Thank you very much Harry for pointing me to mysql_fdw. > I enjoyed it. :-) Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/