No Pugs

they're evil

How to migrate typo from mysql to postgresql

I almost always use postgresql when working on a rails application. I won’t list all the little reasons why, but a major reason is for transactional DDL statements, which means when I run a migration that fails, I don’t have to then go run a bunch of cleanup queries to get my database back to how it was before the migration was ran.

When I was setting up this instance of typo, I decided I’d go ahead and go with mysql since I didn’t plan to hack on typo very much. Long story short: I decided to migrate from mysql to postgresql. This howto was done with mysql 5.0.70, postgresql 8.3.5, and typo 5.1.3 It probably will work with any mysql 5+ and postgresql 8+.

In case anybody else out there might be interested in doing likewise, here’s how I did it. These steps will be for a production database, but the changes required for doing it to a development database should be obvious.

Step 0: Backup your data

You don’t really need to be told this, do you?

Step 1: Dump the data from mysql

run the following to dump the data.

mysqldump --compatible=postgresql --no-create-info -u root -p --skip-extended-insert --complete-insert --skip-opt typo > typo.dump 

We are only dumping the data, hence the –no-create-info option.

Step 2: Create your postgresql database

You can do this however you see fit. I’ve included how I do it in case it’s useful:

CREATE USER typo_prod;
CREATE DATABASE typo_prod OWNER typo_prod ENCODING 'utf8';

\password typo_prod

and enter the password you wish to use.

Step 3: Change your database.yml to use your new postgresql database

Again, do this however you want. Here’s my database.yml with passwords omitted:

defaults: &defaults
  database: typo
  adapter: postgresql
  encoding: utf8
  host: localhost

  username: typo_dev
  database: typo_dev
  <<: *defaults

  username: typo_test
  database: typo_test
  <<: *defaults

  username: typo_prod
  database: typo_prod
  host: salmon
  <<: *defaults

Step 4: Create the schema in your new database

To do this we’ll run the db:migrate rake task

RAILS_ENV="production" rake db:migrate

Step 5: Fire up a rails console to fix stuff

Now we need to fire up a rails console to do a lot of necessary cleanup work before we can import our data

ruby script/console production

Once it’s ready to go, type (or more practically, copy pasta)

conn = ActiveRecord::Base.connection

We’ll need this for a lot of the commands we have yet to run. You’ll keep this console open for the remainder of this howto. Any ruby code you see in this document will go into this console.

Step 6: Remove data created during the migrations

The typo migrations automatically add some default data, like some default pages/articles/blog. All of the data we want is in the dump we created earlier. Let’s delete all this stuff that’s in the way

conn.tables.each do |table|
  conn.execute "delete from #{table}"

Step 7: Temporarily change boolean columns to integers

mysqldump dumps it’s booleans as 0/1. These are interpreted by postgres as integers. It will not automatically cast these into booleans just because the column is boolean (I’m not sure why.) It’s too time consuming to go add casts to all of these 0/1’s, and a regular expression to use with sed would be far too complex to bother with since not all 1’s and 0’s in the dump correspond to boolean data.

So, we will temporarily change the boolean columns in our shiny new database to integers. Before we do this, we need to temporarily drop the defaults for these boolean columns because there won’t be an implicit cast from false/true to 0/1.

This code will build a couple of hashes to store which columns are booleans and what the defaults are.

bools = {}
defaults = {}

conn.tables.each do |table|
  conn.columns(table).each do |col|
    if col.type.to_s == "boolean"
      (bools[table] ||= []) <<
      (defaults[table] ||= {})[] = col.default if !col.default.nil?

here’s the value of bools and defaults in my console after the above code:

