学习目标
学习检查数据库运行状态、锁信息、维护表、重建索引等数据库维护操作技术
学习目标
本章学习了openGauss的基本维护，查询数据库状态，如果查询日志，数据库的锁信息。vauccm维护数据库建表重建索引。
课程作业
1、使用操作系统命令和数据库工具检查实例状态
omm@modb:~$ ps -ef|grep gaussdb
omm 1 0 0 10:08 ? 00:00:09 gaussdb
omm 549 545 0 10:42 pts/0 00:00:00 grep gaussdb
omm@modb:~$ ps -Tp 1
PID SPID TTY TIME CMD
1 1 ? 00:00:00 gaussdb
1 287 ? 00:00:00 jemalloc_bg_thd
1 293 ? 00:00:00 gaussdb
1 294 ? 00:00:00 syslogger
1 295 ? 00:00:00 jemalloc_bg_thd
1 296 ? 00:00:00 alarm
1 297 ? 00:00:00 reaper
1 343 ? 00:00:00 checkpointer
1 298 ? 00:00:00 jemalloc_bg_thd
1 299 ? 00:00:00 jemalloc_bg_thd
1 344 ? 00:00:00 Spbgwriter
1 345 ? 00:00:01 pagewriter
1 347 ? 00:00:00 pagewriter
1 346 ? 00:00:00 pagewriter
1 348 ? 00:00:00 pagewriter
1 349 ? 00:00:00 pagewriter
1 350 ? 00:00:00 WALwriter
1 351 ? 00:00:00 WALwriteraux
1 353 ? 00:00:00 Jobscheduler
1 352 ? 00:00:00 AVClauncher
1 354 ? 00:00:00 asyncundolaunch
1 355 ? 00:00:00 globalstats
1 356 ? 00:00:00 applylauncher
1 357 ? 00:00:00 statscollector
1 358 ? 00:00:01 percentworker
1 360 ? 00:00:00 TrackStmtWorker
1 359 ? 00:00:03 ashworker
1 361 ? 00:00:00 auditor
1 362 ? 00:00:00 2pccleaner
1 363 ? 00:00:00 faultmonitor
1 364 ? 00:00:00 WLMworker
1 365 ? 00:00:00 WLMmonitor
1 366 ? 00:00:00 WLMarbiter
1 367 ? 00:00:00 undorecycler使用gs_ctl检查数据库状态
omm@modb:~$ gs_ctl status -D /var/lib/opengauss/data
[2022-12-14 10:43:25.350][559][][gs_ctl]: gs_ctl status,datadir is /var/lib/opengauss/data
gs_ctl: server is running (PID: 1)
/usr/local/opengauss/bin/gaussdb
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=# show data_directory;
data_directory
-------------------------
/var/lib/opengauss/data
(1 row)
omm=# \q2、检查锁信息
omm=# SELECT * FROM pg_locks;
locktype | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualtransact
ion | pid | sessionid | mode | granted | fastpath | locktag | global_sessionid
------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+----------------
----+-----------------+-----------------+-----------------+---------+----------+-------------------+------------------
relation | 16384 | 12010 | | | | | | | | | 11/631
| 281471187975408 | 281471187975408 | AccessShareLock | t | t | 4000:2eea:0:0:0:0 | 0:0#0
virtualxid | | | | | | 11/631 | | | | | 11/631
| 281471187975408 | 281471187975408 | ExclusiveLock | t | t | b:277:0:0:0:7 | 0:0#0
virtualxid | | | | | | 8/446 | | | | | 8/446
| 281471417089264 | 281471417089264 | ExclusiveLock | t | t | 8:1be:0:0:0:7 | 0:0#0
(3 rows)
omm=# SELECT * FROM pg_thread_wait_status WHERE wait_status = 'acquire lock';
node_name | db_name | thread_name | query_id | tid | sessionid | lwtid | psessionid | tlevel | smpid | wait_status | wait_event | lo
cktag | lockmode | block_sessionid | global_sessionid
-----------+---------+-------------+----------+-----+-----------+-------+------------+--------+-------+-------------+------------+---
------+----------+-----------------+------------------
(0 rows)3、查看数据库版本、日志信息
omm=# SELECT version();
version
-------------------------------------------------------------------------------------------------------------------------------------
------------------
(openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr on aarch64-unknown-linux-gnu, compiled by g++ (G
CC) 7.3.0, 64-bit
(1 row)
omm=# \! ls /var/lib/opengauss/data/pg_log
\qpostgresql-2022-12-14_100906.log postgresql-2022-12-14_100917.log
omm=# 4、检查应用连接数、查看现有最大连接数
omm=# SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
count
-------
21
(1 row)
omm=# SHOW max_connections;
max_connections
-----------------
200
(1 row)5、对表进行VACUUM、VACUUM FULL和ANALYZE操作
omm=# VACUUM;
VACUUM
omm=# VACUUM full;
NOTICE: skipping "pg_type" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_client_global_keys_args" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_authid" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_statistic" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_statistic_ext" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_user_mapping" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_subscription" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_class" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_largeobject" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_attribute" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_proc" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_package" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_partition" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_attrdef" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_constraint" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_inherits" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_index" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_operator" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_opfamily" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_opclass" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_am" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_amproc" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_language" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_largeobject_metadata" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_rewrite" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_aggregate" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_amop" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_trigger" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_description" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_cast" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_namespace" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_conversion" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_enum" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_database" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_depend" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_pltemplate" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_db_role_setting" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_tablespace" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_auth_members" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_shdepend" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_shdescription" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_ts_config" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_ts_config_map" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_ts_dict" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_ts_parser" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_ts_template" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_extension" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_obsscaninfo" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_foreign_data_wrapper" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_foreign_server" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pgxc_class" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pgxc_node" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pgxc_group" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_resource_pool" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_workload_group" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_app_workloadgroup_mapping" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_foreign_table" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_rlspolicy" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_default_acl" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_seclabel" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_shseclabel" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_range" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_collation" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_encrypted_columns" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_column_keys" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_column_keys_args" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_client_global_keys" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_encrypted_proc" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_job" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_asp" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_job_proc" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_object" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_synonym" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_obsscaninfo" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_directory" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_global_chain" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_hashbucket" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_global_config" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "streaming_stream" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "streaming_cont_query" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "streaming_reaper_status" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_matview" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_matview_dependency" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pgxc_slice" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_opt_model" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_model_warehouse" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_recyclebin" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_txn_snapshot" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_job_argument" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_job_attribute" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_uid" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_db_privilege" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_replication_origin" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_publication" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_publication_rel" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_user_status" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_auth_history" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "pg_extension_data_source" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_auditing_policy" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_auditing_policy_access" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_auditing_policy_filters" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_auditing_policy_privileges" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_policy_label" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_masking_policy_actions" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_masking_policy" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE: skipping "gs_masking_policy_filters" --- use xc_maintenance_mode to VACUUM FULL it
VACUUM
omm=# analyse;
ANALYZE6、建表、索引，进行重建索引操作
omm=# create table student(id int primary key, name varchar(50) not null, age int default 19);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "student_pkey" for table "student"
CREATE TABLE
omm=# alter table student add column class bigint;
ALTER TABLE
omm=# create index idex_stu_class on student(class);
CREATE INDEX
omm=# re
reassign refresh reindex release reset revoke
omm=# reindex table student;
REINDEX最后修改时间：2022-12-14 10:50:01
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
