Upgrading the PostgreSQL database from version 9.5.2 to 12.4

Version 21

    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 14 June 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 upgrade script. However, it is not mandatory for the db_upgrade check script. Therefore, you can run the db_upgrade check script without any downtime.
    • For Linux operating systems, verify that the required libraries are present.
      • The libreadline.so library must be of version 6 or later. 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

     

    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 check script (.sh file or .bat file) to verify the compatibility of the old data with the new database version and to determine whether an upgrade can be done.
    3. Run the db_upgrade upgrade script (.sh file or .bat file) 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 [check | 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/psql/bin psql.exe -V
      Linux: $Agent_app_dir/apps/process-engine/data/db/postgresql-linux-x64-binaries/psql/bin -./postgres -V or $Agent_app_dir/apps/process-engine/data/db/postgresql-linux-x64-binaries/psql/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).
        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

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

     

    Issue

    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

    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>              password

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