How to upgrade to PostgreSQL 13 from PostgreSQL 9 on Web Help Desk running on Linux

Starting with Web Help Desk version 12.7.7, PostgreSQL 13 is provided as an option. Although Web Help Desk version 12.7.7 still uses PostgreSQL 9, it is recommended that you switch to PostgreSQL 13 for better security and performance. Starting with Web Help Desk version 12.7.8, PostgreSQL 9 is removed and now runs exclusively on PostgreSQL 13. This article guides you through the upgrade process to PostgreSQL 13.

If you are currently on version 12.7.7 of the Web Help Desk, a script to perform the upgrade to PostgreSQL 13 is available, and you can perform the following steps. The steps assume you are using the embedded PostgreSQL database.

1. Log in to the Web Help Desk UI and verify that you are still on PostgreSQL 9. Navigate to Setup>General>Database
2. You should see the location of the Backup Folder is /usr/local/webhelpdesk/pgsql9/Backups
3. Ensure that the Use Embedded Database is ticked.
4. Click on the Backup Now button to create a backup and wait for it to complete
5. Log in to the Linux server as the root user
6. Navigate to /usr/local/webhelpdesk/bin
7. Run database upgrade script as ./databaseUpgrade
8. You will see the following when it runs and completes

./databaseUpgrade
- Prepare PostgreSQL for upgrade.
Stopping postgresql-9.2 service:                           [  OK  ]
- Backup PostgreSQL embedded database finished successfully.
- Upgrade PostgreSQL 9.2 embedded database to 13.3.
- Upgrade PostgreSQL 9.2 embedded database to 13.3 finished successfully.
- Post upgrade actions.
Created symlink from /etc/systemd/system/PostgreSQLServiceWHD.service to /usr/local/webhelpdesk/bin/pgsql13/etc/PostgreSQL13ServiceWHD.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/PostgreSQL13ServiceWHD.service to /usr/local/webhelpdesk/bin/pgsql13/etc/PostgreSQL13ServiceWHD.service.
Created symlink from /etc/systemd/system/PostgreSQL13ServiceWHD.service to /usr/local/webhelpdesk/bin/pgsql13/etc/PostgreSQL13ServiceWHD.service.
- Post upgrade actions finished successfully.

9. You may now log back into the Web Help Desk UI and verify the Backup Folder is /usr/local/webhelpdesk/pgsql13/Backups

If you are upgrading to version 12.7.8 and above, PostgreSQL 9 is removed, and you may lose your database. It is important that you create a backup of the database and upgrade to PostgreSQL 13 first by following the steps above before upgrading to version 12.7.8. Take a server snapshot before proceeding with the upgrade.

If you performed the upgrade but did not upgrade to PostgreSQL 13, but have a backup of the PostgreSQL 9 database, you may use this backup to restore to PostgreSQL 13. You may follow the steps below. This assumes that you already upgraded to Web Help Desk 12.7.8 and above.

1. Log in to the Web Help Desk server as root user
2. Ensure the Web Help Desk service is started and the PostgreSQL 13 service is running. You may verify the processes by running the commands:

ps aux | grep java

ps aux | grep postgres

3. Pay attention to the location of the PostgreSQL data directory; it should be /usr/local/webhelpdesk/bin/pgsql13/data/
4. Navigate to /usr/local/webhelpdesk/bin/pgsql13/data/ and edit the pg_hba.conf and set the following lines to trust

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

5. Run the command /usr/local/webhelpdesk/whd restart
6. Create the whd database user by running the command:

/usr/local/webhelpdesk/bin/pgsql13/bin/psql -Upostgres -p20293 template1 -c "CREATE ROLE whd NOSUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN PASSWORD 'whd'"

7. Create the database by running the command:

/usr/local/webhelpdesk/bin/pgsql13/bin/createdb -p20293 -Uwhd whd

8. Restore the database from backup by running the command:

/usr/local/webhelpdesk/bin/pgsql13/bin/pg_restore -Fc -v -p 20293 -U whd -d whd "path_to_whd.pgdump"

9. Create a password for the whd user account:

/usr/local/webhelpdesk/bin/pgsql13/bin/psql -p20293 -Uwhd whd

\password

Type in the password as required

\q to quit

10. Navigate to /usr/local/webhelpdesk/bin/pgsql13/data/ and edit the pg_hba.conf and set the lines previously modified from trust to md5
11. Restart the Web Help Desk service by running the command:

/usr/local/webhelpdesk/bin/whd restart

12. Log in back to the Web Help Desk UI
13. If you are presented to connect to the database:
14. Select External PostgreSQL database
15. Enter localhost for host and 20293 for port
16. Enter the database name, username and password
17. Click Test, and if it is successful, click Next and proceed.

Disclaimer: Scripts are not supported under any SolarWinds support program or service. Scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.