Postgres Pro
Downloads
Home   >   mailing lists

Thread: MSSQL -> PostgreSQL

MSSQL -> PostgreSQL

From
Chris Cameron
Date:
I'm looking to convert 2 MSSQL DB's to PostgreSQL. I've searched the
archives and various websites and found a number of solutions.

The problem is, none of them work for me. One of the databases is 150
Megs, the other 3 Gigs. It isn't very feasible for me to go into a 3 gig
file and search/replace all sorts of things (which seems pretty "iffy" a
solution to me).

I've also tried converting the MSSQL tables/data to MySQL dumps (we had
a -very- good tool laying around for that), and then running a
mysql2postgresql  script against it. I've tried the one in
/contrib/mysql/ and the one on pgsql.com. Both died when they ate all
the memory on the machine (2 gigs worth).


So, any suggestions for someone looking to convert a 3+ gig database?
We're willing to pay for any tool that may work, but I haven't been able
to find any.

Thanks,
Chris

--
Chris Cameron
UpNIX Internet Administrator
ardvark.upnix.net
bitbucket.upnix.net
--
http://www.upnix.com

Re: MSSQL -> PostgreSQL

From
Fernando Schapachnik
Date:
Try the migration wizard in PgAdmin II. Is very good.

Fernando.

En un mensaje anterior, Chris Cameron escribió:
> I'm looking to convert 2 MSSQL DB's to PostgreSQL. I've searched the
> archives and various websites and found a number of solutions.

Re: MSSQL -> PostgreSQL

From
Network Administrator
Date:
I'm not versed in MS-SQL (though I will be playing around with it in about a
week) however, I think I might be missing something- other than file system
limitations, table definitions and schema structure, is there a reason why you
can't dump the MS-SQL database tables to coma or tab separated text file?  You
can then use the PostgreSQL copy command to then read the data into the tables.


--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com


Quoting Chris Cameron <chris@upnix.com>:

> I'm looking to convert 2 MSSQL DB's to PostgreSQL. I've searched the
> archives and various websites and found a number of solutions.
>
> The problem is, none of them work for me. One of the databases is 150
> Megs, the other 3 Gigs. It isn't very feasible for me to go into a 3 gig
> file and search/replace all sorts of things (which seems pretty "iffy" a
> solution to me).
>
> I've also tried converting the MSSQL tables/data to MySQL dumps (we had
> a -very- good tool laying around for that), and then running a
> mysql2postgresql  script against it. I've tried the one in
> /contrib/mysql/ and the one on pgsql.com. Both died when they ate all
> the memory on the machine (2 gigs worth).
>
>
> So, any suggestions for someone looking to convert a 3+ gig database?
> We're willing to pay for any tool that may work, but I haven't been able
> to find any.
>
> Thanks,
> Chris
>
> --
> Chris Cameron
> UpNIX Internet Administrator
> ardvark.upnix.net
> bitbucket.upnix.net
> --
> http://www.upnix.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>




____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

Re: MSSQL -> PostgreSQL

From
"Chris Palmer"
Date:
 Chris Cameron writes:

> It isn't very
> feasible for me to go into a 3 gig file and search/replace
> all sorts of things (which seems pretty "iffy" a solution to me).

What kinds of things need to be changed?

Re: MSSQL -> PostgreSQL

From
"scott.marlowe"
Date:
On Fri, 16 May 2003, Chris Palmer wrote:

>  Chris Cameron writes:
>
> > It isn't very
> > feasible for me to go into a 3 gig file and search/replace
> > all sorts of things (which seems pretty "iffy" a solution to me).
>
> What kinds of things need to be changed?

I was thinking "sounds like a job for sed or awk." on this one.

Re: MSSQL -> PostgreSQL

From
"Ian Harding"
Date:
MSSQL Server and PostgreSQL are both very SQL standard compliant. If you are only talking about tables and data, this
isa relatively easy project regardless of the size of the tables.  If you have views, stored procedures, triggers, etc,
youmay be in for some work, but I doubt you do since you could convert to MySQL. 

The suggestions so far (PGAdmin, dump and copy) are both feasible, and there is also the MSSQL Server Data
TransformationServices tool (or whatever it's called now) which can talk directly to PostgreSQL via ODBC.  I have heard
itdoesn't know how to convert MSSQL's version of SERIAL to PostgreSQL's, but you could fix that later with ALTER TABLE
...ALTER COLUMN ... SET DEFAULT ....  

Good luck!  It is worth the effort.


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


>>> Chris Cameron <chris@upnix.com> 05/09/03 09:16AM >>>
I'm looking to convert 2 MSSQL DB's to PostgreSQL. I've searched the
archives and various websites and found a number of solutions.

The problem is, none of them work for me. One of the databases is 150
Megs, the other 3 Gigs. It isn't very feasible for me to go into a 3 gig
file and search/replace all sorts of things (which seems pretty "iffy" a
solution to me).

I've also tried converting the MSSQL tables/data to MySQL dumps (we had
a -very- good tool laying around for that), and then running a
mysql2postgresql  script against it. I've tried the one in
/contrib/mysql/ and the one on pgsql.com. Both died when they ate all
the memory on the machine (2 gigs worth).


So, any suggestions for someone looking to convert a 3+ gig database?
We're willing to pay for any tool that may work, but I haven't been able
to find any.

