Comments

• Jim_Knicely - Select Field - Administrator

  November 2018 edited November 2018

  I think you'll need to drop the b1 projections yourself, but it's really easy to automate the drops..

  Example:

  dbadmin=> SELECT current_fault_tolerance FROM system;
   current_fault_tolerance
  -------------------------
                         1
  (1 row)
  
  dbadmin=> SELECT COUNT(*) FROM nodes;
   COUNT
  -------
       3
  (1 row)
  
  dbadmin=> CREATE TABLE test (c1 INT) SEGMENTED BY HASH(c1) ALL NODES;
  CREATE TABLE
  
  dbadmin=> INSERT /*+ DIRECT */ INTO test SELECT RANDOMINT(10000) FROM vs_columns;
   OUTPUT
  --------
     9319
  (1 row)
  
  dbadmin=> COMMIT;
  COMMIT
  
  dbadmin=> SELECT node_name, projection_name, row_count FROM projection_storage ORDER BY 1, 2;
          node_name         | projection_name | row_count
  --------------------------+-----------------+-----------
   v_test_k1_to_k0_node0001 | test_b0         |      3106
   v_test_k1_to_k0_node0001 | test_b1         |      3111
   v_test_k1_to_k0_node0002 | test_b0         |      3102
   v_test_k1_to_k0_node0002 | test_b1         |      3106
   v_test_k1_to_k0_node0003 | test_b0         |      3111
   v_test_k1_to_k0_node0003 | test_b1         |      3102
  (6 rows)
  
  dbadmin=> SELECT MARK_DESIGN_KSAFE(0);
  WARNING 6022:  Setting K-safety to 0 could result in catastrophic data loss in the event of a failure.  Do not use k=0 in a production environment. For test, dev or other non-production environments, K=0 may be acceptable however Vertica still recommends a minimum value of K=1
    MARK_DESIGN_KSAFE
  ----------------------
   Marked design 0-safe
  (1 row)
  
  dbadmin=> \q
  
  [dbadmin@vertica01 ~]$ admintools -t list_allnodes
   Node                     | Host          | State | Version         | DB
  --------------------------+---------------+-------+-----------------+---------------
   v_test_k1_to_k0_node0001 | 192.168.2.200 | UP    | vertica-9.1.1.4 | test_k1_to_k0
   v_test_k1_to_k0_node0002 | 192.168.2.201 | UP    | vertica-9.1.1.4 | test_k1_to_k0
   v_test_k1_to_k0_node0003 | 192.168.2.202 | UP    | vertica-9.1.1.4 | test_k1_to_k0
  
  [dbadmin@vertica01 ~]$ admintools -t db_remove_node -d test_k1_to_k0 -s v_test_k1_to_k0_node0002,v_test_k1_to_k0_node0003
  connecting to 192.168.2.200
  
  Starting Data Rebalancing tasks.  Please wait....
  This process could take a long time; allow it to complete uninterrupted.
  Use Ctrl+C if you must cancel the session.
  
          Data Rebalance completed successfully.
  Attempting to drop node v_test_k1_to_k0_node0002 ( 192.168.2.201 )
          Shutting down node v_test_k1_to_k0_node0002
          Sending node shutdown command to '['v_test_k1_to_k0_node0002', '192.168.2.201', '/home/dbadmin', '/home/dbadmin']'
          Deleting catalog and data directories
          Update admintools metadata for v_test_k1_to_k0_node0002
  Attempting to drop node v_test_k1_to_k0_node0003 ( 192.168.2.202 )
          Shutting down node v_test_k1_to_k0_node0003
          Sending node shutdown command to '['v_test_k1_to_k0_node0003', '192.168.2.202', '/home/dbadmin', '/home/dbadmin']'
          Deleting catalog and data directories
          Update admintools metadata for v_test_k1_to_k0_node0003
          Reload spread configuration
          Replicating configuration to all nodes
          Checking database state
          Node Status: v_test_k1_to_k0_node0001: (UP)
  
  [dbadmin@vertica01 ~]$ vsql -c "SELECT node_name, projection_name, row_count FROM projection_storage ORDER BY 1, 2;"
          node_name         | projection_name | row_count
  --------------------------+-----------------+-----------
   v_test_k1_to_k0_node0001 | test_b0         |      9319
   v_test_k1_to_k0_node0001 | test_b1         |      9319
  (2 rows)
  
  [dbadmin@vertica01 ~]$ vsql -Atc "SELECT 'DROP PROJECTION ' || projection_schema || '.' || projection_name || ' CASCADE;' FROM projections WHERE is_segmented LIMIT 1 OVER(PARTITION BY anchor_table_id ORDER BY projection_schema, projection_name DESC);" | vsql
  DROP PROJECTION
  
  [dbadmin@vertica01 ~]$ vsql -c "SELECT node_name, projection_name, row_count FROM projection_storage ORDER BY 1, 2;"                                                                                                                                 node_name         | projection_name | row_count
  --------------------------+-----------------+-----------
   v_test_k1_to_k0_node0001 | test_b0         |      9319
  (1 row)
  

  Be careful! First run this command and make sure the DROP commands are for the projections you want to DROP!

  vsql -Atc "SELECT 'DROP PROJECTION ' || projection_schema || '.' || projection_name || ' CASCADE;' FROM projections WHERE is_segmented LIMIT 1 OVER(PARTITION BY anchor_table_id ORDER BY projection_schema, projection_name DESC);"

  In the example above the output from that command are a series of DROP PROJECTION commands that are piped through vsql where they'll run.

  1