Skip to content
CA Application Performance Management - 9.6
Documentation powered by DocOps

Upgrade the APM Database

Last update August 25, 2017

Contents

Upgrade the APM database before other components using the Database only installation option. This action is required for all upgrade paths.

Important! In case the size of the CA APM database is large, then the upgrade takes a longer time to complete. We recommend you to schedule a maintenance window.

To upgrade the APM database, use one of the following methods:

  • How to upgrade the APM database on Oracle
  • How to upgrade the APM database on PostgreSQL

How to Upgrade the APM Database on Oracle

The APM database stores data from Introscope and CA CEM. The schema must be upgraded if the existing deployment uses the APM database on Oracle.

Follow these steps:

  • Verify prerequisites for APM database
  • Upgrade the APM database

Verify Prerequisites for APM Database on Oracle

Before you begin the process to upgrade the APM database, verify the following:

  • The computer meets or exceeds the system requirements for the APM database listed in the Product Compatibility Matrix.
  • Back up your existing data. Use the backup and restore programs for your database. For example, Oracle provides:
    • The Database Configuration Assistant (DBCA) lets you configure the Oracle database for automatic backups.
    • The Oracle Enterprise Manager Database Control lets you schedule regular or ad-hoc backups.
    • The Oracle Recovery Manager (RMAN) client lets you manage both backup and recovery operations.
  • No one else is connected to the database.
  • You have reviewed the APM Database information.
  • You have shutdown all Enterprise Managers.

Upgrade the APM Database on Oracle

The Enterprise Manager installer automatically upgrades the APM database.

Follow these steps:

  1. Run the Enterprise Manager installer  using the method that corresponds to the file type for your platform.
  2. When going through the installer complete these tasks:
    1. In the Choose Install Set screen, click Database Only, and click Next.
    2. In the Choose Install Folder screen, specify the APM Database installation directory.
      The default installation directory is /root/Introscope9.x.
    3. In the APM Database Schema screen, select the Upgrade Schema or Create New Schema option.
    4. In the Target Installation Directory for the APM Database screen, specify the installation directory. The default directory is /database.
    5. In the Database Administration Settings screen, specify the database host, port, SID name, username, and password.
    6. In the Database Settings screen, specify the database name, database user, and password. The default database user name is admin.
    Verify that the settings are correct.
  3. When the installer prompts you to proceed, click Upgrade.
  4. After you confirm that Introscope and its related services and files are shut down and stopped, select Introscope Is Shut Down, Upgrade Now.
    The installer upgrades the necessary files.

How to Upgrade the APM Database on PostgreSQL

The PostgreSQL installation must be upgraded if the existing deployment uses the APM database on PostgreSQL. A newer version of PostgreSQL is installed during the Enterprise Manager upgrade.

Follow these steps:

  1. Verify prerequisites for PostgreSQL upgrade.
  2. Back Up the Existing APM Database on PostgreSQL.
  3. Remove the existing APM Database schema.
  4. Uninstall PostgreSQL (previous version).
  5. Delete remaining database files and postgres user.
  6. Install PostgreSQL (current version).
  7. Restore the APM Database from the backup file.
  8. (Optional) Change PostgreSQL Database Passwords.

Verify Prerequisites for PostgreSQL Upgrade

Before you begin the process to upgrade the APM database on PostgreSQL, verify the following:

  • The computer meets or exceeds the system requirements for the current version of the APM database on PostgreSQL listed in the Product Compatibility Matrix.
  • You have shutdown the Enterprise Managers.
  • No process or person is connected to the database.
  • Review the APM Database installation settings in the Installation Worksheet.
  • If the APM database is behind a firewall, configure the firewall to allow access to the database port.

    Note: For more information about opening ports, see the section CA APM ports.

Back Up the Existing APM Database on PostgreSQL

The backup utility creates a backup file of the APM database. You can customize the backup filename when it has a .backup filename suffix. If you do not specify a filename, the script creates a backup file with the name of the database.

