Huawei Cloud - GausDB

HUAWEI CLOUD GAUSDB

1. Buying a GaussDB Instance(1) Log in to the management console, and choose Service List > Databases > GaussDB.

1. Remotely Logging in to the ECSStep 1: Log in to an ECS using Xfce.Double-click Xfce terminal on the desktop and run the following command to log in to the ECS:

ssh root@*EIP*

Step 2: Download and decompress the gsql client package.

wget https://dbs-download.obs.cn-north-1.myhuaweicloud.com/GaussDB/1642684986086/GaussDB_opengauss_client_tools.zip

unzip GaussDB_opengauss_client_tools.zip

cd /root/GaussDB_opengauss_client_tools/Euler2.5_X86_64

cp GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz /opt

cd /opt/

tar -zxvf GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz

source gsql_env.sh

Step 3: Use the gsql client to connect to the database and create the database and the user.In the following command, 192.168.0.72 is the IP address of the primary GaussDB node, and your password. Replace them as needed.

gsql -h 192.168.0.72 -d postgres -p 8000 -U root -W yourpassword -r

CREATE DATABASE devdb ENCODING 'UTF8' template = template0;

\q

Replace the IP address in the command with the one of the primary GaussDB node.

gsql -h 192.168.0.72 -d devdb -p 8000 -U root -W yourpassword -r

CREATE USER hccdp SYSADMIN IDENTIFIED BY "yourpassword";

\q

Login command. Replace the IP address in the command with the one of the primary GaussDB node.

gsql -h 192.168.0.72 -d devdb -p 8000 -U hccdp -W yourpassword -r

\q

1. Creating, Viewing, Modifying, or Deleting a DatabaseStep 1: Log in to a database.Replace the IP address in the command with the one of the primary GaussDB node.

cd /opt/
source gsql_env.sh
gsql -h 192.168.0.72 -d devdb -p 8000 -U hccdp -W yourpassword -r

Step 2: Create database testdb.

CREATE DATABASE testdb;

Step 3: View databases.Method 1: Query the database list in the pg_database system catalog.

SELECT datname FROM pg_database;

Method 2: Run the \l meta-command to view the database list of the database system.

\l

Step 4: Modify the database.Modify the default search path of the database.

ALTER DATABASE testdb SET search_path TO pa_catalog,public;

Switch to database testdb.

 \c testdb

Enter the password of database testdb, and run the following command to check whether the search path has been modified.

show search_path;

Step 5: Rename the database.Switch to the default database postgres.

\c postgres

Run the following command to change the name of testdb to testdb1:

ALTER DATABASE testdb RENAME TO testdb1;

Check whether the name has been changed.

 \l

Step 6: Delete the database.

DROP DATABASE testdb1;

1. Creating, Viewing, Modifying, and Deleting Row-and Column-Store Tables Step 1: Create a row-store table.

CREATE TABLE PART
(
    P_PARTKEY     BIGINT NOT NULL,
    P_NAME        VARCHAR(55) NOT NULL,
    P_MFGR        CHAR(25) NOT NULL,
    P_BRAND       CHAR(10) NOT NULL,
    P_TYPE        VARCHAR(25) NOT NULL,
    P_SIZE        BIGINT NOT NULL,
    P_CONTAINER   CHAR(10) NOT NULL,
    P_RETAILPRICE DECIMAL(15,2) NOT NULL,
    P_COMMENT     VARCHAR(23) NOT NULL
)
WITH (ORIENTATION = ROW);

Step 2: Create a column-store table.

CREATE TABLE PART1
(
    P_PARTKEY     BIGINT NOT NULL,
    P_NAME        VARCHAR(55) NOT NULL,
    P_MFGR        CHAR(25) NOT NULL,
    P_BRAND       CHAR(10) NOT NULL,
    P_TYPE        VARCHAR(25) NOT NULL,
    P_SIZE        BIGINT NOT NULL,
    P_CONTAINER   CHAR(10) NOT NULL,
    P_RETAILPRICE DECIMAL(15,2) NOT NULL,
    P_COMMENT     VARCHAR(23) NOT NULL
)
WITH (ORIENTATION = COLUMN);

