Upgrading the PostgreSQL database using the replication technique

Prerequisites

• - Since PostgreSQL 18.4 doesn't support SUSE Linux Enterprise Server (SLES) 12 SP5, the Secure Agent must run on SLES 12 SP6 or later.
• - If the Secure Agent runs on Windows, install a Microsoft Visual C++ redistributable later than the 2015-2022 redistributable.
• - Restart the Process Server before you enable the replication upgrade.

Restart the Process Server

1. 1Open the Administrator service and select Runtime Environments.
2. 2On the Runtime Environments page, click the name of the Secure Agent.
Note:
You might have to expand the Secure Agent group to see the list of Secure Agents within the group.
3. 3In the Agent Service Details section, hover over the row that contains the Process Server service and click the Stop Service icon.
4. 4Click the Start Service icon in the row to restart the Process Server service. After the service starts, the status changes to Up and Running. If the service fails to start, check the audit log on the Monitor tab to find the cause of the error.

Enable the replication upgrade

1. 1On the Secure Agent machine, go to the following directory:
<Secure Agent installation directory>/apps/process-engine/data/db/util
Run the database upgrade script with the enable_replication option.
• ▪ For Linux. ./db_upgrade.sh enable_replication
• ▪ For Windows. db_upgrade.bat enable_replication
2. 2Restart the Process Server to start the replication upgrade. In the Process Server logs, confirm that the replication started by looking for the following entries:
• ▪ Starting replication
• ▪ Replication has started successfully
Wait for the data replication from the earlier PostgreSQL version to the newer PostgreSQL version to finish. Replication is complete when all the tables are copied to the newer version.
3. 3Check the replication progress to determine when the data replication from the earlier PostgreSQL version to the newer PostgreSQL version has finished.
1. aConfirm that a switchover is pending. Check for the marker file in the process-engine data folder:
<Secure Agent installation directory>\apps\process-engine\data\readyForSwitchover
Open the file. It shows the replica details, for example:
port = 58795
name = postgre-replica-v33
version = 33
If the file exists, replication was set up and a switchover is pending.
Note:
Note the port value from this file; you need it in step c. This port is assigned per run and changes each time; do not assume a fixed number.
2. bOpen a command prompt at the replica's psql folder. Adjust postgre-replica-v33 to the name from the marker file if the version differs:
cd "<Secure Agent installation directory>\apps\process-engine\data\postgre-replica-v33\db\postgresql-<windows or linux>-x64-binaries\pgsql\bin"
3. cConnect to the replica database using the replica port you noted in step a, not the default port 5432, which is the source database:
• ▪ For Windows:
psql -U bpeluser -h <DB_HOST> -p <replica_port> -d activevos
• ▪ For Linux:
./psql -U bpeluser -h <DB_HOST> -p <replica_port> -d activevos
Enter the database password when prompted.
Note:
The replication status is only visible on the replica instance. If you connect to the source database instead, the query returns 0 rows.
4. dAt the activevos=# prompt, run the following completeness check:
SELECT count(*) AS total,
count(*) FILTER (WHERE srsubstate IN ('r','s')) AS ready,
count(*) FILTER (WHERE srsubstate NOT IN ('r','s')) AS pending
FROM pg_subscription_rel;
Replication is complete when the pending count reaches 0 and all tables show as ready.
5. eOnce the result shows ready = total and pending = 0, close the database session and terminal before starting the switchover: \q. Then close the command prompt window.
Note:
Leaving an open connection to the replica database can block the switchover.
4. 4After replication completes, restart the Process Server to complete the switchover to the newer PostgreSQL version. To confirm that the switchover has started, check the Process Server logs for the following entry:
Replication is completed. Starting Switchover.
The Process Server runs on PostgreSQL version 18.4.