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.
- 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
- Install redmine from apt. apt scripts for redmine-postgresql created a new database and randomized the password.
- Get a database dump from sqlite on basalt:
james@basalt /var/lib/redmine/db $ sqlite3 production.db .dump > ~/redmine_prod_dump2.sql
- postgres was configured to do ident authentication, so had to make some mods to the pg_hba.conf. Reloaded PG.
- Installed the following redmine plugins:
- advanced_roadmap
- bulk_time_entry
- redmine_google_docs
# rake db:migrate_plugins RAILS_ENV=production
and restart redmine (via apache)# apache2ctl restart
- move PCIC theme from /var/lib/redmine/public/themes
- move email.yml over
- 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:- PRAGMA
- any indexes
- any sqlite_sequence
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";'
- 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 —–
blog comments powered by Disqus