Upgrading the PostgreSQL database from version 9.5.2 to 12.4

Version 40

    Overview

    After you upgrade to the February 2021 release, you can upgrade the PostgreSQL database from version 9.5.2 to 12.4. You can choose to upgrade at your own convenience by manually running scripts provided by Informatica. The scripts take a backup of the existing database version so that you can restore to the old database version in case of any upgrade issue.

     

    Click the following link to view the upgrade calendar:

    https://network.informatica.com/community/informatica-network/products/cloud-integration/place-calendar.jspa?container=2…

     

    Advantages of upgrading to version 12.4

    Customers should note that PostgreSQL version 9.5 has been placed under End-of-Life status as of 11 February 2021. Use of version 12.4 will be required starting Monday 5 July 2021. We encourage customers to plan their upgrade of the database to version 12.4 before this date.

     

    Additionally, consider upgrading to version 12.4 to take advantage of the following features:

    • Improved security features and security vulnerability fixes
    • Better performance owing to features such as parallel table scans, parallel query support, parallel joins, and aggregates
    • Support for parallel index scans
    • Performance improvements for queries on tables with partitions
    • Better indexing and auto vacuuming

     

    Prerequisites

    Before you upgrade, ensure that the following prerequisites are met:

    • You have sufficient free disk space for the upgrade. Informatica recommends that you have a minimum free disk space that is twice the size of the Data folder within the following location:
      <Secure Agent installation directory>\apps\process-engine\data\PostGreSql\Data
    • Stop the PostgreSQL database and the Process Engine. In case of a cluster set-up, apart from the primary node, you must also stop the Process Engine on the secondary nodes.
      Note: This step is mandatory to run the db_upgrade script with the upgrade command line option. However, it is not mandatory when you run the db_upgrade script with the check command line option. Therefore, you can run the db_upgrade script with the check command line option without any downtime. See the Upgrade Steps section for more information.

              To stop the Process Engine, go to Administrator > Runtime Environments > click on the agent name > select the service under 'Agent           Service Start or Stop' > click on Stop

              To stop the PostgreSQL database, on the secure agent server go to <Secure Agent>\apps\process-engine\data\db\util and run           server_stop.bat or server_stop.sh script

    • For Linux operating systems, verify that the required libraries are present.
      • The libreadline.so library must be of version 6. To find the version number, run the following command:

                    ldconfig -p | grep readline

      • The GLIBC version must be later than 2.14. To find the version number, run the following command: ldd --version
    • For Windows operating systems, verify that the Microsoft Visual C++ 2015 redistributable package (x64) or a later version is installed on the machine. Use one of the following options to verify:
      • Verify that the vcruntime140.dll file exists in the following location: C:\Windows\System32
      • Verify that the latest version of the Microsoft Visual C++ redistributable shows up when you click Start > Add or remove programs.

             You can download the package from the following link:
              https://www.microsoft.com/en-in/download/details.aspx?id=48145

     

    NOTE: Please make sure to run all the commands/scripts with the same user with which the agent is running. On Linux, run the ps -ef command and check the owner of the agent java processes. On Windows, open Task Manager and see the user name of the agent java processes.

     

     

    Upgrade Steps

    Perform the following steps to upgrade the PostgreSQL database from version 9.5.2 to 12.4:

    1. Navigate to the following directory that contains all the upgrade scripts:
      <Secure Agent installation directory>\apps\process-engine\data\db\util
    2. Run the db_upgrade script (.sh file or .bat file) with the check command line option to verify the compatibility of the old data with the new database version and to determine whether an upgrade can be done. Use the following syntax:
      ./db_upgrade.sh check <dbusername> <dbpassword> <dbport (default:5432)>
    3. Run the db_upgrade script (.sh file or .bat file) with the upgrade command line option to upgrade the existing database version if all the prerequisites are met and the db_upgrade check script completes successfully.
      Note: If you changed the default user name, password, and ports, you will need to specify these details while running the script. For example, use the following syntax:
      ./db_upgrade.sh upgrade <dbusername> <dbpassword> <dbport (default:5432)>
      Otherwise, you can exclude the database user name, password, and port details.
    4. Run the following command to verify that the upgrade was successful:
      Windows: $Agent_app_dir/apps/process-engine/data/db/postgresql-windows-x64-binaries/pgsql/bin psql.exe -V
      Linux: $Agent_app_dir/apps/process-engine/data/db/postgresql-linux-x64-binaries/pgsql/bin -./postgres -V or $Agent_app_dir/apps/process-engine/data/db/postgresql-linux-x64-binaries/pgsql/bin -./psql -V
      If the upgrade was successful, the script returns the output as 12.4.
    5. If the upgrade completed successfully, perform the following steps:
      1. Navigate to the following directory:
        <Secure Agent installation directory>\apps\process-engine\data\db\util
      2. Run the server_start script (.sh file or .bat file) to start the PostgreSQL database.
      3. Run the server_status script (.sh file or .bat file) to verify if the PostgreSQL database is up and running. If the database is up and running, you see the following message: accepting connections
      4. Run the auto-generated analyze_new_cluster script (.sh file or .bat file). If it asks for password, type bpel which is the default password.
        The analyze_new_cluster script generates the optimizer statistics for the data that is migrated to the new database version. The Query Planner uses these statistics to determine the most efficient execution plans for the query.
      5. Restart the Process Engine.
      6. Optionally, if you want to free up disk space, you can run the db_delete_old_version script (.sh file or .bat file) to delete the backup of the old database version.
    6. If the upgrade did not complete successfully and you want to restore the old database version, run the db_restore_old_version script (.sh file or .bat file).
      If you changed the default user name, password, and ports, you will need to specify these details while running the script. For example, use the following syntax:

            db_restore_old_version.bat <dbusername> <dbpassword> <dbport (default:5432)>
            Otherwise, you can exclude the database user name, password, and port details.

     

    Upgrade Logs

    You can access the upgrade logs from the following location:

    <Secure Agent installation directory>/apps/process-engine/logs/PostgreSql/upgrade.log

     

    Rotating PostgreSQL Logs

    The PostgreSQL log is available under the following directory:

    <Secure Agent installation directory>/apps/process-engine/logs/PostGreSql/postgresql.log

     

    The PostgreSQL log gets bulky over time. You can configure log rotation to reduce the file size and easily manage the file.

     

    To rotate the logs based on time, perform the following steps:

    1. Create a file named user.conf under the following location:
      <Secure Agent installation directory>/apps/process-engine/data/PostGreSql/Data
    2. Add the following properties to the user.conf file:

            log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

    log_rotation_age=1440 (This value is in minutes. Hence, a value of 1440 will rotate the logs every day. The file will be overwritten every day.)

     

    To rotate the logs based on size, perform the following steps:

    1. Create a file named user.conf under the following location:
      <Secure Agent installation directory>/apps/process-engine/data/PostGreSql/Data
    2. Add the following properties to the user.conf file:

            log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

            log_rotation_size=10240 (This value is in kilobytes. Hence, a value of 10240 will rotate the logs after the file size exceeds 10MB. The file            will be overwritten when the file size exceeds 10 MB.)

            log_truncate_on_rotation=on

     

    Changes take effect after you restart the PostgreSQL database.

     

    Script Changes

     

    The syntax of some scripts has changed in the February 2021 release. The following table describes the changes:

     

    Script NameOld SyntaxNew Syntax Effective from the February 2021 Release Notes
    db_backupdb_backup.bat <dbusername> <dbpassword> <path to backup file along with file name as .dump extension>db_backup.bat <dbusername> <dbpassword> <path to backup file along with file name as .dump extension><dbport (default:5432)>The dbport argument is optional. You can specify the dbport argument if you use a port that is different from the default port value of 5432. If you do not specify the dbport argument, the default value of 5432 is used.

    db_maintenance

    db_maintenance.bat <dbusername> <dbpassword> [vacuum | reindex] [table1 table2 ...]

    db_maintenance.bat <dbusername> <dbpassword> <dbport> [vacuum | reindex] [table1 table2 ...]The dbport argument is mandatory even if you use the default 5432 port.
    db_restoredb_restore.bat <dbusername> <dbpassword> <path to dump file>db_restore.bat <dbusername> <dbpassword> <path to dump file> <dbport (default:5432)>

    The dbport argument is optional. You can specify the dbport argument if you use a port that is different from the default port value of 5432. If you do not specify the dbport argument, the default value of 5432 is used.

     

     

    Troubleshooting

     

    Issue 1

    After the upgrade, when I run the analyze_new_cluster script, I see the following error:

    could not connect to database template1: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

    Solution

    Manually add the -h /tmp or -h localhost parameter to the analyze_new_cluster script as shown in the following examples, and then run the script:

    <path to postgres bin folder>/vacuumdb -U bpeluser --all --analyze-in-stages -h /tmp

    or

    <path to postgres bin folder>/vacuumdb -U bpeluser --all --analyze-in-stages -h localhost

     

     

    Issue 2

    When I run the upgrade script, I see the following error even though I have stopped the Process Engine:
    "Process engine is up and running"

    Solution

    Perform the following steps to resolve this issue:

    1. Navigate to the following directory:
      Windows: <Secure Agent installation directory>/apps/process-engine/data/db/postgresql-windows-x64-binaries/pgsql/bin
      Linux: <Secure Agent installation directory>/apps/process-engine/data/db/postgresql-linux-x64-binaries/pgsql/bin
    2. Run the following command:
      psql -U bpeluser -d activevos
      If you are prompted for a password, specify the password as bpel and press Enter.
      [if you see a 'No such file.... ' error, please see issue 3.]
    3. Run the following query to find the engines whose engineId have the state value set to 1:
      SELECT * FROM AEENGINE;
    4. Run the following query to update the engineId state value to 0:
      UPDATE AEENGINE SET STATE=0 WHERE ENGINEID=<engineID from the previous command in step 3>;

     

    Issue 3

    ERROR: could not connect to server: could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"

     

    To resolve this issue, add the following path to the LD_LIBRARY_PATH environment variable:

    For example:

    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:<agent_home>/apps/process-engine/data/db/postgresql-linux-x64-binaries/pgsql/lib

     

    NOTE: Replace <agent_home> as per your agent installation directory.

     

     

    Issue 4

    I see the following error when I run the scripts on Linux operating systems:

    "error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory"

    Solution

    Set the LD_LIBRARY_PATH variable to point to the postgre lib folder. For example:

    export LD_LIBRARY_PATH=<Secure Agent installation directory>/apps/process-engine/data/db/postgresql-linux-x64-binaries/pgsql/lib

     

    Issue 5

    In a cluster set-up, the secondary node fails to start up after upgrade.

    Solution

    Perform the following steps to resolve this issue:

    1. Stop the Process Engine and PostgreSQL database on the primary node.
    2. On the primary node,  navigate to the pg_hba.conf file located under the following directory:
      <Secure Agent installation directory>/apps/process-engine/data/PostGreSql/Data
    3. Add the following line to the pg_hba.conf file:
      # TYPE      DATABASE        USER            ADDRESS                                                METHOD
        host        all                      all                  <ip address of secondary node>/32              password

            If there are multiple secondary nodes, you must repeat this line for each secondary node.

     

    Issue 6

    How can I troubleshoot issues related to the upgrade script?

    Solution

    The upgrade script (db_upgrade.bat/.sh) creates log files in the following directory:
    <Secure Agent installation directory>/apps/process-engine/data/db/util

    You can use the log files to troubleshoot issues that occurred during the upgrade.

     

    You can also enable command line logging for the upgrade script. This helps in determining the exact line where the script failed.

     

    Perform the following steps to enable command line logging:

    For Windows: In the first line of the script, change @ECHO off to @ECHO on.

    For Linux: In the second line of the script, add the following phrase: set -x

     

    Note: You must not manually terminate the upgrade script while it is running. Otherwise, various directories such as the data folder, PostgreSQL binaries, and backup folder might be moved to other directories. Running the db_upgrade script again might lead to unexpected errors and failures. If you encounter such issues, run the db_restore_old_version script.

     

    Frequently Asked Questions (FAQs)

    1. How do I know if I am using the PostgreSQL database?

    The Application Integration service uses Process Server to run Application Integration assets. Process Server runs on a PostgreSQL database. Therefore, if you use the Application Integration service and use Process Server to run Application Integration assets, you are using the PostgreSQL database.

     

    2. Is it mandatory to upgrade the PostgreSQL database?

    Yes. If you use Process Server to run Application Integration assets, you must upgrade the PostgreSQL database to version 12.4 before July 5, 2021.

     

    3. How do I find out the assets that use the PostgreSQL database?

    All the assets that have been published on an agent use the PostgreSQL database to store the metadata.

     

    4. What happens if I do not upgrade the Process Server PostgreSQL database from version 9.5 to version 12.4 before the July 2021 release?
    The PostgreSQL database version 9.5 has been placed under End-of-Life (EOL) status as of February 11, 2021.

    Informatica recommends that you upgrade the Process Server PostgreSQL database from version 9.5 to version 12.4 by July 5, 2021, before the July 2021 release.

    After the July 2021 release, Informatica will not be under any obligation to support Secure Agent Process Server issues arising out of the usage of unsupported versions of the PostgreSQL database.

     

    5. How much time will the upgrade take?

    The upgrade time depends on the size of the database. It can range from a few minutes to a couple of hours.

     

    6. Instead of an upgrade, if I uninstall the existing version of the PostgreSQL database, and then perform a new installation, which version of the PostgreSQL database will be used?

    For new Secure Agent installations, the Process Server package uses PostgreSQL database version 12.4.

     

    7. I am using RHEL 6 for the Secure Agent host. RHEL 6 uses GLIBC version 2.14. However, the PostgreSQL database requires a GLIBC version later than 2.14. What are my options?

     

    RHEL 6 ended its maintenance support on November 30, 2020 and is currently in the Extended Life phase. In 2021, Informatica will also be dropping support for RHEL 6. Hence, Informatica recommends users to upgrade from RHEL 6 to a later version that supports a GLIBC version later than 2.14.

    Alternatively, users can wait for the next manual upgrade opportunity available as part of our July 2021 release, where they can directly upgrade from PostgreSQL database version 9.5 to version 12.6. With this upgrade, there would not be any dependency on using a GLIBC version later than 2.14.