Migrate SoftActivity database from PostgreSQL 9.6 to 15. Instructions for admin.

The latest versions of SoftActivity Monitor and TS Monitor, starting from version 14, come bundled with the on-premise PostgreSQL version 15 x64 database server, which boasts enhanced performance, security, and reliability. Notably, earlier versions of SoftActivity utilized PostgreSQL 9.6.

If you are installing SoftActivity Monitor/TS Monitor v14+ as a fresh installation, PostgreSQL 15 will be installed by default. No additional actions are needed. However, in scenarios where you are upgrading from an earlier version, PostgreSQL 15 will not be installed automatically. Furthermore, to maintain data integrity, safeguard against data corruption, and prevent service interruptions, no automatic data migration will be initiated.

For continuity, you can still operate PostgreSQL 9.6 within the SoftActivity system for a limited time, but this version is no longer officially supported and should be upgraded to version 15 by the system administrator.

To install PostgreSQL 15 you will need to perform a complete uninstall of SoftActivity and fresh installation of SoftActivity 14+. The database from the previous version can then be transferred to this new DB server. It is not obligatory to migrate old data; you can choose to do a complete reinstall and start with a fresh new database on PostgreSQL 15. If you opt for this path, you may log into the web console using the default ‘sadmin’ account. Following this, new monitoring data will begin to accumulate in your newly instantiated database.

Determine your version of PostgreSQL installed by SoftActivity software

PostgreSQL 15:

• Windows service is called SapgSrv15
• installed in “C:\Program Files\SoftActivity\Activity Monitor\postgres15”
• data dir is pgdata15
• SoftActivity v14+ comes with PostgreSQL 15

PostgreSQL 9.6:

• Windows service is called SapgSrv
• installed in “C:\Program Files\SoftActivity\Activity Monitor\postgres”
• data dir is pgdata
• SoftActivity v13 or earlier comes with PostgreSQL 9.6

Create dump from PostgreSQL 9.6 database

This step can be performed before or after installing SoftActivity v14.

To create a data dump from your old database using pg_dump from PostgreSQL 9.6, follow the instructions outlined below.

• Create a folder for the dump file. backup folder on C:\ drive is used in this example
• Open your Command Prompt.
• Change the current directory to the postgres\bin folder by typing the following command. For TS Monitor, replace Activity Monitor part with TS Monitor.

cd "%ProgramFiles%\SoftActivity\Activity Monitor\postgres\bin" 

• Important: Determine the current database name in use. Open Options – Database tab – Database Configuration, and note the name selected in the Database dropdown. In the example below the name is postgres, which is a default one. We will use postgres in the next step after -d command line parameter.

• Create a dump file from the existing database:

pg_dump --file="C:\backup\softactivity.dump" --format=c -d postgres -h localhost -U softactivity

In this command:

-d postgres specifies your database name. Can be seen in database settings window, determined in the previous step. By default: postgres
–U softactivity refers to the username used to connect to the PostgreSQL database. By default, this is ‘softactivity’.
–file  determines the location where the dump file will be stored. Please ensure that the specified folder has been created in advance.

You will be prompted to input your PostgreSQL database password. This is the password initially set during the SoftActivity installation.

Upon successful execution of this command, you will find a ‘softactivity.dump’ file in C:\backup directory. Ensure that the file size is larger than 0, otherwise it indicates a failure to create dump.

Save the Agents list

To preserve the list of computers, and their settings, we recommend to export the list, and later restore into the new version.

• Open SoftActivity Monitor, click File menu, click two arrows at the bottom, select Export Agents List.
• Select a folder, such as Documents and enter a file name, for example: agents.xml
• Click Save. You will be prompted to save passwords into the file. You can select this option but ensure the file does not leave your computer.

Upgrade to the latest SoftActivity with PostgreSQL 15

Find your registration name and key; download the latest licensed/full version from the update center.

Uninstall the older version

Launch the Uninstall of SoftActivity Monitor/TS Monitor from Apps & Features in Windows Settings. In TS Monitor you can find the Uninstall button on About tab.

Do not select “Remove Data Folder” check box, to ensure that existing screenshot images are preserved.

The uninstall action will simultaneously remove PostgreSQL 9.6.

As an optional step, you can delete the old database directory along with all its files, given that the new version will not utilize it. To do so, you need to remove the ‘pgdata‘ directory found inside the SoftActivity Data folder. By default, this is located at C:\ProgramData\Salog\data\. Please ensure not to delete the ‘scr-web’ directory, which contains screenshots.

Computer restart maybe needed to complete the uninstall process. If so, please reboot the computer or server.

Install the new version 14+

Proceed to install SoftActivity Monitor version 14 or the latest LICENSED version. Do not install the free demo. During the installation process, ensure that you select the PostgreSQL 15 component among others.

Select the same Data folder as you had in the previous version, so it can access existing screenshots. If you did not change it from default C:\ProgramData\Salog\data, you don’t have to change anything

Do NOT activate it yet by entering a registration key, so that the application and services do not start until we complete the database migration. The activation will be completed later.

Restore database dump into PostgreSQL 15

To restore your dump file into a new database using pg_restore from PostgreSQL 15, follow the steps outlined below:

• Open your Command Prompt.
• Change the current directory to the postgres15\bin folder by executing the following command:

cd "%ProgramFiles%\SoftActivity\Activity Monitor\postgres15\bin" 

• Create a new database by executing the command:

createdb -U softactivity -h localhost softactivity1

In this command, ‘softactivity1’ is the new empty database name. You can choose any desired name for the new database.
-U softactivity specifies the username used to connect to the database, where ‘softactivity’ is the default user.

If asked, input your PostgreSQL password that was set during the previous installation step.

Now restore the previously created dump file into the newly created database by running the following command:

pg_restore -d softactivity1 -h localhost -U softactivity --jobs=8 "C:\backup\softactivity.dump"

In this command, ‘softactivity1’ is the name of your new database.
-U softactivity specifies the username used to connect to the database.

Enter your postgres password used during installation stage in the previous step.

Note: “schema public already exists” error is OK and can be ignored. It does not affect the restore process.

Activate the app with new database and check the results

Now, as the data has been restored into softactivity1 database, we need to activate SoftActivity software and switch it to this new database.

• Open SoftActivity Monitor console app (or TS Monitor config app). It should ask to activate, as this is a fresh install. Copy the registration key you prepared at the earlier steps.

• Important: Open Options – Database tab – Database Configuration. From the Database dropdown select softactivity1 (the database name we restored the dump into). Click Check Connection. Click OK to save the new settings.
  

• (Not needed for TS Monitor product) Now restore the agents list that we backed up earlier. Click File menu – Import Agents list, and select your agents.xml saved earlier in Documents folder. Delete the agents.xml file since it could have contained passwords.

• Click View Reports and login into your on-premise SoftActivity web console. Ensure that you are able to login with your previous account credentials. Verify the presence of your user list on the right side of the console, and check that you can view logs and screenshots as expected.