PostgreSQL upgrade failure

You're viewing Apigee Edge documentation.
Go to the Apigee X documentation. info

Symptom

You get an error when updating the PostgreSQL database.

Error Message

ERROR: PostgreSQL Upgrade FAILED

Possible Causes

Cause Description Troubleshooting instructions apply to
Incorrect replication settings in PostgreSQL configuration file The PostgreSQL upgrade fails due to incorrect replication settings during the upgrade. Edge Private Cloud Users
PostgreSQL installation performed by another install user other than the apigee user PostgreSQL was installed initially with another user as an install user, which leads to upgrade failure. Edge Private Cloud Users

Common Diagnosis Steps

If you get an error when upgrading the PostgreSQL database, perform the following diagnostic steps first:

  1. Rename the PostgreSQL data folder:

    mv /opt/apigee/data/apigee-postgresql/pgdata /opt/apigee/data/apigee-postgresql/pgdata-bkp

  2. Ensure that your original backup data is in a folder named /opt/apigee/data/apigee-postgresql/pgdata-version.old/

    For example:

    /opt/apigee/data/apigee-postgresql/pgdata-9.6.old/

  3. Restore the backup data in /opt/apigee/data/apigee-postgresql/pgdata-version.old/ to /opt/apigee/data/apigee-postgresql/pgdata using following command:

    mv /opt/apigee/data/apigee-postgresql/pgdata-version.old/ /opt/apigee/data/apigee-postgresql/pgdata

    For example:

    mv /opt/apigee/data/apigee-postgresql/pgdata-9.6.old/ /opt/apigee/data/apigee-postgresql/pgdata

  4. Rerun the db_upgrade command: 

    /opt/apigee/apigee-service/bin/apigee-service apigee-postgresql db_upgrade

  5. If you get following error...

    pg_resetxlog: could not open file "global/pg_control" for reading:
No such file or directory

    ...then rename the pg_control.old file to pg_control using following command:

    mv /apigee/apigeeinst/data/apigee-postgresql/pgdata/global/pg_control.old /apigee/apigeeinst/data/apigee-postgresql/pgdata/global/pg_control

  6. Rerun the db_upgrade command: 

    /opt/apigee/apigee-service/bin/apigee-service apigee-postgresql db_upgrade

    If the problem persists, go to Cause: Incorrect replication settings in PostgreSQL configuration file.

Cause: Incorrect replication settings in PostgreSQL configuration file

Diagnosis

  1. Check if the PostgreSQL configuration file /opt/apigee/apigee-postgresql/conf/pg_hba.conf has the replication settings that you intend.
  2. If this file has the expected replication settings, then go to PostgreSQL installation performed by another install user other than "apigee" user.
  3. If not, go to Resolution.

Resolution

  1. Rename the PostgreSQL data folder using the following command:

    mv /opt/apigee/data/apigee-postgresql/pgdata /opt/apigee/data/apigee-postgresql/pgdata-bkp

  2. Ensure that the original backup data is in a folder named: /opt/apigee/data/apigee-postgresql/pgdata-version.old/

    For example:

    /opt/apigee/data/apigee-postgresql/pgdata-9.6.old/

  3. Restore the backup data from /opt/apigee/data/apigee-postgresql/pgdata-version.old/ to /opt/apigee/data/apigee-postgresql/pgdata using following command:

    mv /opt/apigee/data/apigee-postgresql/pgdata-version.old/ /opt/apigee/data/apigee-postgresql/pgdata

    For example:

    mv /opt/apigee/data/apigee-postgresql/pgdata-9.6.old/ /opt/apigee/data/apigee-postgresql/pgdata

  4. If you ever changed slave host then you must update following property in /opt/apigee/customer/application/postgresql.properties to update the config file pg_hba.conf:

    conf_pg_hba_replication.connection=host replication apigee
existing_slave_ip/32 trust\ \nhost replication apigee new_slave_ip/32 trust

  5. Restart the apigee-postgresql service to ensure that the property set in Step 4 above gets updated into the config file pg_hba.conf:

    /opt/apigee/apigee-service/bin/apigee-service apigee-postgresql restart

  6. Make the config file pg_hba.conf immutable using the following command to ensure that it does not get updated with incorrect settings during the PostgreSQL upgrade:

    chattr +i /opt/apigee/apigee-postgresql/conf/pg_hba.conf

  7. Rerun the db_upgrade command:

    /opt/apigee/apigee-service/bin/apigee-service apigee-postgresql db_upgrade

  8. Remove the immutable setting on the config file pg_hba.conf:

    chattr -i /opt/apigee/apigee-postgresql/conf/pg_hba.conf

