Обсуждение: MS SQL Server migration

I recently completed a data migration to Postgres 7.3.1 using the pgAdminII Migration Wizard.  It is awesome, and so
mucheasier to use than MS DTS.  Thanks for such a great tool.  
 
 
However, I was unable to migrate a table with a VARBINARY column using the wizard.  MS DTS was able to migrate the
binarytable to postgres just fine, but had memory problems due to the size of the table (23 million tuples).  So I
endedup using bcp and a writing a hex2octal UDF to get the thing imported.  (And thanks to Ian Harding for a lot of
goodideas in his tech doc).
 
 
Is binary migration going to be supported at some point?  Clearly ODBC can do it, I'm guessing it's just a matter of
writingthe routines.
 
 
Second, I found that all my indexes migrated flawlessly, except for the unique primary key indexes!  PostgreSQL and
MSSQLhave a major difference when you use CREATE TABLE ... PRIMARY KEY.  Postgres simply creates UNIQUE  and NOT NULL
constraints. MSSQL does all that, plus it creates a unique index for the column.  Perhaps in future versions of the
wizard,these indexes would also be migrated?  I saw a bunch of indexes on all my migrated tables, and foolishly assumed
thePK indexes were there as well.
 
 
Roman

> -----Original Message-----
> From: Roman Fail [mailto:rfail@posportal.com]
> Sent: 15 January 2003 18:59
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] MS SQL Server migration
>
>
> I recently completed a data migration to Postgres 7.3.1 using
> the pgAdminII Migration Wizard.  It is awesome, and so much
> easier to use than MS DTS.  Thanks for such a great tool.

Thanks.

> However, I was unable to migrate a table with a VARBINARY
> column using the wizard.  MS DTS was able to migrate the
> binary table to postgres just fine, but had memory problems
> due to the size of the table (23 million tuples).  So I ended
> up using bcp and a writing a hex2octal UDF to get the thing
> imported.  (And thanks to Ian Harding for a lot of good ideas
> in his tech doc).
>
> Is binary migration going to be supported at some point?
> Clearly ODBC can do it, I'm guessing it's just a matter of
> writing the routines.

Hmm, the next version of pgAdmin is being written cross-platform in C++.
I have far less spare time these days and as the Migration Wizard is
something I never use, it goes to the bottom of my personnal list I'm
afraid. Even when we do write it, it will likely be very different from
what we have now.

> Second, I found that all my indexes migrated flawlessly,
> except for the unique primary key indexes!  PostgreSQL and
> MSSQL have a major difference when you use CREATE TABLE ...
> PRIMARY KEY.  Postgres simply creates UNIQUE  and NOT NULL
> constraints.  MSSQL does all that, plus it creates a unique
> index for the column.  Perhaps in future versions of the
> wizard, these indexes would also be migrated?  I saw a bunch
> of indexes on all my migrated tables, and foolishly assumed
> the PK indexes were there as well.

The Wizard does migrate pkeys if it can identify them (which it does
using Microsoft's ADOX library). PostgreSQL itself however does just
create a unique index & not null (which in relational theory is all a
pkey is - unique, not null, and an individual row identifier), the only
difference from any other is (off the top of my head)
pg_index.indisprimary = TRUE for that index. There may also be
additional info in pg_constraint in 7.3. You might try hacking the
system catalogues if you're feeling adventurous. Backup first though!!

Regards, Dave