Thanks,
Chris

--
Chris Cameron
UpNIX Internet Administrator
ardvark.upnix.net
bitbucket.upnix.net
--
http://www.upnix.com


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: MSSQL -> PostgreSQL

From
Network Administrator
Date:
*nod*

I usually use Perl scripts myself to manipulate database dumps into something
"loadable" on a new system.  'Course Perl is probably over kill for someone that
knows sed and/or awk.

Quoting "scott.marlowe" <scott.marlowe@ihs.com>:

> On Fri, 16 May 2003, Chris Palmer wrote:
>
> >  Chris Cameron writes:
> >
> > > It isn't very
> > > feasible for me to go into a 3 gig file and search/replace
> > > all sorts of things (which seems pretty "iffy" a solution to me).
> >
> > What kinds of things need to be changed?
>
> I was thinking "sounds like a job for sed or awk." on this one.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>


--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

Re: MSSQL -> PostgreSQL

From
"Roman Fail"
Date:
I converted a 10 gigabyte production database from MSSQL to Postgres a few months back.  I tried all the various
conversionmethods and here are my impressions:
 
 
*MS Data Transformation Services - converted all the data correctly, but it was very slow and always seemed to choke
afterabout 400,000 records.  Probably when it ran out of 2GB+ memory.
 
 
* MS bcp & psql copy - required quite a bit of hand editing to handle IDENTITY/serial columns and some other minor
issues. Too painful if there are a lot of tables.
 
 
*pgAdmin2 Migration Wizard - awesome.  No memory problems.  Not only did if figure out converting IDENTIY columns, but
itoffered the option to fold all table and field names to lower case, solving the double quote problem.   I used this
toolto convert all my tables save one, which contained VARBINARY datatypes (the wizard just ignores binary fields).  To
convertthese I had to use bcp with a special SQL Server UDF that would convert hexadecimal to octal in an escape
sequence(which is the only way the psql 'copy' command can read in binary data).  I can send you more detailed
informationand the UDF if you are interested.
 
 
Once I got the data into Postgres, my query times on the big tables were horrible compared to MSSQL, and I thought I
wouldhave to switch back.  After several days discussion on the PostgreSQL Performance mailing list (thanks guys!!!), I
figuredout that there were JOIN condition data type mismatches that prevented my indexes from being used.  This problem
isnot very intuitive and easily missed by someone with a MSSQL background.  That said, I recommend making all your
migrated'integer' datatypes settle on either 'int4' or 'int8'....because if you mix them you'll have the same problems
Idid!  
 
 
One caveat of the whole migration, which probably took the most time of all....I had about 20 stored procedures and it
wasa pain to convert them from Transact-SQL to PL/pgSQL.  Once I got the hang of it it wasn't too bad, but it's not as
intuitiveas Transact-SQL IMHO.
 
 
Once I fixed that problem I was flying high - Postgres is significantly faster than MSSQL in my experience, and VERY
stable. 
 
 
Roman Fail
POS Portal, Inc.
 

    -----Original Message----- 
    From: Ian Harding [mailto:ianh@tpchd.org] 
    Sent: Fri 5/16/2003 12:36 PM 
    To: chris@upnix.com 
    Cc: pgsql-general@postgresql.org 
    Subject: Re: MSSQL -> PostgreSQL
    
    

    MSSQL Server and PostgreSQL are both very SQL standard compliant. If you are only talking about tables and data,
thisis a relatively easy project regardless of the size of the tables.  If you have views, stored procedures, triggers,
etc,you may be in for some work, but I doubt you do since you could convert to MySQL.
 
    
    The suggestions so far (PGAdmin, dump and copy) are both feasible, and there is also the MSSQL Server Data
TransformationServices tool (or whatever it's called now) which can talk directly to PostgreSQL via ODBC.  I have heard
itdoesn't know how to convert MSSQL's version of SERIAL to PostgreSQL's, but you could fix that later with ALTER TABLE
...ALTER COLUMN ... SET DEFAULT ....
 
    
    Good luck!  It is worth the effort.
    
    
    Ian Harding
    Programmer/Analyst II
    Tacoma-Pierce County Health Department
    iharding@tpchd.org
    (253) 798-3549
    
    
    >>> Chris Cameron <chris@upnix.com> 05/09/03 09:16AM >>>
    I'm looking to convert 2 MSSQL DB's to PostgreSQL. I've searched the
    archives and various websites and found a number of solutions.
    
    The problem is, none of them work for me. One of the databases is 150
    Megs, the other 3 Gigs. It isn't very feasible for me to go into a 3 gig
    file and search/replace all sorts of things (which seems pretty "iffy" a
    solution to me).
    
    I've also tried converting the MSSQL tables/data to MySQL dumps (we had
    a -very- good tool laying around for that), and then running a
    mysql2postgresql  script against it. I've tried the one in
    /contrib/mysql/ and the one on pgsql.com. Both died when they ate all
    the memory on the machine (2 gigs worth).
    
    
    So, any suggestions for someone looking to convert a 3+ gig database?
    We're willing to pay for any tool that may work, but I haven't been able
    to find any.
    
    Thanks,
    Chris
    
    --
    Chris Cameron
    UpNIX Internet Administrator
    ardvark.upnix.net
    bitbucket.upnix.net
    --
    http://www.upnix.com
    
    
    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org