Re: MS SQL Server 2000 migrate to Postgres 7.x

(I cc'd the pgsql-general list so you might get some more advice...)

There are a lot of ways to migrate tables and data. That part is easy. Probably the easiest way (if everything goes right) is to set up the PostgreSQL ODBC driver on the SQL Server machine, and try to use Data Transformation Services (DTS) or whatever they are calling it now. It lets you move table structures, and move data, optionally modifiying the data in between if required.

The hardest part for you is going to be re-writing your stored procedures from scratch. I chose to export the stored procedures to SQL scripts, then move them to the PostgreSQL machine, and edit them EXTENSIVELY to conform to either pltcl or plpgsql. The logic remains the same and is a good starting point, but the syntax and keywords are very different.

I ended up having to re-write my front-end program too. You will at least have to modify it for the differences in SQL implementations between PG and MSSQL. Since you will want to work on this while still using the other version, I recommend using scripts for everything (data migration, index creation, constraints, new stored procedures) so you can bring over fresh data from your MSSQL install from time to time, and finally when you are done.

I found that I had to modify my MSSQL schema and data even before migrating it since I had made some mistakes that PostgreSQL's less forgiving nature made obvious. Using migration scripts makes that easier to handle too.

Good luck. It will definitely be worth it. PostgreSQL is a better product in a lot of ways.

Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
iharding(at)tpchd(dot)org

"La Union es la Fuerza"

>>> "louis chong" <louis(at)tradelook(dot)com> 09/06/02 09:47AM >>>
Dear friend,

I plan to migrate our ms sql server (table structure and stored procedure) to postgres 7.x.
I know you have experience on this, could you tell me what we need to prepare before starting the migration ?
and our table structure, index and stored procedure can use on postgres ?

i am look forward to your kindly opinions.

Thanks

Louis Chong