Thread: I need to load mysql dump to postgres...
good noon, subj. I don't want to load dump to mysql etc... Is there a program which would just parse mysql dump file and load data to postgresql using plain sql inserts? thanks.
On 10/12/2011 10:37 AM, unclebob wrote: > good noon, > subj. > I don't want to load dump to mysql etc... > Is there a program which would just parse mysql dump file and load data > to postgresql using plain sql inserts? There's no single, simple automatic migration tool. Numerous tools exist to help. See a simple Google search for "convert mysql postgresql", the first result of which is: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL For more complex jobs you may want to look at ETL tools like Pentaho or Talend, but the nature of your question suggests it's probably a fairly simple database. It's often easiest to just hand-write the new schema, then do a data-only MySQL dump in portable mode (with inserts) and run that through psql. Use mysqldump WITHOUT the "--all" or "-a" option so it doesn't dump as much MySQL-specific stuff, and use "--no-create-db --no-create-info" to suppress the schema definitions. Then edit out any remaining MySQL-specific stuff and feed it into psql. -- Craig Ringer
On Tue, 2011-10-11 at 22:37 -0400, unclebob wrote: > Is there a program which would just parse mysql dump file and load > data to postgresql using plain sql inserts? EDB has an open source and free tool for this: http://www.enterprisedb.com/migrationwizard-11 If you are using RPM based distros, there are also RPMs of this in http://yum.postgresql.org -- package name ise mysqlmigrator. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Attachment
On 10/12/2011 03:45 AM, Devrim GÜNDÜZ wrote: > On Tue, 2011-10-11 at 22:37 -0400, unclebob wrote: >> Is there a program which would just parse mysql dump file and load >> data to postgresql using plain sql inserts? > > EDB has an open source and free tool for this: > > http://www.enterprisedb.com/migrationwizard-11 > > If you are using RPM based distros, there are also RPMs of this in > http://yum.postgresql.org -- package name ise mysqlmigrator. > > Regards, Devrim, I'm under debian squeeze and pgsql 8.4. Do you know which version of mysqlmigrator I can try? thanks!
On Wed, 2011-10-12 at 20:12 -0400, unclebob wrote: > > > I'm under debian squeeze and pgsql 8.4. Do you know which version of > mysqlmigrator I can try? Just download the tarball from EDB website, extract it, and run: ant compile ant dist java -jar dist/MigrationWizard.jar openjdk will just work fine, you don't need Sun^H^H^HOracle Java. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Attachment
On 10/13/2011 05:31 AM, Devrim GÜNDÜZ wrote: > On Wed, 2011-10-12 at 20:12 -0400, unclebob wrote: >> >> >> I'm under debian squeeze and pgsql 8.4. Do you know which version of >> mysqlmigrator I can try? > > Just download the tarball from EDB website, extract it, and run: > > ant compile > ant dist > java -jar dist/MigrationWizard.jar > > openjdk will just work fine, you don't need Sun^H^H^HOracle Java. > > Regards, Looks like it's not exactly what I need. It migrates data from db to db, but I need to get data from a file(mysql dump) and load it to postgres. It's a large file and I don't want to load it to mysql first and then migrate data. thanks.
On Thu, 13 Oct 2011, unclebob wrote: > Looks like it's not exactly what I need. It migrates data from db to db, > but I need to get data from a file(mysql dump) and load it to postgres. > It's a large file and I don't want to load it to mysql first and then > migrate data. thanks. When you write, "mysql dump" do you mean an ASCII SQL file? If so, it's not so difficult to make the transition to a form postgres accepts. I did this with a rather large file (called ITIS, for International Taxonomic Information System) that's maintained in a MySQL database by the USGS Biological Services division in Denver. I need it as part of a postgres database. Did the conversion manually and relatively quickly. First, take a look at the top of the file and you'll see MySQL-specific syntax that is obviously not postgres compatible. Using the search-and-replace function in your favorite text editor (emacs, right?) remove all traces of that syntax. When you've removed the obvious incompatibilities, use the psql '\i' option to read in the table(s). Postgres will very helpfully tell you where it chokes. Go back to your text editor and make the recommended changes or deletions. Repeat until the entire database or table has been imported. For a one-time conversion it's quicker to do this manually than to spend time trying to find a pre-built solution. If you'll be doing this again, take good notes and the next one will be a breeze. HTH, Rich