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.

About Roger Keays

Roger Keays is an artist, an engineer, and a student of life. He has no fixed address and has left footprints on 40-something different countries around the world. Roger is addicted to surfing. His other interests are music, psychology, languages, the proper use of semicolons, and finding good food.