python连接高斯数据库_GaussDB 1.0.1升级到1.0.2及1.0.2相关新功能说明

原标题：GaussDB 1.0.1升级到1.0.2及1.0.2相关新功能说明

导读：本文记录 GaussDB 1.0.1升级到1.0.2的全过程，也介绍GaussDB 1.0.2相关新功能说明。

SQL> select* from v$version;

VERSION

----------------------------------------------------------------

GaussDB_100_1 .0.1.SPC2.B003 Release 3ae9d6c

ZENGINE

3ae9d6c

3rows fetched.

[roger@mysqldb GaussDB_T_1 .0.2]$ python upgrade.py --help

upgrade.py is a utility to upgrade a Zengine server.

Usage:

python upgrade.py --help

python upgrade.py -?

python upgrade.py -t upgrade- type--package=path_to_package_file

--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]

[ --GSDB_DATA=path_to_data_dir] [-f cmd_config_file]

python upgrade.py -t pretest --package=path_to_package_file

--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]

[ --GSDB_DATA=path_to_data_dir] [-f cmd_config_file]

python upgrade.py -t precheck --package=path_to_package_file

--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]

[ --GSDB_DATA=path_to_data_dir] [-f cmd_config_file]

python upgrade.py -t prepare --package=path_to_package_file

--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]

[ --GSDB_DATA=path_to_data_dir] [-f cmd_config_file]

python upgrade.py -t replace --package=path_to_package_file

--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]

[ --GSDB_DATA=path_to_data_dir] [-f cmd_config_file]

python upgrade.py -t start --package=path_to_package_file

--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]

[ --GSDB_DATA=path_to_data_dir] [-f cmd_config_file]

python upgrade.py -t upgrade --package=path_to_package_file

--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]

[ --GSDB_DATA=path_to_data_dir] [-f cmd_config_file]

python upgrade.py -t sync --package=path_to_package_file

--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]

[ --GSDB_DATA=path_to_data_dir] [-f cmd_config_file]

python upgrade.py -t restart --package=path_to_package_file

--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]

[ --GSDB_DATA=path_to_data_dir] [-f cmd_config_file]

python upgrade.py -t upgrade-view --package=path_to_package_file

--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]

[ --GSDB_DATA=path_to_data_dir] [-f cmd_config_file]

python upgrade.py -t checkpoint --package=path_to_package_file

--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]

[ --GSDB_DATA=path_to_data_dir] [-f cmd_config_file]

python upgrade.py -t dbcheck --package=path_to_package_file

--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]

[ --GSDB_DATA=path_to_data_dir] [-f cmd_config_file]

python upgrade.py -t flush--package=path_to_package_file

--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]

[ --GSDB_DATA=path_to_data_dir] [-f cmd_config_file]

python upgrade.py -t rollback-check --backupdir=path_to_backup

[ --GSDB_HOME=path_to_gsdb_home] [--GSDB_DATA=path_to_data_dir]

[-f cmd_config_file]

python upgrade.py -t rollback --backupdir=path_to_backup

[ --GSDB_HOME=path_to_gsdb_home] [--GSDB_DATA=path_to_data_dir]

[-f cmd_config_file]

python upgrade.py -t rollback-clean --backupdir=path_to_backup

[ --GSDB_HOME=path_to_gsdb_home] [--GSDB_DATA=path_to_data_dir]

[-f cmd_config_file]

python upgrade.py -s pre-check --config-file=CONFIG_FILE

[ --upgrade-mode=ha|single] [--packtype=run|package]

[-f cmd_config_file]

python upgrade.py -s run --config-file=CONFIG_FILE

[ --auto-rollback=true|false]

[ --upgrade-mode=ha|single] [--packtype=run|package]

[-f cmd_config_file]

python upgrade.py -s cleanup --config-file=CONFIG_FILE

[ --upgrade-mode=ha|single]

[ --packtype=run|package] [-f cmd_config_file]

python upgrade.py -s rollback-check --config-file=CONFIG_FILE

