Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

D toc

This section describes how to upgrade in-use instances of the 

D s item
itemdatabases
rtrue
 for a working deployment of the 
D s platform
.

  • When you upgrade to a new release of the platform, the underlying databases and their structures are automatically migrated to the new format. However, the database releases are not touched. 

In this example, Postgres 9.3 is upgraded to Postgres 9.6.

Pre-requisites

  • The installing user must have write permissions to the directory from which the commands are executed.
  • The installing user must have sudo privileges.
  • Verify that you know the host and port number for each database.

Backup

Before you begin, please verify that you have a valid backup of each 

D s item
itemdatabase
. See Backup and Recovery in the Admin Guide.

Acquire Distribution and Port Information

Before you begin, you must:

  • Acquire the port information for the current database. Typically, this value is 5432.
    d-s-pssql-port
  • Acquire the latest distribution for the database software.

For more information, see Install Databases for PostgreSQL.

For more information, see Install Databases for MySQL.

Database Prep

This procedure describes the process for upgrading the PostgreSQL version in use by the 

D s item
itemdatabases
. This procedure assumes the following:

  • All current databases are co-located with the software on the 
    D s node
    .
  • Some downtime of the databases during the upgrade process is ok.

Before You Begin

  • If you haven't already, please back up each database and all PostgreSQL configuration files. See Backup and Recovery in the Admin Guide.
  • Login to the node where the databases are hosted.

Using non-default ports

The default port for these commands is port 5432.

If you are using a non-standard port for either the pre- or post-upgrade versions of PostgreSQL, you can use the following parameters with the pg_upgrade command:

ParameterDescription
--old-port=<pre-upgrade_port>The port number of the pre-upgrade instance of PostgreSQL
--new-port=<post-upgrade_port>The port number to use for the post-upgrade instance of PostgreSQL

For more information, see https://www.postgresql.org/docs/9.6/pgupgrade.html.

Upgrade PostgreSQL 9.6 to 12.3

The following sections cover upgrading the versions listed below:

  • Source version: PostgreSQL 9.6
  • Upgrade version: PostgreSQL 12.3

Beginning in Release 7.1 Hot Fix 5, PostgreSQL 12.3 is supported on a limited basis. Important notes:

Info

NOTE: Upgrade to PostgreSQL 12.3 is supported in this release for CentOS/RHEL 7 only.


Info

NOTE: Upgrade to PostgreSQL 12.3 is supported in Release 7.1 Hot Fix 5 or later only. For more information on acquiring this build, please contact

D s support
.

Info

NOTE: The database version upgrade must be performed after you have upgraded the software.

PostgreSQL path references

In PostgreSQL 10 and later, directory references to versions of PostgreSQL paths have changed. 

PostgreSQL 9.6 example path:

Code Block
/var/lib/pgsql/9.6/data/pg_hba.conf

PostgreSQL 12.3 example path:

Code Block
/var/lib/pgsql/12/data/pg_hba.conf

For more information, see https://wiki.postgresql.org/wiki/YUM_Installation.

Upgrade for CentOS/RHEL - PostgreSQL 9.6 to 12.3

Info

