Upgrading Vertica

• 1: Upgrade paths
• 1.1: Nonsequential FIPS database upgrades
• 2: Before you upgrade
• 2.1: Verifying platform requirements
• 2.2: Checking catalog storage space
• 2.3: Verify license compliance for ORC and Parquet data
• 2.4: Backing up and restoring grants
• 3: Upgrade Vertica
• 4: After you upgrade
• 4.1: Rebuilding partitioned projections with pre-aggregated data
• 4.2: Verifying catalog memory consumption
• 4.3: Reinstalling packages
• 4.4: Writing bundle metadata to the catalog
• 4.5: Upgrading the streaming data scheduler utility

1 - Upgrade paths

Upgrades are generally incremental: you must upgrade to each intermediate major and minor release. For example, you upgrade from Vertica 9.0 to 10.1 in the following steps:

1. Vertica 9.0 to 9.1

2. Vertica 9.1 to 9.2

3. Vertica 9.2 to 9.3

4. Vertica 9.3 to 10.0

5. Vertica 10.0 to 10.1

If you're upgrading from a FIPS-enabled Vertica 9.2.x database to 10.1.1 and want to maintain your FIPS certification, you must perform a direct upgrade. For instructions, see Nonsequential FIPS database upgrades.

Be sure to read the Release Notes and New Features for each version in your path. Documentation for the current Vertica version is available in the RPM and at https://docs.vertica.com/latest. The same URL also provides access to documentation for earlier versions.

For guidance on upgrading from unsupported versions, contact Vertica Technical Support.

1.1 - Nonsequential FIPS database upgrades

As of Vertica 10.1.1, FIPS support has been reinstated. Prior to this, the last version to support FIPS was Vertica 9.2.x. Vertica upgrades are typically sequential, but if you are upgrading from 9.2.x and want to maintain your FIPS certification, you must first perform a direct, nonsequential upgrade from 9.2.x to 10.1.1 before performing the standard sequential upgrades from 10.1.1 to 11.1.x.

The following procedure performs a direct upgrade from Vertica 9.2.x running on RHEL 6.x to Vertica 10.1.1 on RHEL 8.1.

1. Create a full backup of your Vertica 9.2.x database. This example uses the configuration file fullRestore.ini.

   $ vbr --config-file=/tmp/fullRestore.ini -t init
   $ vbr --config-file=/tmp/fullRestore.ini -t backup
   

   [Transmission]
   concurrency_backup = 1
   port_rsync = 50000
   encrypt = False
   serviceAccessPass = rsyncpw
   hardLinkLocal = False
   checksum = False
   total_bwlimit_restore = 0
   serviceAccessUser = rsyncuser
   total_bwlimit_backup = 0
   concurrency_restore = 1
   
   [Misc]
   snapshotName = full_restore
   restorePointLimit = 1
   retryDelay = 1
   objects =
   retryCount = 0
   tempDir = /tmp/vbr
   
   [Mapping]
   v_fips_db_node0001 = 198.51.100.0:/home/release/backup/
   v_fips_db_node0002 = 198.51.100.1:/home/release/backup/
   v_fips_db_node0003 = 198.51.100.2:/home/release/backup/
   
   [Database]
   dbPort = 5433
   dbPromptForPassword = False
   dbUser =
   dbPassword =
   dbName = fips_db
   

2. Shut down the database gracefully. Do not start the database until instructed.

3. Acquire a RHEL 8.1 cluster with one of the following methods:

   1. Upgrade in place
   2. Reimage your machines
   3. Use a completely different RHEL 8.1 cluster

4. Enable FIPS on your RHEL 8.1 machines and reboot.

   $ fips-mode-setup --enable
   

5. Install Vertica 10.1.1 on the RHEL 8.1 cluster.

   $ install_vertica --hosts node0001, node0002, node0003 \
       --rpm /tmp/vertica-10.1.1-0/x86_64.RHEL8.rpm
   