[ --upgrade-mode=ha|single]

[ --packtype=run|package] [-f cmd_config_file]

python upgrade.py -s rollback --config-file=CONFIG_FILE

[ --upgrade-mode=ha|single]

[ --packtype=run|package] [-f cmd_config_file]

Common options:

--help show this help, then exit.

-? show this help, thenexit.

-P inputpassword.

-t inputthe functionthatneedtobeexecuted.

-sinputthestepforupgrade.

--package input the name of package containing the path.

--backupdir input the name of backup

foldercontainingthepath.

--GSDB_HOME input the name of app floder containing the path.

--GSDB_DATA input the name of data floder containing the path.

--config-file input the name of node configure file,

theformatis:

IP=pkg,app_path,backup_path,data1,data2,..

--auto-rollback if auto-rollback is false,

willnotrollbackwhenrunstepfailed

--upgrade-mode if upgrade-mode is ha,

willupgradeallthenodesintheconfigurefile

--packtype input upgrade package type,

valuescopeis[run, package]

-finputtheconfigfile

thatprovide'parameter=value'.

-P, --package, --backupdir, --GSDB_HOME,

--GSDB_DATA can be configed by a file.

intheconfigfile, interactive=Trueis

equalwithspecify'-P' parameter.

forexample, theconfigfilecontent:

GSDB_HOME=path_to_gsdb_home

GSDB_HOME=path_to_gsdb_home

backupdir=path_to_package_file

interactive=TRUE

---config_file.ini

[roger@mysqldbgauss_upgrade]$ catconfig_file.ini

127.0.0.1=/opt/gauss/gauss_upgrade/GaussDB_T_1.0.2-DATABASE-REDHAT-64bit.tar.gz,/opt/gauss/gauss100,/tmp/gaussdb_backup,/opt/gauss/gaussdata

[roger@mysqldbgauss_upgrade]$

执行升级检查

[roger@mysqldb GaussDB_T_1.0.2-DATABASE-REDHAT-64bit]$ python upgrade.py -s pre- check--config-file=/opt/gauss/gauss_upgrade/config_file.ini --upgrade-mode=single

Begintoprecheck forsingle upgrade.

Oldversion: 1.0.1.SPC2.B003 Newversion: 1.0.2.B319.

Precheck forsingle upgradefinished.

Upgrade[pre- check] step successfully.

[roger@mysqldb GaussDB_T_1 .0.2- DATABASE-REDHAT -64bit]$

开始升级

[roger@mysqldb GaussDB_T_1 .0.2-DATABASE-REDHAT -64bit]$ python upgrade.py -s run --config-file=/opt/gauss/gauss_upgrade/config_file.ini --upgrade-mode= single

Old version: 1.0.1.SPC2.B003 Newversion: 1.0.2.B319.

Precheck stepforsingleupgrade.

Prepare stepforsingleupgrade.

Replace stepforsingleupgrade.

Begin distrubute keytoother instances.

output:

attr:MOUNT

single=============output:

connected.

SQL>

VALUE

----------------------------------------------------------------

(/opt/gauss/gaussdata/protect/kmc_a.ksf, /opt/gauss/gaussdata/protect/kmc_b.ksf)

1rows fetched.

single===========status: 0

Start stepforsingleupgrade.

Upgrade stepforsingleupgrade.

Sync stepforsingleupgrade.

Dbcheck stepforsingleupgrade.

Flush stepforsingleupgrade.

Run forsingleupgrade finished.

Upgrade [run] stepsuccessfully.

手工启动数据库

[roger@mysqldb bin]$ python zctl.py -t start

Successfully started instance.

[roger@mysqldb bin]$

check是否升级成功

[ roger@mysqldb ~]$ zsql / assysdba -q

connected.

SQL> select* fromv$version;

VERSION

----------------------------------------------------------------

GaussDB_T_1 .0.2.B319 Release de68b82

ZENGINE

2rows fetched.

SQL>

执行完毕后可以删除相关软件信息

