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=#DROP DATABASE IF EXISTS amdb;
postgres=#CREATE DATABASE amdb;
postgres=#\c amdb;
amdb=#CREATE EXTENSION "pgcrypto";
amdb=#CREATE EXTENSION CITEXT;
amdb=#CREATE EXTENSION pg_stat_statements;
amdb=#CREATE EXTENSION amcheck;
amdb=#\q
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 15436 -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
New to ADSelfService Plus?
Related Articles
Troubleshooting Applications Manager Plugin database migration failure after service pack upgrade of OPM and APM plugin
When upgrading OpManager from version 12.6.xxx to 12.7.xxx and updating the APM Plugin to 1651x, particularly when using Postgresql as the backend database, an essential one-time migration occurs. This migration involves moving our backend Postgresql ...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 ...How to perform migration to fix "ID out of allocated range" issue or unable to add managed server into admin server due to "server id does not match" error in Applications Manager
If the id value used internally for the monitors crosses the server id value specified in Settings -> Support -> Installation Information -> Server Id - Managed server, then you will get the following alert in Admin server: For existing Managed ...Upgrade guide for OpManager v 12.7 and APM Plugin
After downloading the service pack for OpManager and the compatible service pack for Applications Manager Plugin (APM Plugin) to OpManager installed server, start the upgrade process for OpManager and APM Plugin by following the steps below: Note: If ...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 ...