Slashdot Mirror
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."
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.
Si hoc legere scis nimium eruditionis habes
With Perl. Try asking on Perlmonks. 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.
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.
There are several migration guides at techdocs.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.
There is a company in San Jose called HiT Software, www.hitsw.com, that has a product that uses XML to represent your data and your table definitions among other things. This XML representation can then be loaded into another database. The product's name is jAllora, http://www.hitsw.com/dcprod-allorajava.htm it is written in 100% java and it uses JDBC to access a variety of databases. I was a consultant for HiT most of this year and wrote major parts of this product. I know they support Oracle, Sybase, SQLServer, Postgresql, DB2, and others I'm not sure off.
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.
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?
You should be able to use BCP to dump the data from Sybase and then use COPY FROM [datafile] .. within psql to carry out a bulk upload into Postgres. Think carefully about BLOB's if you have any in the source db.
FAIK Postgres does not currently allow you to return recordsets from 'stored procedures' as you can with MSSQL / Sybase SQL. (This is one factor that is currently stopping me going this route).
Check out pgAdmin II / pgUpt as these may be of use.
HIH.