Migrating from MySQL to PostgreSQL in 60 seconds (with Rails)
57 comments Latest by Cathy Wed, 01 Sep 2010 05:08:27 GMT
I do quite a few MySQL to PostgreSQL migrations for clients of PLANET ARGON and over the years I have gone through different methods of handling this procedure. Typically, it involved exporting data, re-importing it through some shell script that does its best to take into consideration the slight differences between the two databases.
While working on a recent project that was to be migrated from MySQL to PostgreSQL, but after some Refactoring had been done to the code base. I had been meaning to spend a few hours coming up with some simplified process of taking data from MySQL and replicating it to a new PostgreSQL database, while maintaining referential integrity.
My original idea was to build a script that referenced two seperate databases and then copied data out and inserted it into the new db after performing a few data changes. This seemed like too much work and I knew that I should be able to harness the power of Ruby and Rails in my process. My next thought? RailsFS.
In a perfect world, I would mount one instance of the application with a mysql database and another with a postgresql database… and just copy the yaml files to the postgresql and bam, it’d work, right? Well, it doesn’t quite work that way. So, I ruled out that idea.
Then I had another idea. I would build a task in my Rakefile that… imported each of those YAML files into PostgreSQL!
We can now type in the following commands:
This first creates my PostgreSQL database tables using Migrations.rake migrate VERSION=1then…
rake mysql2pgsqlmysql2pgsql runs…and approx 60 seconds later, I had an exact copy of the MySQL database in PostgreSQL.
But wait! PostgreSQL has SEQUENCES… so I then run:
rake migrate VERSION=2...and my SEQUENCES are ALTER’d.
Yes, I will post some code in the near future. But all I am doing is basically running through all the YAML files that are available from RailsFS and adding them into the new database. It automatically figures out the model name…and is fairly generic at the moment. However, I’d like to spend a bit more time stress-testing it before I post it. :-)
I want to try to work on a RailsFS-less version as well so that I can run this on my PowerBook.
Until next time… have fun!
Enjoying the content? Be sure to subscribe to my RSS feed.
Greetings Robby,
I’m looking at migrating from mysql to PostgreSQL and google turned up this article.
I know this is going back 2 years now – have you any updates on these rake tasks?
thanx.Jodi
Jodi,
I’d need to do some digging around for this. I was using FUSE, which just got ported to OSX… so I might be able to write up another example with that soon.
Email me to remind me!
I just performed a mysql to postgresql migration using rails and yaml that doesn’t rely on RailsFS. I summarized the process under How to change databases using Ruby on Rails.
I hope people find it useful. I’d also love to see your approach in more detail, Robby.
http://adqrbswffqseb.host.com desk3 [url=http://adqsbswffqseb.host.com]desk4[/url] [link=http://adqabswffqseb.host.com]desk6[/link]
Where is RailsFS now that _why is gone?
Thank you for your sharing.
jimmy choo shoes 激安ブランドの店 Air Jordan 2009 (2K9)
Manolo Blahnik Shoes 激安ブランド財布コピー Air Jordan 21 (XXI)
Welcome to visit our website http://www.jordanmvp.com .Thanks !
Christian louboutin shoes are high in quality and reasonable in price. It is the best choice for you. It can make you more beautiful and charmful.
http://www.christianlouboutinpub.com">christian louboutin http://www.christianlouboutinpub.com">louboutin shoes <a href=” http://www.christianlouboutinpub.co m”>christian louboutin shoes
Wonder King is a very interesting. The more wonder king zed you can get, the more stronger you are. At the same time, you can get the wonder king gold, then, some players will buy the wk zed from the game, you should sell it as a high price, wk gold is access to your pocket, why not get more gold, you only need to do is to buy wonder king zed.
MP5 Player 2GB MP5 4GB MP5 8GB MP5
MP5 Player 2GB MP5 4GB MP5 8GB MP5
Very happy to be here, to see such a good article and video. I really learned a lot, thank you very much for sharing. I agree with your ideas and opinions. And has been agreed, concern you. We are professional manufacturer of paperless recorder manufacturers, Welcome to it ,i love <a href=”http://www.umbrellabuy.com/”> Children Umbrella very much
If you are looking for the best brand new HP pavilion dv2000 Battery at the most preferred price, you have come to the right place. We provide the highest quality Battery at the lowest price with the highest level of service, all in a secure and convenient platform.
If you are looking for the best brand new http://www.adapterlist.com/hp/pavilion-dv2000.htm at the most preferred price, you have come to the right place. We provide the highest quality Battery at the lowest price with the highest level of service, all in a secure and convenient platform.
If you are looking for the best brand new http://www.adapterlist.com/hp/pavilion-dv2000.htm at the most preferred price, you have come to the right place. We provide the highest quality Battery at the lowest price with the highest level of service, all in a secure and convenient platform.
If you are looking for the best brand new HP pavilion dv2000 Battery at the most preferred price, you have come to the right place. We provide the highest quality Battery at the lowest price with the highest level of service, all in a secure and convenient platform.
Like the Air Jordan Force 3 , colorful Air Jordan Force 4 ,you may never miss it .Especially now Air Jordan Force 5 are released in today market .And the Air Jordan Force 6 will be released recently . So do not miss the chance to get it .Hope hear from you soon .Thank you !
If you want a great couple of comfortable shoes, accept up the casual and sophisticated supra shoes. These amazing Supra shoes will be a sensible asset for those with orthopedic issues and bottom problems. Pick up these designs for comfort all the way.
http://www.jordanmvp.com
http://www.salelouboutins.com
Our company is always your first choice!
http://www.nikesuprashoes.com
http://www.saleguccishoes.org
Welcome to http://www.jordan56.com. Our company wholesale well-known International brands of casual shoes, such as series of NIKE SHOES, AIR JORDANS, ADIDAS, SUPRA SHOES, GUCCI SHOES, COACH HANDBAGS, and CHRISTIAN LOUBOUTIN SHOES. Leaders promise the luxury, comfort and fashion. We provide the best quality and price. We charge for the quality. CHRISTIAN LOUBOUTIN Boots are made from 100% sheepskin and are super comfortable to wear. Thank you for your attention.
<?xml:namespace prefix = o ns = “urn:schemas-microsoft-com:office:office” />http://www.supramvp.com
<?xml:namespace prefix = o ns = “urn:schemas-microsoft-com:office:office” />There are a lot of stars are the fans of Christian Louboutin shoes such as Sarah Jessica Parker, Mariah Carey, Angelina Jolie and Victoria Beckham.
Nike air max shoes store welcome you!
Spura Shoes are made for walking. Because of the collapsible heel, running in these shoes is difficult and takes some practice. Air Jordan shoesare best used for walking and normal daily activities. coach handbagsare an indispensable beauty treatment which every woman adores across the world.
http://www.christianlouboutintrade.com
Christian Louboutinshoes do have its charms! It can not be ignored in high-heel’s world.
If you want information Men’s Nike Air Jordan shoes, then it’s a good thing that you are in luck.
Welcome to visit our supra shoeson sale and coach handbagson sale . All kinds of handbags are an indispensable beauty treatment which every woman adores across the world.
Our company will give you a good price. It’s time to show yourselves.<?xml:namespace prefix = o ns = “urn:schemas-microsoft-com:office:office” />
http://www.jordanmvp.com
http://www.salelouboutins.com
Thanks for sharing your thoughts with us.. they are really interesting.. I would like to read more from you. car dvd laptop ac adapter
I am very enjoyed for this blog. Its an informative topic. It help me very much to solve some problems. Its opportunity are so fantastic and working style so speedy. I think it may be help all of you. Thanks a lot for enjoying this beauty blog with me. I am appreciating it very much! Looking forward to another great blog. Good luck to the author! all the best!
caymanreplica?chopard?watches islandspatek world time earthquake
Nice post
Nice post
Hello every one,welcome to our website. You can find kinds of things here,if you want to buy binocularsyou can visit our website. And there have bird watching binoculars and zoom binoculars.Tobuy binoculars you will get much entertainments in life.rifle scope. If you want to take part in a concert,telescopeis necessary absolutely,so you shouldbuy telescope.And of course,therearebest binoculars,spotting scope, compact binoculars,andmonocular. There are few of stores sellingbinoculars sale in the market,but don’t worry,telescope store lives in our website.
Nike Air Force One
Nike Air Force 1
Nike Air Max
had been done to the code base. I had been meaning to spend a few hours coming up with some simplified process of taking data from MySQL and replicating it to a new PostgreSQL database, while maintaining referential integrity.
I was just browsing for related blog posts for my project research and I happened to discover yours. Thanks for the excellent information!
value city furniture vitamin shoppe Poptropica good idea…..........
This was an excellent article. I really enjoyed it.
Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts.
Your website is really useful for many people I think. You helped me in various situations because I many times I have found the useful information which was really important for me in your site. Reading this your post I have known many new things about the firefighter trainings which I have not known before. It is really great that you share for us such useful stuff. Thanks a lot one more time for the useful information and I will be waiting for more great news from you in the nearest future.
louboutin shoes, The street wear for the street smart speaks a lot about the fashion Christian Louboutin pumps concepts of Christian Louboutin shoes Line. The suave cuts and MBT shoes in theChristian Louboutin Sandals christian louboutin sale, we sale Yves Saint Laurent Slingbacks Christian Louboutin Pumps Hoodies has become a popular Christian Louboutin Boots christian louboutin sale, we sale Yves Saint Laurent Slingbacks Christian Louboutin Pumps Hoodies has become a popular Christian Louboutin Boots flair among those who choose http://www.chlsale.com to mellow on their daily garb replica bags, Christian Louboutin Slingback, Replica Handbags, The World Largest Christian Louboutin Shoes.
Mahina Leather Louis Vuitton Mahina Leather Amelia Wallet M93761 Louis Vuitton Mahina Leather Amelia Wallet Louis Vuitton Mahina Leather Amelia Wallet M58074 Louis Vuitton Mahina Leather Amelia Wallet Louis Vuitton Mahina Leather Amelia Wallet M93762 Louis Vuitton Mahina Leather Amelia Wallet Louis Vuitton Mahina Leather Amelia Wallet M95995 Louis Vuitton Mahina Leather Amelia Wallet Louis Vuitton Mahina Leather Amelia Wallet M95996 Louis Vuitton Mahina Leather Amelia Wallet Louis Vuitton Mahina Leather Amelia Wallet M95968 Louis Vuitton Mahina Leather Amelia Wallet Louis Vuitton Mahina Leather Amelia Wallet M95549 Louis Vuitton Mahina Leather Amelia Wallet
Monogram Idylle Louis Vuitton Sarah Wallet M63007 Louis Vuitton Sarah Wallet Louis Vuitton Sarah Wallet M63006 Louis Vuitton Sarah Wallet Louis Vuitton Sarah Wallet M63008 Louis Vuitton Sarah Wallet Louis Vuitton 6 Key Holder M62993 Louis Vuitton 6 Key Holder Louis Vuitton 6 Key Holder M62991 Louis Vuitton 6 Key Holder Louis Vuitton 6 Key Holder M62992 Louis Vuitton 6 Key Holder
Each & every tips of your post are awesome.
10723044429PMSilkroad Gold | SRO Gold | Silkroad online gold |
mbt womens lami, mbt womens lami
Ed Hardy is the world’s pre-eminent brand in tattoo inspired, punk rock lifestyle products. Ed Hardy Shoes are one of the hottest shoe and worn by style-conscious celebrities and non-celebrities alike. Leather upper with classic Ed Hardy tattoo inspired design. Lightly cushioned canvas foot Rubber sole with design detail. There really is nothing like a great pair of Ed Hardy Women’s Shoes! They’re comfy, and women are reporting that they’re so beautiful they never want to take them off!
Ed hardy mens online store where various Ed hardy men’s active wear, Ed hardy men’s T-shirts, Ed hardy men’s hoodies/outwear,Ed hardy men’s swimwear, Ed hardy men’s swim trunks, Ed hardy men’s shoes, Ed hardy men’s loungewear,Ed hardy men’s sweaters. will be provided to you at the most competitive prices for your wearing pleasure. What’s more, all our Ed hardy products are the latest designs to meet your demands for fashion and individuality. So why not come here and have a look? You can always find the one that is most suitable for you
Paul Smith bag, Paul Smith men’s shoes, Paul Smith shirt are hot-sales. Believe you will find out the paul smith bags that are suitable for your needs
How you did that in 60 seconds :)...
Replica jerseys Nice article,You did a good job,and i just got one NFL Jerseys
Anyway your not alone!
Anyway your not alone!
he new db after performing a few data changes. This seemed like too much work
We are a professional exporter and wholesaler of brand fashion products,Moncler Branson Down Jacket – Grey Moncler Branson Down Jacket – Red Moncler Chartreuse Down Jackets – Grey Moncler Christmas Edition Down Jacket – Black Moncler Double Down Coat – Black，All products have good quality,fast and safe delivery without shipping fee. Our primary goal is to meet our clients’ requirement and establish mutually pleasant business relationships with you.If you are interested, please do not hesitate to contact us.
http://www.wholemass.comRead this article, I was very inspired, really appreciate it.
http://www.theasiabiz.comI like this blog very much because it is competitive, so I will continue to read.
I was just browsing for related blog posts for my project research and I happened to discover yours. Thanks for the excellent information!
welcome to: http://www.pointingtrade.com/ All kinds of world brand shoes,jeans,t-shirts,bikini,beach pants,handbags,wallets,sunglasses,belt,caps,watches etc.. Wholesale Clothing Our company specializes in manufacturing from located in CHINA and Netherlands,With more than 6 Years of experience,we are mainly dealing
with are:Nike shox，Christan Audigier t-shirt，Ed Hardy，COACH Bag，New Era cap ，Tiffany_Ring. More products. nfl jerseys Supply . COACH Bag，Nike shox.Ed Hardy t-shirt. Safe Payment.PaypalNike Shoes
Fast Shipment.5-7 days you can receive your order product. No min order request.You can order only one product CA Bikini
Free Shipping.The price on our website are including everything. http://www.pointingtrade.com/ Wholesale Shoes nfl jerseys MBT Shoes
Jordan Shoes
Nike Shoes
Christian Louboutin Shoes
Thank you for your
support. a leading worldwide wholesale company (or ucan say organization). We supply more than 100 thousand high-quality merchandise and famous brand
name products all at wholesale prices.
Now, I feel good, I started take up real income. vector conversion have never thought that there weren’t any need in big initial investment.Keep working, great job, I love it! digitizing software Thanks.Great post. I have been searching for this exact info for a while now. I will bookmark it in the public bookmarking sites to get you more traffic. stock designs Now lets see if I can do something productive with it.We supply more than 100 thousand high-quality merchandise and famous brand name products all at wholesale prices.
Honri Airclean products mainly includeairborne particle counter, biological air sampler, <a href= “http://www.honri-cn.com/pdshowtwo/firstctg_2462065_1.html”>laminar flow bench,<a href= “http://www.honri-cn.com/pdshowtwo/productshow_8224456.html”>pass box.<a href= “http://www.honri-cn.com/pdshowtwo/productshow_8224430.html”>Air capture hood,<a href= “http://www.honri-cn.com/pdshowtwo/productshow_8224453.html”>Fume hood ,<a href= “http://www.honri-cn.com/pdshowtwo/productshow_8224448.html”>Air shouer and <a href= “http://www.honri-cn.com/pdshowtwo/firstctg_2501794_1.html”>Gelbo Flex Tester System,