NAV Navbar
Logo
Switch version:

GoCD PostgreSQL Addon

This document describes the different aspects of setting up Go (“GoCD”) to use Postgres as its data store, including migration of data from H2 and setting up a secure connection between the GoCD Server and Postgres.

Enabling GoCD to use Postgres

This section describes how to bring up a GoCD Server instance, using Postgres as the data store with no existing data. In case you’re looking to migrate the data from an existing GoCD Server instance, take a look at this. Once the data is migrated, you can start configuring the GoCD Server to use the database, by starting from Step 2 below.

Step 1: Initialize Postgres with an empty database

In order to use this add-on, a Postgres server is needed, to host the GoCD Server’s database. Currently, Postgres versions 9.2 to 9.5 are supported by GoCD, older verions are not supported. If the Postgres server is not yet initialized, it needs to be, before GoCD is able to use it. Along with PostgreSQL server, you need to install postgresql-client and postgresql-contrib packages.

Once initialized and the Postgres service is started, the empty database can be created from the command-line using the psql utility, which Postgres ships with:

$ psql -U postgres -h localhost -c "CREATE DATABASE cruise-or-desired-database-name;"

The GoCD Server uses cruise as the default database name. The database name chosen can be that, or any other valid Postgres database name. While configuring the GoCD Server in a later step, the chosen name can be configured. There is no need to create any schema in that database, since the GoCD Server does it automatically.

Step 2: Install add-on into addons directory

A directory called addons needs to be created (if not already present) in the GoCD Server installation directory. The add-on JAR needs to be placed in that directory. In the examples below, the add-on JAR name is considered to be “go-postgresql-1.2.3.jar”. The name of the real add-on JAR will be different. The commands are:

On Linux (remember to run this as the go user):

$ mkdir -p /var/lib/go-server/addons
$ cp go-postgresql-1.2.3.jar /var/lib/go-server/addons

On Windows:

C:\> md "C:\Program Files (x86)\Go Server\addons"
C:\> copy go-postgresql-1.2.3.jar "C:\Program Files (x86)\Go Server\addons"

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

Step 3: Configure GoCD with Postgres connection details

A Java properties file with the name ‘postgresqldb.properties’ needs to be created in GoCD’s configuration directory. This file should contain information about the Postgres server, so that the GoCD Server can connect to it. Information about the format of this file and valid keys in it can be found in the section Add-on configuration reference below.

The location of GoCD’s configuration directory varies per operating system. The documentation provides information about the locations.

Step 4: Configure the database provider for GoCD

At this point, GoCD needs to be configured to use Postgres as its data store rather than the default. This is done by setting the Java system property go.database.provider to com.thoughtworks.go.postgresql.PostgresqlDatabase.

On Linux, this can usually be done by (on a single line):

$ echo 'GO_SERVER_SYSTEM_PROPERTIES="$GO_SERVER_SYSTEM_PROPERTIES -Dgo.database.provider=com.thoughtworks.go.postgresql.PostgresqlDatabase"' >> /etc/default/go-server

On Windows, this can usually be done by adding a line in the appropriate properties file:

wrapper.java.additional.16="-Dgo.database.provider=com.thoughtworks.go.postgresql.PostgresqlDatabase"

The process of setting a system property is detailed here in the documentation.

Step 5: Start the GoCD Server and verify that Postgres is being used

The GoCD Server can now be started.

On Linux:

$ sudo /etc/init.d/go-server start

On Windows:

C:\> net start "Go Server"

You can verify that GoCD is running with Postgres by verifying the below line in the go-server.log, upon starting the service.

[DB] Using connection configuration jdbc:postgresql://[host]:[port]/[database-name] [User: postgres] [Password Encrypted: false]

Migrating an existing GoCD Server installation to Postgres

Step 1: Upgrade, backup and stop the GoCD Server

Step 1.1: Upgrade the GoCD Server to the latest version.

Step 1.2: Then, backup the H2 database and configuration using the One Click Backup feature of GoCD. During the migration process from H2 to Postgres, data from the backed-up H2 database will be exported into CSV files and no modifications will be done on the H2 database.

Step 1.3: Stop the GoCD Server and ensure that the cruise.lock.db file does not exist under the db/h2db directory, indicating a clean shutdown has finished.

Step 2: Get everything ready for migration

2.1 Create a new directory for migration. In this document, the chosen directory will be /tmp/migration and it will be referred to as the “migration location” for the rest of this document.

2.2 Place the PostgreSQL add-on for GoCD into the migration location. Copy cruise.h2.db (from the backup or from /var/lib/go-server/db/h2db) to the migration location.

2.3 Create a directory called “config” in the migration location (if the migration location is /tmp/migration, the config directory should be /tmp/migration/config). In that directory, place all the configuration files needed to tell GoCD about the Postgres instance it needs to use. At a minimum, this needs to contain a file called “postgresqldb.properties”. More information about the format of these files and valid keys in them can be found in the section “Add-on configuration reference” below.

2.4 Create an empty database on the Postgres instance. This needs to have the same name as the db.name key in the postgresqldb.properties file from Step 2.3. Here is an example:

$ psql -U postgres -h localhost -c 'CREATE DATABASE cruise-or-desired-database-name;'

Step 3: Run the migration!

At this point, the migration directory should look like the one below:

|-- tmp/
   |-- migration/
      |-- config/
         |-- postgresqldb.properties
         |-- cipher [optional: depends on whether db.passwordEncrypted is set to true]
         |-- client.crt ... [optional: depends on Postgres SSL setup]
      |-- cruise.h2.db
      |-- go-postgresql-1.2.3.jar

The migration can now be run from the command-line, in the migration directory:

$ cd /tmp/migration
$ java -Dcruise.config.dir=/tmp/migration/config -Dgo.h2.db.location=/tmp/migration -jar go-postgresql-1.2.3.jar

On Windows, the arguments to the command will be the same. The locations will need to be Windows-specific (like C:\tmp\migration).

Step 4: Use GoCD with the newly migrated database

After a successful migration, the steps mentioned in Enabling GoCD to use Postgres section can now be followed to setup GoCD to use Postgres. “Step 1: Initialize Postgres with an empty database” can be ignored, since Postgres is already initialized with the migrated data.

Please contact support for any queries or issues.

Upgrading an existing installation which uses Postgres

This section will help you upgrade your GoCD Server installation which uses Postgres as its data store.

Suppose you are uprgrading from GoCD Server 15.1.0, which uses go-postgresql-15.1.0-123.jar to GoCD Server 15.2.0, which uses go-postgresql-15.2.0-456.jar (as an example), the steps for an upgrade are as follows:

Step 1: Shut down the GoCD Server

Before upgrading, it is recommended to shut down the GoCD Server and take a backup of the data. The One Click Backup feature of GoCD can be used to take a backup.

On Linux:

$ sudo /etc/init.d/go-server stop

On Windows:

C:\> net stop "Go Server"

Step 2: Replace the old add-on with the new

Remove the old add-on from the GoCD Server’s “addons” directory, and replace it with the new one.

$ sudo su - go
$ rm /var/lib/go-server/addons/go-postgresql-15.1.0-123.jar
$ cp /tmp/go-postgresql-15.2.0-456.jar /var/lib/go-server/addons

Step 3: Upgrade the GoCD Server

Upgrade the GoCD Server as you would, normally. In the case of an RPM based distribution, it is usually done like this:

$ sudo rpm -Uvh go-server-15.2.0-1234.rpm

Add-on configuration reference

This section will help you configure the add-on with details about the Postgres instance it needs to connect to and use. Optionally, you can set it to use SSL for its communication with the Postgres server.

The configuration file for the GoCD PostgreSQL add-on is called “postgresqldb.properties” and it should be created in GoCD’s configuration directory. A sample configuration file could look like this:

db.host=localhost
db.port=5432
db.name=cruise
db.user=postgres
db.password=postgres

The valid keys in the configuration file are mentioned below:

Key Mandatory Default Description
db.host Yes - The value should be the IP address or the hostname of the Postgres server that is to be used.
db.port No 5432 The value should be the port that the Postgres server is using and listening on.
db.name No cruise The database schema name that the GoCD Server should use.
db.user Yes - The database user which GoCD should use to connect to the schema.
db.password No - The password for the user specified by “db.user” property which GoCD should use to connect to the schema.
db.passwordEncrypted No false This property should be set to “true” if the “db.password” property contains an encrypted value that the add-on should decrypt using the GoCD cipher file, before using.
db.maxActive No 32 Maximum number of active connections that should be established with the Postgres server.
db.maxIdle No 32 Maximum number of idle connections that should be maintained with the Postgres server.
db.ssl No false This property should be set to “true” to enable SSL connections to the Postgres server. If this is set to “true”, then the other SSL and certificate related properties (below) should also be set.
db.ssl.mode No verify-full Indicates the verification level of the server certificate when SSL is used. In order to prevent spoofing, SSL certificate verification must be used. However, for evaluation or test environments, this can be set to lower security levels. This flag corresponds to “sslmode” connection parameter which is passed on to “libpq” library used by Postgres. For more details, take a look at libpq SSL support documentation.
db.root.cert No root.pem Filename of the root certificate file. This property needs to be configured if SSL connection is used. This file should be placed in the GoCD Server’s configuration directory. This property corresponds to “sslrootcert” connection parameter which is passed on to “libpq” library used by Postgres. GoCD uses Postgres’ JDBC driver to connect to the database, and pg_dump to perform backups. The former requires either PEM or DER encoded certificates, while the latter could work with either CRT file or PEM. Hence, only PEM encoded certificates can be used with GoCD as of now.
db.client.cert No client.crt Client certificate filename. The certificate in this file will be provided when Postgres server requests a trusted client certificate. This file should be placed in the GoCD Server’s configuration directory. This property corresponds to “sslcert” connection parameter which is passed on to “libpq” library used by Postgres.
db.client.key No client.key RSA private key file for the client certificate. The key file should be placed in the GoCD Server’s configuration directory and must not allow any access to world or group (can be done using: chmod 600 client.key). This property corresponds to “sslkey” connection parameters which is passed on to “libpq” library used by Postgres. If this file is not provided, “One Click Backup” from GoCD will not work.
db.client.pkcs8.key No client_pkcs8.key PKCS8 encoded client key file. This should be placed in the GoCD Server’s configuration directory. This file is required for a successful connection to be established when trusted client certificates are used for authentication. OpenSSL can be used to create a PKCS8 encoded file from a RSA key file by executing openssl pkcs8 -topk8 -outform DER -in client.key -nocrypt > client_pkcs8.key
db.backup.format No custom One Click Backup feature of GoCD backs up both the configuration and database. By default, for servers using Postgres, the custom backup strategy provided by pg_dump is used. The add-on can also be configured to take plaintext backups by configuring setting the value of this property to “plain”. In this case, pg_dump will be invoked with --format=plain --compress=6 as arguments. That causes it to take a plain text backup and compress it.