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?
Sql-server – Copying the database from SQL Server to PostgreSQL
postgresqlsql server
Related Question
- Sql-server – Continuous replication between Oracle Database and Azure SQL Database
- Sql-server – LSCopy job fails on SQL Server 2005 – Insufficient space
- Sql-server – How to migrate SQL Server credentials from 2005 to 2008 R2
- Sql-server – Copy real time data from Azure SQL into Azure Data Warehouse
- Postgresql – Does the fact a table is big can impact the overall performance of a PostgreSQL server
Best Answer
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''
andNULL
so theCOPY
command I used to load was complaining aboutNULL
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.