Re: I need to load mysql dump to postgres...

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