Upgrading to GoCD 20.5.0 and higher

GoCD 20.5.0 introduced several changes to its database implementation in order to build a more flexible model that allows integrating with multiple databases. As part of these changes GoCD changed the technologies used for automated database migrations (from the unmaintained DBDeploy to Liquibase). These changes require a one-time migration of the GoCD database <= 20.4.0 to one compliant with GoCD 20.5.0 and beyond.

GoCD 20.5.0, while continuing to support H2 by default, provides an ability to use PostgreSQL and MySQL. As part of this one-time database migration users can also choose to move to a database of their choice. Possible migrations are:

H2 => PostgreSQL [Recommended]
PostgreSQL => PostgreSQL
H2 => H2
H2 => MySQL [Warning! See note below]

Choosing your migration strategy

GoCD comes bundled with the Java disk-based H2 database. If you have never configured a database, this is what you are using.

Support for PostgreSQL was historically provided through a commercial addon provided by Thoughtworks. This functionality was integrated into core GoCD and made freely available as of 20.5.0.
As a result of this history, all of GoCD’s functional regression tests run against both H2 and PostgreSQL databases.
While support for MySQL was added in 20.5.0, only a basic round of migration tests was completed, and the functional test suite does not regularly run against MySQL as a part of the build pipeline. This is something to be aware of if considering moving to MySQL.
The H2 version used in GoCD 20.5.0 moved to use MVStore as the default storage subsystem. The current state of MVStore (as of October 2022) as per H2 documentation is still marked as experimental. While using the default H2 is fine to get started or experimenting, we would recommend using PostgreSQL for production instances of GoCD.

Choosing your target GoCD version

If you are considering upgrading to a version higher than 20.5.0, please remember the usual caveats around compatibility with older agent versions, noted in Upgrading GoCD. As there have been no major database-related changes between 20.5.0 and 22.3.0, there are no special database-related considerations however you should review the release notes for possible breaking changes in your setup.

Choosing PostgreSQL or MySQL versions

If you are considering migrating from H2 to PostgreSQL or MySQL during this upgrade, you should consider the target version of your database carefully. Ideally, you want to choose an overlapping version based on:

your eventual target GoCD release’s supported database versions.
the database migrator tool’s supported database versions which were validated upon 20.5.0 release. These were:
- PostgreSQL (v9.6 – v12). As of 2022, later versions will probably work correctly, but should be considered unvalidated, so take care to sanity check your data in your specific environment.
- MySQL (v8.0)

Previous commercial PostgreSQL add-on users

Previous commercial PostgreSQL add-on usage was replaced by open-source/free support for PostgreSQL in GoCD 20.5.0. Add-ons / extensions are no longer required, used or supported. However to continue to use PostgreSQL your existing database still needs a one-time manual migration to make it compatible with GoCD 20.5.0 and higher.

Previous commercial Business Continuity add-on users

The business continuity feature was removed in later releases of GoCD due to security concerns in 20.5.0+. You should decommission your standby instance, and upgrade only your primary instance.

Migration Steps

Follow the instructions below to migrate your existing GoCD <= 20.4.0 database to a GoCD 20.5.0 (and beyond) compliant database. The time taken by this migration is dependent on the size of your database. While testing we have seen the migration taking from a few minutes to more than an hour based on the size of the database. Please test on a backup of your GoCD server to understand the time taken for your particular database.

Strong recommendation:: try this migration on a non-production instance or backup of GoCD before attempting it on a production instance.

Step 1: Upgrade to GoCD `20.4.0`

In general, you should be able to migrate from any older version of GoCD directly to 20.5.0 or higher. However, over the few releases prior to 20.5.0 there have been multiple changes to GoCD around installers and agent communication which could involve necessary changes to your setup.

Hence to ensure you are tackling one challenge at a time, it is recommended to do a normal upgrade to GoCD 20.4.0 and validate your setup on GoCD 20.4.0 before performing an upgrade to GoCD 20.5.0 or higher.

Step 2: Backup

Backup your GoCD server. Refer to the Backup GoCD Server documentation for instructions.

Step 3: Stop GoCD Server

Stop your GoCD server, if it is running.

Step 4: Database Migration

Download the latest stable version of the migrator tool from the GitHub releases section of the GoCD database migration tool’s repository.
Uncompress it and cd into the directory.
Run ./bin/gocd-database-migrator --help for usage instructions.