Step 3: View the table information

\d

Step 4: Modify the table attributes by adding a column.

ALTER TABLE part ADD COLUMN p_col1 bigint;

Check whether the column has been added.

 \d part

Step 5: Add the default value in the column.

ALTER TABLE part ALTER COLUMN p_col1 SET DEFAULT 1;

Check whether the default value has been added.

\d part

Step 6: Delete the default value from the column.

ALTER TABLE part ALTER COLUMN p_col1 drop DEFAULT ;

Check whether the default value has been deleted.

\d part

Step 7: Change the data type of a column.

ALTER TABLE part MODIFY p_col1 INT;

Check whether the data type has been changed.

 \d part

Step 8: Rename a column.

ALTER TABLE part RENAME p_col1 to p_col;

Check whether the column has been renamed.

\d part

Step 9: Delete a column.Delete the p_col column from table part.

ALTER TABLE part DROP COLUMN p_col;

Check whether the column has been deleted.

\d part

Step 10: Delete tables.Delete the table PART.

DROP TABLE PART;

Delete the table PART1.

DROP TABLE PART1;

Check whether the tables have been deleted.

\d

1. Managing UsersStep 1: Create user jim.You can set your own password, but you must remember the password.

CREATE USER jim PASSWORD 'yourpassword';

Step 2: View the user list.

SELECT * FROM pg_user;

Step 3: Create user dim and grant the permission to create a database

CREATE USER dim CREATEDB PASSWORD 'yourpassword';

Step 4: Change the login password of user jim from yourpassword to another password

ALTER USER jim IDENTIFIED BY 'yourpassword' REPLACE 'anotherpassword';

Step 5: Assume that the original session is A and the new session is B.

Check whether the password has been changed in session B.
Go to the directory and run the gsql command.

cd /opt/
source gsql_env.sh

Login command. Replace the IP address in the command with the one of the primary GaussDB node.

gsql -h 192.168.0.72 -d postgres -U jim -W yourpassword -p 8000 -r

Step 6: Return to the session A and grant permission CREATEROLE to user jim.

ALTER USER jim CREATEROLE;

Note: Perform steps 7 and 8 in session A.Step 7: Check whether the permission has been granted.

\du jim

Step 8: Lock jim's account.

ALTER USER jim ACCOUNT LOCK;

Step 9: Create a session C, log in as user jim, and check whether the account has been locked.Go to the directory and run the gsql command.

cd /opt/
source gsql_env.sh

Log in to gsql as user jim. Replace the IP address in the command with the one of the primary GaussDB node.

gsql -h 192.168.0.72 -d postgres -U jim -W yourpassword -p 8000 -r

If the following information is displayed, user jim was locked:

Step 10: Return to the session A and unlock jim's account.

ALTER USER jim ACCOUNT UNLOCK;

Step 11: Return to session C, log in as user jim, and check whether the account has been unlocked.Replace the IP address in the command with the one of the primary GaussDB node.

gsql -h 192.168.0.72 -d postgres -U jim -W yourpassword -p 8000 -r

1. Return to session A and delete the user jim.

DROP USER jim CASCADE;

1. Managing SchemasStep 1: Create schema ds.Schema management allows multiple users to use the same database without interfering with each other.Each database can have one or more schemas.When a user is created in a database, the system automatically creates a schema with the same name as the user.

CREATE SCHEMA ds;

Check whether the schema has been created

\dn

Step 2: Change the name of schema from ds to ds_new.

ALTER SCHEMA ds RENAME TO ds_new;

Check whether the schema name has been changed.

\dn

Step 3: Create user jack.

CREATE USER jack PASSWORD 'yourpassword';

Step 4: Change the owner of ds_new to jack

ALTER SCHEMA ds_new OWNER TO jack;

Step 5: View the schema owner.

SELECT s.nspname,u.usename AS nspowner FROM pg_namespace s, pg_user u WHERE s.nspowner = u.usesysid;

