Migrating Bugzilla from MySQL/MariaDB to PostgreSQL
This post is about my (successful) attempt to migrate my Bugzilla installation from MySQL/MariaDB to PostgreSQL.
Method
When I started to research the possibility of migrating Bugzilla from MySQL/MariaDB to PostgreSQL I stumbled over this article which discusses the general process of migrating any MySQL/MariaDB database with the help of pgloader.
Before I went down that path, though, I also found out about the script importxml.pl, which is mentioned only in passing in the current Bugzilla docs, but has its own dedicated man page in older Bugzilla docs:
This script is used to import bugs from another installation of bugzilla.
After a bit of experimentation I decided that this is exactly what I needed: No arduous general-purpose migration with pgloader, but a relatively simple, quick & dirty, one-time migration of the 160 bugs in my personal bugtracker (if you head over to the site you won’t see most of those bugs, they are not visible to the public because they pertain to private household IT problems).
Overview of migration steps
The migration I did consisted of the following steps:
- Set up a new and empty PostgreSQL database.
- Populate the database with the Bugzilla schema.
- Manually configure the new Bugzilla site in the browser. This includes setting parameters and creating products/components/versions/milestones, because these are not part of the automated migration.
- Create a backup of the new site’s database.
- Export bugs data in XML format from the old site.
- Manually tweak the exported XML data.
- Import the XML data into the new site.
- When something goes wrong (rest assured that it will), restore the backup from step 4 and repeat steps 5-7 until all issues have been resolved.
- Post-processing
- Remove old Bugzilla site
Create and configure new site (steps 1-3)
The procedure how to set up a PostgreSQL user/role and an empty database for Bugzilla is explained in the Bugzilla docs (step 1).
After that is done, run the usual
./checksetup.pl
to populate the empty database (step 2).
To begin with step 3, point your web browser to the new Bugzilla site. This article does not discuss how to set up your DNS and web server to serve a new Bugzilla site in parallel to the old one - that is something you have to figure out for yourself.
In the browser log in with the admin user that was created by checksetup.pl and configure the new site exactly the same as the old site. This work is done in the “Administration” area and specifically includes:
- Setting parameters
- Adding products, components, versions and milestones
If the old Bugzilla site has special users/groups, custom fields, or any other customizations, you have to replicate these things in the new site. In short, you have to replicate everything except the actual bugs data.
Create a backup of the new site’s database (step 4)
The new Bugzilla site is now ready for importing the bugs data. I strongly advise that you now create a database backup so that you don’t have to repeat the new site’s configuration should the import fail for some reason (remember Murphy’s Law).
sudo -u postgres pg_dump --clean bugzilla /tmp/bugzilla.sql
The database name in the example is “bugzilla”. Change this and the folder where you store the backup file according to your needs.
Export bugs data in XML format from the old site (step 5)
Point your web browser to the old Bugzilla site. Log in as the administrator, or any other user that has the privileges to access all bugs in all products.
Now make a buglist.cgi query that lists all bugs. As mentioned, in my case I only had 160 bugs to migrate, so this was no problem. If you have a lot of bugs to migrate it might not be practical to list them all in one query - if that is the case you’ll have to find a scheme to separate your bugs into chunks that you can export separately and then reassemble into one XML file for the import.
In my case I navigated to “Search > Simple Search”, changed the bug status from “Open” to “All” (I wanted to migrate all bugs, even those that are already closed) and pressed the “Search” button.
Sort the list that results from the query by bug ID, in ascending order (i.e. the bug with the lowest ID appears first). This is important because when you import bugs data into the new site the bugs will receive new ID numbers, starting with ID 1. By sorting the list you ensure that the exported bugs appear in the same order in which they will be renumbered. Ideally your bugs data starts with bug ID 1 and has no gaps, so the imported bugs will have the same IDs as the originals.
Finally, scroll down to the bottom of the query. There should be a button labeled “XML” that you can click and that will present you with an XML data representation of the bugs that were in your query. Save the XML data to a local file, e.g. bugzilla-all-bugs.xml.
Manually tweak the exported XML data (step 6)
Before you import the bugs data into the new Bugzilla site you can manually tweak the data to improve the results of the import. In my case I had to do these things:
- Add dummy bugs to fill in gaps in the ID numbering
- Add attachment data
- Remove obsolete XML elements
Open the exported XML data (e.g. bugzilla-all-bugs.xml) in a text editor of your choice and perform the tweaks.
Adding dummy bugs
My goal was that bugs should retain their ID during the migration, so that any existing references such as URLs remain valid. A potential problem here is that importxml.pl hands out new bug IDs when it imports data, starting with bug ID 1 in an empty database. This means that if the exported XML data contains gaps in the ID numbering, the imported bugs after the first gap will have a different ID.
Old ID New ID Status
----- ------ ------
1 1 Same ID
2 2 Same ID
<gap>
4 3 Different ID
5 4 Different ID
[...]
My solution for this was to add dummy bugs to fill in the gaps. For each gap I simply copied the data of one of the other bugs (i.e. everything between <bug>...</bug>), changed the bug ID to match the gap and changed the short description (<short_desc> element) to contain an easy-to-find search string such as “dummy bug” (duh!). The dummy bugs can then be deleted later after the import.
Old ID New ID Status
----- ------ ------
1 1 Same ID
2 2 Same ID
3 (dummy) 3 Same ID, will be deleted after the migration
4 4 Same ID
5 5 Same ID
[...]
Add attachment data
When bugs are exported in bulk from a buglist.cgi query page Bugzilla deliberately omits attachment data from the exported XML data. This is unfortunate because the XML data is not complete, which will cause the importxml.pl script to fail.
The issue has been reported in 2010 on the Bugzilla tracker and has been lying dormant since then as an “enhancement”. The report mentions that the problem can be worked around by removing the excludefield URL parameter from the POST request, either with a web browser addon or by modifying the Bugzilla source that generates the following snippet in the buglist.cgi output:
<form method="post" action="show_bug.cgi">
<input type="hidden" name="ctype" value="xml">
[...]
<input type="hidden" name="excludefield" value="attachmentdata">
^
+-- this needs to be removed
<button type="submit" id="xml">
<img src="images/xml.png" width="24" height="24" alt="XML" title="XML Format">
</button>
</form>
In my Bugzilla 5.0.3 installation the snippet appears to be generated by template/en/default/list/list.html.tmpl.
I learned about the excludefield URL parameter too late, while writing up this article, so I haven’t tried any of the two methods outlined above. Instead my workaround for the problem was to manually add the missing attachment data to the exported XML data. Once I knew about the problem it wasn’t such a big deal because I only had 4 attachments in my data. The procedure I followed was this:
- Search the exported XML data for bugs that have the
<attachid>element. - Call up the individual bug in the web UI of the old Bugzilla site (
show_bug.cgi) - Display the bug’s XML representation by clicking the “XML” link in the bottom right
- Copy the
<data>element from the browser into the XML file at the exact spot where it appears in the browser.
The snippet to copy looks like this:
<data encoding="base64">
LS0tIGh0ZG9jc [...] ZXMnXSkpCg==
</data>
Remove obsolete XML elements
In my case the exported XML data contained an XML element <comment_sort_order> which was unknown to the Bugzilla version I used for my new site. The unknown XML element didn’t cause the import to fail, but it caused unnecessary warnings during the import.
I don’t know where that XML element came from (possibly a leftover from an earlier Bugzilla upgrade), but I decided that I don’t need it so I simply deleted it from the exported XML data.
Import the XML data to the new site (step 7)
Once you have finished tweaking the exported XML data, transfer the file to the server where the new Bugzilla installation is located. For instance:
scp bugzilla-all-bugs.xml user@your.server.com:/tmp
Log in to the server and then simply run this:
cd /path/to/bugzilla-site
./importxml.pl -v /tmp/bugzilla-all-bugs.xml
The -v option is not strictly necessary, it merely prints out a bit of additional (verbose) information about what was imported and how the import went.
When the import is done you can check the result in the web browser.
Retry (step 8)
If something went wrong you probably have to fix the XML data and then retry the import. Before you can do that you have to reset the PostgreSQL database to the original state it had after step 3.
These commands completely drop the database, re-create it and then re-populate it with the data you manually entered in step 3 and backed up in step 4.
sudo -u postgres dropdb bugzilla
sudo -u postgres createdb -U postgres -O bugzilla bugzilla
sudo -u postgres psql -d bugzilla </tmp/bugzilla.sql
Post-processing (step 9)
If you added dummy bugs in step 6 then you will now want to delete them in a final post-processing step.
Bugzilla does not normally allow bugs to be deleted individually. There is, however, a workaround: When you delete a component Bugzilla also deletes the bugs assigned to that component, given that you previously have configured the site to allow bugs deletion.
The first step is therefore to allow bugs deletion: In the browser navigate to “Administration > Parameters > Administrative Policies” and enable the parameter allowbugdeletion.
Next, add a new dummy component to every product that contains one or more of the dummy bugs you want to delete. Assign all dummy bugs to their respective dummy component, then delete the dummy components.
Finally you should again disable the parameter allowbugdeletion to avoid accidental bugs deletion in the future.
Remove old Bugzilla site (step 10)
You can now get rid of the old Bugzilla site and drop the old MySQL/MariaDB database.
Also don’t forget to clean up after yourself:
rm /tmp/bugzilla.sql
rm /tmp/bugzilla-all-bugs.xml
Congratulations, you’re done.