Upgrading the PostgreSQL database using the replication technique

1. 1Verify the host requirements.
2. 2Restart the Process Server.
3. 3Configure the synchronization workers
4. 4Enable the replication upgrade.
5. 5Check the replication progress.
6. 6Complete the switchover.

Prerequisites

• - Since PostgreSQL 18.4 doesn't support glibc versions earlier than 2.27, the Secure Agent must run on a Linux distribution that includes glibc 2.27 or later. The Secure Agent does not support hosts with an earlier glibc version.
• - If the Secure Agent runs on Windows, install Microsoft Visual C++ Redistributable (x64) version 14.44 or later.
• - Restart the Process Server before you enable the replication upgrade.
• - For a replication upgrade, ensure that the host has free disk space equal to at least 2 times the current database size.

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.

Configure the synchronization workers

Configure the maximum number of synchronization workers for each subscription to control how many tables the replication copies in parallel. The default value is 2. Set the value based on your replication load, as shown in the following table:

Load	Data size	Recommended value
Low	<10 GB	2 (default)
Medium	10-100 GB	4
High	100+ GB	6-8

1. 1On the Secure Agent machine, go to the following directory:
<Secure Agent installation directory>\apps\process-engine\data\PostGreSql\Data
2. 2Open the user.conf file.
3. 3To set the number of synchronization workers, add the following property with a value that suits your replication load:
max_sync_workers_per_subscription = <value>
4. 4Save the user.conf file.
5. 5To apply the change without restarting the database, connect to the database and run the following query:
SELECT pg_reload_conf();
If the configuration reloads, the query returns t.
6. 6To verify the new value, run the following query:
SHOW max_sync_workers_per_subscription;
7. 7To close the database session, run \q.

Note:

Close the database session before you enable the replication upgrade. An open session can cause the replication to fail.

Enable the replication upgrade

1. 1On the Secure Agent machine, open a command prompt and 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.
Note:
The time replication takes to complete depends on the size of the database.
3. 3After 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.

Check the replication progress

Optionally, check the replication progress before you restart the Process Server to complete the switchover.

1. 1To confirm that a switchover is pending, check for the readyForSwitchover marker file in the following directory:
<Secure Agent installation directory>\apps\process-engine\data\readyForSwitchover
2. 2Open the file to view the replica details, for example:
port = 58795
name = postgre-replica-v33
version = 33
If the file exists, replication is set up and a switchover is pending.
Note:
The port is assigned per run and changes each time. Note the port value for step 4.
3. 3Browse to the psql folder of the replica. If the version differs, replace postgre-replica-v33 with the name in the marker file.
cd "<Secure Agent installation directory>\apps\process-engine\data\postgre-replica-v33\db\postgresql-<windows or linux>-x64-binaries\pgsql\bin"
4. 4To connect to the replica database, run the following command with the replica port from step 2. Do not use the default port 5432, which is the source database. Enter the database password when prompted.
Note:
Replication status is visible only on the replica instance. If you connect to the source database, the query returns 0 rows.
• ▪ 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
5. 5At 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 ready = total and pending = 0.
6. 6To close the database session, run \q. Then close the command prompt window.
Note:
An open connection to the replica database can block the switchover.

Complete the switchover

1. 1To complete the switchover to the newer PostgreSQL version, restart the Process Server after the replication completes.
2. 2To confirm that the switchover started, check the Process Server logs for the following entry:
Replication is completed. Starting Switchover
The Process Server runs on PostgreSQL version 18.4