6. If you acquired your RHEL 8.1 cluster by reimaging or using a different cluster, you must restore your database.

   $ vbr -c /tmp/fullRestore.ini -t restore
   

   If you encounter the following warning, you can safely ignore it.

   Warning: Vertica versions do not match: v9.2.1-xx -> v10.1.1-xxxxxxxx. This operation may not be supported.
   

7. Start the Vertica 10.1.1 database to trigger the upgrade. This should be the first time you've started your database since shutting it down in step 2.

   $ admintools -t start_db -d fips_db
   

2 - Before you upgrade

Before you upgrade the Vertica database, perform the following steps:

• Verify that you have enough RAM available to run the upgrade. The upgrade requires approximately three times the amount of memory your database catalog uses.

  You can calculate catalog memory usage on all nodes by querying system table RESOURCE_POOL_STATUS:

  => SELECT node_name, pool_name, memory_size_kb FROM resource_pool_status WHERE pool_name = 'metadata';
  

• Perform a full database backup. This precautionary measure allows you to restore the current version if the upgrade is unsuccessful.

• Perform a backup of your grants.

• Verify platform requirements for the new version.

• Determine whether you are using any third-party user-defined extension libraries (UDxs). UDx libraries that are compiled (such as those developed using C++ or Java) may need to be recompiled with a new version of the Vertica SDK libraries to be compatible with the new version of Vertica. See UDx library compatibility with new server versions.

• Check catalog storage space.

• If you're upgrading from Vertica 9.2.x and have set the PasswordMinCharChange or PasswordMinLifeTime system-level security parameters, take note of their current values. You will have to set these parameters again, this time at the PROFILE-level, to reproduce your configuration. To view the current values for these parameters, run the following query:

  => SELECT parameter_name,current_value from CONFIGURATION_PARAMETERS
      WHERE parameter_name IN ('PasswordMinCharChange', 'PasswordMinLifeTime');
  

After you complete these tasks, shut down the database gracefully.

2.1 - Verifying platform requirements

The Vertica installer checks the target platform as it runs, and stops whenever it determines the platform fails to meet an installation requirement. Before you update the server package on your systems, manually verify that your platform meets all hardware and software requirements (see Platform requirements and recommendations).

By default, the installer stops on all warnings. You can configure the level where the installer stops installation, through the installation parameter --failure-threshold. If you set the failure threshold to FAIL, the installer ignores warnings and stops only on failures.

2.2 - Checking catalog storage space

Use the commands documented here to determine how much catalog space is available before upgrading. This helps you determine how much space the updated catalog may take up.

Compare how much space the catalog currently uses against space that is available in the same directory:

1. Use the du command to determine how much space the catalog directory currently uses:

   $ du -s -BG v_vmart_node0001_catalog
   2G      v_vmart_node0001_catalog
   

2. Determine how much space is available in the same directory:

   $ df -BG v_vmart_node0001_catalog
   Filesystem     1G-blocks  Used Available Use% Mounted on
   /dev/sda2            48G   19G       26G  43% /
   

2.3 - Verify license compliance for ORC and Parquet data

If you are upgrading from a version before 9.1.0 and:

• Your database has external tables based on ORC or Parquet files (whether stored locally on the Vertica cluster or on a Hadoop cluster)

• Your Vertica license has a raw data allowance

follow the steps in this topic before upgrading.

Background

Vertica licenses can include a raw data allowance. Since 2016, Vertica licenses have allowed you to use ORC and Parquet data in external tables. This data has always counted against any raw data allowance in your license. Previously, the audit of data in ORC and Parquet format was handled manually. Because this audit was not automated, the total amount of data in your native tables and external tables could exceed your licensed allowance for some time before being spotted.

Starting in version 9.1.0, Vertica automatically audits ORC and Parquet data in external tables. This auditing begins soon after you install or upgrade to version 9.1.0. If your Vertica license includes a raw data allowance and you have data in external tables based on Parquet or ORC files, review your license compliance before upgrading to Vertica 9.1.x. Verifying your database is compliant with your license terms avoids having your database become non-compliant soon after you upgrade.

