current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I currently have SQL Server 2005 and I want to make a copy of the entire database into PostgreSQL. I don't want to migrate to new PostgreSQL but just a entire copy. I almost have more than 1 TB of data in SQL Server. Is there any tool that can simplify this copying process? Also, I assume compression techniques have become much more advance in recent years so how much storage I should expect in PostgreSQL?

share|improve this question
    
if you have Sql Server, you can use the data migration wizard included with Integration Services. –  Steve B Jan 13 '12 at 14:46
    
Can I make rough estimation of the memory size that I need in PostGreSql for 1 TB of MS SQL 2005 data? –  Jack_of_All_Trades Jan 13 '12 at 14:56
    
Is that 1TB for just the database or does that include transaction logs? –  Marnix van Valen Jan 13 '12 at 16:09
    
1 TB is just for the database without transaction logs. Also, I should correct my mistake. It is from MS SQL 2000 not 2005 from where I want to copy the database. –  Jack_of_All_Trades Jan 13 '12 at 16:21
add comment

migrated from stackoverflow.com Jan 14 '12 at 18:01

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote

For the person who's done this before I've looked at many tools and there are many pitfalls in doing that. The cheapest way I found to do this was to do a text dump, convert, and load.

My setup was MSSQL 2005 to PostgreSQL 8.x on Linux. The reason I say convert is because in the bulk bcp dump there is no way to distinguish between '' and NULL so the COPY command I used to load was complaining about NULL value for a table that had '' as a part of the primary key.

Dumping the schema also required conversion since the types are not exactly compatible, so it's been a bit of work but no buying of data migration tools which can be expensive for what may be just one time use.

There are other tools like this one but from what I have tested(2 years ago) has limitations.

HTH

share|improve this answer
    
@a_horse_with_no_name Corrected. Force of habit. –  Karlson Jan 13 '12 at 18:12
1  
Postgres certainly treats '' as an empty string. Oracle treats an empty string as NULL. Are you sure this wan't your migration tool that changed that? –  a_horse_with_no_name Jan 13 '12 at 18:20
    
@a_horse_with_no_name I was actually writing the scripts myself. When I was trying to do a bulk import into PostgreSQL it was complaining that I couldn't do that because NULL cannot be part of the primary key. Bulk import by default interpreted this as NULL –  Karlson Jan 13 '12 at 18:30
1  
so it's a problem of that "bulk import" you were using. If that was the COPY command you can tell it to not treat '' as a NULL value. If it was some other tool, you will need to check that tool's manual. Postgres can definitely use '' as a value for a not-null column. –  a_horse_with_no_name Jan 13 '12 at 18:33
    
For me I had to as in the text dump there is no way to distinguish between the '' and what NULL comes out to be. Thankfully for me the table this happened in was deprecated. –  Karlson Jan 13 '12 at 19:05
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.