Upgrading PostgreSQL

Installations of exacqVision Enterprise Manager 24.11 and later require an upgrade to PostgreSQL version 13+ or later. 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 13+, complete the following tasks:

  • Stop all Enterprise Manager services.
  • Install PostgreSQL
  • Create a database on the new PostgreSQL installation
  • Restart the PostgreSQL and Enterprise Manager services

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
  1. Go to the Enterprise Manager directory.
ex) cd /usr/local/exacq/esm
  1. Create a database backup.
./PostgreSQL/9.2/bin/pg_dump -U postgres -d "EnterpriseManager" > ~/pg_backup.psql
  1. Stop the service.
    Note: The number 10 may differ based on your Enterprise Manager installation.
sudo systemctl stop postgresql-10.service

Installing PostgreSQL

  1. To install PostgreSQL 13, enter:
sudo apt-get install postgresql-13

Or, for the latest version, enter:

sudo apt-get install postgresql-17
  1. Change the default PostgreSQL connection type to from peer to trust, then save:
sudo vi /etc/PostgreSQL/{version}/main/pg_hba.conf

Note: Ensure that the line is `local all postgres trust`.

  1. Restart the PostgreSQL service:
    Note: The service name may differ on your system.
sudo systemctl restart postgresql@13-main.service

Creating a database on the new PostgrSQL installation

  1. To create a database on the new PostgreSQL installation, enter:
createdb -U postgres -w "EnterpriseManager"
  1. Change the PostgreSQL password, enter:
psql -U postgres
  1. Then, in the psql terminal:
ALTER USER postgres WITH PASSWORD 'postgres';

Note: If you change the password from the one used in the previous version, you must change the password stored in Enterprise Manager.

  1. To change the password stored in Enterprise Manager, enter:
sudo ./installer decrypt
sudo vi EnterpriseSystemManager.tmp
  1. Edit the password field, then save the sudo ./installer encrypt file.
  2. 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
  1. Open postgresql.conf and change max_connections to 500, then save:
sudo vi /etc/PostgreSQL/{version}/main/postgresql.conf

Restarting the PostgreSQL service

  1. To restart the PostgreSQL service, enter:
sudo systemctl restart postgresql@13-main.service

Note: The service name may differ on your system.

  1. Load the database backup:
psql -U postgres -d "EnterpriseManager" -f ~/pg_backup.psql
  1. 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
  1. Verify that the upgrade is successful, then uninstall the old PostgreSQL version:
sudo ./PostgreSQL/9.2/uninstall-postgresql and select option (1)