Magnus Warker <magnux@mailinator.com> writes:

There are situations where I want to decide for myself what to do, e. g. if there is a datatype in MySQL that's not present in PgSql.

That's covered in the load command, and in the --cast command line switch.

I see your point, but I think it also depends on the migration strategy. I had to do a lot of manual work, but I prefer to do it carefully step by step to having things automated too much.

In my opinion, the more complex the migration is, the more you want to
automate it completely, with test cases that you can run nigthly and
from which you can analyze problems the next day.

Do the full migration once a day for a couple months while you fix the application so that you're confident no surprise will jump at you on
D-day: you've been doing that before.

--
Dimitri Fontaine
PostgreSQL DBA, Architecte

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi,

I would like to migrate a mysql database to pgsql.
I would like to do it table for table.

Can you export a table into a plain-text SQL file so that it can be
edited and imported in pgsql?

Thanks
Magnus

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Magnus Warker schrieb am 30.10.2015 um 20:00:

I would like to migrate a mysql database to pgsql.
I would like to do it table for table.

Can you export a table into a plain-text SQL file so that it can be edited and imported in pgsql?

Ora2Pg has been a reliable tool to migrate from Oracle to Postgres.

The latest release also supports migrating from MySQL to Postgres, you might want to look into that:

http://ora2pg.darold.net/


Apart from that, you can use any tool that is capable of exporting CSV files and then
import them using Postgres' COPY command.

I haven't really used MySQL but I believe SELECT ... INTO OUTFILE should be usable to do this table by table.

I use a SQL tool called SQL Workbench/J which can bulk export all tables from a database:

http://www.sql-workbench.net/
and
http://www.sql-workbench.net/manual/command-export.html

I have used that successfully for migrations from Oracle to Postgres.


Another option is to install mysql_fdw in Postgres and then simply pull the MySQL data through
SQL statements from within Postgres: http://pgxn.org/dist/mysql_fdw/

As a final note: if you need help with Postgres, you'll get better responses on the Postgres mailing list

http://www.postgresql.org/list/

Once you are registered you can also read them through a newsgroup on news.gmane.org

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Magnus Warker <magnux@mailinator.com> writes:

I would like to migrate a mysql database to pgsql.
I would like to do it table for table.

See pgloader:

http://pgloader.io
http://pgloader.io/howto/quickstart.html
https://github.com/dimitri/pgloader

--
Dimitri Fontaine
PostgreSQL DBA, Architecte

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 30-10-15 20:00, Magnus Warker wrote:

Hi,

I would like to migrate a mysql database to pgsql.
I would like to do it table for table.

Can you export a table into a plain-text SQL file so that it can be
edited and imported in pgsql?

mysqldump

Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

mysqldump test test | grep -v '^/'

-- MySQL dump 10.14 Distrib 5.5.45-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.5.45-MariaDB-log

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`i` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `test`
--

LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1),(2);
UNLOCK TABLES;

-- Dump completed on 2015-11-02 9:17:17

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hello!

On 10/30/2015 08:26 PM, Harry Tuttle wrote:

Magnus Warker schrieb am 30.10.2015 um 20:00:

Ora2Pg has been a reliable tool to migrate from Oracle to Postgres.

The latest release also supports migrating from MySQL to Postgres, you
might want to look into that:

http://ora2pg.darold.net/

I gave it a try. But the documentation is for Oracle only. After the
section "Generate a migration template" everything is related to Oracle.

Is there a MySQL documentation somewhere?

Thanks
Magnus

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Magnus Warker <magnux@mailinator.com> writes:

I gave it a try. But the documentation is for Oracle only. After the section "Generate a migration template" everything is related to Oracle.

Did you try pgloader?

--
Dimitri Fontaine
PostgreSQL DBA, Architecte

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 11/14/2015 08:32 PM, Dimitri Fontaine wrote:

Did you try pgloader?

Hi Dimitri,

sorry for not yet having tried the solution you recommended.
It's because I tried mysql_fdw, and I find it great!

I find it great because I can connect the new pgsql database with the
tables of the mysql database. So I can build a migration script step by
step without having to go over the file system.

Let's see how far I will get with it...
Magnus

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Dear folks,

I'd like to post my experiences here, for other people in the same
situation and for my own reference. :-)

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:

1. setup a migration instance

Setup another instance of the application with another name for the
migration. This instance already used a new, empty PgSQL database.
The new database already contained all necessary tables from the
original MySQL database, which had to be adapted (different data type
names, different keywords, ...). Also the new application instance was
adapted (different code for query result fetch loops, ...).

2. add foreign tables

Add foreign tables to the new database so that for every table in the
new database there is a foreign table that points to its pendant in the
old MySQL database. I used the same table names with different prefixes,
e. g. "apl" for my application and "mig" for the table pendant. So for
every table there was an additional table with the "mig" prefix:

apl_table1, mig_table1
apl_table2, mig_table2
...

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;

The evolvement of this script was the main part of the migration. I had
to adapt things again and again: the DDL of the new PgSQL database, the migration script and also the code of the application.

The great advantage here was that the core transport of the data from
the old MySQL database to the new PgSQL database could be done without
going through the file system (with export and import). I simply
recreated the new database and executed the migration script again and
again until everything worked.

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.

(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. :-)

Magnus

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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/

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 01/06/2016 01:08 PM, Robert Klemme wrote:

On 06.01.2016 06:43, Magnus Warker wrote:

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?

It empties the target database to ensure it's really empty. My app has a database creation script that creates some predefined records, not many.

The script copies everything from scratch, but without going through the
file system. I don't know an alternative method. Do you know one?

The database is not so large. It contains about 3000 chess games, 30000
chat entries (text) and the users. I can post the size if I know how to determine it. I guess some files somewhere under /var/lib/pg*...

4. switch the 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

You're correct with the steps. However, It took about 2-3 minutes, in
essence the time to deploy a WAR file to a tomcat server.
What I meant, when saying that the users did not notice anything, was
mainly that there were no problems or errors at all. I did not expect
that, because there are really many differences between the two DBs...

Magnus

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Magnus Warker <magnux@mailinator.com> 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hello Dimitry!

On 01/06/2016 09:31 PM, Dimitri Fontaine wrote:

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

"In one command", that's what you can read there as a slogan. However, I
don't know if this is what I wanted.

There are situations where I want to decide for myself what to do, e. g.
if there is a datatype in MySQL that's not present in PgSql.

Or even worse: In my code I had queries that require random access to
the query (I think it was calling "offset" to move the current pointer).
In fact, this was not really needed, and I could rewrite my code (using "OFFSET" and "LIMIT") so that it requires forward-only reading.

I am sure that I never wanted these things to get automated. I also
would not see any benefit if it was automated. In my opinion you need
such a cycle: "detect problem, think about it, carefully modify the app
to get a solution".

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.

You're right, to avoid these things you have to pay attention. But this
would be another reason for me not to use too much automation.

I see your point, but I think it also depends on the migration strategy.
I had to do a lot of manual work, but I prefer to do it carefully step
by step to having things automated too much.

Thanks
Magnus

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)