Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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.  


> 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

pgadmin-support by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group