3.3 Updating the PostgreSQL Database

The following considerations apply before updating PostgreSQL:

  • NetIQ recommends you to update PostgreSQL to the latest version when you are using PostgreSQL shipped with Identity Manager and when PostgreSQL (shipped with Idenity Manager) is installed on the same server as Identity Applications or Identity Reporting. For information on the supported versions of PostgreSQL, see the Identity Manager 4.8.x System Requirements Guide.

  • If Identity Vault and PostgreSQL are installed on the same server, update Identity Vault before you update PostgreSQL.

NOTE:In addition to the default capabilities offered by PostgreSQL 12.6, this service pack allows you to configure the PostgreSQL database with SSL (OpenSSL 1.0.2y built with FIPS). This service pack also bundles the PostgreSQL Contrib packages.

  1. Stop and disable the PostgreSQL service running on your server.

  2. Navigate to the directory where PostgreSQL is installed. For example, C:\Netiq\IDM.

  3. Rename the postgres directory.

    For example, rename postgres to postgres_old.

  4. Remove the old PostgreSQL service by running the following command:

    sc delete <"postgres service name">

    For example, sc delete "NetIQ PostgreSQL"

  5. Download and mount the Identity_Manager_4.8.4_Windows.iso file.

  6. Navigate to the <ISO mounted location>\common\postgres directory and run the NetIQ_PostgreSQL.exe file.

    NOTE:Ensure that you have the Administrator privileges for the old and new PostgreSQL installation directories.

  7. Specify the path where you want to install PostgreSQL. For example, C:\Netiq\IDM.

  8. Click Next.

  9. Specify the password for the postgres user.

  10. Specify the PostgreSQL port. The default port is 5432.

  11. Do not select the Create database login account and Create empty database check boxes.

  12. Click Next.

  13. Review the details on the Pre-Installation summary page and click Next.

  14. Stop the newly installed PostgreSQL service.

    Go to Services, search for NetIQ PostgreSQL service, and stop the service.

    NOTE:Appropriate users can perform stop operations after providing valid authentication.

  15. Change the permissions for the newly installed PostgreSQL directory by performing the following actions:

    1. (Optional) If postgres user is not created, then perform the following steps to create a postgres user:

      1. Go to Control Panel > User Accounts > User Accounts > Manage Accounts.

      2. Click Add a user account.

      3. In the Add a User page, specify postgres as the user name and provide a password for the user.

    2. Assign permissions for the postgres user to the existing and newly installed PostgreSQL directories. Right-click the corresponding directories and go to Properties > Security > Edit.

    3. Select Full Control for the user to provide complete permissions.

    4. Click Apply.

  16. Access the PostgreSQL directory as postgres user.

    1. Log in to the server as postgres user.

      Before logging in, make sure that postgres can connect to the Windows server by verifying if a remote connection is allowed for this user.

    2. Delete the data directory from the new PostgreSQL installed location.

      For example, C:\NetIQ\IDM\postgres\data.

    3. Open a command prompt and set PGPASSWORD by using the following command:

      set PGPASSWORD=<your pg password>

    4. Change to the newly installed PostgreSQL directory.

      For example, C:\NetIQ\IDM\postgres\bin.

    5. Based on the encoding type that is set for the database, execute the following initdb commands as a postgres user from the bin directory. By default, the encoding type is set to WIN1252.

      If the encoding type is set to WIN1252, run the following command:

      initdb.exe -D <new_data_directory> -E <Encoding> WIN1252 -U postgres

      For example, initdb.exe -D C:\NetIQ\IDM\postgres\data -E WIN1252 -U postgres

      If the encoding type is set to UTF8, run the following command:

      initdb.exe -D <new_data_directory> -E <Encoding> UTF8 -U postgres

      For example, initdb.exe -D C:\NetIQ\IDM\postgres\data -E UTF8 -U postgres

    6. Navigate to the C:\NetIQ\idm\postgres\data\ directory, edit the pg_hba.conf file, and set the Method type from md5 to trust.

      IMPORTANT:You must also set the Method type from md5 to trust in the pg_hba.conf file located in the C:\NetIQ\idm\postgres_old\data\ directory.

  17. Navigate to the C:\NetIQ\idm\postgres\bin directory and run the following command:

    pg_upgrade.exe --old-datadir "C:\NetIQ\IDM\postgres_old\data" --new-datadir

    "C:\NetIQ\IDM\postgres\data" --old-bindir

    "C:\NetIQ\IDM\postgres_old\bin" --new-bindir

    "C:\NetIQ\IDM\postgres\bin"

  18. Once PostgreSQL is upgraded successfully, perform the following steps:

    1. Navigate to the C:\NetIQ\IDM\postgres_old directory.

    2. Copy the pg_hba.conf and postgresql.conf files.

    3. Navigate to C:\NetIQ\IDM\postgres directory.

    4. Replace the files you copied in Step 18.b.

  19. Start the PostgreSQL service.

    Go to Services, search for NetIQ PostgreSQL service, and start the service.

    NOTE:Appropriate users can perform start operations after providing valid authentication.

  20. (Optional) To ensure that the old cluster’s data files are deleted and the service does not start automatically, perform the following steps:

    1. Log in as postgres user.

    2. Navigate to the C:\NetIQ\IDM\postgres\bin directory.

    3. Run the analyze_new_cluster.bat and delete_old_cluster.bat files.