Basic Operation and Maintenance Management

1. Operation cluster

1.1 Start

gpstart -a  #Start the cluster

1.2 Close

gpstop -a   #Stop the cluster (may hang if sessions are connected)
gpstop -a -M fast  #Fast shutdown the cluster

1.3 Restart

gpstop -ar # Restart, waiting for ongoing SQL to finish (may hang if sessions are connected)
gpstop -ar -M fast # Fast restart the cluster

1.4 Reload the configuration

gpstop -u  # Reload configuration files

1.5 Check cluster status

gpstate     #Check cluster status
gpstate -s  #Check the entire Matrixdb cluster's status
gpstate -e  #Check mirror status
gpstate -m  #Check mirror List 
gpstate -f  #Check Standby Master info

1.6 View and modify configuration parameters

Display configuration values:

gpconfig -s {GUC}

# Example: Check the maximum number of connections allowed
gpconfig -s max_connections

Modify the configuration value:

gpconfig -c {GUC} -v {Value} -m {Value} #-m specifies the master node parameter value, -v specifies the segment node parameter value
gpconfig -c {GUC} -v {Value} #When -m is not specified, the parameter modifications of the master node are consistent with those of the segment node.

# example:
gpconfig -c max_connections -v 1000 -m 300
gpconfig -c shared_buffers -v 2GB

2. Log in to the cluster

2.1 psql login database

psql -d ${db_name} -U ${user_name} -h ${ip_addr} -p ${port}

Modify environment variables and add the default value of psql:

#You can modify the default value by modifying the environment variables
i ~/.bash_profile  # Edit with mxadmin user, add variables:

export PGPORT=5432   # Default port
export PGUSER=mxadmin # Default user
export PGDATABASE=postgres  # Default database

Make environment variables effective immediately:

source ~/.bash_profile

Use SQL again to log in to the database:

#Execute psql to log in to the database
psql
#The default value at this time is
psql -d postgres -U mxadmin -h localhost -p 5432

2.2 psql meta command

\l List all databases. 
\d List tables, views, sequences in the current database 
\d [table_name] Show table structure
\dt Show matching tables.
\di Show indexes.
\dt+ table_name Check table size.
\di+ index_name Check index size.
\ds Show sequences.
\dv Show views.
\dm Show materialized views.
\df Show functions.
\dn List schemas.
\du or \dg List roles/users.
\dp or \z Show table permissions.
\dx Show extension info.
\sf function_name Show function code.
\h Show SQL command explanation.
\? List psql commands. 
\c Show current DB and connection info.
\c [database_name] Connect to another DB. 
\e Open text editor. 
\x Set query result output
\! Execute OS command
\o /home/postgres/test.txt Save results to file (close with \o)
\drds Show RDS - related info
\s Show history
\watch 1 Repeat last command every 1 second

3. Creating and deleting libraries

3.1 Creating a library

Method 1, use the createdb tool to create:

createdb test

Method 2: Create using the CREATE DATABASE SQL statement:

psql postgres
postgres=# create database test;

3.2 Delete the library

Method 1: Use dropdb tool to delete:

dropdb test

Method 2: Create using the DROP DATABASE SQL statement:

psql postgres
postgres=# drop database test;