Follow these steps:

  1. Ensure the computer on which you are running the database script has PostgreSQL installed. If desired, you can run the script on a remote computer, as long as that remote computer also has PostgreSQL installed.
  2. Navigate to the database-scripts directory for your operating system.
    • Windows: <EM_home>\install\database-scripts\windows\
    • Linux/Solaris: <EM_home>/install/database-scripts/unix/
  3. Open a command line, and run the following script for your operating system:
    • Windows: dbbackup-postgres.bat
    • Linux/Solaris: dbbackup-postgres.sh

    Use the following syntax and arguments:

    [dbbackup-postgres.bat|./dbbackup-postgres.sh] [dbserverhostip] [dbinstalldir] [dbname] [dbuser] [dbpassword] [dbport] [dbbackupdir] [outputfile <optional>]
    
    • dbserverhostIP
      Specifies the IP address of the computer hosting the database. If you are running it on the computer directly, you can specify localhost.
    • dbinstalldir
      Specifies the directory location of the APM database. If you did not change the defaults, the location is as follows:
      • Windows: <EM_Home>\database
      • Linux: opt/database
      Note: On Linux, this location must be the shortened version of the path if there are spaces in it. For Linux, an example is the directory /root/Introscope Enterprise Manager can be represented as /root/Introscope\ Enterprise\ Manager.
    • dbname
      Specifies the APM database schema name. If you did not change the default, this setting is cemdb.
    • dbuser
      Specifies the user name for the database. If you did not change the default, this setting is admin.

      Note: The dbuser is not the PostgreSQL administrator user name.
    • dbpassword
      Specifies the password for the specified database user.
    • dbport
      Specifies the port on which the database communicates to the Enterprise Manager and other components.
      Default: 5432
    • dbbackupdir
      Specifies the directory location where you want to save the database backup file. You can specify a full or relative path to the directory. For Linux, you escape any blank spaces in the path name. For example, if the backup directory is /root/Introscope Enterprise Manager specify the path as /root/Introscope\ Enterprise\ Manager.
    • outputfile
      (Optional) Specifies the name of the database backup file. If you do not specify this parameter, the script generates a file with named dbname.backup, with the database name supplied.

    The following command is an example that generates a cemdb.backup file in the /opt/database/backups directory:

    sh dbbackup-postgres.sh 127.0.0.1 /opt/database cemdb admin quality 5432 ./backups
    

    When the backup process completes, a confirmation message appears.

Note: If the script encounters errors, verify:
  • The database name is correct.
  • PostgreSQL is installed.
  • The location of the APM database is correct.

Remove the Existing APM Database Schema

Before you uninstall PostgreSQL, remove the existing APM Database schema.

Important! This procedure involves removing all data and schema.

Follow these steps:

  1. Navigate to the database-scripts directory. The location is different, depending on the version you are uninstalling. 
    Example:

    cd <Installation_Dir>/install/database-scripts/unix
  2. Open a command prompt and run the following script:

    dropdb-postgres.sh
    

    Use the following syntax and arguments:

    [dropdb-postgres.bat|./dropdb-postgres.sh] [dbserverhostip] [dbinstalldir] [dbname] [dbuser] [dbpwd] [dbport <optional>]
    

    Include the following arguments:

    • dbserverhostIP
      Specifies the IP address of the computer hosting the database. If you are running the command on the computer directly, you can specify localhost.
    • dbinstalldir
      Specifies the directory location of the APM Database.
      If you did not change the defaults, then the location is as follows:
      Linux: opt/database
    • dbname
      Specifies the database instance name.
      If you did not change the default, the name is cemdb.
    • dbuser
      Specifies the username for the database. If you did not change the default, this username is admin.
    • dbpwd
      Specifies the password for the database user that you specified.
    • dbport
      Specifies the port on which the database communicates to the Enterprise Manager and other components. Default port: 5432
      The script removes all data from the database and displays the following message:

      Dropping database "cemdb"

Uninstall PostgreSQL (Previous Version)

Before proceeding, determine which version of PostgreSQL to uninstall as shown in the following table:

CA APM installation PostgreSQL version
9.0 to 9.0.5 8.3
9.0.6 to 9.0.8
9.1 to 9.5.6
8.4

  • To uninstall PostgreSQL on Windows, use the Windows Control Panel.
  • To uninstall PostgreSQL on Linux (or Solaris), run the uninstall-postgresql command.
  • To uninstall PostgreSQL on Linux as a non-root user, perform these steps:
    1. From the console, execute the following command:

      <non_root_user_directory_absolute_path>/opt/database/bin/pg_ctl -D <non_root_user_directory_absolute_path>/opt/database/data stop
      
    2. At <non_root_user_directory_absolute_path>/opt/database/, execute the following command:

      rm -rf bin doc installer pgAdmin3 scripts stackbuilder include lib postgresql-<version> share
      

      Postgres is uninstalled on Linux as a non-root user.

