Hello everyone,
since I could not find any resources about how to migrate from mySQL to PostgreSQL I had to figure out the steps on my own (upgraded from 1.1.6 to 1.2.0). It took me a while and I wrote a short article for my
blog (it's in German though
). Right now I'm too lazy to translate the full explanations to English so below there is the short English summary. Please feel free to ask if you need any further help. I performed the following steps under linux with bash, sed, grep, ruby, mysqldump and psql installed.
1. Updating mantis installation
I decided to update my mantis installation before the database migration because I wanted to transfer an up to date database. Upgrading is quite easy and is well documented elsewhere on this site
2. Get a database dump which is compatible with PostgreSQL
Visit the
mysql2postgres site and download the
mysql2postgres ruby script. You might have to install a few additional packages depending on your ruby installation (I had to install ruby, ruby-mysql, rubygems, rubygem-pg on my openSUSE system). I had to slightly modify the script to make it translate the
tinyint(4)-type to
boolean. This modified version can be downloaded
here.
Then copy and modify the configuration file to fit your mySQL database settings and to exclude the table
mantis_bug_file_table. A sample configuration would look like this (replace the [] values):
Code: Select all
mysql:
hostname: [address of mySQL server]
port: 3306
socket:
username: [user name]
password: [password]
database: [mantis database]
destination:
# if file is given, output goes to file, else postgres
file: mantis_table_data_raw.sql
postgres:
hostname:
port:
username:
password:
database:
# if tables is given, only the listed tables will be converted. leave empty to convert all tables.
#tables:
#- table1
#- table2
#- table3
#- table4
# if exclude_tables is given, exclude the listed tables from the conversion.
exclude_tables:
- mantis_bug_file_table
#- table6
If you stick to the sample configuration the database dump will be written to the file
mantis_table_data_raw.sql. After this make a working copy of this file (I named mine
mantis_table_data.sql and will refer to it in the following steps).
3. Select relevant data from the dump
Delete all lines until the first
COPY statement in the working copy. After that, put some DELETE statements at the beginning of the file with the following command
Code: Select all
sed -i '1i\DELETE FROM mantis_category_table;\nDELETE FROM mantis_config_table;\nDELETE FROM mantis_plugin_table;\nDELETE FROM mantis_user_table;\n' mantis_table_data.sql
Set the sequences to the proper values by using the following command
Code: Select all
grep "SELECT pg_catalog.setval" mantis_table_data_raw.sql >> mantis_table_data.sql
If the mySQL database and the PostgreSQL database use different character encodings (e. g. Latin1 vs. UTF8) you might want to put a
Code: Select all
SET client_encoding = '[mySQL encoding]';
on top of the dump file, replacing
[mySQL encoding] with whatever encoding your mySQL database uses.
4. Extract and convert binary data (i. e. uploaded files)
Append the data from the formerly skipped table
mantis_bug_file_table to the working copy with the following command (replace
[server address], [user name], [password], [database] with proper values)
Code: Select all
mysqldump -h[server address] -u[user name] -p[password] --database [database] --table mantis_bug_file_table --skip-extended-insert --complete-insert --hex-blob | sed -n -e "/INSERT /s/\`//g" -e "s/',0x\([0-9A-Fa-f]*\),/',E'\\\\x\1\',/g p" >> mantis_table_data.sql
Now set the value of the corresponding id sequence as follows (again, replace the brackets [])
Code: Select all
echo "SELECT pg_catalog.setval('mantis_bug_file_table_id_seq', $(expr $(mysql -h[server address] -u[user name] -p[password] [database] --skip-column-names --batch -e "SELECT MAX(id) FROM mantis_bug_file_table;") + 1), true);" >> mantis_table_data.sql
Let mantis itself do this job for you. Change the mantis configuration to point to your PostgreSQL database and create a database and user. Point your browser to
http://[yoursite]/admin/install.php, fill out the form and have everything created and set.
6. Upload your data
Tell
psql to upload your dump file
Code: Select all
psql -h [server address] -d [database] -U [user name] -W --log-file log1.log -f mantis_table_data.sql &>log2.log
If errors occur, they are reported in
log2.log. In my case some bug descriptions were too long for the
varchar(128) data type. But this could be easily fixed in the dump file. I dropped, recreated and re-filled the database until everything went without errors.
Finally: Congratulations! Everything is now set and ready for use 