I think, therefore I blog

How To Export Redmine Issues Using SQL

By , 18 June 2015

How To Export Redmine Issues Using SQL
How To Export Redmine Issues Using SQL

I stopped using Redmine a few years ago and left all that data in the database just sitting there rotting away. Some of my notes were pretty good so I decided to see if I could get all those issues out of Redmine without reinstalling the whole damn stack again.

Here is the query I came up with to export all the issues and issue comments into a readable text format. Once exported you can load it up in a text editor that supports code folding like VIM.

The query is for postgresql and has some non-standard SQL functions so it is not portable, but you might be able to find some replacements for those functions for your database system.

$ psql -A -F '' -c "
  select '{{{ ' || project_id || '/' || id || ' ' ||  subject || E'\n\n',
         replace(regexp_replace(description, '\s+$', ''), '}}}', '} } }') || E'\n',
         (select array_to_string(array_agg('Comment posted on ' || created_on || E'\n\n' ||
                 replace(regexp_replace(notes, '\s+$', ''), '}}}', '} } }')), E'\n\n') from
                 (select * from journals where notes <> '' and journalized_type='Issue' and
                           journalized_id=issues.id order by created_on) as ordered_comments), E'\n}}}'
         from issues order by project_id desc, created_on desc" -Upostgres -hlocalhost redmine > issues.txt

$ vim issues.txt
:set foldmethod=marker

It's surprising how useful simple text format is. No database, no network, no ruby, no browser. Just good ol' text.

How To Export Redmine Issues Using SQL

About Roger Keays

How To Export Redmine Issues Using SQL

Roger Keays is an artist, an engineer, and a student of life. Since he left Australia in 2009, he has been living as a digital nomad in over 40 different countries around the world. Roger is addicted to surfing. His other interests are music, psychology, languages, and finding good food. Click here to subscribe to his weekly blog, or stalk him on Facebook and Twitter.

Leave a Comment

Please visit https://RogerKeays.com/how-to-export-redmine-issues-using-sql to add your comments.

Join 3,559 People Who Think Outside The Box

I write every Sunday about travel, psychology, technology, and all sorts of interesting stuff. It's completely free, and you can leave whenever you like. Subscribe now, and you'll be the first to get new articles each week.

Read a Good Book

“A spellbinding true story of love, passion and adventure. One can’t help but be swept away by 100% Love Guaranteed.” —Dr Tammie Matson, author of Elephant Dance.

Chat For A While

Your Vote Matters

Which animal will take over when humans go extinct?