PostgreSQL upgrade from v9.5 - v12

[vkrishna]

Hi, 

We completed the SAS 9.4 M6 - M7 upgrade and tried to upgrade PostgreSQL from 9.5 to 12. I followed the steps in this document Upgrading to PostgreSQL 12 after Upgrading to SAS 9.4M7. 9.4_previous directory reporting the correct version - 9.5 and Metadata service started and created analysis report and database configuration ready for upgrade (Step13), and tried to upgrade using --upgrade parameters encountered an error during upgrade -  "System cannot find the path - E:\Program\pg_hba.conf" and we don't have any 'E:\Program' folder. 

SAS Home directory path - "E:\Program Files\SAS"

SAS Config directory path - "E:\Program Files\SASconfig\Lev1"

Single machine installation.

Metadata service is running and do we need to start the WIPDS service manually for the upgrade?

How can I resolve this issue?

E:\Program Files\SAS\SASWebInfrastructurePlatformDataServer\9.4\scripts\upgrade>run.bat --metaserver host.abc.test.com --metaport 8561 --metauser sasadm@saspw --metapass *********
Multiple Instance Mode(metadata required): requiring Additional Metadata parameters
---------------
LOGLEVEL set to : INFO
---------------
2022.03.03_13:31:55 INFO: Starting execution.
2022.03.03_13:31:55 INFO: Processing registry for installed products.
2022.03.03_13:32:07 INFO: Processing: WEBINFDSVRC
2022.03.03_13:32:08 INFO: The following data directory is available: E:\Program Files\SASconfig\Lev1\WebInfrastructurePlatformDataServer\data
2022.03.03_13:32:08 INFO: WEBINFDSVRC is ready to upgrade. There is a previous UIP version that is < 12.
2022.03.03_13:32:11 INFO:

9.4 SYSTEM POSTGRES ANALYSIS:


=============================================
Web Infrastructure Platform Data Server 9.4(WEBINFDSVRC)
=============================================
DataServer Home Binary Location: E:\Program Files\SAS\SASWebInfrastructurePlatformDataServer\9.4
DataServer Data Location: E:\Program Files\SASconfig\Lev1\WebInfrastructurePlatformDataServer\data
DataServer Configuration Location: E:\Program Files\SASconfig\Lev1\WebInfrastructurePlatformDataServer
DataServer User: dbmsowner
DataServer Machine: host.abc.test.com(local)
DataServer Port: 9432
DataServer Version (Disk): 9.5
=============================================

Total Number of Data Servers: 1
Total Number of Local Data Servers: 1
Total Number of Remote Data Servers: 0
Total Number of Data Servers Ready to Upgrade: 1
Total Number of Data Servers Not Eligible For Upgrade: 0

To continue with upgrading data servers on this host use the "--upgrade" parameter.

2022.03.03_13:32:11 INFO: Ending execution

E:\Program Files\SAS\SASWebInfrastructurePlatformDataServer\9.4\scripts\upgrade>run.bat --metaserver host.abc.test.com --metaport 8561 --metauser sasadm@saspw --metapass ******* --upgrade
Multiple Instance Mode(metadata required): requiring Additional Metadata parameters
---------------
LOGLEVEL set to : INFO
---------------
2022.03.03_13:32:48 INFO: Starting execution.
2022.03.03_13:32:48 INFO: Processing registry for installed products.
2022.03.03_13:33:00 INFO: Processing: WEBINFDSVRC
2022.03.03_13:33:01 INFO: The following data directory is available: E:\Program Files\SASconfig\Lev1\WebInfrastructurePlatformDataServer\data
2022.03.03_13:33:01 INFO: WEBINFDSVRC is ready to upgrade. There is a previous UIP version that is < 12.
2022.03.03_13:33:01 INFO: Starting Postgres Update for: WEBINFDSVRC
2022.03.03_13:33:01 INFO: Stopping the service...
2022.03.03_13:33:01 INFO: The version for the backup creation is: 95
2022.03.03_13:33:01 ERROR: Unable to back up the following directory/file as it does not exist and processing will not continue for this dataserver: E:\Program
Caught: java.io.FileNotFoundException: E:\Program\pg_hba.conf (The system cannot find the path specified)
java.io.FileNotFoundException: E:\Program\pg_hba.conf (The system cannot find the path specified)
        at pgupdate.UpdatePostgres.disableSecurity(UpdatePostgres.groovy:958)
        at pgupdate.UpdatePostgres$disableSecurity$22.callCurrent(Unknown Source)
        at pgupdate.UpdatePostgres.updateInstance(UpdatePostgres.groovy:1166)
        at pgupdate.UpdatePostgres$updateInstance$4.call(Unknown Source)
        at pgupdate.SearchForPGInstances.updateDBInstance(SearchForPGInstances.groovy:294)
        at pgupdate.SearchForPGInstances$updateDBInstance$16.call(Unknown Source)
        at pgupdate.SearchForPGInstances$_main_closure4.doCall(SearchForPGInstances.groovy:848)
        at pgupdate.SearchForPGInstances.main(SearchForPGInstances.groovy:723)

