Move FootPrints Service Core 12 Database Server (PostgreSQL)

Version 3
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    FootPrints


    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.

       
    1. Stop the FootPrints Service Core 12 Tomcat server (under Start -> Administrative Tools -> Services)
    2.  
    3. Backup the existing FootPrints Service Core 12 database from the current PostgreSQL server.     
           
      1. Open up pgAdmin.
      2.    
      3. Connect to the PostgreSQL server where the FootPrints database exists.
      4.    
      5. Right click on the database to back up and choose backup.

        User-added image

      6.    
      7. Verify the backup settings match those in the following screen shot:

        User-added image

        Referring to the image above, the "Dump Options #1", "Dump Options #2", "Objects" and "Messages" tab should use default settings.
      8.    
      9. When the options are filled in, click on the "Backup" button.
      10.   
    4.  
    5. 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.
    6.  
    7. 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 RoleNew Login Role
        fpscdb001_admfootprints_adm
        fpscdb001_rptfootprints_rpt
        fpscdb001_secfootprints_sec
        fpscdb001_usrfootprints_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

        User-added image

      •  
      • 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
             
             
        1. 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.       
          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:

          User-added image

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

          User-added image

                 
          • 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.
          •     
        2.    
        3. 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 nameUser name key in properties filePassword key in properties file
          fpscdb001_usrDatabaseUserNameDatabaseUserPassword
          fpscdb001_admDatabaseAdminNameDatabaseAdminPassword
          fpscdb001_rptReportingUserNameReportingUserPassword
          fpscdb001_secSecureUserNameSecureUserPassword

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

        4.   
      •  
      • Update the database connection information in the footprints-environment.properties file to use the newly restored FootPrints Service Core database.

        User-added image

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

       


      Article Number:

      000011128


      Article Type:

      Solutions to a Product Problem



        Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles