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 updateManager.sh 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/startPGSQL.sh --> For start
./bin/stopPGSQL.sh --> For stop
To stop the Applications Manager completely go to /opt/ManageEngine/AppManager14/
sh shutdownApplicationsManager.sh -force
4. Now try to start and stop the Applications Manager and check if the startup is successful
nohup sh startApplicationsManager.sh &
5. If everything is fine the upgrade process can be initiated by invoking updateManager.sh again

          • Related Articles

          • How to recover postgres DB Migration failure during 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 service pack via updateManager during the post ...
          • How to fix "Communication link failure The last packet sent successfully to the server"?

            Issue: Communication link failure error message will trigger based on many reasons. Solution: Communication link failure error message is shown due to various reasons. Follow any of the steps mentioned below to resolve the issue: Insufficient user ...
          • How can I upgrade the MySQL connector.jar?

            For all the following issues mentioned below, the solution is same. Invalid authorization specification message from server. Unknown character set index for field '255' received from server. Communication link failure: javax.net.ssl.SSLException. ...
          • Applications Manager DB Maintenance for PGSQL Backend

            Windows Installations Note: Please take complete db backup using the <AppManager Home>\bin\BackupPGSQLDB.bat (or BackupPGSQLDB.sh) script without fail before following the below steps: Open services.msc, stop “ManageEngine Applications ...
          • Error message while performing database backup of Applications Manager

            While performing a database backup operation of Applications Manager build with Pgsql backend, you may encounter the following error, especially when your database size has grown bigger with a large number of tables.   pg_dump: WARNING: out of shared ...