@Kishore Sanagapalli

Error Description:

org.postgresql.util.PSQLException: ERROR: could not create unique index "pr_sys_serviceregistry_pk"
     [java]   Detail: Key (pysessionid)=(97bbb1a9-27e8-45fd-935c-69f2b2aafa30) is duplicated.
     [java]     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
     [java]     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
     [java]     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
     [java]     at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
     [java]     at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
     [java]     at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
     [java]     at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
     [java]     at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
     [java]     at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
     [java]     at com.pega.pegarules.install.SQLLoaderInternal.readAndExecuteSQLStatements(SQLLoaderInternal.java:160)
     [java]     at com.pega.pegarules.install.SQLLoaderInternal.load(SQLLoaderInternal.java:119)
     [java]     at com.pega.pegarules.install.SQLLoader.loadInner(SQLLoader.java:99)
     [java]     at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:78)
     [java]     at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:66)
     [java] 2022-09-05 13:50:18,274 (                  SQLLoaderInternal) WARNING - Statement: ALTER TABLE pega_cs_data.pr_sys_serviceregistry
     [java] ADD CONSTRAINT "pr_sys_serviceregistry_pk" PRIMARY KEY
     [java] (
     [java]     "pysessionid"
     [java] )
     [java] 2022-09-05 13:50:18,275 (                  SQLLoaderInternal) WARNING - 
     [java] ********************************************************************************
     [java] 
     [java] Encountered a SQL Exception, clean schema before running again.
     [java] 
     [java] ********************************************************************************
     [java] Exception in thread "main" com.pega.pegarules.install.SQLLoaderException: Error executing SQL statements
     [java]     at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:81)
     [java]     at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:66)
     [java] Caused by: org.postgresql.util.PSQLException: ERROR: could not create unique index "pr_sys_serviceregistry_pk"
     [java]   Detail: Key (pysessionid)=(97bbb1a9-27e8-45fd-935c-69f2b2aafa30) is duplicated.
     [java]     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
     [java]     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
     [java]     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
     [java]     at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
     [java]     at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
     [java]     at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
     [java]     at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
     [java]     at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
     [java]     at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
     [java]     at com.pega.pegarules.install.SQLLoaderInternal.readAndExecuteSQLStatements(SQLLoaderInternal.java:160)
     [java]     at com.pega.pegarules.install.SQLLoaderInternal.load(SQLLoaderInternal.java:119)
     [java]     at com.pega.pegarules.install.SQLLoader.loadInner(SQLLoader.java:99)
     [java]     at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:78)
     [java]     ... 1 more
 

[java] 2022-09-05 13:50:18,272 (                  SQLLoaderInternal)    INFO - Failed to execute SQL in file postgres16.sql.

==================================================================================================

From the above statement, it's evident that, Issue will occur when some primary key constraints or Indexes found duplicates. In such cases, to RESUME the upgrade process, We need to do the following.

From the downloaded Pega Platform, in the Path <Drive-Path>\117260_Pega8.6.5\schema\generated\postgres\update\statements, SQL Statements will be availlable which will be used in the Upgrade script execution. From the found error during the script exection, We need to identify the SQL file or step where the execution failed to determine that all the previous ones executed successfully i.e.. the above issue occured during the execution of postgreSQL.16 file and all previous statements (postgreSQL-1 - 16) can be removed from the folder and invoke the script execution again to fix the issue.