Migrate PostgreSQL database to SQL Server

Posted on by Moran Jeffers

Although PostgreSQL is powerful open-source database management system, sometimes its wide range of features and capabilities appears overwhelming for particular projects.In this case organizations may consider transferring their database to another DBMS that is easier to use. The main reason of such migration is to reduce cost of database maintenance. 

Here is the sequence of steps to migrate database from PostgreSQL to SQL Server: 

  • definitions of tablesmust be exportedfrom the source database as CREATE-statements
  • those SQL statements must be converted into the target format
  • load the resulting statements to the target SQL Server
  • export PostgreSQL data into INSERT-statements
  • convert these statements into MS SQL format
  • load it into the target database

PostgreSQL table definitions can be exported into data definition language script via the following command:

pg_dump–schema-only –no-owner –no-privileges the_db_name>table_definitions.sql

The resulting script must be corrected before loading to SQL Server as follows:

  • replace double quotes around database object (table, column, index, constraint) names by square brackets 
  • remove square brackets around types
  • replace default PostgreSQL schema “public” by SQL Server default schema “dbo”
  • replace all PostgreSQL “SERIAL” types by SQL Server equivalent “INT IDENTITY(…)” 
  • convert all data types that are not supported by SQL Server into text form
  • replace the PostgreSQL query terminator “;” with the MS SQL one “GO”

PostgreSQL data can be exported into INSERT-statements via the following command line: 

pg_dump–data-only –column-inserts the_db_name>load_data.sql

Another option to migrate database from PostgreSQL to SQL Server is to use the Integration Services. Here is the sequence of necessary steps: 

  • Open a new Integration Services Project in the SQL Server Business Intelligence Development Studio
  • Drag and drop the Data Flow task to the design pane in the Toolbox. Create data flow by double clickingon the ‘Data flow Task’
  • In the toolbar drag and drop the OLE DB source and destination combiningboth tasks with the green arrow
  • Create new OLE DB connection manager by double clicking on the ‘OLE DB Source’ task and pressing‘New’ button. Also press ‘New’ button in the ‘Configure OLE DB Connection Manager’ dialog.
  • Select the option ‘PostgresSQL Native Provider’ in the Connection Manager
  • Enter the name of PostgreSQLhost in the ‘Server or File name’
  • Activate ‘Use a specific user name and password’ option and check that saving passwordsis allowed. Specify the user to connect to PostgreSQL and the user password.  
  • Select the database name used to create table andpush OK button twice.
  • In the design pane, double click the OLE DB Destination task, select the Native OLE DB\SQL Server Native Client and provide necessary information to configure SQL Server target database
  • In the OLE BD Destination Editor, click the Mapping page and specify mapping between PostgreSQL to SQL Server tables

 

As you may see both approaches require a lot of manual work and become tedious process for large and complex databases. Moreover, there is risk of data loss or corruption connected with the human factor. To avoid it and make the database migration from PostgreSQL to SQL Server a fully automated process, special database conversion tool may be used. 

PostgreSQL to SQL Server converter is one of such tools provided by Intelligent Converters, a software company specializing in database migration and synchronization for all popular DBMS since 2001. The database conversion tool has all necessary features:

  • All versions of PostgreSQL and Microsoft SQL (including Azure SQL)are supported
  • Indexes are converted with all necessary attributes
  • Option to merge PostgreSQL data into an existing MS SQL tables
  • Command line support
  • Stores conversion settings into profile
  • Unicode support

PostgreSQL to SQL Server converter runs on Windows XP(SP2 or SP3)/2003/Vista/Server 2008/7/8/10. It requires PostgreSQL and MS SQL environment (server or client componentsmust be installed on the same machine where program is running).

Related Posts