Re: MS SQL Server migration

From: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
To: "Roman Fail" <rfail(at)posportal(dot)com>, <pgadmin-support(at)postgresql(dot)org>
Subject: Re: MS SQL Server migration
Date: 2003-01-15 21:15:46
Message-ID: 03AF4E498C591348A42FC93DEA9661B88572@mail.vale-housing.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support

> -----Original Message-----
> From: Roman Fail [mailto:rfail(at)posportal(dot)com]
> Sent: 15 January 2003 18:59
> To: pgadmin-support(at)postgresql(dot)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

Browse pgadmin-support by date

  From Date Subject
Next Message Dave Page 2003-01-15 21:22:02 Re: Error installing pgadmin II on Windows 98
Previous Message Roman Fail 2003-01-15 18:58:31 MS SQL Server migration