Verifying your ORC and Parquet usage complies with your license terms

To verify your data usage is compliant with your license, run the following query as the database administrator:

SELECT (database_size_bytes + file_size_bytes) <= license_size_bytes
       "license_compliant?"
       FROM (SELECT database_size_bytes,
                    license_size_bytes FROM license_audits
                    WHERE audited_data='Total'
                    ORDER BY audit_end_timestamp DESC LIMIT 1) dbs,
            (SELECT sum(total_file_size_bytes) file_size_bytes
                    FROM external_table_details
                    WHERE source_format IN ('ORC', 'PARQUET')) ets;

This query returns one of three values:

• If you do not have any external data in ORC or Parquet format, the query returns 0 rows:

   license_compliant?
  --------------------
  (0 rows)
  

  In this case, you can proceed with your upgrade.

• If you have data in external tables based on ORC or Parquet format, and that data does not cause your database to exceed your raw data allowance, the query returns t:

   license_compliant?
  --------------------
   t
  (1 row)
  

  In this case, you can proceed with your upgrade.

• If the data in your external tables based on ORC and Parquet causes your database to exceed your raw data allowance, the query returns f:

   license_compliant?
  --------------------
   f
  (1 row)
  

  In this case, resolve the compliance issue before you upgrade. See below for more information.

Resolving non-compliance

If query in the previous section indicates that your database is not in compliance with your license, you should resolve this issue before upgrading. There are two ways you can bring your database into compliance:

• Contact Vertica to upgrade your license to a larger data size allowance. See Obtaining a license key file.

• Delete data (either from ORC and Parquet-based external tables or Vertica native tables) to bring your data size into compliance with your license. You should always backup any data you are about to delete from Vertica. Dropping external tables is a less disruptive way to reduce the size of your database, as the data is not lost—it is still in the files that your external table is based on.

2.4 - Backing up and restoring grants

After an upgrade, if the prototypes of UDx libraries change, Vertica will drop the grants on those libraries since they aren't technically the same function anymore. To resolve these types of issues, it's best practice to back up the grants on these libraries so you can restore them after the upgrade.

