Nowadays many companies migrate their databases to free open-source DBMS in order to reduce total cost of ownership. PostgreSQL looks really good option among all free systems because it is 100% compliant with SQL standard, supports point-in-time recovery and sophisticated locking mechanisms, provides advanced data types such as multi-dimensional arrays andspatial.
The popular extract-transfer-load (ETL) approach is the most straight forward method to migrate from SQL Server to PostgreSQL. It consists of the following steps:
- All table definitions, indexes and constraints are extracted from SQL Server database in form of CREATE-statements
- Those statements are converted according to PostgreSQL specification of CREATE-queries (with respect to types mapping and naming rules) and loaded to the target database
- Next step is to export SQL Server data into CSV files as external intermediate storage
- The CSV files must be transformed to comply with PostgreSQL format when it is necessary
- Finally, the transformed data must be loaded to the target database
This is how to extract definitions of SQL Server tables in required format:
- InSQL Server 2008 and earlier versionsopen Management Studio, right-click on the database name, then click on ‘Tasks > Generate Scripts’ menu item. Make sure that “data” checkbox is off.
- In SQL Server 2012 and later versionsopen Management Studio, right-click on the database name, then click on ‘Tasks > Generate Scripts’ menu item. In appeared window go to “Set scripting options” tab, click on Advanced link and select “data and schema” in the ‘General’ section.
Don’t forget to transform the resulting script according to the target format before moving to the next step of SQL Server to PostgreSQL migration:
- remove square brackets around types
- change square brackets around database object names by double quotes
- replace all occurrences of schema “dbo” by “public”
- remove all optional SQL Server keywords that are not supported by PostgreSQL (i.e. “WITH NOCHECK”, “CLUSTERED”)
- remove anyspecifications of filegroup, for example “ON PRIMARY”
- change SQL Server auto-number types “INT IDENTITY(…)” by “SERIAL”
- convert types that are not supported by PostgreSQL into equivalents (i.e. “DATETIME” becomes “TIMESTAMP”, “MONEY” becomes NUMERIC(19,4))
- replace SQL Server query terminator “GO” by the PostgreSQL one “;”
The next move to migrate a database from SQL Server to PostgreSQL is processing the data, which can be accomplished with the use of the SQL Server Management Studio by highlighting the database and selecting Tasks > Export Data menu item. Then pass through allsteps of the appeared wizard, select “Microsoft OLE DB Provider for SQL Server” as data source, and “Flat File Destination” as destination.
As soon as the export is finalized, the exported data will exists in the destination file in the comma-separated values (CSV) format.
Next step of SQL Server to PostgreSQL migration is toload data from CSV files to the target tables. For this purpose, use the “COPY” statement as follows:
COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;
If you receive a “Permission denied” error, try the “\COPY” command instead.
The steps above illustrate that manual conversionisa time-consuming procedure with high risk of data loss or corruption. Fortunately, there are some special tools which can migrate database from SQL Server to PostgreSQL within just a couple of clicks. One of such solutions is SQL Serverto PostgreSQL converter, a program having all necessary features to handle migration of large and complicated databases between the two DBMS.This producthas beenproduced by Intelligent Converters, software company focused on database migration and synchronization techniques since 2001.