Prerequisites: Ensure you have Java 8+ installed on the machine which runs the migration.

4.1 Migrating data from H2 to H2

The gocd-database-migrator requires the source-db-url which consists of the location of the GoCD H2 database. The location of the database depends on the distribution your GoCD server is running on. Please refer to GoCD installation documentation to identfiy the file location.

Run the command (The below example is for a GoCD server running on Linux) -

./bin/gocd-database-migrator \
  --insert \
  --progress \
  --source-db-url='jdbc:h2:/var/lib/go-server/db/h2db/cruise' \
  --source-db-user='sa' \
  --source-db-password='' \
  --target-db-url='jdbc:h2:/var/lib/go-server/db/h2db/new_cruise' \
  --target-db-user='sa' \
  --target-db-password=''

For GoCD server running on Windows refer to the below example -

bin\gocd-database-migrator.bat ^
--insert ^
--progress ^
--source-db-url="jdbc:h2:C:\Program Files (x86)\Go Server\db\h2db\cruise" ^
--source-db-user="sa" ^
--source-db-password="" ^
--target-db-url="jdbc:h2:C:\Program Files (x86)\Go Server\db\h2db\new_cruise" ^
--target-db-user="sa" ^
--target-db-password=""

Note: The source-db-url and target-db-url contain just the prefixes of the file names (cruise and new_cruise), even though the actual files are named: cruise.h2.db and new_cruise.mv.db.

Delete, take a backup of or move away the file /var/lib/go-server/db/h2db/cruise.h2.db.
Replace the old database with the migrated database by moving the file /var/lib/go-server/db/h2db/new_cruise.mv.db to /var/lib/go-server/db/h2db/cruise.mv.db.
Ensure that the file permissions and ownership of the new cruise.mv.db file are correct (same as that of the old cruise.h2.db file).

4.2 Migrating data from PostgreSQL to PostgreSQL

Create an empty database in PostgreSQL. Refer to the PostgreSQL docs for information on creating an empty database.

Run the command by providing the right parameters for the required options. An example is shown below:

./bin/gocd-database-migrator \
  --insert \
  --progress \
  --source-db-url='jdbc:postgresql://localhost:5432/cruise' \
  --source-db-user='postgres' \
  --source-db-password='pass' \
  --target-db-url='jdbc:postgresql://localhost:5432/new_cruise' 
  --target-db-user='postgres' \
  --target-db-password='pass'

4.3 Migrating data from H2 to PostgreSQL

Create an empty database in PostgreSQL. Refer to the PostgreSQL docs for information on creating an empty database.

Run the command by providing the right parameters for the required options,

./bin/gocd-database-migrator \
  --insert \
  --progress \
  --source-db-url='jdbc:h2:/var/lib/go-server/db/h2db/cruise' \
  --source-db-user='sa' \
  --source-db-password='' \
  --target-db-url='jdbc:postgresql://localhost:5432/new_cruise' 
  --target-db-user='postgres' \
  --target-db-password='pass'

4.4 Migrating data from H2 to MySQL

Create an empty database in MySQL. Refer to the MySQL docs for information on creating an empty database.

Run the command by providing the right parameters for the required options,

./bin/gocd-database-migrator \
  --insert \
  --progress \
  --source-db-url='jdbc:h2:/var/lib/go-server/db/h2db/cruise' \
  --source-db-user='sa' \
  --source-db-password='' \
  --target-db-url='jdbc:mysql://localhost:3306/new_cruise'
  --target-db-user='root' \
  --target-db-password='password'

Step 5: Configure `db.properties` for your GoCD server

5.1 Enabling GoCD to use H2 Database

GoCD runs on H2 by default. Configuring the db.properties is not required. Just make sure that the directory <<GoCD_installation_directory>>/db/h2db/ does not contain cruise.h2.db (the 20.4.0 H2 database format) and only contains cruise.mv.db (the 20.5.0+ H2 database format).

5.2 Enabling GoCD to use PostgreSQL or MySQL Database

A properties file with the name db.properties needs to be created in GoCD’s configuration directory. This file should contain information about the PostgreSQL or MySQL server, so that the GoCD Server can connect to it. Refer to the GoCD Database Connection Properties documentation for more information about the format of this file and valid keys.

