ALTER COORDINATOR

Description

Modifies the CN status. You can run the SQL statement on any normal CN in the cluster to change the value of the nodeis_active field of a specified node in the pgxc_node system catalog. You can also specify the nodes on which the system catalog is modified.

Precautions

• ALTER COORDINATOR is a statement used to modify the system catalog. Only the administrator and users in internal maintenance mode (for example, CM administrator) can execute this statement. This statement is dedicated for the CN removal feature and must be used together with other operations. You are advised not to run it by yourself.

• After this statement is executed, SELECT reload_active_coordinator() needs to be called to update the connection pool information of the node on which the system catalog is modified.

Syntax

1
2

ALTER COORDINATOR nodename SET status
      WITH (nodename1[, nodename2, nodename3 ...]);

Parameters

• nodename

  Specifies the CN name corresponding to a row of records in the pgxc_node system catalog. After the node name is specified, the value of nodeis_active in the record is changed.

  Value range: a string. Only CNs are supported. Ensure that the node name has a corresponding record in the pgxc_node system catalog.

• status

  Specifies the CN status, that is the updated value of nodeis_acitve in the pgxc_node system catalog.

  Value range:

  - FALSE

  - TRUE

• nodename1[, nodename2, nodename3 ...]

  Specifies the range of nodes on which the SQL statement is executed. When ALTER COORDINATOR is executed, the SQL statement is automatically delivered to all nodes in the range. The current execution node must be included.

  Value range: a string. Only CNs are supported. Ensure that the node name has a corresponding record in the pgxc_node system catalog and the node is normal. Otherwise, the SQL statement fails to be executed.

Examples

• Remove a faulty CN.

  -- The cluster has three CNs: cn_5001, cn_5002, and cn_5003, which are working properly. If cn_5001 is faulty and the removal time meets the requirement, cn_5001 needs to be removed from the cluster.
  gaussdb=# ALTER COORDINATOR cn_5001 SET False WITH (cn_5002,cn_5003);
  
  -- Refresh the pgxc_node system catalog. On the cn_5002 and cn_5003 nodes, run the following SQL statement to update the value of nodeis_active corresponding to the cn_5001 record in the pgxc_node system catalog to false:
  gaussdb=# SELECT reload_active_coordinator();
  
  -- Check the CN status. The value of nodeis_active changes to f (false).
  gaussdb=#  SELECT nodeis_active,node_name FROM pgxc_node where node_name='cn_5001';
   nodeis_active | node_name 
  ---------------+-----------
   f             | cn_5001
  (1 row)

• Restore the CN whose fault has been rectified.

  -- After the cn_5001 fault is rectified, add cn_5001 back to the cluster.
  gaussdb=#  ALTER COORDINATOR cn_5001 SET True WITH (cn_5002,cn_5003);
  
  -- Refresh the pgxc_node system catalog. On the cn_5002 and cn_5003 nodes, run the following SQL statement to update the value of nodeis_active corresponding to the cn_5001 record in the pgxc_node system catalog to true:
  gaussdb=# SELECT reload_active_coordinator();
  
  -- Check the CN status. The value of nodeis_active changes to t (true).
  gaussdb=# SELECT nodeis_active,node_name FROM pgxc_node where node_name='cn_5001';
   nodeis_active | node_name 
  ---------------+-----------
   t             | cn_5001
  (1 row)