Upgrading the PostgreSQL database

Upgrade your PostgreSQL database from version 9.x to version 13.x if you are using BMC Helix Data Manager versions 21.3.01, 21.3, 21.05.

For more information on PostgreSQL 13.x, see PostgreSQL Documentation.

To prepare for the upgrade

1. In the location C:\Users\Administrator\, create a new folder called New_Repository.

2. Download the PostgreSQL 13.x binary files zip from the location PostgreSQL 13 binaries.

3. Copy and extract the downloaded zip file to the New_Repository folder.
4. Open the Windows Services Manager and stop the BMC HDM Repository, BMC HDM Engine, and BMC HDM Workbench services.
5. In C:\Users\Administrator, create a new folder named Old_Repository.
6. Navigate to the HDM Repository installation folder and copy the pgdata and pgsql folder.
   The default location of the HDM Repository installation folder is C:\Program Files\BMC Software\Helix Data Manager\Repository.
7. In C:\Users\Administrator\Old_Repository, paste the pgdata and pgsql folders.
8. Navigate to C:\Users\Administrator\Old_Repository\pgdata folder and edit the pg_hba.conf file.

9. In the following code, change the references of md5 to trust:

   # IPv4 local connections:
    host all all 127.0.0.1/32 md5
   # IPv6 local connections:
    host all all ::1/128 md5

   The new code is as follows:

   # IPv4 local connections:
    host all all 127.0.0.1/32 trust
   # IPv6 local connections:
    host all all ::1/128 trust
10. Save the file.

To perform the upgrade

1. Open the command prompt and navigate to C:\Users\Administrator\New_Repository.

2. Run the following commands one after another:

   pgsql\bin\initdb -D "C:\Users\Administrator\New_Repository\pgdata" -U postgres -E "UTF-8" --lc-collate="English_United States.1252" --lc-ctype="English_United States.1252"
   

   pgsql\bin\pg_ctl start -D "C:\Users\Administrator\New_Repository\pgdata"

   pgsql\bin\pg_ctl stop -D "C:\Users\Administrator\New_Repository\pgdata"
3. Navigate to C:\Users\Administrator\New_Repository\pgdata folder and edit the pg_hba.conf file.

4. In the following code, change the references of md5 to trust:

   # IPv4 local connections:
    host all all 127.0.0.1/32 md5
   # IPv6 local connections:
    host all all ::1/128 md5

   The new code is as follows:

   # IPv4 local connections:
    host all all 127.0.0.1/32 trust
   # IPv6 local connections:
    host all all ::1/128 trust
5. Save the file.
6. Open the command prompt and navigate to C:\Users\Administrator\New_Repository.

7. Run the following commands one at a time:

   pgsql\bin\pg_ctl stop-D ""C:\Users\Administrator\New_Repository\pgdata"

   pgsql\bin\pg_upgrade.exe --old-datadir="C:\Users\Administrator\Old_Repository\pgdata" --old-bindir="C:\Users\Administrator\Old_Repository\pgdata\pgsql\bin" --new-datadir="C:\Users\Administrator\New_Repository\pgdata" --new-       bindir="C:\Users\Administrator\New_Repository\pgsql\bin" -U postgres -p 5432 -P 5433
   

   pgsql\bin\pg_ctl start -D "C:\Users\Administrator\New_Repository\pgdata"

   After the upgrade is complete, a new file called analyze_new_cluster.bat is generated in the New_Repository folder.

Postinstallation tasks

Perform the following steps to configure the new version of PostgreSQL database:

To update the configuration

1. Start the BMC HDM Repository Service.
2. Run the analyze_new_cluster.bat file that is generated in the New_Repository folder after the upgrade.

3. Run the following command from the location C:\Users\Administrator\New_Repository\pgdata to stop the BMC HDM Repository service.

   pgsql\bin\pg_ctl stop -D "C:\Users\Administrator\New_Repository\pgdata"
4. Navigate to C:\Users\Administrator\New_Repository\pgdata folder and edit the pg_hba.conf file.

5. From the following code, change the references of trust to md5:

   # IPv4 local connections:
    host all all 127.0.0.1/32 trust
   # IPv6 local connections:
    host all all ::1/128 trust

   The new code is as follows:

   # IPv4 local connections:
    host all all 127.0.0.1/32 md5
   # IPv6 local connections:
    host all all ::1/128 md5
6. Copy the pg_data and pgsql folders from C:\Users\Administrator\New_Repository folder to the HDM Repository installation folder.
   The HDM Repository installation folder is usually present in the location C:\Program Files\BMC Software\Helix Data Manager\Repository.
7. Start the BMC HDM Repository Service.

To verify the upgrade

1. If you have the PGAdmin tool, log in to the PostgreSQL server by using the HDM credentials.

2. Verify the PostgreSQL version by using the following query:

   "select version ();"

   The PostgreSQL database must be on version 13.x. 

Related topics

System-requirements

Migrating-data