You're viewing **Apigee Edge** documentation.  
[Go to the
**Apigee X** documentation](http://cloud.google.com/apigee/docs/hybrid/release-notes). info

> [!NOTE]
> Was this troubleshooting playbook helpful? Please let us know by clicking [Send Feedback]().

## 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](https://docs.apigee.com/api-platform/troubleshoot/postgres/upgrade-failure#cause_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](https://docs.apigee.com/api-platform/troubleshoot/postgres/upgrade-failure#cause_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](https://docs.apigee.com/api-platform/troubleshoot/postgres/upgrade-failure#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](https://docs.apigee.com/api-platform/troubleshoot/postgres/upgrade-failure#cause_postgresql_installation_performed_by_another_install_user_other_than_the_apigee_user).
3. If not, go to [Resolution](https://docs.apigee.com/api-platform/troubleshoot/postgres/upgrade-failure#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](https://docs.google.com/document/d/1RsjffiHVenh83vFtfvjEHSBZ5_nNq12FIE4vnFdpX4Y/edit#heading=h.xmsd98bypv0k).

## Must gather diagnostic information

If the problem persists after following the above troubleshooting instructions,
gather the following diagnostic information. Contact [Apigee Support](https://support.apigee.com/) 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](https://docs.apigee.com/api-platform/troubleshoot/postgres/upgrade-failure#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`.

   > [!NOTE]
   > **Note:** The following commands are operating system specific, may differ depending on your operating system. Refer to your system's documentation for more information.

   ```
   update-alternatives --display psql 
   ```

   OR

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

   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.