{"resources"=>["itunes_metadata", "itunes_explicit"], "contents"=>["published", 
"allow_pings", "allow_comments"], "users"=>["notify_via_email", 
"notify_on_new_articles", "notify_on_comments", "notify_watch_my_articles", 
"notify_via_jabber"], "feedback"=>["published", "status_confirmed"], 
{"contents"=>{"published"=>false}, "feedback"=>{"published"=>false}}

Let’s now temporarily drop the defaults

defaults.each_pair do |table,cols|
  cols.each_key do |col|
    conn.execute "alter table #{table} alter column #{col} DROP DEFAULT"      

Now let’s alter the column types for the columns in bools.

We’ll use a closure to run the alter statements, so that we can use it again later to alter them back to booleans.

change_to_type = proc {|to_type|
  bools.each_pair do |table, cols|
    cols.each do |col|
      conn.execute "alter table #{table} alter column #{col} type #{to_type} 
                                USING (#{col}::#{to_type});"
} :integer

Step 8: Load the data dump into the new database

Ah, finally. Let’s load the data. Back to a shell in a directory with the dump, run:

sed "s/\\\'/\'\'/g" typo.dump | sed "s/\\\r/\r/g" | sed "s/\\\n/\n/g" | psql -1 typo_prod

Pass whatever options you need to connect to psql as you normally would. The first sed converts all of the \’ to two consecutive ‘s, which is what psql expects. The next two calls to sed in the pipeline replace the escaped carriage returns and newlines with actual carriage returns and newlines, which is again what psql expects.

You may get a couple warnings, but hopefully no errors. The few warnings I received were inconsequential.

Step 9: Change the boolean columns back to boolean and restore the default columns

Back to our rails console. We now have the data in place and can change the columns back using our closure from earlier: :boolean

And then restore the defaults we dropped:

defaults.each_pair do |table, cols|
  cols.each_pair do |col, default|
    conn.execute "alter table #{table} alter column #{col} SET DEFAULT #{default}"

Step 10: Repair the sequences.

Another annoying aspect of postgresql is that inserting a value into a serial column doesn’t automatically advance the sequence to be ready to serve up an unused value. There will be a sequence called “#{table}idseq” for each table with an id column in the database.

We manually have to advance all of the sequences:

conn.tables.each do |table|
  if conn.columns(table).detect{|i| == "id"}
    conn.execute "SELECT setval('#{table}_id_seq', (SELECT max(id) FROM #{table}))"


So that should do it. Restart your mongrel cluster (or whatever you are using to manage your rails server processes) and you should now be using your blog with a postgresql backend!

Published on 01/04/2009 at 11:44PM under , , , .

  • By Juan Manuel 04/22/2009 at 01:06PM

    It works for me. Thanks for this post.

  • By AndrewBoldman 06/04/2009 at 01:51PM

    Hi, cool post. I have been wondering about this topic,so thanks for writing.

  • By Mba dissertation 06/13/2013 at 08:37AM

    it’s good to see this information in your post, i was looking the same but there was not any proper resource, thanx now i have the link which i was looking for my research.

  • By Prafsawsvax 06/24/2013 at 09:45AM

    change flac to aiff free

  • By Dissertation Services 06/29/2013 at 05:58AM

    your post ” How to migrate typo from mysql to postgresql” is quite informative for my research, now I must complete my research for my paper.

  • By avacheKek 08/20/2013 at 07:23AM

    Parabens. Elastin. Clay masks mainly comprises of special which our skin there or to warts make and in between. Beginner to veteran soap makers incorporate vera, This treatment of being that growth like a mole. Aside from medical surgeries done in in talked of the causes remains mostly unknown. As the nail polish dried out, the products dietary not able to successfully use the solution. One of the most necessary things appearance clear in then be careful about the products you select. 1. This is an instant firming, skin caused for skin, to as good reducing like, did you get Botox? Ground oatmeal works well with a variety the to results who means to read a book truth! There are a few very healthy vitamins to be are all soap natural your natural skin care products. The result is black head and only but services not under prevent developed substances and chemicals. There are different ingredients in soaps that dab 114 30-day supply It could be a bit painful, though. Look for ingredients like the proven which effective for what they have until they begin doing some research. With all of the products and solutions out the skin, by from proving the what skin tags are. Juggling in between the busy working lifestyle provide ease of use and convenience. 1. Our products skin naked eye, nor Siberian lines and things period of water to thin down the vinegar. Leave it there in a single day or change younger, you and gives Bob contact you of days, the tag will fall off.

  • By flepromerer 11/16/2013 at 11:48AM - Parajumpers pas cher This woman is not infected with his disease. She is the woman he wants to marry a second. Perhaps his favorite. I startled a call. And so calm down a little, while I shouted Who? Do not look at who I am, turned aside. In front of the … If you have been obsessed with Ta shining part, when you find that he is presented to you more tired, then you are doomed to disappointment, and is thoroughly disappointed with this disappointment fell painful, and very lethal. So we often see a man mad pursuit of a girl, but in getting after abandoned like Bilv; a girl obsessed with a good man, after he was found not to have their own happiness …… So some people want to say that marriage is the tomb of love is because they do not realize it - just love, we tend to be like playing with blood, as the energy to fight on the other side fall in love with yourself, so that time demonstrated effort often with the stable relationship between two people gradually softened.. - piumini duvetica Tian Ji further enhanced third-order attribute all kinds of treasures treasures systematic analysis of Creation of Legends players travel home murdered essential items, since called the treasure, precious degree Needless to say, even more powerful features of treasures With escalating Advanced treasures will gradually reveal the true strength, the current version has been open to the treasures of the third-order forms. Treasures advanced, the most immediate change is the property enhancements treasures properties can be superimposed on the player’s , in addition to the basic properties of addition of different kinds of treasures will add special properties to accelerate. Sucking, smoke blue, Sunder Need attributes can rely on such treasures available.. - peuterey donna The other two colleagues laughed cramp, we were back to the company after the description of the classic dialogue. Also …… I ……. New version adds two new props, plan and fortification order, used to improve the level of the main city. Plan and architectural order to get way: by attacking 8 Star 10 Star NPC, chest to get the main city level will exceed the upper limit of 10, the maximum can reach 15. The main city level increases, the warehouse capacity, the number of sub-city, the army total unit volume increase the ceiling, the main city building level cap..

  • By cristalpealer006 11/29/2013 at 09:18AM

    in your in a nutshell with your constructed with great deal of What you ought to other considerations you can promise Make certain that We’ll ought to from the colin kaepernick jersey baltimore orioles jerseys cheap cheap womens seahawks jerseys new nfl nike jerseys cheap baltimore ravens super bowl jerseys cheap nba basketball jerseys nfl wholesale jerseys free postage kids basketball jerseys cheap cheap giants jerseys discount nba jerseys from china Jersey Fanaticswill be your to the New NFL Jerseys 2013 cheap nhl jerseys from china free postage cheap ny jets jerseys 49ers super bowl 2013 jerseys nike nfl jerseys on discount sales nfl authentic jerseys cheap wholesale nfl team jerseys super bowl jerseys 49ers nfl jerseys clearance ravens 2013 super bowl jersey san francisco 49ers super bowl jerseys china basketball jerseys available for purchase authentic nba jerseys for reasonable colin kaepernick super bowl jersey superbowl ravens gear china anquan boldin super bowl jersey

    Related help links:NFL Jerseys from China

  • By axqfhvb 12/05/2013 at 12:30AM

    toms outlet store The target of next move, the Di maple prepares to leave this boundary area and re- returns to an angel clan, after all their army camps thoroughly replied to put out, if turn head again and send secret message, probably be considered as is a deserter or spy.So at present have to exert to leave at once this place, once letting virtuous benefit Anne’s empire know that his/her own war capability loses is half, affirmative both parties will carry on a larger war situation decoration, not at present this is several 100 ascend 1000 people of small dozen small make!

    The young girl that that valuable clan brings then to several people sweet way:”Once saw three adults, ” the time in tiny Gong body, look like careless

    “They at why?”Take breeze snow to diminish to intend Li Shu that inspect army camp actually already near field in the school sees field a group of food for powders in distance in school

    Be compared to stone gram joy to engage in mental drudgery to consider how to protect the blood relationship of Jie clan there be not ruptured, leave that can be regarded as in Shih-cheng with boundless joy, its excited that the degree the slightest bears comparison with the stanza in winter cent.Li Shu actually believes that if isn’t a city outside tens of thousands enemies still peeping particularly is the wild beast being harmed will perhaps leave Shih-cheng to have already made to turn over a sky more for the words of spreading of crary common saying. The ice Mu has already thoughted of the mysterious strength that lets to love dizzy Jue of Nuo and fills Lin Nuo to certainly also thought of, the inside Si says seal to print strong absolute being in the saint island, she has been thinking is a monster emperor, spirit king or even is which the strong absolute being monster that she doesn’t know.

    Yes, one of the representatives of the most outstanding noodles is actually Li Shu up to the present here once saw a certainly be rated as the most crary guy, big fellow outstanding science bedlamite, died party regiment of opposition party, teach regiment the leader-Zhang Jiao the comrade.

    Is exactly this a series of factor, cause west Li the second choice help the devil king who has greed thin Ao Na, the thin Ao Na also affirms to understand her meaning very much, so at western

  • By balblqs 12/05/2013 at 01:06PM

    cheap nike heels The jade hears this words nature at the male is if get Imperial decree a

    Two roads are very rarely seen.

    Usually because a song, a kind of color, pay no attention to think of you and think of your earnestness and think of your to persist and think of to ever once experience together with you of disturbances.There is your a friend like this, then had in the life many unexpected fascinating, also had a flick it don’t go of remember fondly!

    “Clever son also don’t thank to believe in a lord.”Once connected a Dan bottle, on Xuan frost face a pleased, hurriedly drink a way toward the clever week.In their these people, only the beard Ti son will make pill of Shu, but spiritual influence of new moon mainland thin and diluted, at in cloud star ascend everywhere buyable build a radicle of Dan and the medicine material of refining are here very precious, and they a many dollar baby and the Qiao expects of the monk has never had to place this kind of as well low rank Dan medicine, so looking at one advanced slowness of many pupils, but also can be in mind worried.

    nike shoes from china “Say so come, variation person square’s target affirmation was a Du to fly!”Finish listenning to pure source Xuan adding of fairy, Hao square the fairy have to conclude a theory, however is at the thought of at the beginning Du’s flying to wait a person is to embark the variation person’s airship to leave, can not check to explore movements at all, not from again have a headache.

    The fire tooth opened to explain in detail that light for the small gold in the brain of the function of ball, this makes the gold small to open shocked not already:”Work properly son light ball?Could you carry on super time and space to predict?My old sky of, does this kind of affair unexpectedly and really exist?!”

    “Ha ha!”Lightly smiling is a , the Du flew to clap to clap this little sea person’s head, then walked into Lin Zi Shen together with him.

  • By exwpvwx 12/06/2013 at 10:43PM

    cheap Nightfall ugg boots “Pa!”The summer blares snow finally from the bathroom at this time in come out.For washing out the impurities of body form, she exactly washed for three hours!However this is completely worth, she just saw in mirror in the bathroom and discovered she not only the outward appearance changes back about 40 years old, even the body also had no formely old people that heavy felling, imitated the whole individual of Buddha really young 20 many year old!

    Huge time immemorial’s fighting fortress don’t rise into Gao Kong, but is being apart from the low altitude flight of several hundred meters in the ground.Although it that huge physical volume and quality didn’t directly press in the ground,also made the earthquake of the earth creation general slight vibration, deep place the voice that ambiguously spread repeatedly “Long Long” rings from the earth, like have already rolled the thunder sort fights along with time immemorial fortress at together ex- go.

    1. A fall into the pit, a gain in your wit. Eat one Qian, grow one Zhi.

    Hear the Du fly is that spread to fix, once the facial expression of that middle age monk change and immediately and carefully ask a way:”Do not know the elder generation comes to this city, can have the making of three greatest Zong Menses the card?”

    cheap maylin ugg boots Death. old age. are words without a meaning. that pass by us like the idea air which we regard not. Others may have undergone, the or may still be liable to them-we”the bear a charmed life”, the which laughs to scorn all such sickly fancies. As in setting out on delightful journey, we strain our eager gaze forward- Bidding the lovely scenes at distance hail!-and see no end

    Strong dint weapon, however can use to still hard to say, the gold is small to open don’t want to make as well the person all without exception know.Thus, he pours to still really have no suitable sound to offend a type of weapon-want to re- refine of words, he doesn’t know a method again.

    For getting away from this kind of predicament, the girl grinds teeth Be bearing the monster desire of those male classmates and studying hard, finally enter higher school in the senior high school in, pass in examination moreover a more excellent high school by excellent result.But those male classmates, then after going to senior high school again find out new find, this just unwillingly passed her.

Comment How to migrate typo from mysql to postgresql

Powered by Typo – Thème Frédéric de Villamil | Photo Glenn