4

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?

  • 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
2

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.

| 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
0

Answer for 2020 Use the PostreSQL Migration Toolkit. This will map data types and maintain foreign keys. The utility is free and created by the postgres maintainers, EnterpriseDB.

The command is a little different for MSSQL because you cannot use "dbo" as a postgres schema. It should look like this:


C:\Program Files (x86)\edb\mtk\bin                                                              
λ runMTK.bat -sourcedbtype sqlserver -targetdbtype postgresql -targetSchema public dbo          
Running EnterpriseDB Migration Toolkit (Build 53.0.1) ...                                       
Source database connectivity info...                                                            
conn =jdbc:jtds:sqlserver://localhost:1433/circfilm                              
user =postgres                                                                                  
password=******                                                                                 
Target database connectivity info...                                                            
conn =jdbc:postgresql://127.0.0.1:5432/circfilm                                                 
user =postgres                                                                                  
password=******                                                                                 
Connecting with source SQL Server database server...                                            
Connected to Microsoft SQL Server, version '12.00.6372'                                         
Connecting with target Postgres database server...                                              
Connected to PostgreSQL, version '12.2'                                                         
Importing sql server schema dbo...                                                              
Creating Tables...                                                                              
Creating Table: films                                                                           
Created 1 tables.                                                                               
Loading Table Data in 8 MB batches...                                                           
Loading Table: films ...                                                                        
[films] Migrated 1808 rows.                                                                     
[films] Table Data Load Summary: Total Time(s): 0.332 Total Rows: 1808 Total Size(MB): 0.8710937
Data Load Summary: Total Time (sec): 0.454 Total Rows: 1808 Total Size(MB): 0.892               

Schema dbo imported successfully.                                                                                                                                                     

Migration process completed successfully.                                                       

Migration logs have been saved to C:\Users\user\.enterprisedb\migration-toolkit\logs          

******************** Migration Summary ********************                                     
Tables: 1 out of 1                                                                              

Total objects: 1                                                                                
Successful count: 1                                                                             
Failed count: 0                                                                                 
Invalid count: 0                                                                                

*************************************************************                                   

C:\Program Files (x86)\edb\mtk\bin                                                              
λ                                                                                               

The drivers for both SQL Server and postgres should be copied here: C:\Program Files\Java\jre1.8.x_xxx\lib\ext

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy