PostgreSQL Add-On

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.
You can get the PostgreSQL add-on for GoCD by contacting ThoughtWorks Sales and Support.
In case of Linux, all operations given below should be performed as go user, unless stated otherwise. Please make sure your GoCD Server is stopped before configuring this.

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
      psql=# CREATE DATABASE cruise-or-desired-database-name;
      psql=# \q

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 the warning earlier about running all commands as go user):

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

On Windows:

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

Ensure that there are no other Postgres add-on JAR files in the addons directory.

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:

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

Step 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.

Step 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.

You can get the PostgreSQL add-on for GoCD by contacting ThoughtWorks Sales and Support.

Step 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.

Step 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
      psql=# CREATE DATABASE cruise-or-desired-database-name;
      psql=# \q

Step 3: Run the migration!

Make sure that you've created an empty database on the Postgres instance as mentioned in Step 2.4 above.

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.

Always ensure that you have compatible versions of the Postgres add-on and GoCD Server before you start. You can get the PostgreSQL add-on for GoCD by contacting ThoughtWorks Sales and Support.

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, the GoCD Server can be shutdown using:

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

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
    
Ensure that the add-on is owned and readable by the "go" user. Also, ensure that there is only one add-on of the same type in the "addons" directory. Having more than one of the same type (go-postgresql-15.1.0-123.jar and go-postgresql-15.2.0-456.jar) can lead to unexpected behavior or failure to start up the GoCD Server.

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.jar

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:

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 using the below command:
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.