Move FootPrints Service Core 12 Database Server (PostgreSQL)

Knowledge Article

Article Number

000201399

Old Article Number

000011128

Article Type

Solutions to a Product Problem

Title

Move FootPrints Service Core 12 Database Server (PostgreSQL)

Summary

Move FootPrints Service Core 12 Database Server (PostgreSQL)

Product

FootPrints

Component

Applies to

FootPrints Service Core 12

Problem

This article describes how to move the FootPrints Service Core 12 database to a different database server. This article is specific to FootPrints Service Core 12 installations using a PostgreSQL database server.
These instructions require the passphrase value used during the installation of FootPrints Service Core 12. Please contact FootPrints Service Core Technical Support If this value is not known.
The target PostgreSQL database server must be a supported version of PostgreSQL of the same or new version.
The following information provides guidance related to the issue indicated in the title. For help understanding the general concepts, features, and navigation of version 12, consult the product Help system.
Additionally, these steps must be performed with full Administrator privileges.

Cause

Move FootPrints Service Core 12 Database Server (PostgreSQL)

Solution

IMPORTANT: Before starting this process, create a backup of the footprints-environment.properties file located in the "conf" folder of the FootPrints Service Core installation.

Stop the FootPrints Service Core 12 Tomcat server (under Start -> Administrative Tools -> Services)
Backup the existing FootPrints Service Core 12 database from the current PostgreSQL server.
1. Open up pgAdmin.
2. Connect to the PostgreSQL server where the FootPrints database exists.
3. Right click on the database to back up and choose backup.
4. Verify the backup settings match those in the following screen shot:
  Referring to the image above, the "Dump Options #1", "Dump Options #2", "Objects" and "Messages" tab should use default settings.
5. When the options are filled in, click on the "Backup" button.
On the new PostgreSQL server create the four (4) PostgreSQL Login Roles used by the FootPrints Service Core application.
NOTE: The names of the database login roles created on the new PostgreSQL server do not need to match the names of the login roles names on the old PostgreSQL server, but if the login role names are different on the new server, the database restore script will need to be modified before it is run. The restore step below describes the process for modifying the restore script.
In the example SQL below, the tenant id "fpscdb001" is used to form the role names. Update the SQL statements to create the login roles with the desired names. Use pgAdmin to execute the SQL to create the new login roles.
```
create role fpscdb001_adm with CREATEDB NOCREATEROLE NOCREATEUSER LOGIN PASSWORD 'password';
create role fpscdb001_rpt with NOCREATEDB NOCREATEROLE NOCREATEUSER NOINHERIT LOGIN PASSWORD 'password';
create role fpscdb001_sec with NOCREATEDB NOCREATEROLE NOCREATEUSER NOINHERIT LOGIN PASSWORD 'password';
create role fpscdb001_usr with NOCREATEDB NOCREATEROLE NOCREATEUSER NOINHERIT LOGIN PASSWORD 'password';
```
Save the login role names and passwords used to create each login role. They will be needed in subsequent steps.
Create a new database on the new PostgreSQL server.
NOTE: The database name on the new database server does not need to be the same as the database name on the old database server. Using pgAdmin run the SQL statement below to create the new FootPrints database on the new PostgreSQL server. Replace the parameter new_database_name with the actual name of the database to create.
```
create database new_database_name with encoding='UTF8' owner=postgres connection limit=-1;
```
Restore the FootPrints Service Core 12 database backup to the newly created database on the new PostgreSQL database server.
NOTE: If the database login roles created on the new PostgreSQL server are different from the database login roles on the old PostgreSQL server, the references to the login roles in the backup SQL file must be updated before restoring the backup. To do this, search and replace in the backup SQL file each of the four (4) old login role names with the new login role names created on the new server. For example, if the login roles on the older PostgreSQL server were:

Old Login Role New Login Role
fpscdb001_adm footprints_adm
fpscdb001_rpt footprints_rpt
fpscdb001_sec footprints_sec
fpscdb001_usr footprints_usr
Each occurrence of the old login role name in the backup file must be replaced with the new login role name. Use the SQL command below to restore the PostgreSQL backup file. Replace the parameters in bold with a value specific to the new PostgreSQL database server where the backup will be restored.
```
C:\Program Files\PostgreSQL\9.2\bin\psql.exe -h host -p port -U postgres_admin_user -d database_name -f footprints.sql
```
Skip this step if the login role names on the new PostgreSQL are the same as the login role names on the old PostgreSQL server.
If the login role names were changed, update the footprints-environment.properties file on the FootPrints Service Core application server (not on the database server) with the new login role names.

Be careful to copy each role name to the correct location in the footprints-environment.properties file.

The footprints-environment.properties file can be found in the "conf" folder under the folder where FootPrints Service Core was installed, for example:

C:\Program Files\BMC Software\FootPrints Service Core\conf\footprints-environment.properties

Encrypt the database user passwords and save them in the footprints-environment.properties file. The footprints-environment.properties file can be found in the "conf" folder under the folder where FootPrints Service Core was installed, for example:

C:\Program Files\BMC Software\FootPrints Service Core\conf\footprints-environment.properties

On the FootPrints Service Core application server (not the database server) where FootPrints is installed, run the following command once for each of the four database login roles to encrypt each login?s password.
- Replace the path values in the command with the location where FootPrints is currently installed.
- Replace the "Your Passphrase Here" value with the passphrase value used during the installation of FootPrints.
- Replace the "passwordToBeEncrypted" value with the plain text password to be encrypted.
```
java -cp "C:\Program Files\BMC Software\FootPrints Service Core\web\WEB-INF\lib\*;C:\Program Files\BMC Software\FootPrints Service Core\conf" com.numarasoftware.footprints.tool.EncryptionTool -passphrase "Your Passphrase Here" -text passwordToBeEncrypted
```
NOTE: If Java is not on the system path you may need to fully qualify the java executable, for example:
```
C:\Program Files\Java\jre7\bin\java -cp "C:\Program Files\BMC Software\FootPrints Service Core\web\WEB-INF\lib\*;C:\Program Files\BMC Software\FootPrints Service Core\conf" com.numarasoftware.footprints.tool.EncryptionTool -passphrase "Your Passphrase Here" -text passwordToBeEncrypted
```
The command will return the encrypted password in the "Result" field:

An error message which says, "Error: Could not complete the requested operation.", means the passphrase provided is incorrect.

Copy the encrypted password value and paste it into the appropriate location in the footprints-environment.properties file.

User-added image

Be careful to copy each password to the correct location in the footprints-environment.properties file. This table shows the default mapping of database login names to entries in the footprints-environment.properties file.

Database Login name	User name key in properties file	Password key in properties file
fpscdb001_usr	DatabaseUserName	DatabaseUserPassword
fpscdb001_adm	DatabaseAdminName	DatabaseAdminPassword
fpscdb001_rpt	ReportingUserName	ReportingUserPassword
fpscdb001_sec	SecureUserName	SecureUserPassword

NOTE: The login role names may be different than those displayed in the table above.

Update the database connection information in the footprints-environment.properties file to use the newly restored FootPrints Service Core database.
Start the FootPrints Service Core 12 Tomcat Server (under Start -> Administrative Tools -> Services. FootPrints Service Core will now be using the restored database on the new PostgreSQL Server.

Attachment(s):