Delete Remaining Database Files and Postgres User

To finish uninstalling PostgreSQL, remove the postgres user account and remaining database files.

Follow these steps:

  1. Delete any remaining files in the APM Database installation directory. For example, delete the contents of the /opt/database directory.
  2. Remove the user postgres by running the following command:

    userdel -r postgres
    

    The user account and its home directory is removed.
    PostgreSQL is uninstalled.

Install PostgreSQL (New Version)

In this procedure, install the APM database only. Installing the APM database installs the current version of PostgreSQL (9.2).

Even though the overall process is to upgrade the database, here you install a new, empty database. Later you restore the contents of your previous database into this new PostgreSQL installation.

Note: You can install the Enterprise Manager on one server and the APM Database (PostgreSQL) on a different or remote server. To accomplish this, run the Enterprise Manager installer on the remote server and select the "database only" option to install the PostgreSQL database. Open the Tess-db-cfg.xml file and update the connection to the Enterprise Manager. You do not have to migrate the database.

Follow these steps:

  1. Locate and launch the appropriate installer for your environment.
    The installer guides you through the installation.
    The Introduction screen opens.
  2. Click Next to continue.
    The CA End User License Agreement screen displays.
  3. Click the option to accept the terms of the agreement and click Next to continue.
    The Choose Install Set screen displays.
  4. Accept the terms of the CA End User License Agreement (EULA).
    If you are running the installer in console mode on Linux or Solaris, edit the ca-eula.txt file to accept the agreement and save the file. When the installer prompts you for the location, specify a relative or absolute location and filename.

    Note: Specify the installation path and directory using ASCII characters only. Non-ASCII characters, such as Japanese, are not supported for PostgreSQL installation.
  5. Click Database Only in the Choose Install Set window.
  6. Click Next to accept the default installation directory, or click Browse to specify a different location for the APM Database installation in the Choose Install Folder window.
    Default directories are as follows:
    • Windows: C:\Program Files\CA APM\Introscope<version>\
    • UNIX: /root/Introscope<version>/
  7. Click Next to continue and in the Choose APM Database screen, PostgreSQL is already selected.
  8. Click the Install Database option in the Install or Choose Existing APM Database window.
  9. Click Next to continue and the Target Installation Directory for the APM Database screen displays.
  10. Enter the installation directory for the APM database.
    Default directories are as follows:
    • Windows: <EM_Home>\database
    • UNIX: /opt/database
  11. Click Next to continue.
  12. Enter the database connection port and the PostgreSQL Administrator password in the APM Database Administration Settings window.

    Important! The PostgreSQL Administrator password must comply with the password security policies of your organization. The database user name cannot contain special characters. The database password can contain only alphanumeric characters.
  13. Click Next to continue and enter the database name, database user, and password in the APM Database Settings window.
  14. Click Next to continue and review the summary of the installation settings and verify that the settings are correct in the Database Configuration Summary window.
  15. Click Install to perform the installation.
  16. Click Done to close the installer.

Restore the APM Database from the Backup File

When restoring a database, you create a database from the backup file. The new database has the username and password that you specify when invoking the restore script. The amount of time it takes to restore a database is directly related to the database size.

Important! If the database exists, the restore function destroys the specified database before it creates a database with the name specified.

Follow these steps:

  1. Verify that no users are connected to the new, empty database. If users are connected to the database, you cannot restore the database. Shut down any Enterprise Managers that connect to the database.
  2. Navigate to the <Installation_Dir>/install/database-scripts/ directory for your operating system.
    For example, on a Linux or Solaris computer, navigate to the <EM_Home>/install/database-scripts/unix directory.
  3. Open a command line and run the following script:

    dbrestore-postgres.sh
    

    Use the following syntax and arguments:

    ./dbrestore-postgres.sh] [dbserverhostip] [dbinstalldir] [dbserviceuser] [dbservicepassword] [dbname] [dbuser] [dbpassword] [dbport] [backupfile]
    

    Include the following arguments:

    • dbserverhostIP
      Specifies the IP address of the computer hosting the database. If you are running it on the computer directly, you can specify localhost.
    • dbinstalldir
      Specifies the directory location of the APM database.
      If there are spaces in the directory path, the database installation directory must be the shortened version of the path. For example, c:progra~1\cawily~1. Default location is as follows:
      • Windows: <EM_Home>\database
      • Linux: opt/database
      • Solaris: opt/database/postgres/9.2-pgdg 
    • dbserviceuser
      Specifies the PostgreSQL administrator username for the current PostgreSQL installation. The default is postgres.
    • dbservicepassword
      Specifies the PostgreSQL administrator password.
    • dbname
      Specifies the database instance name. If you did not change the default, the name is cemdb.
    • dbuser
      Specifies the username for the database into which you are restoring data. If you did not change the default, this name is admin.
    Note: This name is NOT the PostgreSQL administrator username.
  4. When the restore process completes, a confirmation message displays that the restore is successful.

