【参赛作品21】opengauss场景使用和日常管理
作者：大数据模型
案例一 常用管理SQL语句应用实践
用户初次猎鲜openGauss，没有明确的需求，只想体验一下增加删除，前提不用openGauss默认自带例如表空间和默认schema。关键技术涉及创建表空间，创建数据库，创建数据模式，创建表 插入数据 修改数据 查询数据等等。
创建表空间
创建表空间tpch_ts
openGauss=# create tablespace tpch_ts relative location 'tablespace/tpch_ts';
CREATE TABLESPACE
openGauss=# select spcname from pg_tablespace;
spcname
------------
pg_default
pg_global
tpch_ts
(3 rows)
创建数据库
创建数据库tpchdb，并与表空间tpch_ts绑定
openGauss=# create database tpchdb with tablespace=tpch_ts;
CREATE DATABASE
openGauss=# select datname from pg_database;
datname
template1
tpchdb
template0
postgres
(4 rows)
查询数据库tpchdb与哪一个表空间的ID绑定
openGauss=# select datname,dattablespace from pg_database where datname=‘tpchdb’;
datname | dattablespace
---------±--------------
tpchdb | 16384
(1 row)
查询数据库tpchdb对应的表空间
openGauss=# select oid,spcname from pg_tablespace where oid=16384;
oid | spcname
-------±--------
16384 | tpch_ts
(1 row)
创建数据模式
查看相关数据库有哪些模式
tpchdb=# select catalog_name,schema_name,schema_owner from information_schema.schemata;
catalog_name | schema_name | schema_owner
--------------+--------------------+--------------
tpchdb | pg_toast | omm
tpchdb | cstore | omm
tpchdb | pkg_service | omm
tpchdb | dbe_perf | omm
tpchdb | snapshot | omm
tpchdb | blockchain | omm
tpchdb | pg_catalog | omm
tpchdb | public | omm
tpchdb | sqladvisor | omm
tpchdb | dbe_pldebugger | omm
tpchdb | information_schema | omm
tpchdb | db4ai | omm
创建模式scha
tpchdb=# create schema scha;
CREATE SCHEMA
tpchdb=# select catalog_name,schema_name,schema_owner from information_schema.schemata;
catalog_name | schema_name | schema_owner
--------------±-------------------±-------------
tpchdb | pg_toast | omm
tpchdb | cstore | omm
tpchdb | pkg_service | omm
tpchdb | dbe_perf | omm
tpchdb | snapshot | omm
tpchdb | blockchain | omm
tpchdb | pg_catalog | omm
tpchdb | public | omm
tpchdb | sqladvisor | omm
tpchdb | dbe_pldebugger | omm
tpchdb | information_schema | omm
tpchdb | db4ai | omm
tpchdb | scha | omm
(13 rows)
创建数据表
tpchdb=# create table scha.test1(v1 varchar(20));
CREATE TABLE
tpchdb=# insert into scha.test1 values(‘scha test data’);
INSERT 0 1
tpchdb=# select * from scha.test1;
v1
scha test data
(1 row)
案例二 开发一个应用
基于openGauss初步了解后，用户准备开发一个CMS应用，后端 用的就是openGauss。关系涉及表索引、创建表分区等。
分区表索引分为LOCAL索引与GLOBAL索引，一个LOCAL索引对应一个具体分区，而GLOBAL索引则对应整个分区表
在java中实现OpenGauss数据库的连接
https://opengauss.obs.cn-south-1.myhuaweicloud.com/2.0.1/x86_openEuler/openGauss-2.0.0-JDBC.tar.gz
package HospitalDatabase;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.*;
public class PostgreSqlJdbcConnAddDatas {
public static void main(String args[]) {
Connection c = null;
Statement stmt = null;
try {
Class.forName(“org.postgresql.Driver”);
/*
* @para:url,usename,password
* @return: null
*/
c = DriverManager.getConnection(“jdbc:postgresql://192.168.159.129:26000/postgres”,“jack”,“gauss@111”);
c.setAutoCommit(false);
System.out.println(<span class="hljs-string">"连接数据库成功！"</span>);
stmt = c.createStatement();
<span class="hljs-comment">/*
* @功能:插入数据
* @参数:表名称（列名1，列名2···列名n）+（数据1，数据2，···数据n）
*/</span>
// String sql = "INSERT INTO Course (Cno,Cname,Cpno,Ccredit) "
// + “VALUES (1,‘数据库’,5,4);”;
// stmt.executeUpdate(sql);
// System.out.println(“新增数据成功！”);
<span class="hljs-comment">/*
* @功能:查询数据
* @参数:表名
*/</span>
// ResultSet rs = stmt.executeQuery(“select * from sc”);
// while(rs.next()){
// int sno = rs.getInt(“Sno”);
// int cno = rs.getInt(“cno”);
// int grade = rs.getInt(“Grade”);
// System.out.println(cno + “,” + cno + “,” + grade);
// }
// System.out.println(“查询数据成功！”);
//
/*
* @功能:更新数据
* @参数:
*/
String sql = <span class="hljs-string">"UPDATE sc set grade = 250 where cno=1 "</span>;
stmt.executeUpdate(sql);
c.commit();
ResultSet rs1 = stmt.executeQuery(<span class="hljs-string">"select * from sc order by cno"</span>);
<span class="hljs-keyword">while</span>(rs1.next()){
<span class="hljs-keyword">int</span> sno = rs1.getInt(<span class="hljs-string">"Sno"</span>);
<span class="hljs-keyword">int</span> cno = rs1.getInt(<span class="hljs-string">"Cno"</span>);
<span class="hljs-keyword">int</span> grade = rs1.getInt(<span class="hljs-string">"Grade"</span>);
System.out.println(sno + <span class="hljs-string">","</span> + cno + <span class="hljs-string">","</span> + grade);
}
System.out.println(<span class="hljs-string">"更新数据成功！"</span>);
<span class="hljs-comment">/*
* @功能:删除数据
* @参数:
*/</span>
// String sql = "Delete from sc where Cno=2 ";
// stmt.executeUpdate(sql);
// c.commit();
//
// ResultSet rs1 = stmt.executeQuery(“select * from sc order by cno”);
// while(rs1.next()){
// int sno = rs1.getInt(“Sno”);
// int cno = rs1.getInt(“Cno”);
// int grade = rs1.getInt(“Grade”);
// System.out.println(sno + “,” + cno + “,” + grade);
// }
// System.out.println(“删除数据成功！”);
stmt.close();
c.commit();
c.close();
} <span class="hljs-keyword">catch</span> (Exception e) {
e.printStackTrace();
System.err.println(e.getClass().getName() + <span class="hljs-string">": "</span> + e.getMessage());
System.exit(<span class="hljs-number">0</span>);
}
// System.out.println(“新增数据成功！”);
}
}
在go中实现OpenGauss数据库的连接
import (
“database/sql”
)
创建表分区
同样表结构字段，同样1000万数据，分成三组测试，一个有分区，一个没分区没索引，一个有索引，通过explain看消耗时间。暂时没有测试分区表索引，openGauss分区表索引分为LOCAL索引与GLOBAL索引，一个LOCAL索引对应一个具体分区，而GLOBAL索引则对应整个分区表
加表分区
没有分区没有索引
CREATE TABLE scha.customer_address
(
ca_address_sk serial ,
ca_address_id character(16) NOT NULL ,
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20)
);
insert into scha.customer_address values(generate_series(1,10000000),‘ca_ad’,‘street’,‘street_name’,‘street_type’,‘suite’,‘city’,‘county’,‘cn’,‘zip’,‘country’,100.00,‘location_type’);
没有分区没有索引
tpchdb=# explain analyze select * from scha.customer_address where ca_address_sk=3000 ;
QUERY PLAN
Seq Scan on customer_address (cost=0.00…333502.95 rows=1 width=131) (actual time=3.236…23640.419 rows=1 lo
ops=1)
Filter: (ca_address_sk = 3000)
Rows Removed by Filter: 9999999
Total runtime: 23640.698 ms
(4 rows)
加分区
CREATE TABLE scha.customer_address
(
ca_address_sk serial ,
ca_address_id character(16) NOT NULL ,
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20)
)
PARTITION BY RANGE (ca_address_sk)
(
PARTITION P1 VALUES LESS THAN(5000),
PARTITION P2 VALUES LESS THAN(10000),
PARTITION P3 VALUES LESS THAN(15000),
PARTITION P4 VALUES LESS THAN(20000),
PARTITION P5 VALUES LESS THAN(25000),
PARTITION P6 VALUES LESS THAN(30000),
PARTITION P7 VALUES LESS THAN(40000),
PARTITION P8 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;
tpchdb=# explain analyze select * from scha.customer_address where ca_address_sk=3000 ;
QUERY PLAN
Partition Iterator (cost=0.00…92728.70 rows=26887 width=280) (actual time=2.845…4.628 rows=1 loops=1)
Iterations: 1
-> Partitioned Seq Scan on customer_address (cost=0.00…92728.70 rows=26887 width=280) (actual time=2.835
…4.618 rows=1 loops=1)
Filter: (ca_address_sk = 3000)
Rows Removed by Filter: 4998
Selected Partitions: 1
Total runtime: 4.900 ms
(7 rows)
加索引
create index index_ca_address_sk on scha.customer_address(ca_address_sk);
tpchdb=# explain analyze select * from scha.customer_address where ca_address_sk=3000 ;
QUERY PLAN
Bitmap Heap Scan on customer_address (cost=760.62…75391.54 rows=26886 width=280) (actual time=0.192…0.193
rows=1 loops=1)
Recheck Cond: (ca_address_sk = 3000)
Heap Blocks: exact=1
-> Bitmap Index Scan on index_ca_address_sk (cost=0.00…753.89 rows=26886 width=0) (actual time=0.110…0.
110 rows=1 loops=1)
Index Cond: (ca_address_sk = 3000)
Total runtime: 0.326 ms
(6 rows)
案例三 开发数据产品方向
用户准备开发一个基于OLAP基础之上的分析应用，后端 用的就是openGauss。关系涉及表索引、创建表分区、列式表等等。
列存表为：YES/NO/LOW/MIDDLE/HIGH，默认值为LOW
CREATE TABLE scha.customer_address_column
(
ca_address_sk serial ,
ca_address_id character(16) NOT NULL ,
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20)
)WITH (ORIENTATION = COLUMN,compression=high);
tpchdb=# dt+ scha.
List of relations
Schema | Name | Type | Owner | Size | Storage
| Description
--------±-----------------------------±------±------±-----------±----------------------------------------
±------------
scha | customer_address_column_middle | table | omm | 97 MB | {orientation=column,compression=middle}
|
scha | customer_address_column_high | table | omm | 97 MB | {orientation=column,compression=high}
|
scha | customer_address_column_low | table | omm | 97 MB | {orientation=column,compression=low}
这里测试下发现列式生成特别慢，而且middle、high、low压缩比都是一样。
案例四 开发数据监控
案例五 备份与恢复实践应用
物理备份
[omm@hybriddb03 ~]$gs_basebackup -D ./backupop -p 26000 -P -l backup20211130
INFO: The starting position of the xlog copy of the full build is: 1/D7000028. The slot minimum LSN is: 1/D7000148.
[2021-11-30 15:19:58]:begin build tablespace list
[2021-11-30 15:19:58]:finish build tablespace list
[2021-11-30 15:19:58]:begin get xlog by xlogstream
[2021-11-30 15:19:58]: check identify system success
[2021-11-30 15:19:58]: send START_REPLICATION 1/D7000000 success
[2021-11-30 15:19:58]: keepalive message is received
[2021-11-30 15:19:58]: keepalive message is received
[2021-11-30 15:20:01]: keepalive message is received
671193/671193 kB (100%), 2/2 tablespaces
[2021-11-30 15:20:09]:gs_basebackup: base backup successfully
物理恢复
模拟故障发生后，进行物理数据恢复，下面把服务停掉，并把物理文件删除掉
[omm@hybriddb03 ~]$ gs_om -t stop
[root@hybriddb03 cluster]# rm db1/* -rf
还原数据文件
[omm@hybriddb03 ~]$ cp -rf backupop/* /data/opengauss/cluster/db1/
[omm@hybriddb03 ~]$ gs_om -t start
逻辑备份
[omm@hybriddb03 ~]$ gs_dump -p 26000 tpchdb -f ./backup/tpchdb.sql
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:50]: The total objects number is 411.
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:50]: [100.00%] 411 objects have been dumped.
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:51]: dump database tpchdb successfully
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:51]: total time: 1751 ms
逻辑恢复
[omm@hybriddb03 ~]$ gs_dump -p 26000 tpchdb -f ./backup/tpchdb.sql
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:50]: The total objects number is 411.
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:50]: [100.00%] 411 objects have been dumped.
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:51]: dump database tpchdb successfully
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:51]: total time: 1751 ms
案例六 数据库日志与企业应用实践
openGauss运行时数据库节点以及openGauss安装部署时产生的日志统称为系统日志。如果openGauss在运行时发生故障，可以通过这些系统日志及时定位故障发生的原因，根据日志内容制定恢复openGauss的方法。
安装布署是 /data/opengauss
数据库节点的运行日志
[root@hybriddb02 omm]# ll /data/opengauss/log/omm/pg_log/dn_6002/
total 9563872
-rw-------. 1 omm dbgrp 20971637 Nov 29 17:29 postgresql-2021-11-29_165730.log
-rw-------. 1 omm dbgrp 16889281 Nov 29 17:55 postgresql-2021-11-29_172930.log
-rw-------. 1 omm dbgrp 466061 Nov 29 23:59 postgresql-2021-11-29_175554.log
-rw-------. 1 omm dbgrp 1118029 Nov 30 15:23 postgresql-2021-11-30_000000.log
-rw-------. 1 omm dbgrp 20971633 Nov 30 15:57 postgresql-2021-11-30_152502.log
-rw-------. 1 omm dbgrp 20971676 Nov 30 16:29 postgresql-2021-11-30_155716.log
-rw-------. 1 omm dbgrp 20971706 Nov 30 17:01 postgresql-2021-11-30_162924.log
OM openGauss安装卸载时产生的日志
[root@hybriddb02 omm]# ll /data/opengauss/log/omm/om/
total 72
-rw-------. 1 omm dbgrp 66009 Dec 15 09:20 gs_local-2021-11-29_163542.log
-rw-------. 1 omm dbgrp 1943 Dec 10 14:34 gs_om-2021-12-10_143432.log
预写式日志WAL（Write Ahead Log，也称为Xlog）是指如果要修改数据文件，必须是在这些修改操作已经记录到日志文件之后才能进行修改，即在描述这些变化的日志记录刷新到永久存储器之后。在系统崩溃时，可以使用WAL日志对openGauss进行恢复操作。
[root@hybriddb02 omm]# ll /data/opengauss/cluster/db1/pg_xlog
total 2408452
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:01 0000000100000001000000C7
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:03 0000000100000001000000C8
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:04 0000000100000001000000C9
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:05 0000000100000001000000CA
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:06 0000000100000001000000CB
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:07 0000000100000001000000CC
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:07 0000000100000001000000CD
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:08 0000000100000001000000CE
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:09 0000000100000001000000CF
案例七 主从复制知识与应用实践
案例八 系统表及系统视图应用实践
案例九 oracle迁移到openGauss应用实践
数据库对象迁移：大部分数据库系统主要用到表、索引、序列、存储过程、触发器等对象，不同数据库的数据库对象定义不一样，这部分工作主要涉及表重定义、函数或存储过程代码改造等工作。
- 应用代码改造：不同数据库的SQL语法有差异，尽管OpenGauss的语法和Oracle很相似，在SQL语法和函数方面仍然存在一定差异，因此SQL和应用代码的改写不可避免。
- 数据迁移测试：当数据库对象迁移工作完成之后，需进行数据迁移测试，具体为迁移Oracle数据库数据到OpenGauss，同时验证迁移后数据的准确性，例如迁移后数据量是否和Oracle库中的数据量一致？是否存在乱码？中文是否能正常显示？
- 功能测试：前三步工作完成之后需要对新系统进行功能测试，这块工作主要由测试人员进行，开发人员、DBA配合。
- 性能测试：前四步工作完成之后需要对新系统进行性能测试，包含业务代码的性能和数据库性能，这块工作主要由测试人员进行，开发人员、DBA配合，性能测试对系统的最高业务吞吐量进行模拟测试。
- 生产割接：以上步骤完成之后，基本具备生产割接的条件，正式割接前建议至少做两次割接演练，重点记录数据迁移测试时间、停服务时间，以及验证整个迁移步骤是否有问题。
在以上六项改造工作中，DBA都承担着重要的角色。