Routine Maintenance Check Items

Checking MogDB Status

MogDB provides tools to check database and instance status, ensuring that databases and instances are running properly to provide data services.

Check instance status.
```
gs_check -U omm -i CheckClusterState
```
Check parameters.
```
mogdb=# SHOW parameter_name;
```
In the above command, parameter_name needs to be replaced with a specific parameter name.

Modify parameters.

gs_guc reload  -D /mogdb/data/dbnode -c "paraname=value"

Checking Lock Information

The lock mechanism is an important method to ensure data consistency. Information check helps learn database transactions and database running status.

Query lock information in the database.
```
mogdb=# SELECT * FROM pg_locks;
```

Query the status of threads waiting to acquire locks.

mogdb=# SELECT * FROM pg_thread_wait_status WHERE wait_status = 'acquire lock';

Kill a system process.

Search for a system process that is running and run the following command to end the process:
```
ps ux
kill -9 pid
```

Collecting Event Statistics

Long-time running of SQL statements will occupy a lot of system resources. You can check event occurrence time and occupied memory to learn about database running status.

Query the time points about an event.

Run the following command to query the thread start time, transaction start time, SQL start time, and status change time of the event:
```
mogdb=# SELECT backend_start,xact_start,query_start,state_change FROM pg_stat_activity;
```
Query the number of sessions on the current server.
```
mogdb=# SELECT count(*) FROM pg_stat_activity;
```
Collect system-level statistics.

Run the following command to query information about the session that uses the maximum memory:
```
mogdb=# SELECT * FROM pv_session_memory_detail() ORDER BY usedsize desc limit 10;
```

Checking Objects

Tables, indexes, partitions, and constraints are key storage objects of a database. A database administrator needs to routinely maintain key information and these objects.

View table details.
```
mogdb=# \d+ table_name
```
Query table statistics.
```
mogdb=# SELECT * FROM pg_statistic;
```
View index details.
```
mogdb=# \d+ index_name
```
Query partitioned table information.
```
mogdb=# SELECT * FROM pg_partition;
```
Collect statistics.

Run the ANALYZE statement to collect related statistics on the database.

Run the VACUUM statement to reclaim space and update statistics.
Query constraint information.
```
mogdb=# SELECT * FROM pg_constraint;
```

Checking an SQL Report

Run the EXPLAIN statement to view execution plans.

Backing Up Data

Never forget to back up data. During the routine work, the backup execution and backup data validity need to be checked to ensure data security and encryption security.

Export a specified user.

gs_dump dbname -p port -f out.sql -U user_name -W password

Export a schema.

gs_dump dbname -p port -n schema_name -f out.sql

Export a table.

gs_dump dbname -p port -t table_name -f out.sql

Checking Basic Information

Basic information includes versions, components, and patches. Periodic database information checks and records are important for database life cycle management.

Check version information.
```
mogdb=# SELECT version();
```

Check table size and database size.

mogdb=# SELECT pg_table_size('table_name');
mogdb=# SELECT pg_database_size('database_name');

Issue