NOTE: For this release, PostgreSQL 12.3 is supported for supported versions of CentOS/RHEL 7 only. See Product Support Matrix.

  1. Install the Postgres repo for yum. Below, PostgreSQL 12.3 is installed.

    1. For CentOS 7.x:

      Code Block
      languagebash
      wget https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
      sudo yum -y install pgdg-redhat-repo-latest.noarch.rpm
    2. For CentOS 8.x: Not supported

    3. For Red Hat Enterprise Linux 7.x:

      Code Block
      languagebash
      wget https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
      sudo yum -y install pgdg-redhat-repo-latest.noarch.rpm
    4. For Red Hat Enterprise Linux 8.x: Not supported

  2. Install the upgraded version. Below, PostgreSQL 12.3 is installed.

    Code Block
    languagebash
    sudo yum install postgresql12-server
  3. Create a new data directory for the new database version:

    1. For CentOS 7.x:

      Code Block
      languagebash
      sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
      sudo systemctl enable postgresql-12
    2. For RHEL 7.x:

      Code Block
      languagebash
      sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
      sudo systemctl enable postgresql-12
  4. Use the following commands to verify that the upgrade is possible:

    Code Block
    languagebash
    su postgres
    cd ~
    /usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-9.6/bin --new-bindir=/usr/pgsql-12/bin --old-datadir=/var/lib/pgsql/9.6/data/ --new-datadir=/var/lib/pgsql/12/data/ --check 
    Info

    NOTE: Please be sure to include the --check command line switch at the end of the above command.


    If all is well, you should see a Clusters are compatible message.

  5. Stop the old version of the database service as the root user:
    1. CentOS/RHEL:

      Code Block
      languagebash
      sudo service postgresql-9.6 stop
  6. Run the upgrade as the postgres user:
    1. CentOS/RHEL:

      Code Block
      languagebash
      su postgres
      /usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-9.6/bin --new-bindir=/usr/pgsql-12/bin --old-datadir=/var/lib/pgsql/9.6/data/ --new-datadir=/var/lib/pgsql/12/data/

      Part of the output should include the following. These hashes will be re-indexed in a later step.

      Code Block
      Your installation contains hash indexes.  These indexes have different
      internal formats between your old and new clusters, so they must be
      reindexed with the REINDEX command.  The file
          reindex_hash.sql
      when executed by psql by the database superuser will recreate all invalid
      indexes; until then, none of these indexes will be used.
      
      
      
      
      Upgrade Complete
      ----------------
      Optimizer statistics are not transferred by pg_upgrade so,
      once you start the new server, consider running:
          ./analyze_new_cluster.sh
      
      
      Running this script will delete the old cluster's data files:
          ./delete_old_cluster.sh
  7. Copy in pg_hba.conf:

    1. Copy the default version from the upgraded software:

      Code Block
      languagebash
      cp /opt/trifacta/bin/setup-utils/db/pg_hba.conf.SAMPLE /var/lib/pgsql/12/data/pg_hba.conf
      Tip

      Tip: You may find it easier to using a diff tool to compare your current version of this file (/var/lib/pgsql/9.6/data/pg_hba.conf) to the new file (/var/lib/psql/12/data/pg_hba.conf). If there are only a few updates, then you may find it easier to copy your current version into the target directory and then edit it to apply the differences.

    2. If you have made modifications to this configuration file:

      1. Open this file:

        Code Block
        /var/lib/pgsql/9.6/data/pg_hba.conf
      2. Apply custom values to this file:

        Code Block
        /var/lib/pgsql/12/data/pg_hba.conf
  8. Start new PostgreSQL:

    Code Block
    languagebash
    systemctl start postgresql-12
  9. Use the following command to reindex the hashes:

    Code Block
    languagebash
    psql -f reindex_hash.sql
  10. Verify that PostgreSQL is up and running:

    Code Block
    languagebash
    systemctl status postgresql-12.service

Upgrade PostgreSQL 9.3 to 9.6

The following sections cover upgrading the versions listed below:

  • Source version: PostgreSQL 9.3
  • Upgrade version: PostgreSQL 9.6

Upgrade for CentOS/RHEL - PostgreSQL 9.3 to 9.6

  1. Install the Postgres repo for yum. Below, PostgreSQL 9.6 is installed.
    1. For CentOS 7.x:

      Code Block
      wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
      sudo yum -y install pgdg-centos96-9.6-3.noarch.rpm
    2. For CentOS 8.x:

      Code Block
      wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-8-x86_64/pgdg-centos96-9.6-3.noarch.rpm
      sudo yum -y install pgdg-centos96-9.6-3.noarch.rpm
    3. For Red Hat Enterprise Linux 7.x:

      Code Block
      wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
      sudo yum -y install pgdg-redhat96-9.6-3.noarch.rpm
    4. For Red Hat Enterprise Linux 8.x:

      Code Block
      wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-8-x86_64/pgdg-centos96-9.6-3.noarch.rpm
      sudo yum -y install pgdg-redhat96-9.6-3.noarch.rpm
  2. Install the upgraded version. Below, PostgreSQL 9.6 is installed.

    Code Block
    sudo yum install postgresql96.x86_64 postgresql96-server.x86_64 postgresql96-contrib.x86_64 -y
  3. Create a new data directory for the new database version:

    1. For CentOS 7.x, CentOS 8.x:

      Code Block
      sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb
    2. For RHEL 7.x, RHEL 8.x:

      Code Block
      sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb
  4. Use the following commands to verify that the upgrade is possible:

    Code Block
    su postgres
    cd ~
    /usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/9.3/data/ --new-datadir=/var/lib/pgsql/9.6/data/ --check 

    If all is well, you should see a Clusters are compatible message.

  5. Stop the old version of the database service as the root user:
    1. CentOS/RHEL:

      Code Block
      sudo service postgresql-9.3 stop
  6. Run the upgrade as the postgres user:
    1. CentOS/RHEL:

      Code Block
      su postgres
      /usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/9.3/data/ --new-datadir=/var/lib/pgsql/9.6/data/
