MS SQL to PostgreSQL migration strategies

Posted on by

Nowadays many companies migrate their databases to free open-source DBMS in order to reduce total cost of ownership. PostgreSQL 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 and spatial.

The most straight forward way to migrate from MS SQL to PostgreSQL is based on popular extract-transfer-load (ETL) model:

  • All table definitions, indexes and constraints are extracted from SQL Server database in form of CREATE-statements
  • 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
  • Next step is to export SQL Server data into CSV files as external intermediate storage
  • The CSV files must be transformed to comply with PostgreSQL format when it is necessary
  • Finally, the transformed data must be loaded to the target database

This is how to extract definitions of MS SQL tables in required format:

  • In MS SQL 2008 and earlier versions open Management Studio, right-click on the database name, then click on ‘Tasks > Generate Scripts’ menu item. Make sure that “data” checkbox is off.
  • In MS SQL 2012 and later versions open Management Studio, right-click on the database name, then click on ‘Tasks > Generate Scripts’ menu item. In appeared window go to “Set scripting options” tab, click on Advanced link and select “data and schema” in the ‘General’ section.

Don’t forget to transform the resulting script according to PostgreSQL format before moving to the next step:

  • remove square brackets around types
  • change square brackets around database object names by double quotes
  • replace all occurrences of schema “dbo” by “public”
  • remove all optional MS SQL keywords that are not supported by PostgreSQL (i.e. “WITH NOCHECK”, “CLUSTERED”)
  • remove any specifications of filegroup, for example “ON PRIMARY”
  • change MS SQL auto-number types “INT IDENTITY(…)” by “SERIAL”
  • convert types that are not supported by PostgreSQL into equivalents (i.e. “DATETIME” becomes “TIMESTAMP”, “MONEY” becomes NUMERIC(19,4))
  • replace SQL Server query terminator “GO” by the PostgreSQL one “;”

The next move is to process the data, which can be accomplished with the use of the MS SQL Management Studio.

  • Right-click on database, then click Tasks, Export Data
  • Go through the wizard and select “Microsoft OLE DB Provider for SQL Server” as data source, and “Flat File Destination” as destination.

As soon as the export is finalized, the exported data will exists in the destination file in the comma-separated values (CSV) format.

Now it is time to load data from CSV files to PostgreSQL tables. For this purpose, use the “COPY” statement as follows:

COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;

If you receive a “Permission denied” error, try the “\COPY” command instead.

The steps above illustrate that manual conversion is a 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.

 

Posted in Technology and tagged .