1. Save the following SQL to a file named user_ddl.sql. It creates a view named user_ddl which contains the grants on all objects in the database.

   CREATE OR REPLACE VIEW user_ddl AS
   (
   SELECT 0 as grant_order,
          name principal_name,
          'CREATE ROLE "' || name || '"' ||    ';' AS sql,
          'NONE' AS object_type,
          'NONE' AS object_name
     FROM v_internal.vs_roles vr
    WHERE NOT vr.predefined_role -- Exclude system roles
      AND ldapdn = ''            -- Limit to NON-LDAP created roles
   )
   UNION ALL
   (
     SELECT 1, -- CREATE USERs
            user_name,
            'CREATE USER "' || user_name || '"' ||
            DECODE(is_locked, TRUE, ' ACCOUNT LOCK', '') ||
            DECODE(grace_period, 'undefined', '', ' GRACEPERIOD  ''' || grace_period || '''') ||
            DECODE(idle_session_timeout, 'unlimited', '', ' IDLESESSIONTIMEOUT ''' || idle_session_timeout || '''') ||
            DECODE(max_connections, 'unlimited', '', ' MAXCONNECTIONS ' || max_connections || ' ON ' || connection_limit_mode) ||
            DECODE(memory_cap_kb, 'unlimited', '', ' MEMORYCAP ''' || memory_cap_kb || 'K''') ||
            DECODE(profile_name, 'default', '', ' PROFILE ' || profile_name) ||
            DECODE(resource_pool, 'general', '', ' RESOURCE POOL ' || resource_pool) ||
            DECODE(run_time_cap, 'unlimited', '', ' RUNTIMECAP ''' || run_time_cap || '''') ||
            DECODE(search_path, '', '', ' SEARCH_PATH ' || search_path) ||
            DECODE(temp_space_cap_kb, 'unlimited', '', ' TEMPSPACECAP ''' || temp_space_cap_kb || 'K''') || ';' AS sql,
            'NONE' AS object_type,
            'NONE' AS object_name
       FROM v_catalog.users
      WHERE NOT is_super_user -- Exclude database superuser
        AND ldap_dn = ''      -- Limit to NON-LDAP created users
   )
   UNION ALL
   (
     SELECT 2, -- GRANTs
            grantee,
            'GRANT ' || REPLACE(TRIM(BOTH ' ' FROM words), '*', '') ||
            CASE
              WHEN object_type = 'RESOURCEPOOL' THEN ' ON RESOURCE POOL '
              WHEN object_type = 'STORAGELOCATION' THEN ' ON LOCATION '
              WHEN object_type = 'CLIENTAUTHENTICATION' THEN 'AUTHENTICATION '
              WHEN object_type IN ('DATABASE', 'LIBRARY', 'MODEL', 'SEQUENCE', 'SCHEMA') THEN ' ON ' || object_type || ' '
              WHEN object_type = 'PROCEDURE' THEN (SELECT ' ON ' || CASE REPLACE(procedure_type, 'User Defined ', '')
                                                                      WHEN 'Transform' THEN 'TRANSFORM FUNCTION '
                                                                      WHEN 'Aggregate' THEN 'AGGREGATE FUNCTION '
                                                                      WHEN 'Analytic' THEN 'ANALYTIC FUNCTION '
                                                                      ELSE UPPER(REPLACE(procedure_type, 'User Defined ', '')) || ' '
                                                                    END
                                                     FROM vs_procedures
                                                    WHERE proc_oid = object_id)
              WHEN object_type = 'ROLE' THEN ''
              ELSE ' ON '
            END ||
            NVL2(object_schema, object_schema || '.', '') || CASE WHEN object_type = 'STORAGELOCATION' THEN (SELECT '''' || location_path || ''' ON ' || node_name FROM storage_locations WHERE location_id = object_id) ELSE object_name END ||
            CASE
              WHEN object_type = 'PROCEDURE' THEN (SELECT CASE WHEN procedure_argument_types = '' OR procedure_argument_types = 'Any' THEN '()' ELSE '(' || procedure_argument_types || ')' END
                                                     FROM vs_procedures
                                                    WHERE proc_oid = object_id)
              ELSE ''
            END ||
            ' TO ' || grantee ||
            CASE WHEN INSTR(words, '*') > 0 THEN ' WITH GRANT OPTION' ELSE '' END
            || ';',
            object_type,
            object_name
     FROM (SELECT grantee, object_type, object_schema, object_name, object_id,
                    v_txtindex.StringTokenizerDelim(DECODE(privileges_description, '', ',' , privileges_description), ',')
                      OVER (PARTITION BY grantee, object_type, object_schema, object_name, object_id)
             FROM v_catalog.grants) foo
    ORDER BY CASE REPLACE(TRIM(BOTH ' ' FROM words), '*', '') WHEN 'USAGE' THEN 1 ELSE 2 END
   )
   UNION ALL
   (
     SELECT 3, -- Default ROLEs
            user_name,
            'ALTER USER "' || user_name || '"' ||
              DECODE(default_roles, '', '', ' DEFAULT ROLE ' || REPLACE(default_roles, '*', ''))  || ';' ,
            'NONE' AS object_type,
            'NONE' AS object_name
       FROM v_catalog.users
      WHERE default_roles <> ''
   )
   UNION ALL -- GRANTs WITH ADMIN OPTION
   (
     SELECT 4, user_name, 'GRANT ' || REPLACE(TRIM(BOTH ' ' FROM words), '*', '') || ' TO ' || user_name || ' WITH ADMIN OPTION;',
            'NONE' AS object_type ,
            'NONE' AS object_name
       FROM (SELECT user_name, v_txtindex.StringTokenizerDelim(DECODE(all_roles, '', ',', all_roles), ',') OVER (PARTITION BY user_name)
               FROM v_catalog.users
              WHERE all_roles <> '') foo
      WHERE INSTR(words, '*') > 0
   )
   UNION ALL
   (
     SELECT 5, 'public', 'ALTER SCHEMA ' || name || ' DEFAULT ' || CASE WHEN defaultinheritprivileges THEN 'INCLUDE PRIVILEGES;' ELSE 'EXCLUDE PRIVILEGES;' END, 'SCHEMA', name
       FROM v_internal.vs_schemata
      WHERE NOT issys -- Exclude system schemas
   )
   UNION ALL
   (
     SELECT 6, 'public', 'ALTER DATABASE ' || database_name ||  ' SET disableinheritedprivileges = ' || current_value || ';',
            'DATABASE', database_name
       FROM v_internal.vs_configuration_parameters
      CROSS JOIN v_catalog.databases
      WHERE parameter_name = 'DisableInheritedPrivileges'
   )
   UNION ALL -- TABLE PRIV INHERITENCE
   (
     SELECT 7, 'public' , 'ALTER TABLE ' || table_schema || '.' || table_name ||
            CASE WHEN inheritprivileges THEN ' INCLUDE PRIVILEGES;' ELSE ' EXCLUDE PRIVILEGES;' END,
            'TABLE' AS object_type,
            table_schema || '.' || table_name AS object_name
       FROM v_internal.vs_tables
       JOIN v_catalog.tables ON (table_id = oid)
   )
   UNION ALL -- VIEW PRIV INHERITENCE
   (
     SELECT 8, 'public', 'ALTER VIEW ' || table_schema || '.' || table_name || CASE WHEN inherit_privileges THEN ' INCLUDE PRIVILEGES;' ELSE ' EXCLUDE PRIVILEGES; ' END,
            'TABLE' AS object_type, table_schema || '.' || table_name AS object_name
       FROM v_catalog.views
   )
   UNION ALL
   (
     SELECT 9, owner_name, 'ALTER TABLE ' || table_schema || '.' || table_name || ' OWNER TO ' || owner_name || ';',
            'TABLE', table_schema || '.' || table_name
       FROM v_catalog.tables
   )
   UNION ALL
   (
     SELECT 10, owner_name, 'ALTER VIEW ' || table_schema || '.' || table_name || ' OWNER TO ' || owner_name || ';', 'TABLE',
            table_schema || '.' || table_name
       FROM v_catalog.views
   );
   

2. From the Linux command line, run the script in the user_ddl.sql file:

   $ vsql -f user_ddl.sql
   CREATE VIEW
   

3. Connect to Vertica using vsql.

4. Export the content of the user_ddl's sql column ordered on the grant_order column to a file:

   => \o pre-upgrade.txt
   => SELECT sql FROM user_ddl ORDER BY grant_order ASC;
   => \o
   

5. Upgrade Vertica.

6. Select and save to a different file the view's SQL column with the same command.

   => \o post-upgrade.txt
   => SELECT sql FROM user_ddl ORDER BY grant_order ASC;
   => \o
   

7. Create a diff between pre-upgrade.txt and post-upgrade.txt. This collects the missing grants into grants-list.txt.

   $ diff pre-upgrade.txt post-upgrade.txt > grants-list.txt
   

8. To restore any missing grants, run the remaining grants in grants-list.txt, if any:

   => \i 'grants-list.txt'
   

ERROR 4856: Syntax error at or near "Any" at character

=> GRANT EXECUTE ON FUNCTION public.MapLookup() TO public;
GRANT PRIVILEGE

3 - Upgrade Vertica

Repeat this procedure for each version in your upgrade path:

1. Perform a full backup of your existing database. This precautionary measure lets you restore from the backup, if the upgrade is unsuccessful. If the upgrade fails, you can reinstall the previous version of Vertica and restore your database to that version.

   If your upgrade path includes multiple versions, create a full backup with the first upgrade. For each subsequent upgrade, you can perform incremental backups. However, Vertica recommends full backups before each upgrade if disk space and time allow.

2. Use admintools to stop the database.

3. On each host where an additional package is installed, such as the R language pack, uninstall it. For example:

   rpm -e vertica-R-lang
   

   Important

   If you omit this step and do not uninstall additional packages, the Vertica server package fails to install in the next step.

4. Make sure you are logged in as root or sudo and use one of the following commands to run the RPM package installer:
   
   

   • If you are root and installing an RPM:
     

   # rpm -Uvh pathname
   

   • If you are using sudo and installing an RPM:
     

   $ sudo rpm -Uvh pathname
   

   • If you are using Debian:

   $ sudo dpkg -i pathname
   

5. On the same node on which you just installed the RPM, run update_vertica as root or sudo. This installs the RPM on all the hosts in the cluster. For example:

   Red Hat or CentOS

   # /opt/vertica/sbin/update_vertica --rpm /home/dbadmin/vertica-12.0.x.x86_64.RHEL6.rpm --dba-user mydba
   

   Debian

   # /opt/vertica/sbin/update_vertica --deb /home/dbadmin/vertica-amd64.deb --dba-user mydba
   

   The following requirements and restrictions apply:

   • The DBADMIN user must be able to read the RPM or DEB file when upgrading. Some upgrade scripts are run as the DBADMIN user, and that user must be able to read the RPM or DEB file.

   • Use the same options that you used when you last installed or upgraded the database. You can find these options in /opt/vertica/config/admintools.conf, on the install_opts line. For details on all options, see Installing Vertica with the installation script.

     Caution

     If you omit any previous options, their default settings are restored. If you do so, or if you change any options, the upgrade script uses the new settings to reconfigure the cluster. This can cause issues with the upgraded database.

   • Omit the --hosts/-s host-list parameter. The upgrade script automatically identifies cluster hosts.

   • If the root user is not in /etc/sudoers, an error appears. The installer reports this issue with S0311. See the Sudoers Manual for more information.

6. Start the database. The start-up scripts analyze the database and perform necessary data and catalog updates for the new version.

   If Vertica issues a warning stating that one or more packages cannot be installed, run the admintools --force-reinstall option to force reinstallation of the packages. For details, see Reinstalling packages.

7. When the upgrade is complete, the database automatically restarts.

   Note

   Manually restart any nodes that fail to start up.

8. Perform another database backup.

Upgrade duration

Duration depends on average in-memory size of catalogs across all cluster nodes. For every 20GB, you can expect the upgrade to last between one and two hours.

You can calculate catalog memory usage on all nodes by querying system table RESOURCE_POOL_STATUS:

=> SELECT node_name, pool_name, memory_size_kb FROM resource_pool_status WHERE pool_name = 'metadata';

Post-upgrade tasks

After you complete the upgrade, review post-upgrade tasks in After you upgrade.

4 - After you upgrade

After you finish upgrading the Vertica server package on your cluster, a number of tasks remain.

Required tasks

• If you created projections in earlier releases with pre-aggregated data (for example, LAPs and TopK projections) and the projections were partitioned with a GROUP BY clause, you must rebuild these projections.

• Verify on each node that the upgrade reduced database catalog memory usage.

• Verify your database retained the grants from before you upgraded. See Backing up and restoring grants for more information.

• Reinstall packages such as the R language pack that you uninstalled before upgrading. For each package, see its install/upgrade instructions.

  Note

  Vertica Place is automatically reinstalled with the Vertica server package.

• If the upgrade was unable to install one or more packages, reinstall them with admintools.

• Upgrade the Management Console.

• If your Vertica installation is integrated with Hadoop, upgrade the HCatalog connector.

Optional tasks

• Import directed queries that you exported from the previous version. For details, see Batch query plan export and Exporting directed queries from the catalog.

• If you're upgrading from Vertica 9.2.x and have set the PasswordMinCharChange or PasswordMinLifeTime system-level security parameters, set them again at the PROFILE-level.

4.1 - Rebuilding partitioned projections with pre-aggregated data

If you created projections in earlier (pre-10.0.x) releases with pre-aggregated data (for example, LAPs and TopK projections) and the anchor tables were partitioned with a GROUP BY clause, their ROS containers are liable to be corrupted from various DML and ILM operations. In this case, you must rebuild the projections:

1. Run the meta-function REFRESH on the database. If REFRESH detects problematic projections, it returns with failure messages. For example:

   => SELECT REFRESH();
                                                  REFRESH
   -----------------------------------------------------------------------------------------------------
   Refresh completed with the following outcomes:
   Projection Name: [Anchor Table] [Status] [ Refresh Method] [Error Count]
   "public"."store_sales_udt_sum": [store_sales] [failed: Drop and recreate projection] [] [1]
   "public"."product_sales_largest": [store_sales] [failed: Drop and recreate projection] [] [1]
   "public"."store_sales_recent": [store_sales] [failed: Drop and recreate projection] [] [1]
   
   (1 row)
   

   Vertica also logs messages to vertica.log:

   2020-07-07 11:28:41.618 Init Session:ox7fabbbfff700-aoo000000oosbs [Txnl <INFO> Be in Txn: aoooooooooo5b5 'Refresh: Evaluating which projection to refresh'
   2020-07-07 11:28:41.640 Init Session:ex7fabbbfff7oe-aooooeeeeoosbs [Refresh] <INFO> Storage issues detected, unable to refresh projection 'store_sales_recent'. Drop and recreate this projection, then refresh.
   2020-07-07 11:28:41.641 Init Session:Ox7fabbbfff700-aooooeooooosbs [Refresh] <INFO> Storage issues detected, unable to refresh projection 'product_sales_largest'. Drop and recreate this projection, then refresh.
   2020-07-07 11:28:41.641 Init Session:Ox7fabbbfff700-aeoeeeaeeeosbs [Refresh] <INFO> Storage issues detected, unable to refresh projection 'store_sales_udt_sum'. Drop and recreate this projection, then refresh.
   

2. Export the DDL of these projections with EXPORT_OBJECTS or EXPORT_TABLES.

3. Drop the projections, then recreate them as defined in the exported DDL.

4. Run REFRESH. Vertica rebuilds the projections with new storage containers.

4.2 - Verifying catalog memory consumption

Vertica versions ≥ 9.2 significantly reduce how much memory database catalogs consume. After you upgrade, check catalog memory consumption on each node to verify that the upgrade refactored catalogs correctly. If memory consumption for a given catalog is as large as or larger than it was in the earlier database, restart the host node.

Known issues

Certain operations might significantly inflate catalog memory consumption. For example:

• You created a backup on a 9.1.1 database and restored objects from the backup to a new database of version ≥ 9.2.

• You replicated objects from a 9.1.1 database to a database of version ≥ 9.2.

To refactor database catalogs and reduce their memory footprint, restart the database.

4.3 - Reinstalling packages

In most cases, Vertica automatically reinstalls all default packages when you restart your database for the first time after running the upgrade script. Occasionally, however, one or more packages might fail to reinstall correctly.

To verify that Vertica succeeded in reinstalling all packages:

1. Restart the database after upgrading.

2. Enter a correct password.

If any packages failed to reinstall, Vertica issues a message that specifies the uninstalled packages. In this case, run the admintools command install_package with the option --force-reinstall:

$ admintools -t install_package -d db-name -p password -P pkg-spec --force-reinstall

Options

Examples

Force reinstallation of default packages:

$ admintools -t install_package -d VMart -p 'password' -P default --force-reinstall

Force reinstallation of one package, flextable:

$ admintools -t install_package -d VMart -p 'password' -P flextable --force-reinstall

4.4 - Writing bundle metadata to the catalog

Vertica internally stores physical table data in bundles together with metadata on the bundle contents. The query optimizer uses bundle metadata to look up and fetch the data it needs for a given query.

Vertica stores bundle metadata in the database catalog. This is especially beneficial in Eon mode: instead of fetching this metadata from remote (S3) storage, the optimizer can find it in the local catalog. This minimizes S3 reads, and facilitates faster query planning and overall execution.

Vertica writes bundle metadata to the catalog on two events:

• Any DML operation that changes table content, such as INSERT, UPDATE, or COPY. Vertica writes bundle metadata to the catalog on the new or changed table data. DML operations have no effect on bundle metadata for existing table data.

• Invocations of function UPDATE_STORAGE_CATALOG, as an argument to Vertica meta-function DO_TM_TASK, on existing data. You can narrow the scope of the catalog update operation to a specific projection or table. If no scope is specified, the operation is applied to the entire database.

  Important

  After upgrading to any Vertica version ≥ 9.2.1, you only need to call UPDATE_STORAGE_CATALOG once on existing data. Bundle metadata on all new or updated data is always written automatically to the catalog.

For example, the following DO_TM_TASK call writes bundle metadata on all projections in table store.store_sales_fact:

=> SELECT DO_TM_TASK ('update_storage_catalog', 'store.store_sales_fact');
                                  do_tm_task
-------------------------------------------------------------------------------
 Task: update_storage_catalog
(Table: store.store_sales_fact) (Projection: store.store_sales_fact_b0)
(Table: store.store_sales_fact) (Projection: store.store_sales_fact_b1)
(1 row)

Validating bundle metadata

You can query system table STORAGE_BUNDLE_INFO_STATISTICS to determine which projections have invalid bundle metadata in the database catalog. For example, results from the following query show that the database catalog has invalid metadata for projections inventory_fact_b0 and inventory_fact_b1:

=> SELECT node_name, projection_name, total_ros_count, ros_without_bundle_info_count
    FROM v_monitor.storage_bundle_info_statistics where ros_without_bundle_info_count > 0
    ORDER BY projection_name, node_name;
    node_name     |  projection_name  | total_ros_count | ros_without_bundle_info_count
------------------+-------------------+-----------------+-------------------------------
 v_vmart_node0001 | inventory_fact_b0 |               1 |                             1
 v_vmart_node0002 | inventory_fact_b0 |               1 |                             1
 v_vmart_node0003 | inventory_fact_b0 |               1 |                             1
 v_vmart_node0001 | inventory_fact_b1 |               1 |                             1
 v_vmart_node0002 | inventory_fact_b1 |               1 |                             1
 v_vmart_node0003 | inventory_fact_b1 |               1 |                             1
(6 rows)

Best practices

Updating the database catalog with UPDATE_STORAGE_CATALOG is recommended only for Eon users. Enterprise users are unlikely to see measurable performance improvements from this update.

Calls to UPDATE_STORAGE_CATALOG can incur considerable overhead, as the update process typically requires numerous and expensive S3 reads. Vertica advises against running this operation on the entire database. Instead, consider an incremental approach:

• Call UPDATE_STORAGE_CATALOG on a single large fact table. You can use performance metrics to estimate how much time updating other files will require.

• Identify which tables are subject to frequent queries and prioritize catalog updates accordingly.

4.5 - Upgrading the streaming data scheduler utility

If you have integrated Vertica with a streaming data application, such as Apache Kafka, you must update the streaming data scheduler utility after you update Vertica.

From a command prompt, enter the following command:

/opt/vertica/packages/kafka/bin/vkconfig scheduler --upgrade --upgrade-to-schema schema_name

Running the upgrade task more than once has no effect.

For more information on the Scheduler utility, refer to Scheduler tool options.