After you upgrade

• 1: Rebuilding partitioned projections with pre-aggregated data
• 2: Verifying catalog memory consumption
• 3: Reinstalling packages
• 4: Writing bundle metadata to the catalog
• 5: Upgrading the streaming data scheduler utility

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.

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.

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

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

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.