How to resolve the PGSQL Migration failure during the AppManager upgrade?

How to resolve the PGSQL Migration failure during the AppManager upgrade?

If Applications Manager is updated to a version above 14844 then the bundled backend postgresql database will be upgraded from version 9 to 11 as a part of the product upgrade.  After applying the PPM via updateManager during the post invocation the DB migration will happen, if the migration is stuck for long time or failed due to any of the reasons below:
  1. Migration failed due to port occupancy
  2. Tables getting corrupted
You can follow the below steps to verify whether the migration is happening properly or not

1. Verify the size of pgsql directory which is present in AppManager_Home/working and check whether it's increasing
2. Check the backend query execution by connecting to the new and old pgsql database:
SELECT pid, age(clock_timestamp(), query_start), state, query FROM pg_stat_activity WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start;
Make sure that the query is not in idle state for long time.

New pgsql (version10/11):
Port = check port number in /pgsql/data/amdb/postgres_ext.conf
PgServerPath = C:\ManageEngine\AppManager14\working\pgsql\bin

Old pgsql (version9):
Port = check port number in /pgsql_9/data/amdb/postgres_ext.conf
PgServerPath = C:\ManageEngine\AppManager14\working\pgsql_9\bin

PGSQL Commands to stop/start the DB server:

C:\ManageEngine\AppManager14\working\pgsql\bin>pg_ctl.exe -w -D ..\data\amdb -o -p15435 stop -s -m fast
C:\ManageEngine\AppManager14\working\pgsql_9\bin>pg_ctl.exe -w -D ..\data\amdb -o -p15436 stop -s -m fast

C:\ManageEngine\AppManager14\working\pgsql\bin>pg_ctl.exe -w -D ..\data\amdb -o -p15435 start
C:\ManageEngine\AppManager14\working\pgsql_9\bin>pg_ctl.exe -w -D ..\data\amdb -o -p15436 start

Please check if both old and new db are able to start successfully and then apply the service pack again

For Linux installations:
Make sure that the Applications Manager installed user and the user who has invoked is same.
Create a new non root user with administrator privileges (We recommend to install product with a non-root admin user always).

1. Change the ownership for AppManager14/ directory with that user created
2. Changed the ownership to pgsql directory for postgres user
chown  -R postgres:postgres  /opt/ManageEngine/AppManager14/working/pgsql/
3. Go to AppManager14/working/ directory and execute the below command and check if the pgsql starts completely.
./bin/ --> For start
./bin/ --> For stop
To stop the Applications Manager completely go to /opt/ManageEngine/AppManager14/
sh -force
4. Now try to start and stop the Applications Manager and check if the startup is successful
nohup sh &
5. If everything is fine the upgrade process can be initiated by invoking again

                    New to ADSelfService Plus?