[roger@mysqldb GaussDB_T_1. 0. 2-DATABASE-REDHAT- 64bit]$ python upgrade.py -s cleanup --config-file= /opt/gauss/gauss_upgrade/config_file.ini --upgrade-mode=single

clean backup andtmp files.

Upgrade [cleanup] step successfully.

该版本中新增的关于备份恢复方面的功能：

在1.0.2版本中backup命令新增了基于表空间的备份:

[roger@mysqldb GaussDB_T_1.0.2-DATABASE-REDHAT-64bit]$ python upgrade.py -s cleanup --config-file=/opt/gauss/gauss_upgrade/config_file.ini --upgrade-mode=single

clean backupandtmp files.

Upgrade[ cleanup] step successfully.SQL> backupdatabasecopy oftablespaceusersformat'/tmp/gaussdb_backup/backup_0302';

Succeed.

[roger@mysqldb gauss]$ ls -ltr /tmp/gaussdb_backup/backup_0302

total 215080

-rw ------- 1 roger roger 10485760 Mar 2 21:05 ctrl_0_0.bak

-rw ------- 1 roger roger 75055104 Mar 2 21:05 data_USERS_4_1.bak

-rw ------- 1 roger roger 134209536 Mar 2 21:05 data_USERS_4_0.bak

-rw ------- 1 roger roger 484352 Mar 2 21:05 arch_32_0.bak

-rw ------- 1 roger roger 3512 Mar 2 21:05 backupset

新增针对archivelog的restore

新增基于数据文件的恢复（之前只能恢复全库）

[roger@mysqldb ~]$ zengine mount -D /opt/gauss/gaussdata &

[ 1] 10005

[roger@mysqldb ~]$ starting instance(mount)

instance started

[roger@mysqldb ~]$

[roger@mysqldb ~]$

[roger@mysqldb ~]$ zsql / as sysdba -q

connected.

SQL> restore filerecover fileid 4from '/tmp/gaussdb_backup/backup_0302';

Succeed.

SQL> alter database open;

Succeed.

SQL> select id,file_name,status,HIGH_WATER_MARK from v$datafile;

ID FILE_NAME STATUS HIGH_WATER_MARK

------------ ---------------------------------------- -------------------- ---------------

0/opt/gauss/gaussdata/system ONLINE 2778

1/opt/gauss/gaussdata/temp1_01 ONLINE 2

2/opt/gauss/gaussdata/temp1_02 ONLINE 1

3/opt/gauss/gaussdata/undo ONLINE 66490

4/opt/gauss/gaussdata/user1 ONLINE 25546

5/opt/gauss/gaussdata/user2 ONLINE 1

6/opt/gauss/gaussdata/user3 ONLINE 1

7/opt/gauss/gaussdata/user4 ONLINE 1

8/opt/gauss/gaussdata/user5 ONLINE 1

9/opt/gauss/gaussdata/temp2_01 ONLINE 2

10/opt/gauss/gaussdata/temp2_02 ONLINE 1

11/opt/gauss/gaussdata/temp2_undo ONLINE 2

12/opt/gauss/gaussdata/sysaux ONLINE 13798

安全方面增强

新增加了个内置高级包：

dbms_redact 用于脱敏

dbms_rls 用户安全策略控制

逻辑备份增强

exp新增对于分区的支持：

SQL> exp -h;

The syntax oflogic export is:

Format: EXP KEYWORD=value orKEYWORD=value1,value2,...,valueN;

Example: EXP TABLES=EMP,DEPT,MGR;

orEXP USERS=USER_A,USER_B;

orEXP DIST_RULES=RULE_1,RULE_2;

Keyword Deion ( Default)

---------------------------------------------------------------------------------------------------------------------------

USERS List ofschema names. Specify a percent sign (%) toexport all users.

TABLES List oftable names. Specify a percent sign (%) toexport all tables.

DIST_RULES List ofdistribute rule names. Specify a percent sign (%) toexport all distribution rules. Supported only forsharding.

