This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Upgrading Vertica

The process of upgrading your database with a new Vertica version includes:.

1: Before you upgrade

1.1: Verifying platform requirements
1.2: Checking catalog storage space
1.3: Verify license compliance for ORC and Parquet data
1.4: Backing up and restoring grants
1.5: Nonsequential FIPS database upgrades

2: Upgrade Vertica
3: After you upgrade

3.1: Rebuilding partitioned projections with pre-aggregated data
3.2: Verifying catalog memory consumption
3.3: Reinstalling packages
3.4: Writing bundle metadata to the catalog
3.5: Upgrading the streaming data scheduler utility

Upgrading your database with a new Vertica version includes the following steps:

You can upgrade your database from its current Vertica version to any higher version. Before upgrading, make sure that you have performed a full database backup and have tested the new version in an environment that closely resembles your production database.

Tip

If you want to test out a new version of Vertica for an Eon Mode database without spinning up a new cluster, you can sandbox a secondary subcluster and then upgrade the subcluster within the sandbox. By sandboxing the subcluster, you can try out the new version of Vertica stress-free and continue to use your main cluster as usual. After confirming that the upgrade works and performs as expected, you can downgrade the sandboxed subcluster, remove the sandbox, and proceed to upgrade the main cluster.

Be sure to read the Release Notes and New Features for the Vertica version to which you intend to upgrade. Documentation and release notes for the current Vertica version are available in the RPM and at https://docs.vertica.com/latest, which also provides access to documentation for earlier versions.

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

1 - Before you upgrade

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

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.
Note that any user or role with the same name as a predefined role is renamed to OLD_n_name, where n is an integer that increments from zero until the resulting name is unique and name is the previous name of the user or role.
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.

1.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.

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 and hardware 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.

Caution

Changing the failure threshold lets you immediately upgrade and bring up the Vertica database. However, Vertica cannot fully optimize performance until you correct all warnings.

1.2 - Checking catalog storage space

Use the commands documented here to determine how much catalog space is available before upgrading.

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:

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
```

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% /

1.3 - Verify license compliance for ORC and Parquet data

If you are upgrading from a version before 9.1.0 and:.

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.

Note

You can still choose to upgrade your database if it is not compliant. However, soon after you upgrade, you will begin getting warnings that your database is out of compliance. See Managing license warnings and limits for more information.

1.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.

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.

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
);

From the Linux command line, run the script in the user_ddl.sql file:
```
$ vsql -f user_ddl.sql
CREATE VIEW
```
Connect to Vertica using vsql.
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
```
Upgrade Vertica.

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

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
```
To restore any missing grants, run the remaining grants in grants-list.txt, if any:
```
=> \i 'grants-list.txt'
```

Note

Attempting to restore grants to users with the ANY keyword triggers the following error:

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

To avoid this error, use () instead of (ANY) as shown in the following example:

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

1.5 - Nonsequential FIPS database upgrades

As of Vertica 10.1.1, FIPS support has been reinstated.

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. If you are upgrading from 9.2.x and want to maintain your FIPS certification, you must first perform a direct upgrade from 9.2.x to 10.1.1 before performing further upgrades.

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.

Important

If you have any questions or want additional guidance for performing this upgrade, contact Vertica Support.

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

Shut down the database gracefully. Do not start the database until instructed.
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
Enable FIPS on your RHEL 8.1 machines and reboot.
```
$ fips-mode-setup --enable
```

Install Vertica 10.1.1 on the RHEL 8.1 cluster.

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

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.
```
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 - Upgrade Vertica

Before running the upgrade script, be sure to review the tasks described in Before You Upgrade.

Important

Before running the upgrade script, be sure to review the tasks described in Before you upgrade.

To upgrade your database to a new Vertica version, complete the following steps:

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.
Use admintools to stop the database.
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.
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
```
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-version.RHEL8.x86_64.rpm --dba-user mydba
```
- Debian:
```
# /opt/vertica/sbin/update_vertica --deb /home/dbadmin/vertica_version_amd64.deb --dba-user mydba
```
Note
You can upgrade the Vertica server running on AWS instances created from a Vertica AMI. To upgrade the Vertica server on these AWS instances, you need to add the --dba-user-password-disabled and --point-to-point arguments to the upgrade script.

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 Install 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.
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.

When the upgrade completes, the database automatically restarts.

Note
Any user or role with the same name as a predefined role is renamed to OLD_n_name, where n is an integer that increments from zero until the resulting name is unique and name is the previous name of the user or role.
Manually restart any nodes that fail to start.
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.

3 - 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 Managing directed queries.
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.

3.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.

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.

Export the DDL of these projections with EXPORT_OBJECTS or EXPORT_TABLES.
Drop the projections, then recreate them as defined in the exported DDL.
Run REFRESH. Vertica rebuilds the projections with new storage containers.

3.2 - Verifying catalog memory consumption

Vertica versions ≥ 9.2 significantly reduce how much memory database catalogs consume.

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.

3.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.

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:

Restart the database after upgrading.
Enter a correct password.

If any packages failed to reinstall, Vertica issues a message that specifies the uninstalled packages. You can manually reinstall the packages with admintools or the HTTPS service:

To reinstall with admintools, run the install_package command with the option --force-reinstall:
```
$ admintools -t install_package -d db-name -p password -P pkg-spec --force-reinstall
```
To reinstall with the HTTPS service, make a POST request to the /v1/packages endpoint on any node in the cluster:

Important
Only the dbadmin can access the HTTPS service.
```
$ curl -X POST -k -w "\n" --user dbadmin:password "https://ip-address:8443/v1/packages?force-install=true"
```
The -w flag prints information to the console after the command succeeds.

admintools options

Option	Function
`-d` `db-name` `--dbname=`*`db-name`	Database name
`-p` `password` `--password=``pword`	Database administrator password
`-P` `pkg` `--package=``pkg-spec`	Specifies which packages to install, where `pkg` is one of the following: The name of a package—for example, `flextable` `all`: All available packages `default` : All default packages that are currently installed
`--force-reinstall`	Force installation of a package even if it is already installed.

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

3.4 - Writing bundle metadata to the catalog

Vertica internally stores physical table data in bundles together with metadata on the bundle contents.

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.

3.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.