How to convert SQL Server database to PostgreSQL
|[Validate Migration] [Types] [Queries] [Triggers]|
Microsoft SQL is one of the world's most popular database management systems with user-friendly interface that is easy to learn and use. However, it has certain disadvantages such as high cost of ownership for large databases and strict licensing policy. These facts may lead some users to the need of shifting from MS SQL to another DBMS.
When planning a database migration, it is logical to review open-source systems in order to reduce total cost of ownership. There are three major and important open-source RDBMS: SQLite, MySQL and PostgreSQL.
SQLite is a self-contained file-based database system that is designed for embedding into applications. So it should not be considered as replacement for large databases in multi-user environment.
MySQL is far more powerful than SQLite and it provides a lot of features that are expected from advanced RDBMS such as security, scalability, wide range of supported storage types for different purposes. However, it does not implement the full SQL standard, does not provide full text search and has poor support for concurrent writes in some database engines.
PostgreSQL is a very powerful standards-compliant RDBMS providing integral object-oriented and/or relational database functionality. This all make it the best choice for projects that require high level of reliability and data integrity.
Now let's consider these steps more closely. This is how you can export MS SQL table definitions:
The resulting script must be corrected before loading to PostgreSQL as follows:
Now is the time to process data. It also can be done via Microsoft SQL Management Studio: right-click on database, Tasks, Export Data. Go through the wizard and select "Microsoft OLE DB Provider for SQL Server" as data source, "Flat File Destination" as destination. After the export is completed, you can see all data exported into the specified file in comma-separated values (CSV) format.
If some table contains binary data it is required to apply the workaround. Select "Write a query to specify the data to transfer" option on the wizard page called "Specify Table Copy or Query". On the next wizard page called "Provide a Source Query" you should compose SELECT-query as follows:
select <non-binary field #1>, <non-binary field #2>, cast(master.sys.fn_varbintohexstr( cast(<binary field name> as varbinary(max))) as varchar(max)) as <binary field name> from <table name>;
Unfortunately, this approach is not applied for large binary data (1MB+) since the query goes into infinite hang. You can import the resulting CSV file into PostgreSQL table via "COPY" command:
COPY <table name> FROM <path to csv file> DELIMITER ',' CSV;
If you see "Permission denied" error after running this statement, try to use "\COPY" command instead.
As you may see, database migration from Microsoft SQL to PostgreSQL is a complicated time-consuming process requiring a lot of efforts. Manual conversion is costly and slow process, it can lead to incorrect results and cause data loss or corruption. Fortunately, there are many tools to convert MS SQL to PostgreSQL, which can migrate data between those two DBMS with just a few clicks of mouse button. Intelligent Converters offer one of such solutions - SQL Server to PostgreSQL conversion tool.