TABLESPACE_FILTER List oftablespace names, the data orobjects inthese tablespaces will be exported. Case-sensitive words enclosed by'`' or '"'.

FILE Output file (EXPDAT.DMP)

FILETYPE Output file type: (TXT), BIN

LOG Log file ofscreen output

COMPRESS Compress output file ( 0), only forFILETYPE=BIN, values is0~ 9, litter forfaster compress speed, 0isnotcompressed.

CONTENT Specifies data tounload wherethe valid keyword, values are: (ALL), DATA_ONLY, andMETADATA_ONLY.

QUERY Predicate clause used toexport a subset ofa table, eg. "where rownum <= 10"

SKIP_COMMENTS Donotadd comments todump file. (N)

FORCE Continueeven ifan SQL erroroccurs during a table dump. (N)

SKIP_ADD_DROP_TABLE Donotadd a DROP TABLE statement before eachCREATE TABLE statement. (N)

SKIP_TRIGGERS Donotdump triggers. (N)

QUOTE_NAMES Quote identifiers. (Y)

TABLESPACE Defaulttransport all tablespaces except forsystem reserved. (N)

COMMIT_BATCH Batch commit rows, commit once ifset0.( 1000)

INSERT_BATCH Batch insert rows. ( 1)

FEEDBACK Feedback row count, feedback once ifset0( 10000)

PARALLEL Table data export parallelism settings, range 2~ 16, The defaultvalue is0

CONSISTENT Cross - table consistency(N)

CREATE_USER Export user definition(N),Used inconjunction withUSERS.

ROLE Export user roles expect system preset roles (N),Used inconjunction withUSERS.

GRANT Grant role andpemission toUSER (N),Used inconjunction withUSERS andROLE.

WITH_CR_MODE Export tables andindexes withCR_MODE options (N)

ENCRYPT Export files will be encrypted.

REMAP_TABLES Table 's name will remapped to another tablename.

PARTITIONS Export tables 's data within the input partition.

新增加的函数：

1) current_local_Scn

SQL> SELECTCURRENT_LOCAL_SCN FROMSYS_DUMMY;

CURRENT_LOCAL_SCN

--------------------

6755116323168257

1rows fetched.

2)DBA_FBDR_2PC(从undo表空间中查询已完成的两阶段事务信息)

SQL> select* FROM TABLE(DBA_FBDR_2PC(6755116323168257,1));

GLOBAL_TRAN_ID LOCAL_TRAN_ID TLOCK_LOBS TLOCK_LOBS_EXT FORMAT_ID BRANCH_ID OWNER PREPARE_SCN COMMIT_SCN

---------------------------------------------------------------- -------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------- ---------------------------------------------------------------- -------------------- -------------------- --------------------

0rows fetched.

3）DBA_PAGE_CORRUPTION

这个函数功能非常强大和实用。

SQL> select * from table(dba_page_corruption( 'DATABASE'));

FILE_ID FILE_NAME INFO_TYPE EXAMINED_NUM SUCCEED_NUM CORRUPT_NUM PAGE_ID PAGE_TYPE MARKED_CHECKSUM CALC_CHECKSUM

------------ ---------------------------------------- ------------- ------------ ------------ ------------ ------------ ------------------ --------------- -------------

0/opt/gauss/gaussdata/system FILESUMMARY 277827780

3/opt/gauss/gaussdata/undo FILESUMMARY 66490664900

4/opt/gauss/gaussdata/user1 FILESUMMARY 25546255460

5/opt/gauss/gaussdata/user2 FILESUMMARY 110

6/opt/gauss/gaussdata/user3 FILESUMMARY 110

7/opt/gauss/gaussdata/user4 FILESUMMARY 110

8/opt/gauss/gaussdata/user5 FILESUMMARY 110

9/opt/gauss/gaussdata/temp2_01 FILESUMMARY 220

10/opt/gauss/gaussdata/temp2_02 FILESUMMARY 110

11/opt/gauss/gaussdata/temp2_undo FILESUMMARY 220

