SQL Server to PostgreSQL Tutorial

[SQL Server to PostgreSQL Converter] [Introduction to Migration] [FAQ] [Command Line]

The process of database migration from SQL Server to PostgreSQL consists of the following steps:

Select Migration Mode
Connect to SQL Server
Connect to PostgreSQL
Select Databases
Select Tables
Select Views

Step 1 of 6. Select Migration Mode

Specify how to convert SQL Server database into PostgreSQL format:

Migrate to PostgreSQL server directly - use this approach to migrate database from SQL Server to PostgreSQL server directly. You need PostgreSQL server launched and also necessary privileges to write into database on the target server. See Connect to PostgreSQL Server section for related information.

Export into script file - use this approach to perform deferred conversion and get more control over the process. Following this way, the program stores the data into a local script file instead of moving it to PostgreSQL server directly. The resulting file contains SQL statements to create all tables and to fill them with the data. See Import PostgreSQL script files article for related information.

SQL Server to PostgreSQL: Select Migration Mode

Step 2 of 6. Connect to SQL Server

This wizard screen allows to provide necessary information for connection to SQL Server or Azure SQL. In "MS SQL Server" field the name of SQL Server instance must be specified. Leave it empty if connecting to the local server. Custom SQL Server port (other than 1433) can also be specified in "MS SQL Server" field as follows:

Sql_server_name,1234

where 1234 is the custom port. Then select the type of authentication to use when connecting to SQL Server:

Windows authentication - SQL Server validates the connection via Windows user information. The user has to be authenticated by Windows before access is granted.
SQL Server authentication - SQL Server will use the standard security validation. To use this option, specify user name and password recognized on the target SQL Server.

SQL Server to PostgreSQL Migration: Connect to SQL Server

In "SQL Server authentication" mode SQL Server to PostgreSQL converter lists database tables owned by the specified user only. You can change it using "Connect as database owner" checkbox. It will force the program to display all tables from "dbo" tablespace, but only if the specified user has enough privileges to read them. Otherwise, the option will not take effect.

If connection to SQL Server failed, read How to configure SQL Server article for possible workaround.

If you are connecting to Azure SQL, see How to connect to Windows Azure SQL article for related information.

Step 3 of 6. Connect to PostgreSQL Server

This wizard screen collects information to connect to PostgreSQL server. The SQL Server to PostgreSQL converter is able to connect to both local and remote PostgreSQL servers.

By default SQL Server to PostgreSQL converter uses local connection mode. It is indicated by radio button "Local" selected. For remote connection select "Remote" radio button, specify the host (network name or IP address) and the port. The port value is necessary only if it differs from the default PostgreSQL port number 5432. Otherwise you may leave this field as is.

Enter user name and password or leave these fields blank for anonymous connection.

SQL Server to PostgreSQL Migration: Connect to PostgreSQL

By default SQL Server to PostgreSQL converter connects to database with name equal to the username specified. If there is no such database or PostgreSQL server does not allows access to it, custom database may be specified on this wizard page.

Step 4 of 6. Select Databases

On this wizard page SQL Server and PostgreSQL database (or script file, if you selected the corresponding option on the 1st wizard page) must be specified. Drop down the combo box to see all available SQL Server databases. This feature requires the appropriate permissions and does not work for Azure SQL.

SQL Server to PostgreSQL converter can process existing target database as follows:

Overwrite the entire database
Overwrite existing tables only
Skip existing tables
Merge
Synchronize

SQL Server to PostgreSQL Migration: Select Databases

Existing PostgreSQL script file will be overwritten. Also, there are conversion settings to customize:

Convert table definitions only - select this checkbox to convert only table definitions of SQL Server database. All the data will not be migrated.
Skip converting indexes - select this checkbox if you don't want to convert indexes
Make names case sensitive - select this checkbox to make table and column names case sensitive. Such names must be enclosed in quotes when composing query.
View tables owned by the user - by default the program lists all tables found in the database specified. Select this checkbox to work with tables owned by the specified SQL Server user only.

Step 5 of 6. Select Tables

This wizard page is designed to manage the list of SQL Server or Azure SQL tables. To add new table for converting it into PostgreSQL format, select it in "Available tables" list box and click "Add" button (or double-click the selected item). Use "Add all" button to add all available tables. To remove an item from "Selected tables" list highlight it and click "Remove" button. Use "Remove all" button to remove all items from "Selected tables" list box.

Push "Add Query" button to add SELECT-query filtering data that will be migrated from SQL Server to PostgreSQL. See Using Queries article for the further information.

To edit table or query double click the corresponding item in "Selected tables" list box. If it is a table, dialog box appears to edit table attributes and to define custom type mapping. See Edit Table article for the further information. If selected item is a query, SQL Server to PostgreSQL converter displays the dialog box to edit SQL-statement of the query.

SQL Server to PostgreSQL Migration: Select Tables

Step 6 of 6. Select Views

This wizard page is designed to manage list of SQL Server views for migration. "Available queries" list box contains all available views of the SQL Server database except those are selected already.

To add new view for migrating from SQL Server to PostgreSQL, highlight it in "Available queries" list box and click "Add" button (or double-click the selected item). Use "Add all" button to add all available SQL Server views. To remove an item from "Selected queries" list you have to highlight it and click "Remove" button. Use "Remove all" button to remove all SQL Server views from "Selected queries" list box.

Sometimes it is important to convert views in the particular order to preserve dependencies. Use "up" and "down" buttons in right part of the wizard page to organize all queries in the appropriate order.

SQL Server to PostgreSQL Migration: Select Views