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 more easy 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:
- export table definitions from the source SQL database
- convert them into SQL Server 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”
- replaceall 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 the SQL Server Business Intelligence Development Studio and open a new Integration Services Project
- Drag and drop the Data Flow task to the design pane in the Toolbox. Double click in the Data flow Task in order to create a Data Flow
- In the toolbar drag and drop the OLE DB source and destination combiningboth tasks with the green arrow
- double click on the OLE DB Source task and press the New button to create a new OLE DB connection manager. In the Configure OLE DB Connection Manager, press ‘New’ button.
- In the Connection Manager, select the Native OLE DB\PostgresSQL Native Provider
- In the Server or File name, specify the name of the Postgres Server
- Select the option ‘Use a specific user name and password’. Check the allow saving password option. Specify the user to connect to PostgreSQL and the user password.
- In the initial catalog, select the database used to create the table. Press OK 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).