How to recover postgres DB Migration failure during upgrade?

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 invocation the DB migration will happen, if the migration is stuck for long time or failed then the same can be identified in postgresqlinstaller logs present in the appmanager\logs

D:\ManageEngine\AppManager14\working\_pgupgrade\exec_pgdumpRestore.bat] execute::Total Time outs occured: 6

For Applications Manager installed in Windows server:

Before initiating the migration take a note of the ports of old and new database
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

Open a command prompt with administrator rights and then execute the commands one by one in a separate terminal for old and new

Stop the DB server by executing the below commands in the correct path:
New Database - AppManager_home>\working\pgsql\bin>pg_ctl.exe -w -D ..\data\amdb -o -p15435 stop -s -m fast
Old Database  - AppManager_home>\working\pgsql_9\bin>pg_ctl.exe -w -D ..\data\amdb -o -p15436 stop -s -m fast

Start the DB server by executing the commands below:
AppManager_home>\working\pgsql\bin>pg_ctl.exe -w -D ..\data\amdb -o -p15435 start
<AppManager_home>\working\pgsql_9\bin>pg_ctl.exe -w -D ..\data\amdb -o -p15436 start

Now connect to the new database

AppManager_home>\working\pgsql\bin>set PGPASSWORD=appmanager
AppManager_home>\working\pgsql\bin>psql.exe -U postgres -p 15435 -h localhost

postgres=#CREATE DATABASE amdb;
postgres=#\c amdb;
amdb=#CREATE EXTENSION "pgcrypto";
amdb=#CREATE EXTENSION pg_stat_statements;
amdb=#CREATE EXTENSION amcheck;

To dump the database from old to new execute the below commands one by one: (SQL Dump)

AppManager_home\working\pgsql_9\bin>set PGPASSWORD=appmanager
AppManager_Home\working\pgsql_9\bin>pg_dump.exe -U postgres -p 25436 -h localhost -c -v -f C:\Programfiles\ManageEngine\AppManager14\dump9.sql amdb
AppManager_Home\working\pgsql_9\bin\>cd AppManager_Home\working\pgsql\bin
AppManager_Home\working\pgsql\bin>set PGPASSWORD=appmanager
AppManager_Home\working\pgsql\bin>psql.exe -U postgres -p 15435 -h localhost -d amdb -c -v -f F:\ManageEngine\AppManager14\dump9.sql

Note - Once the second command is executed dump9.sql file will be created and by executing the last command data will be restored
The port and path should be replaced with the actual appmanager directory