Excerpt

Upgrade for Ubuntu 16.04 (Xenial) - PostgreSQL 9.3 to 9.6

Steps:

  1. Stop the platform:

    Code Block
    service trifacta stop
  2. Install the upgraded version. Below, PostgreSQL 9.6 is installed.

    Code Block
    sudo apt-get install postgresql-9.6 postgresql-server-dev-9.6 postgresql-contrib-9.6 -y
  3. Stop PostgreSQL:

    Code Block
    sudo systemctl stop postgresql
    service stop postgresql
    service postgresql stop
  4. Upgrade the PostgreSQL 9.3 version to PostgreSQL 9.6, using the newly installed version:

    D s property overflow

    Code Block
    sudo su - postgres -c '/usr/lib/postgresql/9.6/bin/pg_upgrade \
    -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.6/bin \
    -d /var/lib/postgresql/9.3/main/ -D /var/lib/postgresql/9.6/main/ \
    -O "-c config_file=/etc/postgresql/9.6/main/postgresql.conf" \
    -o "-c config_file=/etc/postgresql/9.3/main/postgresql.conf"'
  5. Remove the old version of PostgreSQL (9.3):

    Code Block
    sudo apt-get remove postgresql-9.3 -y
  6. Restart PostgreSQL and the platform:

    Code Block
    service postgresql start
    service trifacta start

Upgrade for Ubuntu 18.04 (Bionic Beaver) - PostgreSQL 9.3 to 9.6

PostgreSQL should not be on version 9.3 if you are using this version of the operating system.

Transfer Settings

For all operating systems, you must transfer the settings from your old version of PostgreSQL to the new one.

  1. Update the port number and any other settings in the platform configuration. 
    D s config
    methodtriconf
     
    1. Replace all instances of the PostgreSQL port number for the earlier version and replace with the desired port number for the upgrade version. 
      d-s-pssql-port
    2. Make the above changes and save the file.
  2. Transfer settings from the configuration files for your old database version to the new one. Please review the old and new versions of these files:

    Info

    NOTE: It is risky to perform a straight copy of these configuration files. Settings may change. New ones may be introduced. Setting values specific to to the installation may be overwritten in a copy. Please retain a backup of both versions of each file before migrating settings.

    Path to PostgreSQL 9.3 filePath to PostgreSQL 9.6 file
    /var/lib/pgsql/9.3/data/pg_hba.conf/var/lib/pgsql/9.6/data/pg_hba.conf
    /var/lib/pgsql/9.3/data/postgresql.conf/var/lib/pgsql/9.6/data/postgresql.conf
  3. Start the service:
    1. CentOS/RHEL:

      Code Block
      sudo service postgresql-9.6 start
    2. Ubuntu:

      Code Block
      sudo service postgresql start
    3. When the service restarts, you can check the cluster status using the following script:

      Code Block
      ./analyze_new_cluster.sh
  4. After you have completed the database installation, you must review the port number of the newly installed database, which may have changed between versions of the database software. That new port number must be applied through the 

    D s item
    itemsoftware
    . For more information, see Change Database Port.

  5. If all is well, restart the platform. See Start and Stop the Platform in the Install Guide. 

Verify and Cleanup

  1. Verify operations on all databases:
    1. Login to the application.
    2. Load a dataset from Flow View.
    3. Run a job. 
    4. Schedule a job and execute it.
    5. See Verify Operations in the Admin Guide.
  2. If all of the above tests pass, you can use the following script to delete the old PostgreSQL version and its data directory:

    Code Block
    ./delete_old_cluster.sh
  3. Restart the 
    D s platform
    . See Start and Stop the Platform in the Install Guide.