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
$ mkdir -p /var/lib/go-server/addons $ cp go-postgresql-1.2.3.jar /var/lib/go-server/addons
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
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
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:
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.
$ sudo /etc/init.d/go-server start
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
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.
$ sudo /etc/init.d/go-server stop
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:
||Yes||-||The value should be the IP address or the hostname of the Postgres server that is to be used.|
||The value should be the port that the Postgres server is using and listening on.|
||The database schema name that the GoCD Server should use.|
||Yes||-||The database user which GoCD should use to connect to the schema.|
||No||-||The password for the user specified by “db.user” property which GoCD should use to connect to the schema.|
||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.|
||Maximum number of active connections that should be established with the Postgres server.|
||Maximum number of idle connections that should be maintained with the Postgres server.|
||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.|
||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.|
||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.|
||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.|
||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.|
||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
||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