5

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?

Improve this question
4
  • if you have Sql Server, you can use the data migration wizard included with Integration Services. Commented Jan 13, 2012 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? Commented Jan 13, 2012 at 14:56
  • Is that 1TB for just the database or does that include transaction logs? Commented Jan 13, 2012 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. Commented Jan 13, 2012 at 16:21

2 Answers 2

Reset to default
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
7
  • @a_horse_with_no_name Corrected. Force of habit. Commented Jan 13, 2012 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? Commented Jan 13, 2012 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 Commented Jan 13, 2012 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. Commented Jan 13, 2012 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. Commented Jan 13, 2012 at 19:05
2

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\edb\mtk\lib Use the JTDS driver for SQL Server, not the JDBC driver you get from Microsoft.

UPDATE

If you want to use some of the more obscure options, they go after the runMTK.bat command, not at the end. Otherwise the errors are non-descript.

c:\Program Files\edb\mtk\bin>runMTK.bat -truncLoad -dataOnly -tables table_test -sourcedbtype sqlserver -targetdbtype postgresql -targetSchema public dbo

Improve this answer
3
  • "Use the JTDS driver for SQL Server" is a pretty bad advice. It hasn't been updated in over 8 years and I doubt it can connect to any modern SQL Server version (lacking e.g. implementation for current TLS/SSL protocols). The Microsoft JDBC driver is way better than the abandoned jTDS driver. Commented Dec 10, 2021 at 22:23
  • Shouldn’t matter as the OP is using SQL Server 2000 Commented Dec 11, 2021 at 10:29
  • I agree that the JTDS driver is ancient and to be avoided but the EnterpriseDB Migration Toolkit doesnt work with the MS jdbc driver. jtds did 400k rows in 20 seconds with postgres lower case and correct data types, to a cloud sql instance no less. openquery with odbc was 45 minutes to do 25k rows. Commented Dec 11, 2021 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.