Migrating Redmine to pizza

These are quite possibly the worst notes ever, but it gives some idea of what we had to do to migrate redmine from running on Gentoo with a sqlite3 backend, to running on Debian with a PostgreSQL backend.

1. Non-sid redmine was crusty, so we started by modifying /etc/apt/sources.list and /etc/apt/preferences to get newest version. Add to sources.list "deb http://http.us.debian.org/debian/ sid main contrib non-free" and add to /etc/apt/preferences.d/redmine:

   Package: *
   Pin: release o=Debian,a=stable
   Pin-Priority: 101
   
   Package: *
   Pin: release a=sid
   Pin-Priority: 1
   
   Package: rubygems
   Pin: release a=sid
   Pin-Priority: 999
   
   Package: rubygems1.8
   Pin: release a=sid
   Pin-Priority: 999
   
   Package: rake
   Pin: release a=sid
   Pin-Priority: 999
   
   Package: librack-ruby1.8
   Pin: release a=sid
   Pin-Priority: 999
   

2. Install redmine from apt. apt scripts for redmine-postgresql created a new database and randomized the password.
3. Get a database dump from sqlite on basalt:

   james@basalt /var/lib/redmine/db $ sqlite3 production.db .dump > ~/redmine_prod_dump2.sql
   

4. postgres was configured to do ident authentication, so had to make some mods to the pg_hba.conf. Reloaded PG.
5. Installed the following redmine plugins:
   advanced_roadmap
   bulk_time_entry
   redmine_google_docs
   Most of these are simple. You just have to copy the plugins into /usr/share/redmine/vendor/plugins/, update the database

   # rake db:migrate_plugins RAILS_ENV=production

   and restart redmine (via apache)

   # apache2ctl restart

6. move PCIC theme from /var/lib/redmine/public/themes
7. move email.yml over
8. Now for the trickiest part, converting the dump from sqlite3 to go into PostgreSQL. Most of this came down to just going through the sql file w/ an editor and deleteing no-relevant things. Since all of the tables existed already, we replaced all of the CREATE TABLE statements with DELETE FROM statements. This was just a regular expression search and replace in emacs. M-x query-replace-regexp. pattern =

   CREATE TABLE "\([^"]+\)".*;

   , replacement =

   DELETE FROM \1;

   After that we removed:
   1. PRAGMA
   2. any indexes
   3. any sqlite_sequence
   The final step was resetting the sequence values to start above the highest id value in the database. We wrote a quick plpgsql function:

   CREATE OR REPLACE FUNCTION reset_seq(seq varchar, seq_table varchar) RETURNS integer AS $$
   DECLARE
     id_value integer;
   BEGIN
     EXECUTE 'SELECT max(id) + 1 FROM ' || seq_table  INTO id_value;
     IF id_value IS NOT NULL
     THEN
     EXECUTE 'ALTER SEQUENCE ' || seq || ' RESTART ' || id_value;
     END IF;
     RETURN id_value;
   END;
   $$ LANGUAGE plpgsql;
   

   And then Dave hacked up some perl to iterate over the sequence list and call reset_seq on each of them. Something like this:

   cat sequences.txt | cut -d "|" -f 2 | perl -ne 'chomp; s/^\s+//g; s/\s+$//g; $seq_string = reverse($_); $seq_string =~ /([^_]+)_([^_]+)_(.*)/; $col = reverse($2); $table = reverse($3); $seq_string = reverse($seq_string); print "SELECT reset_seq('$col', '$table');\n";'
   

9. Last step was to run it through PostgreSQL:

   
   root@pizza:/etc/redmine/default# psql -U redmine -W redmine_default
   Password for user redmine: 
   psql (8.4.9)
   Type "help" for help.
   
   redmine_default=> \i /home/hiebert/redmine_prod_dump.sql
   

QED —–