Migrating Gerrit from H2 to PostgreSQL

The last 3 months have been busy and challenging. I’ve moved cities and changed jobs. I now work for Red Hat on the Gluster project as a CI/Automation Engineer. After 2 years in Delhi, I’ve moved to Mumbai, right when the monsoons hit. I feel like I haven’t seen the city properly dry ever since we moved. On the plus side, I’ve gotten back into running again. Despite the crazy rains, I see other crazy people like me out running every weekend 🙂

One of the first things I did when I started in May was to make a list of things I thought we need to fix. The first thing I noticed is that we were running quite an old version of Gerrit running on H2. For some reason, it fell over every couple of days. At that point, someone had to login to the server and restart Gerrit.

The top two potential causes were a large H2 database file and an old version of Gerrit. So I decided to upgrade Gerrit. The first step was to move from H2 to PostgreSQL. I looked up how to convert from H2 all over the internet. Eventually, I decided the best way to go about it is to export to CSV and import the CSV files into PostgreSQL. So here’s a rough idea of how it went about:

1. Get the list of tables.
2. Use regular expressions in vim to generate the SQL to export all the tables.
3. Create the PostgreSQL database and change Gerrit settings.
4. Initialize Gerrit again so it will create the appropriate tables in PostgreSQL.
5. Import the CSV files into PostgreSQL.

Sounds suspiciously easy. Except it’s not. I learned a fun thing about PostgreSQL’s COPY. The HEADER parameter means that the first column is a header and will be ignored. If the order of columns in the CSV file doesn’t match the one in PostgreSQL, it doesn’t do anything about it.

If your CSV has the following:

id, email, name 

And your table has the following:

id, name, email 

PostgreSQL doesn’t do the intuitive thing on it’s own.

You have to explicitly define that. For some reason, I didn’t run into this in staging, perhaps H2 generated a CSV in the right order. My eventual script specified the order when importing.

NOTE: If you’re upgrading Gerrit, the table names or columns may be different. I recommend generating them on your own based on what’s in your database.