Code42 server version 6.0 and later provides the option to store data in an external PostgreSQL database rather than the H2 database built into the authority server. This article describes why and how to install an authority server that uses PostgreSQL, and tips for managing this configuration.
- A PostgreSQL database server:
- Version 9.4 is recommended
- Version 9.2 or later is required
Version 9.2 reaches end-of-life in September 2017. Version 9.3 reaches end-of-life in September 2018.
- Code42 server version 6.0.0 or later on Linux
The PostgreSQL option is not supported on Windows.
- Understand how to install and use PostgreSQL
Choosing whether an authority server uses PostgreSQL or H2 happens during the authority server installation process. To convert an existing installation from H2 to PostgreSQL, contact sales about engaging our PRO Services team.
Code42's built-in H2 database
In the default installation, a Code42 authority server uses an H2 database to store user information, configuration parameters, and performance records (everything except the user data Code42 apps send to Code42 store points).
- The H2 database is an integral part of the authority server.
- The H2 database installs automatically when the authority server installs.
- Database connection and maintenance are built into the authority server.
As an alternative to H2, you can install a Code42 authority server to use a PostgreSQL database.
- PostgresSQL is an open-source database for enterprise-scale applications.
- Code42 recommends using PostgreSQL when you build a Code42 environment to serve 10,000 or more endpoint users.
- You are responsible for PostgreSQL installation, configuration, networking, and maintenance.
- When you install a Code42 authority server, provide your database address and user credentials when prompted by the installation script.
Compare H2 and PostgreSQL
The following table compares technical details of H2 and PostgreSQL databases.
|Feature||H2 Database||PostgreSQL Database|
|Number of users||Up to 10,000||More than 10,000|
|Proximity and control||
Internal to Code42 authority server
Automatic connection and daily backup
External to Code42 authority server
Entirely under customer control
|Database access||For one Code42 authority server||For multiple authority servers|
|Performance||Table locking limits update performance||Row locking results in faster updates|
|Memory utilization||Governed by the Java engine||
More efficient memory utilization and management
More efficient queries
Database tuning available
Install an authority server connected to PostgreSQL
Step one: Install a PostgreSQL server
Your PostgreSQL server must be up and running and accessible before you install your authority server.
- Install a PostgreSQL database server in a location where your authority server will be able to connect to it.
- Create a PostgreSQL user with the roles LOGIN, CREATEDB, and PASSWORD.
- Create a new, password-protected database named proserver.
- Make note of the following, for later use:
- The machine's domain name or IP address
- The username and password for the database
Step two: Install an authority server
Run the authority server install script on Linux and respond to the prompts as described below:
The default response,
The alternative to an authority server is a storage server. Storage servers always use H2.
To use a PostgreSQL database, respond with
The default response, no, means your authority server will use its internal H2 database.
host is a domain name or IP address
5432 is the standard port for PostgreSQL
proserver is the name of a newly created, empty database
The user needs the PostgreSQL roles LOGIN, CREATEDB, and PASSWORD.
The password needed to log into and modify the database.
PostgreSQL in the install script
The following shows the authority server install script implementing PostgreSQL.
Validate the database connection
After you define the database connection, the install script validates it by:
- Checking that the authority server can make the connection and log in.
- Determining whether the database is empty.
- If the database is not empty, determining whether it matches the schema of a Code42 authority server.
If the database is not empty, the install script provides crucial warnings and prompts. The only reason to proceed with a non-empty database is to re-install a Code42 authority server and resume use of existing data.
Do not connect to a database that holds anything other than Code42 authority server data.
Do not connect to a database in use by any other Code42 authority server.
Either connection will likely cause permanent loss of your Code42 data.
Maintenance and security of a PostgreSQL database is your responsibility. When you use a PostgreSQL database, the authority server's tools for maintenance and security are largely disabled.
Identify an Authority Server's database
To identify an existing authority server's database from the server's command line, issue the command
A PostgreSQL database is also identified in the file /opt/proserver/conf/conf_local.groovy.
Back up your database
Backing up your PostgreSQL database is entirely your responsibility. The authority server's processes for backing up the default H2 do not apply to PostgreSQL. Code42 strongly recommends the following:
- On a regular schedule, copy your PostgreSQL database to a mirror installation or dump a backup copy of your database.
- Immediately before upgrading your authority server, dump a copy of your database.
Server restart and recovery
If you need to restart your Code42 authority server, the server will automatically reconnect with the PostgreSQL database.
To move or reinstall your Code42 authority server, perform a standard install. When prompted, provide the address and credentials for the existing database. The address and credentials are available in a text file: /opt/proserver/conf/conf_local.groovy.
Upgrade your database server
You can upgrade your PostgreSQL database server to a newer version, from 9.2 to 9.4 for example, with only momentary interruption of the authority server that relies on it.
Dump a copy of the database for safe-keeping before proceeding.
In the commands below, replace 9.<x> with the number of the older, currently running version;
replace 9.<n> with the new version number.
- Determine what version of PostgreSQL is currently running on your authority server's Linux host:
- Compare your version to the current one listed at PostgreSQL.
- Install the new version with the commands below.
sudo apt-get update
sudo apt-get install postgresql-9.<n>
sudo pg_dropcluster --stop 9.<n> main
apt-get install postgresqlfails, add the PostgreSQL distribution site to the Linux source list.
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list' wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
- Stop your authority server.
sudo /opt/proserver/bin/proserver stop
- Upgrade PostgreSQL.
sudo pg_upgradecluster 9.<x> main
- Restart your authority server.
sudo /opt/proserver/bin/proserver start
- Sign in to the console.
If the Code42 server starts and you can sign in, then you know the server can connect to its database.
- Discard the previous version of PostgreSQL.
sudo pg_dropcluster --stop 9.<x> main
- Confirm that the new database is running:
The command should report that 9.n main is running.