104 Comments

  1. trouble ejaculating when using viagra
    viagra buy viaonlinebuy.us best online pharmacy for viagra
    over the counter viagra substitute cvs

  2. Hey there would you mind stating which blog platform you’re working with? I’m planning to start my own blog in the near future but I’m having a hard time making a decision between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design and style seems different then most blogs and I’m looking for something completely unique. P.S Sorry for being off-topic but I had to ask!

  3. donde comprar viagra sin receta
    buy viagra canada https://viaonlinebuy.us/ viagra pills
    can you take viagra with antibiotics

  5. what drugs are contraindicated with viagra
    women’s testimionial when taking viagra
    how long do most men stay erect with viagra

  6. viagra 100mg street price
    <a href="https://viaonlinebuy.us/#">viagra without a doctor prescription</a>
    how many men over 60 take viagra

  8. I am sure this paragraph has touched all the internet people, its really really nice paragraph on building up new webpage.

  13. viagra 100mg street value
    <a href=https://viaonlinebuy.us/#>buy viagra online canada</a>
    how hard is it to get a presription for viagra

  16. how do i take viagra to get the best results
    where to buy viagra online https://www.v1agrabuy.com viagra without doctor prescription
    what is the cost per pill of viagra with cvs

  17. viagra dose for 270 lbs
    cheapest viagra www.v1agrabuy.com best online pharmacy for viagra
    where to buy cheap viagra 100mg online without prescription

  18. I’ve been surfing on-line greater than 3 hours today, but I by no means discovered any interesting article like yours. It is pretty price sufficient for me. In my view, if all website owners and bloggers made good content as you probably did, the net can be much more helpful than ever before.

  24. Greetings! Very helpful advice within this post! It is the little changes that make the biggest changes. Thanks a lot for sharing!

  25. where can i find samples of viagra today
    <a href=https://www.v1agrabuy.com/#>when to buy viagra</a>
    how do they reverse viagra

  30. is viagra and sildenafil the same
    <a href="https://www.v1agrabuy.com/#">where to buy viagra online</a>
    premature ejaculation 2 minutes on viagra less than 1 minute without

  32. Incredible! This blog looks just like my old one! It’s on a completely different subject but it has pretty much the same page layout and design. Outstanding choice of colors!

  33. she said no after i took viagra
    buy viagra https://viaonlinebuy.us/ viagra pills
    does georgia require a video cosultation for viagra

  34. using viagra after gastric bypass surgery
    viagra pills https://viaonlinebuy.us/ viagra without doctor prescription
    where can i buy viagra without prescription

  35. how does viagra work and what are the side effects
    viagra without doctor prescription https://viaonlinebuy.us online viagra
    closest thing to viagra

  38. Hi, i think that i saw you visited my site thus i came to "return the favor".I’m attempting to find things to improve my web site!I suppose its ok to use a few of your ideas!!

  39. will a 25mg daily dose of viagra help ed
    <a href=https://viaonlinebuy.us/#>viagra vs cialis</a>
    can you take viagra with high blood pressure

  42. what is a substitute for viagra
    what is the normal milligram dosage of viagra prescribed
    viagra recetesiz satilir mi

  43. how to get viagra predcription cheaper on medicaid
    gayfuror viagra
    what would happen if you insert viagra into head of penis

  46. how long does viagra expire
    generic viagra trusttnstore.com buy viagra online
    what over the counter drug is similar to viagra

  47. These are truly fantastic ideas in concerning blogging. You have touched some fastidious things here. Any way keep up wrinting.

  48. when is the generic for viagra available
    sildenafil https://trusttnstore.com/ online cialis
    how can i buy viagra online without a prescription?

  51. how much does one viagra pill cost
    viagra 100mg https://trusttnstore.com/ cialis generic
    at what point should a man consider viagra

  53. what schedule drug is viagra
    <a href=https://trusttnstore.com/#>viagra prices</a>
    how much water to drink with viagra

  57. generic viagra soft tabs 100mg
    buy cialis online https://slimex365.com/3hrud cialis generic
    where to get viagra today

  59. who makes the generic viagra
    viagra sale trusttnstore.com cialis 20mg
    premature ejaculation 2 minutes on viagra less than 1 minute without

  61. what do doctors do for viagra erection over 4 hours
    <a href="http://gongpo.moum.kr/yD2t#">viagra cheap</a>
    viagra after prostatectomy

  63. viagra replacement pills rite aid
    <a href=http://xsle.net/3hh24#>cialis generic</a>
    when to start viagra after prostatectomy

  66. natural viagra for womenhow to use viagra for first time
    generic viagra canada price https://popularedstore.com/ viagra without doctor prescriptionbuy cialis online
    cheap viagra pills onlineviagra porn videos

  67. what does viagra goes for on the street
    female viagra pillsgeneric sildenafil https://popularedstore.com/ cialis vs viagracialis tadalafil
    viagra how long does it lastwhat store to get viagra

  68. how long does it take to get a prescription of viagra
    <a href=https://jtbtigers.com/3f0rj#>viagra online</a>
    what is the cost of 30 viagra at walmart

  70. Hi would you mind stating which blog platform you’re using? I’m looking to start my own blog soon but I’m having a difficult time choosing between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design and style seems different then most blogs and I’m looking for something unique. P.S Apologies for being off-topic but I had to ask!

  71. can i take viagra while on amlodipine atenolol lisinopril and atorvastatin
    tadalafil tablets https://lmeed.com/ viagra dosage
    how much is a bottle of viagra

  72. how long will viagra work for how long does viagra stay in your system
    viagra samples generic viagra sildenafil citrate https://edzxc.com/ sildenafil coupon
    buy generic 100mg viagra online

  73. how long before viagra works how often can i take viagra
    tadalafil without a doctor’s prescription buy sildenafil citrate 100mg online https://edphrm.com/ viagra 100mg
    lowest price viagra

  74. when will viagra be generic
    cialis without a doctor’s prescription https://edzxc.com/ cialis prices
    how much bigger does viagra make a flaccid penis how long a window for having sex on viagra

  75. what would happen if a person crushed and snorted viagra rite aid viagra price
    viagra without a doctor prescription from canada cialis price https://aviib.com/ generic viagra trusted pharmacy cialis 5mg
    viagra commercial women

  76. how do you take liquid viagra
    generic cialis canada pharmacy free viagra samples https://lmeed.com/ canadian pharcharmy online viagra viagra online canada pharmacy
    caremore formulary 2019 does it cover viagra, cialis what happens if you take viagra daily

  77. what works faster viagra or cialis how many times per day can you take viagra
    natural viagra substitute viagra canada online pharmacy https://ingogf.com/ viagra without a doctor prescription tadalafil cialis
    viagra doesn’t work cheap viagra online

  78. can you buy viagra in mexico se puede tomar viagra despues de un cateterismo
    cialis online https://aviib.com/ generic tadalafil viagra professional
    riding viagra erection does viagra help with ejaculation

  79. do men who take viagra have orgasms how many errections can i have with viagra
    viagra erection after ejaculation generic viagra online pharmacy https://cialisdendi.com/ sildenafil generic cialis canada
    how much do 100mg viagra go for on the street

  80. viagra overdose treatment how to get a viagra sample
    cost of viagra https://edphrm.com/ generic viagra canada price
    pornstars stomach issues from viagra what kind of doctor can prescribe viagra

  81. how does viagra affect a woman over the counter alternatives to viagra
    natural viagra alternatives that work https://goeedl.com/ sildenafil citrate
    viagra after hernia surgery viagra canada online

  82. can you take viagra with antibiotics aking viagra under 18
    viagra vs cialis viagra generic availability https://cialisdendi.com/ viagra without a doctor prescription from canada
    where to buy viagra pearland home remedy viagra

  83. who is viagra for
    tadalafil 20 mg sildenafil 20 mg tablet https://edjass.com/ cheap viagra sildenafil 20 mg tablet vs viagra
    how do you tell the dosage of viagra

  84. does walmart sell viagra drogas don saludero_viagra en colombia precio
    tadalafil cialis https://edzssl.com/ online viagra viagra for women
    viagra how long what is the cheapest viagra substitute there is

  85. viagra sports performance enhancer
    sildenafil 20 mg vs viagra https://goeedl.com/ cialis side effects sildenafil 20 mg
    flomax and viagra interaction

  86. how do you know if its safe to take viagra fastest way to get a viagra prescription
    sildenafil 20 mg vs viagra https://ingogf.com/ cialis side effects sildenafil 20 mg
    viagra generic price viagra prescription discounts

  87. [url=http://folkrockmusic.ru/page/17/
    ]want to buy viagra online
    [/url]
    [url=http://rockindiemusic.ru/copywriters/
    ]can i buy viagra online from canada
    [/url]
    [url=http://hardrock4u.ru/downloads-audio/16/skachat-torrent-schumann-piano-concerto-tchaikovsky-piano-concerto-no-1-barenboim-celibidache-munchner-philharmoniker-barenboim-daniel/
    ]buy viagra in canada
    [/url]
    [url=http://gethardrock.ru/styles/rock-hard-rock/page/138/
    ]buy viagra in canada legally
    [/url]
    [url=http://electronicmusic4u.ru/downloadmp3online/label/soundtrack-various-artists/page/2/
    ]online prescription viagra buy online
    [/url]

  88. katie cummings viagra brother
    canada pharmacy viagra generic https://edjass.com/ viagra pills viagra.com
    guy squirt blood penis injecting viagra

  89. does viagra help pe how can i get free viagra samples <a href=https://nancyedpharm.com/>why was viagra patent extended</a> what competes with viagra can i take one half viagra how viagra works and side effects

  90. [url=http://www.moscow-penobeton.ru/contacts.html]buy viagra without consultation uk
    [/url]
    [url=http://78piterburg.ru/vstrechi/category2/vstrecha4.html]buy viagra cheapest price
    [/url]
    [url=http://hyperplay.ru/tape_development-of-a-child-4-months_1.html]do i need prescription to buy viagra canada
    [/url]
    [url=http://krasnokam.ru/1181.html]what states buy the most viagra
    [/url]
    [url=http://creativnee.ru/osobennosti_web_dizaina.html]can you buy viagra off the shelf
    [/url]

  91. [url=http://davai-pozhenimsya.ru/gifki-pjatnica-90-prikolnyh-gif-animacij-o.html]buy antibiotics and viagra online no script cheap
    [/url]
    [url=http://paypalvyvod.ru/?p=222]is it legal to buy viagra from canada
    [/url]
    [url=http://saransk-fans.com/index/image/image/image/image/image/index.php?d=pages&p=articles_pov_fans_fans03]what is the best site to buy generic viagra
    [/url]
    [url=http://autoshed.ru/2019/01/02/%D0%B3%D0%BE%D0%BB%D0%BB%D0%B8%D0%B2%D1%83%D0%B4%D1%81%D0%BA%D0%B8%D0%B5-%D0%B0%D0%BA%D1%82%D1%91%D1%80%D1%8B-%D1%81%D0%BD%D0%B8%D0%BC%D1%83%D1%82%D1%81%D1%8F-%D0%B2-%D1%84%D0%B8%D0%BB%D1%8C%D0%BC/]buy viagra in store
    [/url]
    [url=http://car-pic.ru/texnika-dlya-sada.html]viagra safe buy over internet paypal
    [/url]

  92. viagra caseiro viagra action <a href=https://nancyedpharm.com/>how many mg of viagra should one take.</a> what happens if a girl takes a viagra viagra para la mujer compuesto

  93. how often should i take viagra pharmacy express viagra how does viagra do anything if you dont have ed <a href=https://trustgenericstore.com/>what happens if you overdoes on viagra</a> viagra not working for performance anxiety viagra natural alternatives renal and viagra

  94. <a href=http://domles43.ru/stroim-dom-byistro-kachestvenno-krasivo-httpteploblokru.html>when can you buy generic viagra
    </a> <a href=http://hear.su/quest.php_id=329.html>buy keyword qoclick com viagra
    </a> <a href=http://mosdogmastroy.ru/sberbank-poluchil-zayavki-na-3-7-mlrd-rublej-po-novoj-programme-ipoteki/>viagra nz buy
    </a> <a href=http://caramele.ru/category/stil/psikhologiya/>buy viagra online from australia
    </a> <a href=http://poedinokvkusa.ru/sendvich-s-zharenoj-ryboj-i-sousom-pesto/>buy viagra online fedex delivery
    </a>

  95. viagra boots <a href=https://viagraalexandria.com/>how to buy geneeic viagra</a> how do i get viagra without going to the doctor?

  96. he f**** her after taking viagra mexican viagra price <a href=https://trustgenericstore.com/>viagra unreliable what next</a> how much does a 100mg viagra cost viagra in a cage

  97. does viagra stop you coming early what is viagra made out of <a href=https://debviagrals.com/>how long does viagra work</a> viagra over the counter usa viagra paypal us

  98. <a href=http://s-osago.ru/index.php?p=100>where can you buy generic viagra in alexandria va
    </a> <a href=http://nailslux.ru/ManikurVDomashnihUsloviyah/idei-manikura-poshagovoe-foto-v-domashnih-usloviyah>buy viagra online with out prescriptions
    </a> <a href=http://cerkva.ks.ua/napravleniya/katolitscizm/katolicheskii-kul-t.html>buy real viagra online paypal accepted
    </a> <a href=http://vsiasemia.ru/soveti/kak-vyiti-zamyj-za-lubimogo.html>buy viagra in malaysia
    </a> <a href=http://www.aikidoofgreensboro.net/MartialArts/martial-arts-practice>buy viagra australia online
    </a>

  99. what was viagra initially used for how much horney goat weed equals one viagra <a href=https://getviragra.com/>blue pill viagra</a> make natural viagra where to buy viagra tablets

  101. does viagra keep you hard after coming? <a href=https://medvirgra.com/>viagra movie</a> other pills like viagra

  102. what happens if your dog eats a 100 mg viagra how to get viagra from your doctor symptoms of taking viagra <a href=https://getviragra.com/>blue facebook pill report</a> viagra government funding snopes how to order generic viagra online how much how many erections from a dose of viagra

  103. pushhealth viagra viagra for heart disease what does viagra look like <a href=https://medvirgra.com/>citrate sildenafil</a> is viagra a controlled substance

  104. when did viagra come out how long does it take for erection after taking viagra viagra erowid <a href=https://viagreusa.com/>viagra side effects men</a> when viagra generic

Leave a Reply

Your email address will not be published. Required fields are marked *

Human Verification: In order to verify that you are a human and not a spam bot, please enter the answer into the following box below based on the instructions contained in the graphic.