Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #691

Re: migrate from mysql to pgsql

Show all headers | View raw

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/

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

migrate from mysql to pgsql Magnus Warker <magnux@mailinator.com> - 2015-10-30 20:00 +0100
  Re: migrate from mysql to pgsql Harry Tuttle <SOZRBLNTLEEE@spammotel.com> - 2015-10-30 20:26 +0100
    Re: migrate from mysql to pgsql Magnus Warker <magnux@mailinator.com> - 2015-11-14 04:25 +0100
      Re: migrate from mysql to pgsql Dimitri Fontaine <dimitri@2ndQuadrant.fr> - 2015-11-14 20:32 +0100
        Re: migrate from mysql to pgsql Magnus Warker <magnux@mailinator.com> - 2015-11-18 17:58 +0100
  Re: migrate from mysql to pgsql Dimitri Fontaine <dimitri@2ndQuadrant.fr> - 2015-11-01 22:09 +0100
  Re: migrate from mysql to pgsql Luuk <luuk@invalid.lan> - 2015-11-02 09:18 +0100
  Re: migrate from mysql to pgsql Magnus Warker <magnux@mailinator.com> - 2016-01-06 06:43 +0100
    Re: migrate from mysql to pgsql Robert Klemme <shortcutter@googlemail.com> - 2016-01-06 13:08 +0100
      Re: migrate from mysql to pgsql Magnus Warker <magnux@mailinator.com> - 2016-01-06 13:35 +0100
    Re: migrate from mysql to pgsql Dimitri Fontaine <dimitri@2ndQuadrant.fr> - 2016-01-06 21:31 +0100
      Re: migrate from mysql to pgsql Magnus Warker <magnux@mailinator.com> - 2016-01-31 06:21 +0100
        Re: migrate from mysql to pgsql Dimitri Fontaine <dimitri@2ndQuadrant.fr> - 2016-01-31 22:19 +0100

csiph-web