The Microsoft SQL (MS SQL) database management system (DBMS) is popular due to its user-friendly interface and ease of understanding. However, it has two significant drawbacks: strict licensing policies and a high cost of ownership, particularly for managing large databases. To reduce ownership costs, it is recommended to consider open-source databases like PostgreSQL. PostgreSQL is a widely used open-source DBMS that combines relational and object-oriented database functionality, offering data integrity and high reliability.
To migrate a database from SQL Server to PostgreSQL, the following process is required.
- Export SQL Server table definitions:
- For SQL 2008 and earlier versions, right-click on the database in Management Studio, select Tasks, and then Generate Scripts. Ensure that the “data” option is set to false (default).
- For SQL 2012 and later versions, follow the same steps but on the “Set scripting options” tab, click on Advanced, and choose “data only” or “data and schema” for “Types of data to script” (in the General section).
Correct the resulting script before proceeding to the next step.
- Load the statements exported from SQL Server to PostgreSQL with the following pre-processing:
- Remove SQL Server specific statements such as “SET ANSI_NULLS ON,” “SET QUOTED_IDENTIFIER ON,” and “SET ANSI_PADDING ON.”
- In database object names all square brackets must be replaced by double quotes.
- Replace the default SQL Server schema “dbo” with PostgreSQL’s “public.”
- Remove all optional keywords not supported by the target DBMS, such as “WITH NOCHECK” and “CLUSTERED.”
- Remove all references to file groups (e.g., “ON PRIMARY”) since this feature is not supported by PostgreSQL.
- Auto-increment attribute of integer type “INT IDENTITY(…)” must be converted into PostgreSQL SERIAL data type.
- Carefully map all SQL Server data types that are not supported by PostgreSQL. For example, DATETIME must be converted into TIMESTAMP, MONEY must be converted into NUMERIC(19,4).
- Replace the SQL Server query terminator “GO” with the PostgreSQL terminator “;”.
- The next step is to process the data, which can be done using SQL Server Management Studio:
- Right-click on the database, select Tasks, and then Export Data.
- Go through the wizard and choose “Microsoft OLE DB Provider for SQL Server” as the data source and “Flat File Destination” as the destination.
- Once the export is performed, the data will be in the destination file in comma-separated values (CSV) format.
- If some tables contain binary data, a workaround is required. On the wizard page called “Specify Table Copy or Query,” select the option “Write a query to specify the data to transfer.” On the next page called “Provide a Source Query” design the following SELECT-query to process the binary data:
SELECT
nonbinaryfield1,
nonbinaryfield2,
cast( master.sys.fn_varbintohexstr(
cast( binaryfield as varbinary(max))) as varchar(max)
) as binary-field-name
FROM
mssql_table_name
- To load the resulting CSV file into the target PostgreSQL table, use the “COPY” command:
COPY <table name> FROM <path to CSV file> DELIMITER ‘,’ CSV.
If you encounter a “Permission denied” error message with the “COPY” command, try using the “\COPY” command instead.
It’s important to note that SQL Server to PostgreSQL database migration requires significant effort and can be a complex process. Manual conversions are costly, time-consuming, and prone to data loss or corruption. However, there are modern tools available that facilitate data conversion and migration between the two DBMS with just a few clicks.
One such tool is SQL Server to PostgreSQL converter provided by Intelligent Converters, a vendor specializing in database conversion and synchronization since 2001. This tool enables direct connection to both source and target databases, offering high-quality conversion without the need for ODBC drivers or middleware components. SQL Server to PostgreSQL converter can handle migration of the following database entries: schema, data, indexes, constraints, foreign keys and views. It also supports scripting, automation, and scheduling of conversions.
About the Author
