MOVE_RETIRED_LOCATION_DATA

Moves all data from the specified retired storage location or from all retired storage locations in the database. MOVE_RETIRED_LOCATION_DATA migrates the data to non-retired storage locations according to the storage policies of the objects whose data is stored in the location. This function returns only after it completes migration of all affected storage location data.

The Tuple Mover migrates data of retired storage locations when it consolidates data into larger ROS containers.

Syntax

MOVE_RETIRED_LOCATION_DATA( ['location‑path'] [, 'node'] )

Arguments

location‑path

The path of the storage location as specified in the LOCATION_PATH column of system table STORAGE_LOCATIONS. This storage location must be marked as retired.

If you omit this argument, MOVE_RETIRED_LOCATION_DATA moves data from all retired storage locations.
node

The node on which to move data of the retired storage location. If location‑path is undefined on node, this function returns an error.

If you omit this argument, MOVE_RETIRED_LOCATION_DATA moves data fromlocation‑path on all nodes.

Privileges

Superuser

Examples

  1. Query system table STORAGE_LOCATIONS to show which storage locations are retired:
    => SELECT node_name, location_path, location_label, is_retired FROM STORAGE_LOCATIONS 
   WHERE is_retired = 't';
    node_name     |    location_path     | location_label | is_retired
------------------+----------------------+----------------+------------
 v_vmart_node0001 | /home/dbadmin/SSDLoc | ssd            | t
 v_vmart_node0002 | /home/dbadmin/SSDLoc | ssd            | t
 v_vmart_node0003 | /home/dbadmin/SSDLoc | ssd            | t
(3 rows)
  2. Query system table STORAGE_LOCATIONS for the location of the messages table, which is currently stored in retired storage location ssd:
    => SELECT node_name, total_row_count, storage_type, location_label FROM STORAGE_CONTAINERS 
   WHERE projection_name ILIKE 'messages%';
    node_name     | total_row_count | storage_type | location_label
------------------+-----------------+--------------+----------------
 v_vmart_node0001 |          333514 | ROS          | ssd
 v_vmart_node0001 |          333255 | ROS          | ssd
 v_vmart_node0002 |          333255 | ROS          | ssd
 v_vmart_node0002 |          333231 | ROS          | ssd
 v_vmart_node0003 |          333231 | ROS          | ssd
 v_vmart_node0003 |          333514 | ROS          | ssd
(6 rows)
  3. Call MOVE_RETIRED_LOCATION_DATA to move the data off the ssd storage location.
    => SELECT MOVE_RETIRED_LOCATION_DATA('/home/dbadmin/SSDLoc');
          MOVE_RETIRED_LOCATION_DATA
-----------------------------------------------
 Move data off retired storage locations done

(1 row)
  4. Repeat the previous query to verify the storage location of the messages table:
    => SELECT node_name, total_row_count, storage_type, location_label FROM storage_containers 
   WHERE projection_name ILIKE 'messages%';
    node_name     | total_row_count | storage_type | location_label
------------------+-----------------+--------------+----------------
 v_vmart_node0001 |          333255 | ROS          | base
 v_vmart_node0001 |          333514 | ROS          | base
 v_vmart_node0003 |          333514 | ROS          | base
 v_vmart_node0003 |          333231 | ROS          | base
 v_vmart_node0002 |          333231 | ROS          | base
 v_vmart_node0002 |          333255 | ROS          | base
(6 rows)

See Also