Upgrading PostgreSQL for Older Software

If you’re running some older software and want to take advantage of Modern Database speeds, you don’t have many options. That said, you can upgrade PostgreSQL to a more modern version without having to change software code(there are some exceptions to this). To upgrade your version of Postgres you will need to downgrade the hashing algorithm from SCRAM-SHA-256 to MD5.

1. Start by downloading PostgreSQL 15.5 from their website, the following link takes you straight to their download page: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
2. Take backups of all the databases in PostgreSQL 8.4. This can be done with PG Admin.
3. Shutdown anything that may be using a Database connection.
4. Change PostgreSQL 8.4’s port to something like 5431 Open File explorer and navigate to PostgreSQL’s data directory in Program Files: C:\Program Files (x86)\PostgreSQL\8.4\data and open postgresql.conf – doing so allows you to swap your PG version back if upgrade fails.
5. Restart PostgreSQL 8.4 either by resetting the PostgreSQL service in the Open Cmd.exe and run the following commands:
   A. Cd C:\Program Files (x86)\PostgreSQL\8.4\bin
   B. PSQL –U postgres
   C. SELECT pg_reload_conf();
   NOTE: make sure that the username entered cmd.exe is all lowercase otherwise login will fail.
6. Start the PostgreSQL 15.5 installation, using the standard port (5432) set the password and locale. After installation, restart the machine, and make sure the PostgreSQL-x64-15 service is running. Once it’s installed, do not continue to add the SYSDBA user, and tablespace follow instructions below.
7. First we need to Downgrade encryption from scram-sha-256 to md5 Follow 9 and 10 to downgrade SQL, step 8 can be done at any time before adding databases, but must be done after downgrading the encryption.
8. Setup user roles and table space in PG 15, this will require the creation of a new tablespace folder. Now add the tables we will be restoring.
   Below are detailed instructions for downgrading PG15’s encryption from scram-sha-256 to md5. Please read through it all as there is important information below, if you experience any problems, eg: can’t log into the postgres user when opening PSQL, or unable to run backups\restores in PgAdmin.

Downgrade scram-sha-256 Hashing to md5

Downgrading the hashing is required unless you can modify the source code to use scram-sha-256 if you cant, it will likely run on md5 so try downgrading it.

1. We need to change our password encryption to md5 from scram-sha-256. To do this we need to modify two PostgreSQL config files: pg_hba.conf and postgresql.conf.
2. Modify the C:\Program Files\PostgreSQL\15\Data\pg_hba.conf file using the following process:
   • Take a copy of the file so we don’t lose the original (not that we will be using it, but it’s good practice to do so)
   • Change the scram-sha-256 in the places shown in the screenshot below to ‘trust’ NOTE: I recommend using ‘trust’ in lowercase rather than ‘Trust’ with a capital ‘T’. If you experience problems when trying to ran PSQL that are not caused by Capitalization issues in the command, this is likely the problem.

Once you’re finished the file should look like this:

11. Modify the C:\Program Files\PostgreSQL\15\Data\postgresql.conf file, the easiest way to do this, is to do a search for ‘password’ in notepad, it will bring you to a hashed option called ‘password_encryption’ we need to un-hash this and set it to md5.
12. As you can see below, the setting is now un-hashed set to md5.

NOTE: Now that the pg_hba.conf file is set to trust, a password is not needed to log into the Postgres user, this is a very vulnerable position to be in, the original password that was setup during installation was encrypted using scram-sha-256, and can’t be decrypted using md5 we need to reset the password now using psql.exe using the following process.

13. After changing these two files DO NOT restart the Postgres service, instead we must reload the configuration files, instead do the following
    Load the windows terminal, cmd.exe, and then use the following commands:
14. CD C:\Program Files\PostgreSQL\15\bin
15. PSQL –U postgres *NOTE: PSQL can be in either uppercase or lowercase – if you get an error that starts like WARNING: Console code page (437) differs from Windows code page (1252) then try the opposite case to the one you have (on my machine it needs to be lowercase), at this time you don’t expect a Password prompt, if you get one there’s something wrong. Enter the postgres username in lowercase, otherwise login will fail, even if the pg_hba.conf file is set to trust.

you should receive a prompt that looks like: postgres=#
at the prompt enter the following:
Reload the configuration files we changed earlier:

0. SELECT pg_reload_conf(); to verify the settings are correct:
   2. SHOW password_encryption;
      The response to the above command should be md5 as shown in the screenshot below
   3. SELECT * FROM pg_hba_file_rules();
      You should get a response showing that the pg_hba.conf file lines that are now set to trust as shown below:

Now change the postgres user’s password:
4. \password postgres
You should then be prompted to enter a new password twice,

After the password has been changed the command prompt can be exited, you can quit PSQL by issuing the /q command then simply close the command window.

12. Now that we have changed the password with the pg_hba.conf file set to trust and the postgressql.conf file set to md5, we must change the ph_hba.conf file from trust to md5 as shown below:

After saving the changes to pg_hba.conf, we can restart the PostgreSQL-x64-15 service by pressing CTRL + ALT + DELETE and selecting task manager, on the services tab find PostgreSQL-X64-15 right clicking and selecting restart.
NOW we can continue setting up Postgres as we would normally

1. create User role for SYSDBA.
2. add the FjPgData tablespace.
3. Add the databases we want to restore, then restore their backups, be sure to check the bin directory in PgAdmin backups and restores won’t work if this directory isn’t set to the PostgreSQL bin directory, this is found under the File-Preferences then in the preferences window on the left find Paths, then select Binary Paths, scroll down to PostgreSQL Binary Paths. This is likely set to ‘PostgreSQL 12’ with a location that looks like ‘$DIR/../runtime’ backups and restores will not work. Scroll down to PostgreSQL 15 and select the folder icon on the right, and navigate to the bin path for PG 15, if you saved this in the default location during installation it should be in C:\Program Files\PostgreSQL\15\bin then set this location as default. The screenshot below demonstrates this.

Goto the start menu, and open PGAdmin 4 then creating the user role for SYSDBA,

Enter the SYSDBA username

Enter the password

Then set all privileges for the user, and click save

Now add the tablespace for FjPgData:

Set the name to FjPgData, and be sure to change the owner to SYSDBA, it defaults to Postgres.

Set the location, remember to create a folder for the tablespace, it must be an empty folder, and must be a different folder to the one we are using for the tablespace in PG 8.4.

Now we must add Databases we will be restoring, start by right clicking database and selecting create, and database

Set the database name to the name of the DB we will be creating, and make sure to set the Owner to SYSDBA, this defaults to Postgres.

Now we must set the definitions for the database as follows:

These are the only requirements for creating a new empty database for our data to be restored to, we can now save this database.

13. We can now run the restore batch files for each database we have created. After the restore has been run validate that the data is now in the database by going to PGAdmin, and right clicking the database in question and selecting the Query tool