登录
gsql -d postgres -h 192.168.0.123 -p 8000 -U root -P Sinoregal@2025 -r
create database devdb encoding ‘UTF8’ template=template0;
create database testdb;
create database mydb with owner=jack;
create user hccdp sysadmin password “Sinoregal@2025”;
create user jim password “Sinoegal@2025”;
create user dim createdb password “Sinoregal@2025”;
alter user jim identified by ‘newSinoregal@2025’ replace ‘Sinoregal@2025’;
alter user jim createrole;
alter user jim account lock;
alter user jim account unlock;
drop user jim cascade;
alter database testdb set search_path to pa_catalog,public;
show search_path;
set search_path = sch_a;
alter database testdb rename to testdb1;
drop database testdb1;
create table part (
p_partkey bigint not null,
p_retailprice decimal(15,2) not null
) with (orientation= row);
alter table part add primary key(p_partkey);
alter table part add constraint pk_part_id primary key(p_partkey);
alter table part add column p_col1 bigint;
alter table part alter column p_col1 set default 1;
alter table part alter column p_col1 drop default;
alter table part modify p_col1 int;
alter table part rename p_col1 to p_col;
alter table part drop column t_col;
drop table part;
create schema ds;
show search_path;
create schema tsk authorization user1;#指定要用户
将当前模式ds更名为ds_new: alter schema ds rename to ds_new;
create user jack password ‘Sinoregal@2025’;
alter schema ds_new owner to jack;
drop schema ds_new;
create user joe password ‘Sinoregal@2025’;
create user user3 with valid begin ‘2025-03-25 00:00:00’ valid until ‘2025-04-25 23:59:59’ identified by ‘Sinoregal@2025’ connecttion limit 100;
alter user user3 rename to user4;
alter user joe with sysadmin;
alter user joe wity nosysadmin;
create tablespace tbs1 relative location ‘tablespace/tbs1’ maxsize ‘100G’;
create tablespace tab2 owner jack location ‘/gauss/data/tbs2’;
create schema tpcds;
create table tpcds.reason (
c1 int not null
);
grant usage on schema tpcds to joe;
grant all privileges on tpcds.reason to joe;
grant select (c1,c2),update (c3) on tpcds.reason to joe;
grant create,connect on database postgres to joe with grant option;
create role tpcds_manager password ‘Sinoregal@2025’;
grant usage,create on schema tpcds to tpcds_manager;
create role manager password ‘Sinoregal@2025’;
grant joe to manager with admin option;
create user senior_manager password ‘Sinoregal@2025’;
grant manager to senior_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 joe cascade;
SELECT pg_sleep(300);
SELECT pid,usename,query FROM pg_stat_activity WHERE QUERY LIKE ‘%sleep%’;
SELECT pg_terminate_backend(pid);
CREATE TABLE employee (id varchar(100),name varchar(100),salary int);
CREATE TABLE ep_grade (id varchar(100),grade varchar(100),flag int);
Insert into employee values(1,‘张三’,5000);
Insert into employee values(2,‘李四’,5000);
Insert into employee values(3,‘王五’,5000);
CREATE OR REPLACE PROCEDURE proc_emp() AS
DECLARE
EP_ID VARCHAR(100);
GRADE VARCHAR(10);
SALARY INT;
CURSOR C1 IS select distinct id,grade FROM ep_grade where Flag = 2021;
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;
DBE_OUTPUT.PRINT_LINE(‘ID: ‘||EP_ID||’, Grade: ‘||GRADE||’, updated.’);
END LOOP;
CLOSE C1;
END;
/
call proc_emp();
CREATE UNIQUE INDEX bmsql_oorder_idx1 ON bmsql_oorder USING btree (o_w_id, o_d_id, o_carrier_id, o_id);
create index bmsql_customer_idx1 on bmsql_customer (c_w_id, c_d_id, c_last, c_first);
\copy bmsql_warehouse from ‘/root/tpcc/bmsql_warehouse.csv’ with ( FORMAT ‘csv’, DELIMITER ‘,’, ENCODING ‘utf8’);
analyze bmsql_stock;
tar xzvf OpenJDK11U-jdk_x64_linux_openj9_linuxXL_11.0.10_9_openj9-0.24.0.tar.gz
mv jdk-11.0.10+9 /usr/lib/
ln -s /usr/lib/jdk-11.0.10+9/bin/java /usr/local/bin/java
ln -s /usr/lib/jdk-11.0.10+9/bin/javac /usr/local/bin/javac
static final String JDBC_DRIVER = “com.huawei.opengauss.jdbc.Driver”;
static final String DB_URL = “jdbc:opengauss://192.168.0.72:8000/demo”;
static final String USER = “db_dev”;
static final String PASS = “yourpassword”;
static String connection_url = DB_URL + “?user=” + USER + “&password=” + PASS;
javac -d . exptConnection.java
yum install tree
tree
java -cp /opt/expt/db/libs/opengaussjdbc.jar:. expt.db.basic.exptConnection
static final String DB_URL = “jdbc:opengauss://192.168.0.72:8000,192.168.0.71:8000,192.168.0.108:8000/demo”;
static final String PARM = “autoBalance=true”;
static String connection_url = DB_URL + “?user=” + USER + “&password=” + PASS + “&” + PARM;
static final String PARM = “targetServerType=master”;
CREATE TABLE part_tbl1 (a int, b int)
PARTITION BY RANGE(a)
(
PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (100),
PARTITION part3 VALUES LESS THAN (MAXVALUE)
);
CREATE TABLE part_tbl2 (a int, b int)
PARTITION BY RANGE(a)
(
partition part1 START(1) END(100) EVERY(50),
partition part2 END(200),
partition part3 START(200) END(300),
partition part4 start(300)
);
create unique index idx_a on t1(a);
create index idx_pt1_local on pt1(id) local;
create index idx_pt1_glo on pt1(id) global;
drop index indexname;
IF sex = ‘m’ THEN
pretty_sex := ‘man’;
ELSE
IF sex = ‘f’ THEN
pretty_sex := ‘woman’;
END IF;
END IF;
IF number_tmp = 0 THEN
result := ‘zero’;
ELSIF number_tmp > 0 THEN
result := ‘positive’;
ELSIF number_tmp < 0 THEN
result := ‘negative’;
ELSE
result := ‘NULL’;
END IF;
CREATE OR REPLACE PROCEDURE proc_loop(i in integer, count out integer) AS
BEGIN
count:=0;
LOOP
IF count > i THEN
raise info 'count is %. ', count;
EXIT;
ELSE
count:=count+1;
END IF;
END LOOP;
END;
/
CALL proc_loop(10,5);
CREATE TABLE integertable(c1 integer) DISTRIBUTE BY hash(c1);
CREATE OR REPLACE PROCEDURE proc_while_loop(maxval in integer) AS
DECLARE
i int :=1;
BEGIN
WHILE i < maxval LOOP
INSERT INTO integertable VALUES(i);
i:=i+1;
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE proc_for_loop() AS
BEGIN
FOR I IN 0…5 LOOP
DBE_OUTPUT.PRINT_LINE('It is ‘||to_char(I) || ’ time;’) ;
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE proc_case_branch(pi_result in integer, pi_return out integer) AS
BEGIN
CASE pi_result
WHEN 1 THEN
pi_return := 111;
WHEN 2 THEN
pi_return := 222;
WHEN 3 THEN
pi_return := 333;
END CASE;
raise info ‘pi_return : %’,pi_return ;
END;
/
CALL proc_case_branch(3,0);
CREATE OR REPLACE PROCEDURE test_cursor
IS
VAR1 INT;
CURSOR C1 IS SELECT col1 FROM shippable_table;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO VAR1;
EXIT WHEN C1%NOTFOUND;
DBE_OUTPUT.PRINT_LINE(VAR1);
END LOOP;
CLOSE C1;
END;
/
查看WDR相关配置项。
SELECT name,setting FROM pg_settings WHERE name LIKE ‘%wdr%’;
WDR的数据表保存在snapshot这个schema下以snap_开头的表，其数据来源于dbe_perf这个schema内的视图。
SELECT relname FROM pg_class WHERE relname LIKE ‘%snap_%’;
select create_wdr_snapshot();
select * from pg_node_env;
SELECT * FROM snapshot.snapshot ORDER BY start_ts DESC LIMIT 10;
生成WDR报告。
\a\t
\o /root/index.html
生成报告语句格式示例：
SELECT generate_wdr_report(begin_snap_id Oid, end_snap_id Oid, int report_type, int report_scope, int node_name);
参数说明：
begin_snap_id：要查看的某段时间性能的开始的snapshot的id（表snapshot.snaoshot中的snapshot_id）。
end_snap_id：结束snapshot的id，默认end_snap_id大于begin_snap_id（表snapshot.snaoshot中的snapshot_id）。
report_type：指定生成report的类型，可取值summary、detail、all。
report_scope：指定生成report的范围，可取值cluster、node。
node_name：在report_scope指定为single node时，需要把该参数指定为对应节点的名称，取pg_node_env中node_name字段值。在report_scope为cluster时，该值可以指定为省略或者为NULL。
SELECT generate_wdr_report(34, 35, ‘all’, ‘cluster’);
SELECT generate_wdr_report(34, 35, ‘all’, ‘node’, ‘dn_6001_6002_6003’);
执行如下命令关闭输出选项及格式化输出命令。
\a
\t
\o /root/index.html
mv /root/index.html /var/www/html/
create database db1;
create t1(c1 int, c2 int);
insert into t1 values (1,2),(3,4),(5,6);
t1:
begin;
select pg_sleep(1000);
update t1 set c2=c2+10 where c1=1;
t2:
begin;
update t1 set c2=c2+100 where c1=1;
SELECT pid FROM pg_stat_activity WHERE datname = ‘db1’ and state=‘idle in transaction’;
SELECT pg_terminate_backend(pid);
\copy t1 from ‘t1.csv’ with ( FORMAT ‘csv’, DELIMITER ‘,’, ENCODING ‘utf8’);
\copy t1 to ‘t1.csv’ with ( FORMAT ‘csv’, DELIMITER ‘,’, ENCODING ‘utf8’);
=========================================
gs_dump postgres -U u1 -W Huawei@123 -p 16000 -s -t t1 -f /data/t1.sql –F c
-U 数据库用户名
-W 数据库用户密码
-p 数据库CN连接端口号
-s 只导出对象定义，不导出对象数据
-t 只导出指定的该表
-f 将输出发送至指定文件
-F 导出文件的格式，取值有四种：p 纯文本、c 自定义归档、d 目录归档和t tar归档格式
导出整个数据库对象：
gs_dump postgres -p 16000 -s -f /data/all.sql
导出数据库所有对象和数据：
gs_dump postgres -p 16000 -f /data/all.sql
从postgres 数据库中导出所有对象定义
gs_dump postgres -p 16000 -s -f /data/all.sql -F c
向db1库中恢复上一步导出的对象定义
gs_restore -d db1 -p 16000 /data/all.sql
gsql -d db1 -p 16000 -U u1 -W Huawei@123 -f /data/table.sql
gds -d /input_data/ -p 10.186.251.85:8808 -H 0.0.0.0/0 -l /log/gds_log.txt –D –t 2
create foreign table t1_foreign(a1 varchar2(10), a2 int)
SERVER gsmpp_server OPTIONS (location ‘gsfs://10.186.251.85:8808/t1.txt’,
format ‘text’, encoding ‘utf8’, delimiter ‘^’, null ‘’）per node reject limit ‘value’
with error_t1_foreign;
insert into t1 select * from t1_foreign;
create foreign table t1_foreign_output(a1 varchar2(10), a2 int)
SERVER gsmpp_server OPTIONS (location ‘gsfs://10.185.240.41:8000/’,
format ‘text’, encoding ‘utf8’, delimiter ‘^’, null ‘’)
write only;
insert into t1_foreign_output select * from t1;
导出的文本命名格式为t1_foreign_output.dat.0
执行计划：explain select * from tab1;
统计更新：analyze tab1;
SELECT /*+hint hint */ * FROM …
EXPLAIN
SELECT /*+ tablescan(t1) */ t1.a FROM t1 WHERE t1.a = 1;
/*+ [no] tablescan(table) /
/+ [no] indexscan/indexonlyscan(tablename indexname) */
/*+ [no] nestloop/hashjoin/mergejoin(table1 table2) */
/*+ leading(join_table_list) /
/+ leading(t1 (t2 t3)) */
create or replace procedure proc_nation() as
declare
res_name varchar(20);
res_region varchar(20);
TYPE ref_cur_type is ref cursor;
my_cur ref_cur_type;
begin
open my_cur for select n_name,n_region from nation;
fetch my_cur into res_name,res_region;
while my_cur%FOUND loop
case res_region
when 0 then
dbe_output.print_line(‘国家：’||res_name||‘，属于亚洲’);
when 1 then
dbe_output.print_line(‘国家：’||res_name||‘，属于非洲’);
end case;
end loop;
end;
/
create or replace procedure proc_nan(name in varchar(20), region out varchar(20)) as
declare
region_name varchar(20);
type ref_cur_type is ref cursor;
my_cur ref_cur_type;
begin
case name
when 1 then
region := ‘兖州’;
when 2 then
region := ‘福州’;
end case;
end;
/