Thread: SQL Server 2000 to PostgreSQL 8.0.3
Greetings. I am having difficulty moving a SQL Server 2000 database over to PostgreSQL 8.0.3. In SQL Server, I am performing a backup of the database I want to move. I save that backup on my Desktop. I am then opening up PGAdminIII and attempting to perform a Restore of the database saved on my Desktop. The first step I take is to create a new database, MyDB. Next, I highlight my new database and then select Restore from the Tools menu. The Restore Database window pops up and I Browse to the database backup I've saved on my Desktop. After selecting the backup, the 'OK' button stays grayed and I am unable to proceed. Any ideas as to how I can work around this? Thanks in advance. Josh in Tampa
I am pretty sure a backup from sql is not going to restore on postgres. I wrote a .net application that read from one and wrote to the other. Does not like tables large then memory available though, since npgsql .net components by default uses a read whole table approach (there is a cursor way, but I never got that implemented). If you are .net savy and want the source for the conversion let me know and I will forward off list. I am sure there are other methods as well (maybe dump to a csv file and try reading that with aqua studio). Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 jfradkin@wazagua.com www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of josh@segrestfarms.com Sent: Tuesday, August 09, 2005 8:37 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] SQL Server 2000 to PostgreSQL 8.0.3 Greetings. I am having difficulty moving a SQL Server 2000 database over to PostgreSQL 8.0.3. In SQL Server, I am performing a backup of the database I want to move. I save that backup on my Desktop. I am then opening up PGAdminIII and attempting to perform a Restore of the database saved on my Desktop. The first step I take is to create a new database, MyDB. Next, I highlight my new database and then select Restore from the Tools menu. The Restore Database window pops up and I Browse to the database backup I've saved on my Desktop. After selecting the backup, the 'OK' button stays grayed and I am unable to proceed. Any ideas as to how I can work around this? Thanks in advance. Josh in Tampa ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
josh@segrestfarms.com wrote: > Greetings. > > I am having difficulty moving a SQL Server 2000 database over to > PostgreSQL 8.0.3. > > In SQL Server, I am performing a backup of the database I want to > move. I save that backup on my Desktop. > That backup is in Microsofts own format, PostgreSQL will not be able to read this. > I am then opening up PGAdminIII and attempting to perform a Restore of > the database saved on my Desktop. The first step I take is to create > a new database, MyDB. Next, I highlight my new database and then > select Restore from the Tools menu. The Restore Database window pops > up and I Browse to the database backup I've saved on my Desktop. > After selecting the backup, the 'OK' button stays grayed and I am > unable to proceed. > > Any ideas as to how I can work around this? Thanks in advance. Use a program such as Aqua Data Studio (http://www.aquafold.com) to connect to the MSSQL db and export the data as INSERT statements. Then run the sql in PGAdmin. > > > Josh in Tampa > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
Only thing you may run into moving data is field types.
I had to convert my bit fields to binary and my datestammp to timestamp.
I would just be sure the field types are supported or you may have to do a
search replace on the text file created by aquastudio.
Although when I used aqua I exported using aqua and used pgadmin to do the
inserts so I had to also change their go to a ; (which was actually an
option on the export page I did not see at first).
Best of luck with the conversion.
Also here is my cheat sheet of stuff I looked at often while converting it
involves some sql differences.
The isnull is now coalesce, and date diff is a just math, so I had to
reverse the order of my date diffs and convert to date - date
date_part('epoch',date-date) returns in secs so /60
to_char( i.opendate,'yyyy') as year,
to_char( i.opendate,'q') as quarter,
to_char( i.opendate,'MM') as month,
to_char( i.opendate,'D') as weekday,
to_char( i.opendate,'WW') as week,
to_char( i.opendate,'HH24:MI') as time
coalesce(to_char(tblresponsesection.feedbackcompleteddate,'Mon DD YYYY'),
'not completed') as completed
Left 105 chars substring(tblquestions.question,1,105)
as question,
Right 5 chars substring(c.casenum::text, 1, 11),
length("substring"(c.casenum::text, 1, 11))-5, 5)
coalesce(c.apprehdate, c.opendate) as apprehdate,
to_char( coalesce(c.apprehdate,c.opendate),'yyyy') as
year,
to_char( coalesce(c.apprehdate, c.opendate),'q') as
quarter,
to_char( coalesce(c.apprehdate, c.opendate),'MM') as
month,
to_char( coalesce(c.apprehdate, c.opendate),'D') as
weekday,
to_char( coalesce(c.apprehdate, c.opendate),'WW') as
week,
to_char( coalesce(c.apprehdate, c.opendate),'HH24:MI')
as time,
~* is a = that is not case sensitive same as = is mssql
ilike is a case insensitive like
current_timestamp
SET ENABLE_SEQSCAN=FALSE;
You will want to get used to doing
explain analyze in front of your sql to see what you may need to modify or
key. I would subscribe to the performance list sql list to see how folks do
stuff. I had to de-normalize some of my larger tables sets and now my
performance is as good as my SQL server was.
Ex: explain analyze select * from tblcase where clientnum = 'WAZ' select *
from tblcase where clientnum = 'WAZ'
Will display the joins etc involved in getting the data.
Joel Fradkin
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of josh@segrestfarms.com
Sent: Tuesday, August 09, 2005 8:37 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] SQL Server 2000 to PostgreSQL 8.0.3
Greetings.
I am having difficulty moving a SQL Server 2000 database over to
PostgreSQL 8.0.3.
In SQL Server, I am performing a backup of the database I want to move.
I save that backup on my Desktop.
I am then opening up PGAdminIII and attempting to perform a Restore of
the database saved on my Desktop. The first step I take is to create a
new database, MyDB. Next, I highlight my new database and then select
Restore from the Tools menu. The Restore Database window pops up and I
Browse to the database backup I've saved on my Desktop. After selecting
the backup, the 'OK' button stays grayed and I am unable to proceed.
Any ideas as to how I can work around this? Thanks in advance.
Josh in Tampa
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Check out WinSql (http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp) It has the ability to extract data from Sql*Sucker and drop it into PostGreSql, and you can have that functionality free for 90 days to boot. -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of josh@segrestfarms.com Sent: Tuesday, August 09, 2005 8:37 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] SQL Server 2000 to PostgreSQL 8.0.3 Greetings. I am having difficulty moving a SQL Server 2000 database over to PostgreSQL 8.0.3. In SQL Server, I am performing a backup of the database I want to move. I save that backup on my Desktop. I am then opening up PGAdminIII and attempting to perform a Restore of the database saved on my Desktop. The first step I take is to create a new database, MyDB. Next, I highlight my new database and then select Restore from the Tools menu. The Restore Database window pops up and I Browse to the database backup I've saved on my Desktop. After selecting the backup, the 'OK' button stays grayed and I am unable to proceed. Any ideas as to how I can work around this? Thanks in advance. Josh in Tampa ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
Here's my 2 cents. If you have access to DTS in SQL Server 2000, it will be one convenient solution for simple transfer. It's a matter of creating a new task, defining the SQL Server database as source and PostgreSQL database as destination, and clicking through. There will be some mismatches in data types, but you can convert data using scripts inisde the DTS task on the fly. Regards, Ben Kim Developer College of Education Texas A&M University
I have successfully moved the schema over to PostgreSQL. Now I am working on the data. In reviewing my tables, I found that for some strange reason a handful of fields are not double-quoted. Strange quirk: -- Table: "Orders" -- DROP TABLE "Orders"; CREATE TABLE "Orders" ( "OrderID" int4 NOT NULL, "CustomerID" int4 NOT NULL, "DateCreated" timestamptz, "TotalPrice" numeric(19,4), "ShipAddressID" int4, "ShipDate" timestamptz, "ShippingMethod" varchar(50), "sfrepID" varchar(50), "StatusID" int4, "TrackingNumber" varchar(50), ponumber varchar(50), <--------------HERE "Comment" char(254), "sfOrderNumber" varchar(50), "UpdateDate" timestamptz ) WITH OIDS; ALTER TABLE "Orders" OWNER TO postgres; What to make of this strangeness? Ben Kim wrote: >Here's my 2 cents. > >If you have access to DTS in SQL Server 2000, it will be one convenient >solution for simple transfer. > >It's a matter of creating a new task, defining the SQL Server database as >source and PostgreSQL database as destination, and clicking through. There >will be some mismatches in data types, but you can convert data using >scripts inisde the DTS task on the fly. > > >Regards, > >Ben Kim >Developer >College of Education >Texas A&M University > > > > >
>In reviewing my tables, I found that for some strange reason a handful >of fields are not double-quoted. Strange quirk: >-- Table: "Orders" > >-- DROP TABLE "Orders"; > >CREATE TABLE "Orders" >( > "OrderID" int4 NOT NULL, > "CustomerID" int4 NOT NULL, > "DateCreated" timestamptz, > "TotalPrice" numeric(19,4), > "ShipAddressID" int4, > "ShipDate" timestamptz, > "ShippingMethod" varchar(50), > "sfrepID" varchar(50), > "StatusID" int4, > "TrackingNumber" varchar(50), > ponumber varchar(50), <--------------HERE > "Comment" char(254), > "sfOrderNumber" varchar(50), > "UpdateDate" timestamptz >) >WITH OIDS; >ALTER TABLE "Orders" OWNER TO postgres; In my case, DTS's "create" always wrapped the field names with double quote. The script seems to me not from DTS. Regards, Ben Kim Developer College of Education Texas A&M University
Ben Kim <bkim@coe.tamu.edu> writes:
>> In reviewing my tables, I found that for some strange reason a handful
>> of fields are not double-quoted. Strange quirk:
>> ...
> In my case, DTS's "create" always wrapped the field names with double
> quote. The script seems to me not from DTS.
I think it's from pg_dump. pg_dump (and most other PG-related tools)
will only quote names that need to be quoted per the PG rules ...
in other words, if it's all lower case, it does not need quotes.
regards, tom lane
I have successfully migrated my db from SQL Server to PostgreSQL. (Aqua Data Studio 4.0 did the trick) Now, I need to move this PG db from its current installation on Windows over to my RedHat box. So, I performed a Backup on my Windows machine and saved the .backup file to my Windows Desktop. I used WinSCP to copy the .backup file from my Windows Desktop over to my RedHat root/Desktop. Then I swivel over to my RedHat machine and I open up PGAdminIII (excited because I'm thinking I've about got this migration complete) and unfortunately under the Tools menu, the Backup and Restore selections are disabled (grayed out). And so I'm a little stumped. Surely there is a workaround, but I have yet to discover it. Thanks in advance for any help you might be able provide. Tom Lane wrote: >Ben Kim <bkim@coe.tamu.edu> writes: > > >>>In reviewing my tables, I found that for some strange reason a handful >>>of fields are not double-quoted. Strange quirk: >>>... >>> >>> > > > >>In my case, DTS's "create" always wrapped the field names with double >>quote. The script seems to me not from DTS. >> >> > >I think it's from pg_dump. pg_dump (and most other PG-related tools) >will only quote names that need to be quoted per the PG rules ... >in other words, if it's all lower case, it does not need quotes. > > regards, tom lane > > > >
josh@segrestfarms.com wrote: > I have successfully migrated my db from SQL Server to PostgreSQL. (Aqua > Data Studio 4.0 did the trick) If you are comfortable with Aquastudio then just Aquastudio to push it to your Linux box :) Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
josh@segrestfarms.com wrote: > I am having difficulty moving a SQL Server 2000 database over to > PostgreSQL 8.0.3. > > In SQL Server, I am performing a backup of the database I want to move. > I save that backup on my Desktop. > > I am then opening up PGAdminIII and attempting to perform a Restore of > the database saved on my Desktop. The first step I take is to create a > new database, MyDB. Next, I highlight my new database and then select > Restore from the Tools menu. The Restore Database window pops up and I > Browse to the database backup I've saved on my Desktop. After selecting > the backup, the 'OK' button stays grayed and I am unable to proceed. As already mentioned, there is no unified backups for DBMS. You may move structure and data, but not stored procedures and triggers, by installing postgresql-odbc on mssql server machine and using export/import tools from enterprise manager of mssql to export from mssql and import into postgresql-odbc connection. For user types, triggers, storeds you'll need to do rewrite it by hands. -- Wbr, Sergey Moiseev