Troubleshooting PostgreSQL Upgrade & Startup Issues

Troubleshooting PostgreSQL Upgrade & Startup Issues

When troubleshooting PostgreSQL issues, we are currently identifying the generic trace, but we missed identifying the absolute cause of that issue and following the workaround, provided. Most of those issues have their own workaround in our KBs with the exact cause.

Trace what we find - 1

  1. com.zoho.postgres.exception.MigrationFailureException: Migration failed for ALL
  2. com.zoho.postgres.exception.MigrationFailureException: Migration failed for DATA
  3. Problem during upgrade.com.zoho.postgres.exception.MigrationFailureException: Restoration failed for PRE_DATA
  4. com.zoho.postgres.exception.MigrationFailureException: Restoration failed for POST_DATA

How do I find the exact cause?
  1. Search for the keyword 'ERROR:' which will be located above the failure message.
  2. Check all the files that were generated in that upgrade attempt, such as updatemgrlog0.txt , updatemgrlog0.txt.1 & updatemgrlog0.txt.2 if they exist.

What are the cases? (4)

Case 1 : Presence of CtrlZ characters

  1. ERROR: missing data for column "columnoldvalue"
  2. ERROR: extra data after last expected column
  3. \n Invalid Command
Resolution : KB

Case 2 : Privilege Changes of Removed Functions

  1. ERROR: function <function_name> does not exist
Resolution : KB

Case 3 : Corruption

  1. ERROR: could not create unique index "requestnotificationtodesc_pk"
  2. DETALLE: Key (notificationid)=(293933) is duplicated
  3. (OR)
  4. could not locate a valid checkpoint record
  5. (OR)
  6. invalid checkpoint record
These corruption tickets shall be moved to G3-Corruption Team.

Case 4 : Database Inconsistency

  1. Key (<column>)=(<value>) is not present in table "<table>".
These Database inconsistency ticket will be handled by CART-Hariharan P M

Case 5 : Temporary Connectivity Issues

  1. Sep 17, 2024 3:24:30 PM  [com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer]  [INFO] : is DB registered as a service ::: false
  2. Sep 17, 2024 3:24:30 PM  [com.zoho.postgres.migrate.BundledPGMigration]  [SEVERE] : nulljava.lang.NullPointerException
  3. at com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer.isPgReady(DefaultPostgresDBInitializer.java:1194)
  4. at com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer.getServerStatus(DefaultPostgresDBInitializer.java:973)
  5. at com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer.isServerStarted(DefaultPostgresDBInitializer.java:913)
Refer this KB for Troubleshooting Connectivity Issues

Trace what we find-2

Postgresql Upgrade Sanity Failure

  1. PostgreSQL upgrade sanity failed.
Resolution :
  1. We can resolve this by executing this below query as postgres user (https://pitstop.manageengine.com/portal/en/kb/articles/how-to-login-into-postgres-database) and invoke upgrade.
  1. REINDEX DATABASE SERVICEDESK

Trace what we find -3

  1. Please login to the system as <machine_name>\Administrator and execute initPgsql.bat script with administrator privileges. If issue is not yet solved, please contact support

How do I find the exact cause?
Check the "[pglog]" above this error message in the same file. Match the exact cause with the below cases.

Case 1 : Permission Restriction

  1. waiting for server to start....postgres: could not access directory "C:/ManageEngine/ServiceDesk/pgsql/data": Permission denied
  2. (OR)
  3. waiting for server to start....postgres: could not access directory "C:/Program Files/ManageEngine/ServiceDesk/pgsql/data": No such file or directory
  4. Run initdb or pg_basebackup to initialize a PostgreSQL data directory
Resolution :
  1. Open a command prompt as the user, with whom you had installed the application. Or, with the user who you would like to start the application service. Or, with the user who you would like to invoke upgrade. Execute <product_installation_location> \ bin \ initPgsql script.
Refer : "Permission restriction" in KB

Case 2 : corruption

  1. FATAL: could not create lock file "postmaster.pid": Permission denied
This ticket shall be moved to G3-Corruption Team

Case 3 : Environmental Delay

  1. waiting for server to start............................................................... stopped waiting
  2. pg_ctl: server did not start in time
Resolution :
  1. We can resolve this issue by excluding <product_installation_folder> from the Antivirus/Windows Defender Intervention and For a safer side, ensuring no other process is running inside the <product_installation_folder> directory.
  2. If possible, try disabling those defender processes temporarily. Refer this KB further for troubleshooting connectivity failures. 

Case 4 : Unstable application instance

  1. program "postgres" was found by "pg_ctl.exe" but was not the same version as pg_ctl
Resolution :
  1. This issue is caused by an unstable application instance. Hence, make a fresh setup and restore the data there.

Case 5 : Native Library Conflicts (Linux Specific cases)

  1. su: cannot open session: Module is unknown
Resolution :
  1. LD_LIBRARY conflict issue caused in Linux. Refer to this KB.

ToDo If the issue persist

If the issue persists even after following all these instructions, kindly report it to our support team with the below files.
  1. <product_installation_location>\logs
  2. <product_installation_location>\pgsql\data\postgresql.conf
  3. <product_installation_location>\pgsql\data\pg_hba.conf
  4. <product_installation_location>\pgsql\ext_conf
  5. output of <product_installation_location> \ bin \ initPgsql script



      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • How to resolve Connectivity issues with Postgresql Database from our product?

                      Log Traces Feb 20, 2024 6:40:00 PM [SYSERR] [INFO] : java.sql.SQLException: java.lang.Exception: Exception during getConnection from pool Exception occurred during get connection from datasource Nov 15, 2023 1:29:10 PM [SYSERR] [INFO] : Caused by: ...

                    • Application startup issue with Windows Services

                      Upon starting the application from Windows Services (from the windows server), if you receive an error like "Error2: The system cannot find the file specified", please follow the below steps to fix the issue: ** Right-click on the ManageEngine ...

                    • Application startup issue due to mismatch in java

                      A startup issue after the upgrade to the version 14.3 Error message: "Application startup halted due to mismatch in java". Issue: After a JRE upgrade, typically, if the "jre_old" folder exists, it will be deleted. Then, the "jre" folder will be ...

                    • Postgres Database startup failures with InitPgsql error trace

                      [com.adventnet.db.adapter.postgres.DefaultPostgresDBInitializer] [SEVERE] : Please login to the system as <machine_name>\Administrator and execute initPgsql.bat script with administrator privileges. If issue is not yet solved, please contact support ...

                    • solution for postgres 11 upgrade issues

                      When Supportcenter Plus is upgraded to 14300 or later, postgres is also upgraded to 11.17. If you are using bundled postgres, data migration through pgdump and restore mechanism also happens. To ensure efficiency in Postgres 11.17, make sure your ...

                      Related Products