Home / server / Questions / 6782
Accepted
Tom Feiner
Asked: 2009-05-11 04:15:24 +0800 CST

How to migrate data from a Firebird database to PostGreSQL on Linux

  • 772

Are there any good tools to migrate existing firebird databases to PostgreSQL for Linux systems?

I've looked at: FBexport which can be used to dump the data as insert statements, but it's mainly written to export/import from one firebird db to another, not as a migration tool.

There's also: Firebird to PostgreSQL Win32 tool, but it's only for win32 systems.

Is there any good tool to do this? Or should I just roll my own?

linux migration postgresql firebird

4 Answers

  1. Best Answer

    When I needed to move some complicated data from a MySQL database to a PostgreSQL database, I wrote a perl script that connected to the MySQL database, selected the data, did whatever conversions were needed (like tinyint(1) to boolean and autoincrement to sequence, etc) and inserted it into the PostgreSQL database. For simpler conversions, I did a mysql_dump to a file of insert statements as your FBexport will do, then fixed up what needed to be fixed with a sed script. It's not automated and perfect, but it was fast and good enough.

    • 2

  2. Check this out:
    http://www.sqlmanager.net/en/products/postgresql/datapump

    I've never used it so I can't say whether it's good or not.

    • 1

  3. From the reponses I got here, I understand there are no specific migration tool for Firebird -> PostgreSQL on Linux.

    I guess I'll have to write this myself.

    The major issue here is optimizing the performance of such a migration.

    I did find a great resource in chapter 14 of the the postgreSQL documentation, which explains what are the best ways to insert large amounts of data to postgreSQL, like:

    • Disabling auto commits.
    • Using COPY instead of a series of INSERT statements.
    • Removing indexes (or more importantly, only create them at the end of the data inserts).
    • Various memory configuration tunings.
    • 0

  4. You can use FullConverter, but i don't think any comercial tool will do the trick with one click. you'll need to check the constraints, indexes and auto-increments.

    you could also try to write your own bash/perl/python/whatever script to do the job.

    • 0