Обсуждение: database conversion to postgres
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 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
On May 19, 2005, at 11:01 AM, Keith Worthington wrote: > 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. There is also SQL::Translator, a set of perl modules for doing these types of conversions. It isn't perfect, but does reduce the grunt-work. Sean