Upgrade Internal PostgreSQL Instances from 9.3 to 9.6

Customer Managed Applies to customer-managed instances of Alation

Available from release V R6 (5.10.x)

Alation uses PostgreSQL databases as the internal server database and the database for Alation Analytics functionality. Starting with V R6 (5.10.x), these internal PostgreSQL instances can be deployed on PostgreSQL version 9.6.

Note

Releases before V R6 use PostgreSQL 9.3.

New Alation installations of V R6 (5.10.x) will use PostgreSQL 9.6 by default.

Existing Alation installations which update to V R6 from previous releases will continue to be fully operable on PostgreSQL 9.3; however, we strongly recommend upgrading your Alation PostgreSQL instances to version 9.6. after you update Alation to V R6.

Note

PostreSQL version 9.3 is at end of life and no longer receives updates or bug fixes. For details on PostgreSQL versioning, refer to PostgreSQL documentation.

Update Internal PostgreSQL Databases to Version 9.6

Note

This instruction only applies to Alation instances updated to V R6 from previous releases and does not apply to new installations of V R6.

Upgrade to 9.6 action will update the internal Alation database ( rosemeta ) and the Alation Analytics database if it is enabled in your instance.

Prerequisites

  • sudo access to Alation shell

  • A valid backup of your Alation instance or a full system image or snapshot. For information on how to create a backup, see Create Backups Manually.

Standalone Instances

To upgrade:

  1. Enter the Alation shell:

    sudo /etc/init.d/alation shell
    
  2. Stop the Supervisor services:

    alation_supervisor stop all
    
  3. Set user as alation:

    sudo su alation
    
  4. Run the following script:

    alation_action upgrade_postgres
    

    The upgrade will be performed in-place without copying any files and is expected to complete rather quickly.

  5. Start the Supervisor services:

    alation_supervisor start all
    
  6. To check the PostgreSQL version after upgrade, see Check PostgreSQL Version.

  7. Exit the shell

    exit
    

    Important

    After the upgrade, any remaining data in the 9.3 folders must NOT be deleted: you could suffer data loss if you delete it. Alation provides instructions on how to correctly clean up the leftover 9.3 data: How to Clean up Postgres 9.3 Data

HA Instances

To upgrade PostgreSQL in an HA setup, perform the following steps.

  1. Make sure the Prerequisites are met.

  2. Revert the HA cluster to standalone mode. For details, see: Reverting the High Availability Pair to Standalone Mode.

  3. On the Secondary server, check that the scheduled queries are disabled: Enable or Disable Query Scheduling.

  4. On Secondary, if your instance has Alation Analytics enabled, set the Alation Analytics feature flag to False:

    • On Secondary, enter the Alation shell and set parameter alation.feature_flags.enable_alation_analytics to False:

      sudo /etc/init.d/alation shell
      alation_conf alation.feature_flags.enable_alation_analytics -s False
      
    • Don’t exit the shell yet. Do step 5.

  5. On Secondary, stop the supervisor:

    alation_supervisor stop all
    
  6. On Primary, if your instance has Alation Analytics enabled, set the Alation Analytics feature flag to False. If not, skip this step.

    sudo /etc/init.d/alation shell
    alation_conf alation.feature_flags.enable_alation_analytics -s False
    
  7. On Primary, from the Alation shell, stop the Supervisor:

    # if not in the shell - enter the shell first
    sudo /etc/init.d/alation shell
    alation_supervisor stop all
    
  8. On Primary, run the PostgreSQL upgrade action:

    Still in the shell, set user as alation:

    sudo su alation
    

    Run:

    alation_action upgrade_postgres
    
  9. On Secondary, run the PostgreSQL upgrade action:

    If not in the shell, enter the shell first:

    sudo /etc/init.d/alation shell
    

    Then:

    sudo su alation
    alation_action upgrade_postgres
    
  10. To check the PostgreSQL version after upgrade, see Check PostgreSQL Version.

  11. On Primary, if you previously disabled Alation Analytics, set it back to enabled from the Alation shell:

    alation_conf alation.feature_flags.enable_alation_analytics -s True
    
  12. Rebuild HA setup for Primary and Secondary instances starting from Step 3: “Placing the Primary server into master mode”.

  13. If using Alation Analytics, check that the Alation Analytics feature parameter is back to True on the Secondary instance (it should automatically return to True after the cluster is rebuilt):

    # if not in the shell - enter the shell first
    sudo /etc/init.d/alation shell
    alation_conf alation.feature_flags.enable_alation_analytics
    

    Should be: alation_conf alation.feature_flags.enable_alation_analytics = True

  14. On Primary, start the Supervisor:

    alation_supervisor start all
    

    Important

    After the upgrade, any remaining data in the 9.3 folders must NOT be deleted: you could suffer data loss if you delete it.

Check PostgreSQL Version

After upgrade, you can check the PostgreSQL version in the following way. While in Alation shell, run:

alation_psql

Look at the version in the output. You should see “9.6.x”, for example:

psql (9.6.13)
Type "help" for help.

To exit the Postgres shell and return to the Alation shell, type:

\q

Restore from Backup with PostgreSQL 9.3 on V R6

It is possible to restore an Alation backup from an instance running on 9.3 PostgreSQL to a clean installation of Alation V R6. Note that the data will not be automatically upgraded to the 9.6 format, but the VR6 instance will switch to use PostgreSQL version 9.3. After completing the restore action, it will be possible to run the upgrade_postgres action to upgrade PostgreSQL to 9.6.