Generate DDL for Changed Configuration Parameters
Jim_Knicely
Administrator
The CONFIGURATION_CHANGES system table records the change history of system configuration parameters. If you want to apply all of the changes to a different Vertica database, you can generate the necessary DDL commands to do that!
Example:
dbadmin=> SELECT DISTINCT ddl dbadmin-> FROM (SELECT CASE dbadmin(> WHEN current_level = 'DATABASE' OR (current_level = 'DEFAULT' AND INSTR(allowed_levels, 'DATABASE') > 0) THEN dbadmin(> 'SELECT set_config_parameter(''' || parameter || ''', ''' || value || ''');' dbadmin(> WHEN current_level = 'NODE' THEN dbadmin(> 'ALTER NODE ' || configuration_changes.node_name || ' SET ' || parameter || ' = ''' || value || ''';' dbadmin(> END AS ddl dbadmin(> FROM configuration_changes dbadmin(> JOIN configuration_parameters dbadmin(> ON parameter = parameter_name dbadmin(> WHERE allowed_levels <> 'SESSION' dbadmin(> LIMIT 1 OVER (PARTITION BY configuration_changes.node_name, parameter ORDER BY event_timestamp DESC)) foo dbadmin-> ORDER BY 1; ddl ------------------------------------------------------------------ ALTER NODE v_test_db_node0001 SET TransactionMode = 'READ ONLY'; ALTER NODE v_test_db_node0002 SET TransactionMode = 'READ ONLY'; ALTER NODE v_test_db_node0003 SET TransactionMode = 'READ ONLY'; SELECT set_config_parameter('AWSEndpoint', '10.180.112.34'); SELECT set_config_parameter('AWSRegion', 'us-east-1'); SELECT set_config_parameter('AuditConfidenceLevel', '0'); SELECT set_config_parameter('AuditErrorTolerance', '100'); SELECT set_config_parameter('EnableForceOuter', '1'); SELECT set_config_parameter('EnableResegmentMerge', '0'); SELECT set_config_parameter('MaxParsedQuerySizeMB', '5120'); SELECT set_config_parameter('SortWorkerThreads', '5'); (11 rows)
Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/CONFIGURATION_PARAMETERS.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/CONFIGURATION_CHANGES.htm
Have fun!
0