The location of GoCD’s configuration directory varies per operating system. Usually, on a Linux system using the RPM or Debian installers, this file will need to be at /etc/go/db.properties. The installation documentation provides information about the locations.

Sample configuration for db.properties for PostgreSQL:

db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://localhost:5432/new_cruise
db.user=postgres
db.password=pass

Sample configuration for db.properties for MySQL:

db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/gocd
db.user=root
db.password=password

Step 6: Only for users using the (old) commercial PostgreSQL addon

Once you have completed the above steps, including configuring a new db.properties config for core GoCD:

Remove the PostgreSQL addon jar from the addons directory (typically /var/lib/go-server/addons on Linux)
Remove the postgresqldb.properties file from the configuration directory (typically /etc/go on Linux).

Step 7: Upgrade GoCD Server

Upgrade your GoCD server to 20.5.0+ and start the server.

Troubleshooting

Possible issues you might see are:

Database is read-only

You might see a message such as this, after upgrade, in the GoCD server logs:

Caused by: org.h2.jdbc.JdbcSQLNonTransientException: The database is read only; SQL statement:
UPDATE PUBLIC.DATABASECHANGELOGLOCK SET LOCKED = TRUE, LOCKEDBY = '10.16.0.5 (10.16.0.5)', LOCKGRANTED = '2020-06-17 15:07:20.707' WHERE ID = 1 AND LOCKED = FALSE [90097-200]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:505)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
	at org.h2.message.DbException.get(DbException.java:205)

This can happen due to the H2 DB file (usually at /var/lib/go-server/db/h2db/cruise.mv.db on Linux) having the wrong permissions or ownership.

MySQL: Identifier case senitivity

You might see a message such as this in the GoCD server logs, if you are using MySQL:

Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.apache.commons.dbcp2.BasicDataSource]: Factory method 'getDataSource' threw exception; nested exception is java.sql.SQLException: Unable to migrate the database
        at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:189)
        at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:588)
        ... 73 common frames omitted
Caused by: java.sql.SQLException: Unable to migrate the database
        at com.thoughtworks.go.server.database.migration.DatabaseMigrator.migrate(DatabaseMigrator.java:68)
        at com.thoughtworks.go.server.database.Database.getDataSource(Database.java:63)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.base/java.lang.reflect.Method.invoke(Unknown Source)
        at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:162)
        ... 74 common frames omitted
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set db-migration-scripts/initial/create-trigger.xml::107::gocd(generated):
     Reason: liquibase.exception.DatabaseException: Table 'gocd.buildStateTransitions' doesn't exist [Failed SQL: (1146) CREATE TRIGGER lastTransitionedTimeUpdate
                AFTER INSERT ON buildStateTransitions
                FOR EACH ROW
                BEGIN
                    UPDATE stages SET lastTransitionedTime = NEW.statechangetime WHERE stages.id = NEW.stageid;
                END]
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:646)
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:83)
        at liquibase.Liquibase.update(Liquibase.java:202)
        at liquibase.Liquibase.update(Liquibase.java:179)
        at liquibase.Liquibase.update(Liquibase.java:175)
        at com.thoughtworks.go.server.database.migration.DatabaseMigrator.migrate(DatabaseMigrator.java:54)
        ... 80 common frames omitted
Caused by: liquibase.exception.DatabaseException: Table 'gocd.buildStateTransitions' doesn't exist [Failed SQL: (1146) CREATE TRIGGER lastTransitionedTimeUpdate
                AFTER INSERT ON buildStateTransitions
                FOR EACH ROW
                BEGIN
                    UPDATE stages SET lastTransitionedTime = NEW.statechangetime WHERE stages.id = NEW.stageid;
                END]
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:402)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:59)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:131)
        at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1276)
        at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1258)
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:609)
        ... 86 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: Table 'gocd.buildStateTransitions' doesn't exist
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
        at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:398)
        ... 91 common frames omitted

If you see this, the most probable cause is that your MySQL instance has case-sensitive identifiers turned on. GoCD needs case-insensitive identifiers and you will need to change your MySQL instance to enable that. Please note that, according to the documentation, it is not possible to change the lower_case_table_names variable once the MySQL instance is initialized. You might need to recreate the instance.