Migrating to External Database

This article covers the steps required to migrate TeamCity data from the database of one type to another. For details on using an external database from the first TeamCity start, as well as the general external database information and database-specific configuration steps, refer to this article.

The most typical case for migration is when you evaluated TeamCity with the default internal database and need to switch to an external database to prepare your TeamCity installation for production use. The recommended steps are also applicable when switching from one external database to another. You can also follow them to move between database servers of the same type, but in that case, the database-specific data transfer is regularly more preferable.

There are several ways to migrate data into a new database:

Switch with no data migration: build configurations settings will be preserved, but not the historical builds data or users.
Full migration: all the data is preserved except for any database-stored data provided by the third-party plugins.
Backup and then restore: the same as full migration, but using the two-step approach.

Switch with No Data Migration

If you want a fast switch to an external database and do not want to preserve existing data like users and builds on the server, follow the steps below. After the switch, the server will start with an empty database, but preserve all the settings stored under TeamCity Data Directory (see details on what is stored where).

Create and configure an external database to be used by TeamCity.
Shut down the TeamCity server.
Create a backup copy of the <TeamCity Data Directory> used by the server.
Clean up the system directory: you must remove the messages and artifacts directories from the system directory of your <TeamCity Data Directory>; you may delete the old HSQLDB files: buildserver.* to remove the no longer needed internal storage data.
Start the TeamCity server.

Full Migration

These steps describe switching to another database while preserving all the data. This is done by the TeamCity migration tool — maintainDB command-line utility.

The maintainDB.[cmd|sh] shell/batch script is located in the <TeamCity Home Directory>/bin directory and is used for migrating as well as for backing up and restoring TeamCity data. The utility is only available in the TeamCity .tar.gz and .exe distributions.

TeamCity supports HSQLDB, MySQL, Oracle, PostgreSQL, and Microsoft SQL Server; the migration is possible between any of these databases.

The target database must be empty before the migration process (it must NOT contain any tables).

To migrate all your existing data to a new external database:

1. Create and configure an external database to be used by TeamCity and install the database driver into TeamCity. Do not modify any TeamCity settings at this stage.

2. Shut down the TeamCity server.

3. Create a temporary properties file with a custom name (for example, database.<database_type>.properties) for the target database using the corresponding template (<TeamCity Data Directory>/config/database.<database_type>.properties.dist). Configure the properties and place the file into any temporary directory. Do not modify the original database.<database_type>.properties file.

4. Run the maintainDB tool with the migrate command and specify the absolute path to the newly created target database properties file with the -T option:

maintainDB.[cmd|sh] migrate -T <path to database.properties file>

If you don't have the TEAMCITY_DATA_PATH environment that points to the TeamCity Data Directory, add the -A parameter to the command call:

maintainDB.[cmd|sh] migrate -A <path to TeamCity Data Directory> -T <path to database.properties file>

Upon the successful completion of the database migration, the temporary file will be copied to the <TeamCity Data Directory>/config/database.properties file which will be used by TeamCity. The temporary file can be safely deleted. If you are migrating between external databases, the original database.properties file for the source database will be replaced with the file specified via the -T option. The original database.properties file will be automatically renamed to database.properties.before.<timestamp>.

5. Start the TeamCity server. This must be the same TeamCity version that was run last time (TeamCity upgrade must be performed as a separate procedure).

After you make sure the migration succeeded, you may delete the old HSQLDB files: buildserver.* to remove the no longer needed internal storage data.

Backup and Restore

You can create a backup and then restore it using different target database settings. You will probably need to specify restore options to restore only the database data.

Troubleshooting

Extended information during migration execution is logged into the logs\teamcity-maintenance.log file. Also, logs\teamcity-maintenance-truncation.log contains extended information on possible data truncation during the migration process.
If you encounter an "Out of memory" error, try increasing the number in the -Xmx512m parameter in the maintainDB script. On a 32-bit platform, the maximum is about 1300 MB.
Alternatively, run HSQLDB in the standalone mode via
java -Xmx256M -cp ..\webapps\ROOT\WEB-INF\lib\hsqldb.jar org.hsqldb.Server -database.0 <TeamCity Data Directory>\system\buildserver -dbname.0 buildserver
and then run the migration tool pointing to the database as the source: jdbc:hsqldb:hsql://localhost/buildserver sa ''
If you get "The input line is too long" error while running the tool, change the script to use an alternative classpath method.
For maintainDB.bat, remove the lines below the "Add all JARs from WEB-INF\lib to classpath" comment and uncomment the lines below the "Alternative classpath: Add only necessary JARs" comment.

Last modified: 12 April 2023