You have now successfully upgraded the APM Database on PostgreSQL.

Database still Connected to the Users

Symptom:

I see that the database is still connected to users.

Solution:

Restart the PostgreSQL server using the following command:

/etc/rc.d/init.d/postgresql-<version> restart

Error Messages in the Console Output

Important! If you see any message in the console other than those listed in the following section, the database restore might not have been successful.

Symptom:

I see the following message in the console when I execute "dbrestore-postgres.bat" or "dbrestore-postgres.sh":

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 857; 2612 16386 PROCEDURAL LANGUAGE
plpgsql postgres
pg_restore: [archiver (db)] could not execute query: ERROR: language "plpgsql" already exists
Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
pg_restore: WARNING: no privileges could be revoked for "public"
pg_restore: WARNING: no privileges were granted for "public"
WARNING: errors ignored on restore: 1

Solution:

Ignore the message and verify that the data restore was successful.

Symptom:

I see the following message in the console when I execute "dbrestore-postgres.bat" or "dbrestore-postgres.sh":

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 11063; 0 0 ACL public postgres
pg_restore: [archiver (db)] could not execute query: ERROR: role "postgres" does not exist  
Command was: REVOKE ALL ON SCHEMA public FROM PUBLIC; 
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
WARNING: errors ignored on restore: 1

Solution:

Ignore the message and verify that the data restore was successful.

(Optional) Change PostgreSQL Database Passwords

After you install a new APM database, particularly if you did so with default configurations, for security reasons you change the passwords for the following:

  • postgres service user account (operating system)
  • PostgreSQL administrator account (PostgreSQL)

You can enter the password into the xml file in plain text, using the property plaintext=true. When the Enterprise Manager runs for the first time, the password is encrypted. The plain text password is replaced with the encrypted version and the property plaintext is reset to plaintext=false.

<property name="plainTextPasswords">false</property>

For information about changing the service user account password, see the documentation of your operating system. For information about PostgreSQL, see the documentation installed along with PostgreSQL.

Note: You can also use pgAdmin to access the PostgreSQL database and change the password.

Follow these steps:

  1. Connect to the database with psql:
    • On Windows, click Start, Programs, PostgreSQL <version>, SQL Shell (psql).
    • On Linux: enter the following:

      psql -U username -d databasename
      
    A command-line window opens, prompting for your password.
  2. Enter the PostgreSQL Administrator password that you specified during installation.
    If the password is correct, a welcome message and some command information displays, and the command prompt changes to "postgres=#".
  3. Enter the following:

    ALTER USER postgres WITH PASSWORD 'newpassword';
    

    Where newpassword is the new password. Ensure that the new password complies with the password security requirements of your organization.

  4. Press Enter to commit the change.
    If the change is successful, ALTER ROLE displays as a confirmation message.
Was this helpful?

Please log in to post comments.

  1. Duane Nielsen
    2017-02-08 03:58

    If you change the password on the database, dont you also have to change it in tess-db-cfg.xml? Or you will break the EM...

    1. Lucie Stehnova
      2017-02-10 03:56

      Hi Duane,

      We are now checking with the developers to see how this works for 9.x releases. 
      As Hugo mentioned already, for 10.x releases, you can enter the password into the xml file in plain text, using the property plaintext=true. When it first runs, the password is encrypted, the plain text password is replaced with the encrypted version and the property plaintext is reset to plaintext=false. 

      Thank you

      Lucie

    1. Lucie Stehnova
      2017-02-13 07:29

      Hi Duane,

      the developers tested it and can confirm that for 9.6 and later, after the installation, there is <property name="plainTextPasswords">false</property> and the password is encrypted after you run the EM for the first time.

      Lucie