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

I guess I'd call myself a problem solver. Either that, or I'm some sort of organic machine designed to convert oxygen into carbon dioxide. You could go either way on that. I'm into languages and stuff. I wrote a book. It's okay, I guess. What else? I like reading, swimming, eating, and playing music. Satisfied? Sheesh.

Leave a Comment

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