Re: database conversion to postgres

Jeff Dyke wrote:
> I'm very new to postgres but not to databases. I'm currently working
> on a back end platform of Adaptive Server Anywhere 9.0 (Sybase) and
> would like to move to Postgres.  I have a total of 6 production
> databases, so about 24(dev/test/qa etc) dbs that need to be converted
> first, so I'll have plenty of practice for the prod versions. The
> largest of which is ~6G, and the smallest only ~100M.
>
> I've done some searching on and have not found anything quick and
> easy, and didn't really expect to, but was interested in any pointers
> anyone may have.
>
> The dbs are currently on a windows platform and will remain so, at
> least for this first conversion.
>
> I don't mind doing it programatically and that may be my only
> choice...PHP/Perl.   Currently i connect to sybase solely over
> odbc....anyway.  Any/all comments and welcomed and appreciated.
>
> Thanks
> Jeff
>

Jeff,

I am not familiar with the utilities that you may have with Sybase.  Do
you have one that will output the SQL statements required to build the
database?  If so then you could use that file as input to psql to
rebuild the database in PostgreSQL.  Of course it will probably choke
but reviewing the error log would point you in the direction of the
required changes.  A good scrub with an editor like sed or Perl may
clean up a number of common changes.  Once you have a sed or Perl script
that will modify your Sybase SQL definitions to standard SQL definitions
then you can build the database structure.  Next comes the data.  For
that I would suggest dumping the data out as csv text files and using
the COPY command to put them into PostgreSQL.  Depending on your data
and the database structures this approach may or may not be adequate.

I would think that a sed or Perl script that converts a Sybase SQL data
dictionary dump to a standard SQL data dictionary would generate some
interest.

--
Kind Regards,
Keith