Upgrading PostgreSQL

Installations of exacqVision Enterprise Manager 24.11 and later require an upgrade to PostgreSQL version 13+ or later. If your Enterprise Manager installation is version 24.11 or earlier, it is likely that your PostgreSQL version is 10 or earlier. Enterprise Manager running on Windows systems automatically upgrades to PostgreSQL 13+ without any input from the user. Linux systems require the user to manually upgrade.

Note: If you have exited the Enterprise Manager upgrade procedure to complete the PostgreSQL upgrade, you must first choose one of the following two options:

• Remove the existing Enterprise Manager version. This is the recommended option and preserves all Enterprise Manager configurations.

sudo apt-get --purge remove esm

• Re-run the installer for your current Enterprise Manager version.

sudo dpkg -i [CURRENT_EM_VERSION].deb

To manually upgrade PostgreSQL to version 17, complete the following tasks:

• Back up the Enterprise Manager database
• Stop all Enterprise Manager services.
• Stop the PostgreSQL services
• Install PostgreSQL
• Configure PostgreSQL database and password
• Restart the PostgreSQL and Enterprise Manager services

Backing Up the Enterprise Manager database

Note: The number 10 may differ based on your Enterprise Manager installation.

sudo /usr/lib/postgresql/10/bin/pg_dump -U postgres -d "EnterpriseManager" > ~/pg_backup.psql

Stopping Enterprise Manager services

1. On the terminal, enter the following commands:

sudo systemctl stop enterprise-importer.service
sudo systemctl stop enterprise-webservice.service
sudo systemctl stop enterprise-datarolloff.service
sudo systemctl stop enterprise-sendemail.service

2. Go to the Enterprise Manager directory.

ex) cd /usr/local/exacq/esm

3. Create a database backup.

./PostgreSQL/9.2/bin/pg_dump -U postgres -d "EnterpriseManager" > ~/pg_backup.psql

4. Stop the service.
   Note: The number 10 may differ based on your Enterprise Manager installation.

sudo systemctl stop postgresql-10.service

Stopping PostgreSQL services

sudo systemctl stop postgresql@10-main.service

Installing PostgreSQL

1. To install PostgreSQL 17, enter:

sudo apt-get install postgresql-17

2. If the 17 folder has not been created in ‘/etc/postgresql/’ check the cluster for PostgreSQL 17:

pg_lsclusters

3. If PostgreSQL 17 is not in the list, create a new cluster:

sudo pg_createcluster 17 main --datadir=/var/lib/postgresql/17/main --start

4. Check the data directory in the new cluster:

sudo -u postgres psql
SHOW data_directory;

5. If the data directory shows /var/lib/postgresql/17/main, the new PostgreSQL 17 cluster is being used correctly. Continue to the Configuring PostgreSQL database and password section below. If this still shows /var/lib/postgresql/10/main, PostgreSQL 17 still using the old database and data directory files. Continue to Step 6: Stop PostgreSQL 10 services below.
6. Stop PostgreSQL 10 services:

sudo systemctl stop postgresql@10-main

7. Update the port for PostgreSQL 17:

sudo nano /etc/postgresql/17/main/postgresql.conf

Then change the port setting to 5432

8. Change the following configurations:

sudo nano /etc/postgresql/17/main/pg_hba.conf
local all postgres trust

9. Ensure that PostgreSQL 17 is active on port 5432:

pg_lsclusters

Configuring PostgreSQL database and password

1. Log onto PostgreSQL 17:

sudo -u postgres psql

2. Set a password:

ALTER USER postgres WITH PASSWORD 'postgres';

3. Create the required database:

CREATE DATABASE "EnterpriseManager";

4. Reopen pg_hba.conf and change all auth types to md5/scram-sha-256, then save:

sudo vi /etc/PostgreSQL/{version}/main/pg_hba.conf

5. Open postgresql.conf and change max_connections to 500, then save:

sudo vi /etc/PostgreSQL/{version}/main/postgresql.conf

Restarting the PostgreSQL and Enterprise Manager services

1. To restart the PostgreSQL service, enter:

sudo systemctl restart postgresql@17-main

Note: The service name may differ on your system.

2. Load the database backup if required:

psql -U postgres -d "EnterpriseManager" -f ~/pg_backup_4.psql

3. Start all Enterprise Manager services:

sudo systemctl start enterprise-importer.service
sudo systemctl start enterprise-webservice.service
sudo systemctl start enterprise-datarolloff.service
sudo systemctl start enterprise-sendemail.service

4. Optional: If you want to downgrade to PostgreSQL 10, complete the following steps:
   1. Stop Enterprise Manager services

b. sudo systemctl stop postgresql@17-main
c. sudo systemctl start postgresql@10-main

4. Restart EM Services

5. Verify that the upgrade is successful, then uninstall the old PostgreSQL version:

sudo pg_dropcluster 10 main --stop