Help Center/ GaussDB(DWS)/ Management Guide/ Managing Logical Clusters/ Tutorial: Setting a Read-Only Logical Cluster and Binding It to a User
Updated on 2024-09-05 GMT+08:00
View PDF

Tutorial: Setting a Read-Only Logical Cluster and Binding It to a User

Scenario

If your workloads vary greatly in different periods of time, a three-node cluster may be unable to handle all the throughput during peak hours; but a six-node cluster may be too large, wasting resources and increasing costs. In this case, you can follow this tutorial and the instructions in Elastically Adding or Deleting a Logical Cluster to use only three nodes during off-peak hours at night, six nodes during daytime, and nine nodes during peak hours.

This tutorial describes how to configure a new logical cluster (without service data) as read-only and switch some users to the cluster. In this way, tables created by those users are still in the original Node Group, but the computing logic is switched to the read-only logical cluster.

Prerequisites

A six-node cluster has been created and divided into two logical clusters: v3_logical and lc1. The lc1 cluster has no service data. For details, see Creating a DWS 3.0 Cluster.

Configuring a Read-Only Logical Cluster and Switching Users to the Cluster

  1. Connect to the database as system administrator dbadmin and run the following SQL statement to check whether the logical cluster is created: 

    1
    		 
    SELECT group_name FROM PGXC_GROUP;

  2. Set logical cluster lc1 to be read-only. 

    1
2
3
    		 
    SET xc_maintenance_mode=on;
ALTER NODE GROUP lc1 SET READ ONLY;
SET xc_maintenance_mode=off;

  3. Create a user. 

    1
    		 
    create user testuser password 'testuser12#$%';

  4. Bind the user to the logical cluster lc1. Replace variables in the following statements ( such as testuser and lc1) as needed.

    Find the NodeGroup of the user. If a record can be found, set the record to the default_storage_nodegroup of the user so that the tables created by the user will still be in the original Node Group. If no records are found, directly run the two ALTER statements in the end. 
    1
2
    		 
    SELECT nodegroup FROM pg_user WHERE usename='testuser';
ALTER USER testuser SET default_storage_nodegroup='nodegroup'; // Replace nodegroup with the node group name obtained in the preceding SQL statement.

    Bind the user to the new read-only logical cluster. In this way, the computing logic of the user is switched to the read-only logical cluster for execution.

    1
2
    		 
    ALTER USER testuser NODE GROUP lc1;
ALTER USER testuser SET enable_cudesc_streaming=ON;