how to migrate data from mySQL to PostgreSQL

Question

Trying to migrate a database from MySQL to PostgreSQL. All the documentation I have read covers, in great detail, how to migrate the structure. I have found very little documentation on migrating the data.

I want to do the migration by using transport tablespace, anyone have tools migratin, or anyone know how to migrate using transport tablespace from mySQL to PostgreSQL

i have 104 table in MySQL and 200 MB data

Answer 1

I am not sure, that you can migrate using transport tablespace from mySQL to PostgreSQL.
In order to migrate big data sets you can use PostgreSQL COPY.

Generally, It is much better to use some program, that automates the process of migration (db structure and data). Here are a few open source tools, that can be very helpful:

FromMySqlToPostgreSql is a feature-reach tool and very easy to use.
It maps data-types, migrates constraints, indexes, PKs and FKs exactly as they were in your MySQL db.
Under the hood it uses PostgreSQL COPY, so data transfer is very fast.
FromMySqlToPostgreSql written in PHP (>= 5.4).

pgloader   is a data loading tool for PostgreSQL, using the COPY command. Its main advantage over just using

COPY

or

\copy

, and over using a Foreign Data Wrapper, is its transaction behaviour, where pgloader will keep a separate file of rejected data, but continue trying to copy good data in your database. The default PostgreSQL behaviour is transactional, which means that any erroneous line in the input data (file or remote database) will stop the entire bulk load for the table. pgloader also implements data reformatting, a typical example of that being the transformation of MySQL datestamps 0000-00-00 and 0000-00-00 00:00:00 to PostgreSQL

NULL

value (because our calendar never had a year zero).
pgloader written in LISP and distributed as source code, so you you need to compile it before using.

Hope it will help you...