Technical Tip: Upgrading a FortiSOAR externalized PostgreSQL database

Steps for a major upgrade of a FortiSOAR externalized PostgreSQL database, for example, from PostgreSQL 14 to PostgreSQL 16:

Pre-requisites:

1. Upgrade the FortiSOAR instance to the required version. For more information, see the FortiSOAR Upgrade Guide.
2. Stop all the FortiSOAR instance services before upgrading PostgreSQL.
3. Take a snapshot of the VM before starting the upgrade.
4. Back up the following files from /var/lib/pgsql/14/data:
   • Pg_hba.conf
   • Postgresql.conf

Upgrade Process:

1. Stop the PostgreSQL service using the following command:

systemctl stop postgresql-14.service

2. Rename the PostgreSQL-14 data directory using the following command:

mv /var/lib/pgsql/14/data /var/lib/pgsql/14/data_bkp

3. Remove the existing PostgreSQL installation from the system using the following command:

yum remove PostgreSQL

4. Upgrade the OS of the externalized DB. There are two ways to upgrade OS: the leapp method or the rpm method. See the following articles for details:

• Upgrading from RHEL 8 to RHEL 9
• How to Upgrade From RHEL 8 to RHEL 9 (Step by Step)
  

   

Note:

Ensure the FortiSOAR upgrade is completed before proceeding with the OS upgrade.

5. After upgrading the OS, modify the PostgreSQL-16 repository configuration file /etc/yum.repo.d/fsr-pg.repo:

[fsr-pgdg]
name=FortiSOAR PostgreSQL repository
baseurl=https://repo.fortisoar.fortinet.com/7.5.0/third-party/postgres/pgdg16/
enabled=1
gpgcheck=1
gpgkey=https://repo.fortisoar.fortinet.com/7.5.0/third-party/postgres/RPM-GPG-KEY-PGDG-16

6. Install the PostgreSQL-16 packages on the system using the following commands:
   
   yum install postgresql16-server postgresql16
   yum install postgresql16-contrib

7. Initialize the PostgreSQL database using the following command:
   
   /usr/pgsql-16/bin/postgresql-16-setup initdb
   
   
8. Rename the PostgreSQL-14 data directory using the following command:
   
   mv /var/lib/pgsql/14/data_bkp /var/lib/pgsql/14/data
   

9. Back up the fsr-pg.repo file and remove it from the /etc/yum.repo.d directory. Create a new file named fsr-pg-14.repo in the same directory. Then, add the following content to the fsr-pg-14.repo file: 
   
   [fsr-pgdg]
   name=FortiSOAR PostgreSQL repository
   baseurl=https://repo.fortisoar.fortinet.com/7.5.0/third-party/postgres/pgdg14/
   enabled=1
   gpgcheck=1
   gpgkey=https://repo.fortisoar.fortinet.com/7.5.0/third-party/postgres/RPM-GPG-KEY-PGDG-14​
   
   
10. Upgrade from PostgreSQL-14 to PostgreSQL-16 using the following command:

cd /var/lib/pgsql && sudo -u postgres /usr/pgsql-16/bin/pg_upgrade --old-datadir=/var/lib/pgsql/14/data --new-datadir=/var/lib/pgsql/16/data/ --old-bindir=/usr/pgsql-14/bin/ --new-bindir=/usr/pgsql-16/bin/ -j 8 –k

11. Once the upgrade is successful, reindex the database using the following command:

reindexdb -U cyberpgsql --jobs=8 --all; sudo cp -f /var/lib/pgsql/14/data/pg_hba.conf /var/lib/pgsql/16/data/pg_hba.conf

12. Once reindexing is complete, compare the new postgresql.conf and pg_hba.conf files with the backed-up ones and reapply any custom configurations.

Note:
This step is necessary only if custom changes are made to the configuration files.

13. Restart the PostgreSQL 16 service using the following command and verify all the data:

systemctl restart postgresql-16.service

14. Remove the fsr-pg-14.repo file and restore the fsr-pg.repo file at /etc/yum.repo.d.

Steps for a minor upgrade of a FortiSOAR externalized PostgreSQL database, for example, from PostgreSQL 16 to PostgreSQL 16.x:

Pre-requisites.

1. Upgrade the FortiSOAR instance to the required version. For more information, see the FortiSOAR Upgrade Guide.
2. Stop all the FortiSOAR instance services before upgrading PostgreSQL.
3. Take a snapshot of the VM before starting the upgrade.
4. Back up the following files from /var/lib/pgsql/16/data:
   • Pg_hba.conf
   • Postgresql.conf
     

Upgrade Process:

1. Modify the PostgreSQL repository configuration file /etc/yum.repo.d/fsr-pg.repo as follows:
   
   

[fsr-pgdg]
name=FortiSOAR PostgreSQL repository
baseurl=https://repo.fortisoar.fortinet.com/7.6.1/third-party/postgres/pgdg16/
enabled=1
gpgcheck=1
gpgkey=https://repo.fortisoar.fortinet.com/7.6.1/third-party/postgres/RPM-GPG-KEY-PGDG-16​

2. Upgrade PostgreSQL using the following command:

yum update postgresql16 –y

3. Once the upgrade process is complete, compare the new postgresql.conf and pg_hba.conf files with the backed-up ones and reapply any custom configurations.

Note:
This step is necessary only if custom changes are made to the configuration files.