redmine mysql/postgresql converter

Start point: redmine + mysql
End point:   redmine + postgresql_9.0

1.create mysql dump

     mysqldump -u root -p --compatible=postgresql --skip-add-drop-table --skip-add-locks  --skip-create-options --skip-extended-insert --default-character-set=utf8 -n -t -r redmine_mysql.sql redmine

2.escape strings for postgres (,' -> ,E')

     sed "s/,'/,E'/g" redmine_mysql.sql >> redmine_postgres.sql

3.create empty postgresql db

     rake db:migrate RAILS_ENV=production

4.truncate tables

DO language plpgsql $$
DECLARE
     vr record;
     i integer;
BEGIN
     for vr in (select table_name from information_schema.tables where table_schema='public' and table_type='BASE TABLE')
     loop
          execute 'TRUNCATE TABLE ' || vr.table_name;
     end loop;
END
$$;

5.convert boolean column into character varying(111)

DO language plpgsql $$
DECLARE
     vr record;
BEGIN
     for vr in (select table_name, column_name from information_schema.columns where table_schema='public' and data_type = 'boolean')
     loop
     execute 'ALTER TABLE ' || vr.table_name || ' ALTER ' || vr.column_name || ' TYPE character varying(111)';
     end loop;
END
$$;

6.import dump

     psql -q -U postgres -h localhost -d redmine -f redmine_postgres.sql

7.convert character varying(111) into boolean

DO language plpgsql $$
DECLARE
     vr record;
BEGIN
     for vr in (select table_name, column_name from information_schema.columns where table_schema='public' and data_type = 'character varying' and character_maximum_length=111)
     loop
     execute 'ALTER TABLE ' || vr.table_name || ' ALTER ' || vr.column_name || ' TYPE bool using ' || vr.column_name || '::bool';
     end loop;
END
$$;

8.actualize sequences

DO language plpgsql $$
DECLARE
     vr record;
     i integer;
BEGIN
     for vr in (select sequence_name, substr(sequence_name,1,length(sequence_name)-7) table_name from information_schema.sequences)
     loop
          execute 'select coalesce(max(id),1)+1 from ' || vr.table_name into i;
          execute 'ALTER SEQUENCE ' || vr.sequence_name || ' RESTART with ' || i;
     end loop;
END
$$;