{"id":210,"date":"2021-11-20T06:47:34","date_gmt":"2021-11-20T06:47:34","guid":{"rendered":"https:\/\/originsofttech.net\/?p=210"},"modified":"2021-11-20T06:47:34","modified_gmt":"2021-11-20T06:47:34","slug":"ms-sql-to-postgresql-migration-strategies","status":"publish","type":"post","link":"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/","title":{"rendered":"MS SQL to PostgreSQL migration strategies  \u00a0"},"content":{"rendered":"<p><span style=\"font-weight: 400;\">Many companies follow the long-term modern trend to migrate databases from commercial DBMS to free open-source equivalent in order to reduce total cost of ownership. <\/span><a href=\"https:\/\/www.postgresql.org\/\"><b>PostgreSQL<\/b><\/a><span style=\"font-weight: 400;\"> 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.<\/span><\/p>\n<p><b>The most straight forward way to migrate from MS SQL to PostgreSQL is based on popular extract-transfer-load (ETL) model:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">All table definitions, indexes and constraints are extracted from SQL Server database in form of CREATE-statements<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">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<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Next step is to export SQL Server data into CSV files as external intermediate storage<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">The CSV files must be transformed to comply with PostgreSQL format when it is necessary<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Finally, the transformed data must be loaded to the target database<\/span><\/li>\n<\/ul>\n<p><b>This is how to extract definitions of MS SQL tables in required format:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">InMS SQL 2008 and earlier versions open Management Studio, right-click on the database name, then click on \u2018Tasks &gt; Generate Scripts\u2019 menu item. Make sure that &#8220;data&#8221; checkbox is off.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">In MS SQL 2012 and later versions open Management Studio, right-click on the database name, then click on \u2018Tasks &gt; Generate Scripts\u2019 menu item. In appeared window go to &#8220;Set scripting options&#8221; tab, click on Advanced link and select &#8220;data and schema&#8221; in the \u2018General\u2019 section.<\/span><\/li>\n<\/ul>\n<p><b>Don\u2019t forget to transform the resulting script according to PostgreSQL format before moving to the next step:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">remove square brackets around types<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">replace all square brackets around names of database entries by double quotes<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">replace all occurrences of schema &#8220;dbo&#8221; by &#8220;public&#8221;<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">remove all optional MS SQL keywords that are not supported by PostgreSQL (i.e. &#8220;WITH NOCHECK&#8221;, &#8220;CLUSTERED&#8221;)<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">remove any specifications of filegroup, for example &#8220;ON PRIMARY&#8221;<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">change MS SQL auto-number types &#8220;INT IDENTITY(&#8230;)&#8221; by &#8220;SERIAL&#8221;<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">convert types that are not supported by PostgreSQL into equivalents (i.e. &#8220;DATETIME&#8221; becomes &#8220;TIMESTAMP&#8221;, &#8220;MONEY&#8221; becomes NUMERIC(19,4))<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">replace SQL Server query terminator &#8220;GO&#8221; by the PostgreSQL one &#8220;;&#8221;<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">Next step of the database migration procedure is to process the data, which can be accomplished with the use of the MS SQL Management Studio.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Right-click on database, then click Tasks, Export Data<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Using intuitive interface of the wizard specify &#8220;Microsoft OLE DB Provider for SQL Server&#8221; as data source and &#8220;Flat File Destination&#8221; as destination.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">After the export is completed, the resulting data will appear in the specified destination file according to the comma-separated values (CSV) format.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Now it is time toload data from CSV files to PostgreSQL tables. For this purpose, use the &#8220;COPY&#8221; statement as follows:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">COPY &lt;table name&gt; FROM &lt;path to csv file&gt; DELIMITER &#8216;,&#8217; CSV;<\/span><\/p>\n<p><span style=\"font-weight: 400;\">If you receive a &#8220;Permission denied&#8221; error, try the &#8220;\\COPY&#8221; command instead.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">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 MS SQL to PostgreSQL within just a couple of clicks. One of such solutions is MS SQL to PostgreSQL converter, a program having all necessary features to handle migration of large and complicated databases between the two DBMS.It is developed by Intelligent Converters, software company focusing on database conversion and synchronization techniques since 2001.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Learn more about MS SQL to PostgreSQL converter at <\/span><a href=\"https:\/\/www.convert-in.com\/mss2pgs.htm\"><b>https:\/\/www.convert-in.com\/mss2pgs.htm<\/b><\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Many companies follow the long-term modern trend to migrate databases from commercial DBMS to free&#8230;<\/p>\n","protected":false},"author":1,"featured_media":82,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>MS SQL to PostgreSQL migration strategies \u00a0 - Origin Soft Tech<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MS SQL to PostgreSQL migration strategies \u00a0 - Origin Soft Tech\" \/>\n<meta property=\"og:description\" content=\"Many companies follow the long-term modern trend to migrate databases from commercial DBMS to free...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/\" \/>\n<meta property=\"og:site_name\" content=\"Origin Soft Tech\" \/>\n<meta property=\"article:published_time\" content=\"2021-11-20T06:47:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"800\" \/>\n\t<meta property=\"og:image:height\" content=\"600\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Mary Sandoval\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Mary Sandoval\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/\"},\"author\":{\"name\":\"Mary Sandoval\",\"@id\":\"https:\/\/originsofttech.net\/#\/schema\/person\/dc7e4b1baac592a7179533def6a1bc70\"},\"headline\":\"MS SQL to PostgreSQL migration strategies \u00a0\",\"datePublished\":\"2021-11-20T06:47:34+00:00\",\"dateModified\":\"2021-11-20T06:47:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/\"},\"wordCount\":580,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/originsofttech.net\/#organization\"},\"articleSection\":[\"Tech\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/\",\"url\":\"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/\",\"name\":\"MS SQL to PostgreSQL migration strategies \u00a0 - Origin Soft Tech\",\"isPartOf\":{\"@id\":\"https:\/\/originsofttech.net\/#website\"},\"datePublished\":\"2021-11-20T06:47:34+00:00\",\"dateModified\":\"2021-11-20T06:47:34+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/originsofttech.net\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MS SQL to PostgreSQL migration strategies \u00a0\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/originsofttech.net\/#website\",\"url\":\"https:\/\/originsofttech.net\/\",\"name\":\"Origin Soft Tech\",\"description\":\"Tech Blog\",\"publisher\":{\"@id\":\"https:\/\/originsofttech.net\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/originsofttech.net\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/originsofttech.net\/#organization\",\"name\":\"Origin Soft Tech\",\"url\":\"https:\/\/originsofttech.net\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/originsofttech.net\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/12\/ORIGIN-SOFT-TECH.png\",\"contentUrl\":\"https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/12\/ORIGIN-SOFT-TECH.png\",\"width\":300,\"height\":125,\"caption\":\"Origin Soft Tech\"},\"image\":{\"@id\":\"https:\/\/originsofttech.net\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/originsofttech.net\/#\/schema\/person\/dc7e4b1baac592a7179533def6a1bc70\",\"name\":\"Mary Sandoval\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/originsofttech.net\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/80c7bdc8bf681232498c9cc53122270f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/80c7bdc8bf681232498c9cc53122270f?s=96&d=mm&r=g\",\"caption\":\"Mary Sandoval\"},\"sameAs\":[\"https:\/\/originsofttech.net\"],\"url\":\"https:\/\/originsofttech.net\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MS SQL to PostgreSQL migration strategies \u00a0 - Origin Soft Tech","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/","og_locale":"en_US","og_type":"article","og_title":"MS SQL to PostgreSQL migration strategies \u00a0 - Origin Soft Tech","og_description":"Many companies follow the long-term modern trend to migrate databases from commercial DBMS to free...","og_url":"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/","og_site_name":"Origin Soft Tech","article_published_time":"2021-11-20T06:47:34+00:00","og_image":[{"width":800,"height":600,"url":"https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c.jpg","type":"image\/jpeg"}],"author":"Mary Sandoval","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Mary Sandoval","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/#article","isPartOf":{"@id":"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/"},"author":{"name":"Mary Sandoval","@id":"https:\/\/originsofttech.net\/#\/schema\/person\/dc7e4b1baac592a7179533def6a1bc70"},"headline":"MS SQL to PostgreSQL migration strategies \u00a0","datePublished":"2021-11-20T06:47:34+00:00","dateModified":"2021-11-20T06:47:34+00:00","mainEntityOfPage":{"@id":"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/"},"wordCount":580,"commentCount":0,"publisher":{"@id":"https:\/\/originsofttech.net\/#organization"},"articleSection":["Tech"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/","url":"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/","name":"MS SQL to PostgreSQL migration strategies \u00a0 - Origin Soft Tech","isPartOf":{"@id":"https:\/\/originsofttech.net\/#website"},"datePublished":"2021-11-20T06:47:34+00:00","dateModified":"2021-11-20T06:47:34+00:00","breadcrumb":{"@id":"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/originsofttech.net\/ms-sql-to-postgresql-migration-strategies\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/originsofttech.net\/"},{"@type":"ListItem","position":2,"name":"MS SQL to PostgreSQL migration strategies \u00a0"}]},{"@type":"WebSite","@id":"https:\/\/originsofttech.net\/#website","url":"https:\/\/originsofttech.net\/","name":"Origin Soft Tech","description":"Tech Blog","publisher":{"@id":"https:\/\/originsofttech.net\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/originsofttech.net\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/originsofttech.net\/#organization","name":"Origin Soft Tech","url":"https:\/\/originsofttech.net\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/originsofttech.net\/#\/schema\/logo\/image\/","url":"https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/12\/ORIGIN-SOFT-TECH.png","contentUrl":"https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/12\/ORIGIN-SOFT-TECH.png","width":300,"height":125,"caption":"Origin Soft Tech"},"image":{"@id":"https:\/\/originsofttech.net\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/originsofttech.net\/#\/schema\/person\/dc7e4b1baac592a7179533def6a1bc70","name":"Mary Sandoval","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/originsofttech.net\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/80c7bdc8bf681232498c9cc53122270f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/80c7bdc8bf681232498c9cc53122270f?s=96&d=mm&r=g","caption":"Mary Sandoval"},"sameAs":["https:\/\/originsofttech.net"],"url":"https:\/\/originsofttech.net\/author\/admin\/"}]}},"featured_image_urls":{"full":["https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c.jpg",800,600,false],"thumbnail":["https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c-150x150.jpg",150,150,true],"medium":["https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c-300x225.jpg",300,225,true],"medium_large":["https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c-768x576.jpg",640,480,true],"large":["https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c.jpg",640,480,false],"1536x1536":["https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c.jpg",800,600,false],"2048x2048":["https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c.jpg",800,600,false],"covernews-slider-full":["https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c.jpg",800,600,false],"covernews-slider-center":["https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c.jpg",667,500,false],"covernews-featured":["https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c.jpg",800,600,false],"covernews-medium":["https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c.jpg",453,340,false],"covernews-medium-square":["https:\/\/originsofttech.net\/wp-content\/uploads\/2020\/09\/c.jpg",333,250,false]},"author_info":{"info":["Mary Sandoval"]},"category_info":"<a href=\"https:\/\/originsofttech.net\/category\/tech\/\" rel=\"category tag\">Tech<\/a>","tag_info":"Tech","comment_count":"0","_links":{"self":[{"href":"https:\/\/originsofttech.net\/wp-json\/wp\/v2\/posts\/210"}],"collection":[{"href":"https:\/\/originsofttech.net\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/originsofttech.net\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/originsofttech.net\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/originsofttech.net\/wp-json\/wp\/v2\/comments?post=210"}],"version-history":[{"count":1,"href":"https:\/\/originsofttech.net\/wp-json\/wp\/v2\/posts\/210\/revisions"}],"predecessor-version":[{"id":211,"href":"https:\/\/originsofttech.net\/wp-json\/wp\/v2\/posts\/210\/revisions\/211"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/originsofttech.net\/wp-json\/wp\/v2\/media\/82"}],"wp:attachment":[{"href":"https:\/\/originsofttech.net\/wp-json\/wp\/v2\/media?parent=210"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/originsofttech.net\/wp-json\/wp\/v2\/categories?post=210"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/originsofttech.net\/wp-json\/wp\/v2\/tags?post=210"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}