Re: porting a full Ms Sql Server to postgres

On 24 Sep 2004 at 17:23, pwbyrne@hotmail.com wrote:

> Hi,
>
> I'm looking for details, or tools about porting a full Ms Sql Server
> 2000 database to Postgres on Linux.
>
> Is this possible?  We have the whole nine yards, stored procedures,
> triggers, and all.
>
> Is there a way to automate this process?
>

You can probably to a semi-automated process. I have sucessfully
transfereed the bulk of the data from SQlServer to PostgreSQL using
the SQLServers Data Transformation Services (DTC) and the
PostgreSQL ODBC driver. As long as you defined the tables and
columns the same in both databases you should be OK.

If you need to create the schema first, you can "script" the SQLServer
database and hack the output file.

Put indexes and foreign keys on the PostgreSQL database *after* you
transfer the data.

You would need to redefine the triggers and stored procedures
manually.

For our own specific databases I have written a small program (in C#)
to dump the contents of SQLServer tables into a PostgreSQL COPY
command format. This speeds up the actual data transfer by an order of
magnitude or so!

Cheers,
Gary.