Step 6: Delete user jack and schema ds_new.

DROP SCHEMA ds_new;

DROP USER jack;

1. Granting System Permissions and Object Permissions to Users or RolesStep 1: Create user joe and grant permission sysadmin to it.Note: You can set your own password, but you need to replace the password in the following commands with the new one.

CREATE USER joe PASSWORD 'yourpassword';

ALTER USER joe with sysadmin;

Check whether the permission has been granted.

\du

Step 2: Revoke permission sysadmin of user joe, create schema tpcds, and create table reason for the schema.

ALTER USER joe with nosysadmin;

CREATE SCHEMA tpcds;

 CREATE TABLE tpcds.reason
(
    r_reason_sk            INTEGER               NOT NULL,
    r_reason_id            CHAR(16)              NOT NULL,
    r_reason_desc                VARCHAR(20)                  
);

Assume that the current session is A and a new session is B. In session B, connect to the database as user joe and check whether user joe has the permission for the table reason in schema tpcds.

cd /opt/
source gsql_env.sh

Replace the IP address in the command with the one of the primary GaussDB node.

gsql -h 192.168.0.174 -d postgres -p 8000 -U joe -W yourpassword -r

Check whether user joe has the permission for the table reason in schema tpcds.

select * from tpcds.reason;

Step 3: Return to session A, and grant the permission to use schema tpcds and all permissions for table reason to user joe.

GRANT USAGE ON SCHEMA tpcds TO joe;

GRANT ALL PRIVILEGES ON tpcds.reason TO joe;

Switch to session B and check whether user joe has the permission for table reason in schema tpcds.

select * from tpcds.reason;

Step 4: Return to session A and grant joe the query permission to query the r_reason_sk, r_reason_id, and r_reason_desc columns and the permission to update the r_reason_desc column in table reason in schema tpcds.

GRANT select (r_reason_sk,r_reason_id,r_reason_desc),update (r_reason_desc) ON tpcds.reason TO joe;

Step 5: Grant connection and schema creation permissions for database postgres to user joe, and allow this user to grant these permissions to other users.

GRANT create,connect on database postgres TO joe WITH GRANT OPTION;

Switch to session B and check whether user joe has the permissions to query and update table reason in schema tpcds.Check whether the query permission is in effect.

SELECT r_reason_sk,r_reason_id,r_reason_desc FROM tpcds.reason;

Check whether the update permission is in effect.

UPDATE tpcds.reason SET r_reason_desc='test';

Step 6: Return to session A, create role tpcds_manager, grant access and role creation permissions on schema tpcds to this role, but do not allow this role to grant those permissions to others.

CREATE ROLE tpcds_manager PASSWORD 'yourpassword';

Check whether role tpcds_manager has been added

\du

GRANT USAGE,CREATE ON SCHEMA tpcds TO tpcds_manager;

Step 7: Create role manager, grant joe's permissions to manager, and allow manager to grant these permissions to others.

CREATE ROLE manager PASSWORD 'yourpassword';

GRANT joe TO manager WITH ADMIN OPTION;

Step 8: Create role senior_manager and grant manager's permissions to senior_manager.

CREATE USER senior_manager PASSWORD 'yourpassword';

GRANT manager TO senior_manager;

Step 9: Revoke permissions and delete users.

DROP USER manager;

REVOKE ALL PRIVILEGES ON tpcds.reason FROM joe;

REVOKE ALL PRIVILEGES ON SCHEMA tpcds FROM joe;

REVOKE USAGE,CREATE ON SCHEMA tpcds FROM tpcds_manager;

DROP ROLE tpcds_manager;

DROP USER senior_manager;

DROP USER joe CASCADE;

1. Checking Whether an Object Already Exists Before Creating ItStep 1: Check whether a user already exists before creating it.

SELECT usename FROM pg_user WHERE usename='dbtest';

CREATE USER dbtest WITH sysadmin PASSWORD 'yourpassword';

Step 2: Exit the database as user hccdp, and connect to the database as the new user dbtest.Exit the current database.

\q

