Data Migration from Sybase to PostgreSQL?

hp9000 asks: "I've been asked to work on a project that will involve data migration from Sybase to PostgreSQL. I'd like to know if there's any tool, similar to Oracle's Migration Workbench, or even a shell script, to move all data from Sybase tables to PostgreSQL tables, creating the tables/tablespaces/etc in the process or at least generating a script to do so. Any kind of tool, so to speak, that would allow me to perform the migration will be great. I'm not interested in the sql code migration (that will come later if necessary), only tables, indexes, etc. at this point."

Just gonna throw this out there...

[ClubPetey]

By the time you find a tool to help with the migration, learn how the tool works, fix the inevitable quirks, and solve the blantant errors with the software, you could have exported the schema from Sybase, translated it with search/replace by hand, and created the new schema in PostgreSQL for a lot less time and money.

Once the schemas are setup, you can use any number of raw copy tools out there. Borland Delphi comes with one (DataPamp) there are several java-based ones out there. Or you could just write a series of "INSERT INTO" SQL statements with ODBC. This assumes that you are a competent DBA and not like all those Oracle "experts" out there that can't write SQL.

Should be relatively easy

[imrdkl]

With Perl [perl.org]. Try asking on Perlmonks [perlmonks.org]. Perl has modules for both database types, and handles to both databases can be opened simultaneously in a single script, reducing the need for intermediete format conversion. If there is a mapping of all datatypes, I have not worked with Postgres, then it should be doable.

It's easy

[gruntvald]

Run Sybase. From the File menu, choose Save As... select "postgresql" from the drop down list.

Aaarrgh! I just woke from a bizarre dream! Seriously, the Perl::DBD stuff should take care of it, without the need for an intermediary "file based" stage. And I echo another posters sentiment - exporting the schema, then manually reading it, has some significant benefits, including the ability to "tweak" it and do some "what ifs". I did this during the design of a postgresql db once and using a simple shell script was able to build and destroy different schemas for testing very quickly.

Checkout techdocs.postgresql.org

[markjugg]

There are several migration guides at techdocs.postgresql.org. [postgresql.org] There are none listed for Sybase, which makes me suspect there may not currently be one written. However, the other guides may have some good general tips for you.

Scripting approach

[Cato]

If there are no suitable tools available at the right price, here's an approach that may work for you - just write SQL statements, one per table, that retrieve the data from Sybase in the format of a PostgreSQL 'insert' statement. For example (may not be exactly right syntax...):

select 'insert into emp values ( "' + empname + '", "' + address1 '", "' + city + '" )'
from emp

This generates the insert statements, which can then be run against PostgreSQL.

If you have a large number of tables and columns, you could generate these queries by doing a small application (e.g. using Perl or whatever your favourite language is) that is driven by the Sybase catalogues (data dictionary).

Another approach is to just extract the data in flat file format, e.g. CSV, and generate the 'insert' statements using a small app. However, the first approach outlined avoids writing any apps and is fine for a not-too-complex database.

Whatever you do, be sure to do 'select count(*)' before and after, and consider using the flat file approach with detailed error reporting when a row can't be transferred. Things will go wrong, but typically only on a subset of the data, so make it easy to retry on that subset.

Moving the Data Is Only Half The Battle

[superid]

I'm in the same boat. We've got a Sybase installation that has evolved over the past 7 years. It's grown to about 20GB, which I know is really not that big. I don't think I'd have any problem moving the data, as most of our data typing is pretty standard.

However, we have hundreds and hundreds (my guess is nearly 3000) triggers and stored procedures. Migrating the data is completely useless for me unless we can somehow migrate the T-SQL too....and I strongly suspect thats not gonna happen.

So, does anyone have any thoughts on this?

Re:Moving the Data Is Only Half The Battle

[CodeArt]

Excuse me, but what is the reason you are not considering moving databases to SQL Server 2000? Microsoft SQL Server is closest to Sybase ASE among other database products.

Re:Moving the Data Is Only Half The Battle

[the eric conspiracy]

One reason might be that Porstgres is free, whilst SQL server has a $20,000 per CPU license fee.

Re:Moving the Data Is Only Half The Battle

[the eric conspiracy]

Really? Where do you get it at that price?

Re:Moving the Data Is Only Half The Battle

[hp9000]

Money. If we can get rid of Solaris/Sybase and use Linux/Postgres, for some of our clients that would mean a $2 million drop in price. IT is not what it used to be, where money would flow freely and people wouldn't care where it went. Today, I have to justify spending $10 on a new mouse.