Cause: PostgreSQL installation performed by another install user other than the apigee user

Diagnosis

  1. Check if there is any other user that has rolesuper set to true by following these steps:

    1. On the PostgreSQL node, log in to PostgreSQL using following command:

      psql -h $(hostname -i)  -U apigee

    2. Run the following SQL query:

      select * from pg_authid;

    3. Check if there are multiple users having rolesuper set to true. If there are, then the PostgreSQL upgrade is failing because another user has been set as install user instead of the apigee user. Any user with the rolesuper role and a lower OID is considered to be an install user.

      To check this kind of role problem, run the following SQL queries:

      select oid,rolname,rolsuper from pg_roles;
select * from pg_user;

Resolution

  1. Login to PostgreSQL using following command:

    psql -h $(hostname -i)  -U apigee

  2. Rename the existing apigee role in old DB to a temporary user (for example: apigee2):

    update pg_authid set rolname ='apigee2' where rolname = 'apigee';

  3. Let's say there was another install user srcapige. Rename the srcapige role to apigee:

    update pg_authid set rolname ='apigee' where rolname = 'srcapigee';

  4. Rename the apigee2 to the srcapige user:

    update pg_authid set rolname ='secapigee' where rolname = 'apigee2';

  5. Update the password for all the renamed users:

    ALTER ROLE apigee WITH PASSWORD '<secret>'; 
ALTER ROLE srcapige WITH PASSWORD '<secret>';

  6. Rename the PostgreSQL data folder using the following command:

    mv /opt/apigee/data/apigee-postgresql/pgdata /opt/apigee/data/apigee-postgresql/pgdata-bkp

  7. Ensure that there is original backup data inside a folder named /opt/apigee/data/apigee-postgresql/pgdata-version.old/

    For example:

    /opt/apigee/data/apigee-postgresql/pgdata-9.6.old/

  8. Restore the data in the /opt/apigee/data/apigee-postgresql/pgdata-version.old/ folder to /opt/apigee/data/apigee-postgresql/pgdata using following command:

    mv /opt/apigee/data/apigee-postgresql/pgdata-version.old/ /opt/apigee/data/apigee-postgresql/pgdata

    For example:

    mv /opt/apigee/data/apigee-postgresql/pgdata-9.6.old/ /opt/apigee/data/apigee-postgresql/pgdata

  9. Rerun the db_upgrade command:

    /opt/apigee/apigee-service/bin/apigee-service apigee-postgresql db_upgrade

  10. Restart all apigee-qpidd and edge-qpid-server services:

    /opt/apigee/apigee-service/bin/apigee-service apigee-qpidd restart
/opt/apigee/apigee-service/bin/apigee-service edge-qpid-server restart

If the problem still persists, go to Must gather diagnostic information.

Must gather diagnostic information

If the problem persists after following the above troubleshooting instructions, gather the following diagnostic information. Contact Apigee Support and share this information with the support team:

  1. Add set -x on the second line of the/opt/apigee/apigee-postgresql/lib/actions/db_upgrade file.

  2. Follow the Common diagnosis steps and provide the console output of the db_upgrade command to the support team:

    opt/apigee/apigee-service/bin/apigee-service apigee-postgresql db_upgrade

  3. Provide the following log files to the support team:

    /opt/apigee/var/log/apigee-postgresql/apigee-postgresql.log
/opt/apigee/var/log/apigee-postgresql/update.log

  4. Provide the output of the following operating system commands to check if alternatives are set to auto.

    update-alternatives --display psql

    OR 

    update-alternatives --display pgsql
update-alternatives --list psql

    OR 

    update-alternatives --list pgsql

    If alternatives are set to manual, you can set them to auto using following command:

    update-alternatives --auto psql

    Setting the alternatives to auto points the psql and postgres binaries to the upgraded versions. Otherwise, the binaries will point to the older version, which can cause issues.