12/opt/gauss/gaussdata/sysaux FILESUMMARY 13798137980

11rows fetched.

SQL> select * from table(dba_page_corruption( 'TABLESPACE', 3));

FILE_ID FILE_NAME INFO_TYPE EXAMINED_NUM SUCCEED_NUM CORRUPT_NUM PAGE_ID PAGE_TYPE MARKED_CHECKSUM CALC_CHECKSUM

------------ ---------------------------------------- ------------- ------------ ------------ ------------ ------------ ------------------ --------------- -------------

4/opt/gauss/gaussdata/user1 FILESUMMARY 25546255460

5/opt/gauss/gaussdata/user2 FILESUMMARY 110

6/opt/gauss/gaussdata/user3 FILESUMMARY 110

7/opt/gauss/gaussdata/user4 FILESUMMARY 110

8/opt/gauss/gaussdata/user5 FILESUMMARY 110

5rows fetched.

SQL> select * from table(dba_page_corruption( 'DATAFILE', 3));

FILE_ID FILE_NAME INFO_TYPE EXAMINED_NUM SUCCEED_NUM CORRUPT_NUM PAGE_ID PAGE_TYPE MARKED_CHECKSUM CALC_CHECKSUM

------------ ---------------------------------------- ------------- ------------ ------------ ------------ ------------ ------------------ --------------- -------------

3/opt/gauss/gaussdata/undo FILESUMMARY 66490664900

1rows fetched.

SQL> select * from table(dba_page_corruption( 'PAGE', 4, 10));

FILE_ID FILE_NAME INFO_TYPE EXAMINED_NUM SUCCEED_NUM CORRUPT_NUM PAGE_ID PAGE_TYPE MARKED_CHECKSUM CALC_CHECKSUM

------------ ---------------------------------------- ------------- ------------ ------------ ------------ ------------ ------------------ --------------- -------------

4/opt/gauss/gaussdata/user1 PAGE 11010btree_segment 3601936019

1rows fetched.

LSCN2GSCN(将本地SCN转换为GTS SCN)

SQL> selectcurrent_local_Scn() fromsys_dummy;

CURRENT_LOCAL_SCN

--------------------

6758768140668929

1rows fetched.

SQL> selectLSCN2GSCN(6758768140668929) fromsys_dummy;

LSCN2GSCN( 6758768140668929)

---------------------------

158298313993801729

1rows fetched.

PENDING_TRANS_SESSION(查询正在执行的两阶段事务信息)

rank(聚合、分析函数)

SQL> selectRANK( 2) WITHIN GROUP( ORDERBYa) as"rank"FROMroger.test;

rank

------------

2

1rows fetched.

7.TO_BIGINT(将数据转换成BIGINT类型)

SQL> selectto_bigint(12341) fromsys_dummy;

TO_BIGINT( 12341)

--------------------

12341

1rows fetched.

8.TO_INT(将数据转换成INT类型)

SQL> selectto_int(99999) fromsys_dummy;

TO_INT( 99999)

-------------

99999

1rows fetched.

9.TRY_GET_SHARED_LOCK(为一个会话尝试获取一把锁名为name_expr的共享咨询锁)

SQL 操作 （支持交集查询)

SQL> conn roger/Roger007@ 127.0.0.1: 1611

connected.

SQL> create table test_2 asselect* fromtest limit 5;

Succeed.

SQL> selecta fromtest intersect selecta fromtest_2;

A

----------------------------------------

26.531219482421875

605.14545440673828125

645.55263519287109375

710.174560546875

757.1773529052734375

墨天轮原文链接：https://www.modb.pro/db/22102（复制到浏览器中打开或点击左下角的“阅读原文”）

144页！分享珍藏已久的数据库技术年刊

更有GaussDB专属课程邀你参与 ↓

点击下图查看更多 ↓

云和恩墨大讲堂 |一个分享交流的地方

万人交流社群

请备注：云和恩墨大讲堂

点个“在看”

你的喜欢会被看到👇返回搜狐，查看更多

责任编辑：