Обсуждение: MSSQL -> PostgreSQL
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
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.
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
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?
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.
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
*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
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