Connect to database devdb as user dbtest.Replace the IP address in the command with the one of the primary GaussDB node.

gsql -h 192.168.0.174 -d postgres -p 8000 -U dbtest -W yourpassword -r

Step 3: Check whether the schema has been created.

SELECT nspname FROM pg_namespace WHERE nspname='dbtest';

If a schema has already been created, the following alert will be displayed.

CREATE SCHEMA dbtest;

Step 4: Check whether a database already exists before creating it.

SELECT datname FROM pg_database WHERE datname='db_test';

CREATE DATABASE db_test;

Step 5: Check whether a table already exists before creating it.

SELECT tablename FROM pg_tables WHERE schemaname='dbtest';

CREATE TABLE test (id int, name varchar(100));

Step 6: View all objects created by the current user.

SELECT relname FROM pg_class WHERE relowner=(select usesysid from pg_user where usename='dbtest');

Step 7: Delete database objects and users, and then exit the current database.

DROP TABLE test;

DROP SCHEMA dbtest;

DROP DATABASE db_test;

\q

1. Viewing and Deleting SessionsStep 1: Log in to the database as user hccdp.

cd /opt/
source gsql_env.sh

Replace the IP address in the command with the one of the primary GaussDB node. The password is the one you have set.

gsql -h 192.168.0.174 -d postgres -p 8000 -U hccdp -W yourpassword -r

Step 4: Query pg_stat_activity using sleep.

SELECT pid,usename,query FROM pg_stat_activity WHERE QUERY LIKE '%sleep%';

Step 5: Use function pg_terminate_backend to end a session.Note: Replace pid in the following command with the digits in the preceding figure.

SELECT pg_terminate_backend(pid);

Step 6: View the original shell interface.The following error is displayed:

1. Designing a Stored Procedure for Outputting a Specific Value in a Search TableStep 1: Create a table.

CREATE TABLE employee (id varchar(100),name varchar(100),salary int);

CREATE TABLE ep_grade (id varchar(100),grade varchar(100),flag int);

Step 2: Import data.

Insert into employee values (1,'Sam',5000);
Insert into employee values (2,'Lucy',5000);
Insert into employee values(3,'Linda',5000);
Insert into employee values (4,'Jack',5000);
Insert into employee values (5,'Mary',5000);
Insert into employee values(6,'Alex',5000);
Insert into employee values(7,'Anna',5000);
Insert into employee values (8,'Peter',5000);
Insert into ep_grade values(1,'A',2022);
Insert into ep_grade values(2,'S',2021);
Insert into ep_grade values(3,'B',2021);
Insert into ep_grade values(4,'B',2021);
Insert into ep_grade values(5,'A',2021);
Insert into ep_grade values(6,'C',2022);
Insert into ep_grade values(7,'C',2021);
Insert into ep_grade values(8,'B',2022);

Step 3: Integrate the previous statements according to the structure of the stored procedure

CREATE OR REPLACE PROCEDURE proc_emp()
AS

(1) Declare parameters.

DECLARE
   EP_ID VARCHAR(100);
   GRADE VARCHAR(10);
   SALARY INT;

(2) Declare a cursor.

CURSOR C1 IS select distinct id,grade FROM ep_grade where Flag = 2021;

3) Design a loop statement.

BEGIN
   OPEN C1;
   LOOP
      FETCH C1 INTO EP_ID,GRADE;
      EXIT WHEN C1%NOTFOUND;
      Case GRADE 
      when 'S' 
         then update employee set salary = salary+1000 where id=EP_ID;
      when 'A'
         then update employee set salary = salary+500  where id=EP_ID;
      when 'B'
          then update employee set salary = salary+100 where id=EP_ID;
      when 'C'
          then update employee set salary = salary-200 where id=EP_ID;
      END Case;

(4) Design the content output of the stored procedure.

DBE_OUTPUT.PRINT_LINE('ID: '||EP_ID||', Grade: '||GRADE||', updated.');
END LOOP;
CLOSE C1;
END;
/

Step 4: Call a stored procedure.

call proc_emp();