[gwootton]

I'm guessing this is the result of the path for the data server lacking quotes somewhere, so the space is truncating the path. I suspect this needs to be changed either in Metadata or the upgrade script so a Technical Support track is probably the best path forward.

[vkrishna]

Thanks @gwootton .

I contacted SASTS and waiting for the response. 

[gwootton]

I took a look in my environments and found in SAS Management Console -> Server Manager -> Web Infrastructure Platform Data Server -> Properties, in the Extended Attributes tab is the "DataStoreDir" attribute that contains the path to the data directory (which is where pg_hba.conf is). In looking at the groovy scripts in that upgrade directory that seems to be what it uses to define the location of pg_hba.conf. Can you try adding quotes around that?

[vkrishna]

Do you want me to add quotes to the directory path in the Value column? 

[gwootton]

Right I'm thinking if you enclose that path in quotes in SAS Management Console and run the script again it might work for you.

[vkrishna]

I updated the directory path in SMC. Analysis report lists dataserver version '0' and dataservers ready to upgrade: '0'. I verified the versions in 9.4 (12.8) and 9.4_PREVIOUS (9.5) directories. I didn't run the script with --upgrade option. 

I reverted the change in SMC and It reports correct data server versions and number of dataservers ready for upgrade as 1 in the analysis report. 

E:\Program Files\SAS\SASWebInfrastructurePlatformDataServer\9.4\scripts\upgrade>run.bat --metaserver host.domain.test.com --metaport 8561 --metau
ser sasadm@saspw --metapass ************
Multiple Instance Mode(metadata required): requiring Additional Metadata parameters
---------------
LOGLEVEL set to : INFO
---------------
2022.03.04_15:13:45 INFO: Starting execution.
2022.03.04_15:13:45 INFO: Processing registry for installed products.
2022.03.04_15:13:56 INFO: Processing: WEBINFDSVRC
2022.03.04_15:13:59 INFO:

9.4 SYSTEM POSTGRES ANALYSIS:


=============================================
Web Infrastructure Platform Data Server 9.4(WEBINFDSVRC)
=============================================
DataServer Home Binary Location: E:\Program Files\SAS\SASWebInfrastructurePlatformDataServer\9.4
DataServer Data Location: "E:\Program Files\SASconfig\Lev1\WebInfrastructurePlatformDataServer\data"
DataServer Configuration Location: "E:\Program Files\SASconfig\Lev1\WebInfrastructurePlatformDataServer\
DataServer User: dbmsowner
DataServer Machine: host.domain.test.com(local)
DataServer Port: 9432
DataServer Version (Disk): 0
=============================================

Total Number of Data Servers: 1
Total Number of Local Data Servers: 1
Total Number of Remote Data Servers: 0
Total Number of Data Servers Ready to Upgrade: 0
Total Number of Data Servers Not Eligible For Upgrade: 0

All local discovered dataservers are at the correct version.

2022.03.04_15:13:59 INFO: Ending execution

[gwootton]

Looks like it's picking up the quoted path but its subsetting without updating the quotes (note "DataServer configuration location" does not include a quote at the end). So I suppose this is not the correct solution. Perhaps you could try using the method in the doc that does not use metadata? ("Upgrading PostgreSQL 12 without Using Metadata")

[vkrishna]

I already tried the upgrade without metadata. Similar issue: The System cannot find the path specified.

E:\Program Files\SAS\SASWebInfrastructurePlatformDataServer\9.4\scripts\upgrade>run.bat --singleinstance --pgscript "E:\Program Files\SASconfig\Lev1\WebInfrastructurePlatformDataServer\webinfdsvrc.bat
" --pguser dbmsowner --pgpass *************
The system cannot find the path specified.

[gwootton]

Sounds like your Technical Support track is going to be your best path forward then.