Basic Operations Management

1. Cluster Operations

1.1 Start the Cluster

gpstart -a  # Start the cluster

1.2 Stop the Cluster

gpstop -a   # Stop the cluster (if there are active sessions, shutdown will hang)
gpstop -a -M fast  # Stop the cluster quickly

1.3 Restart the Cluster

gpstop -ar # Restart the cluster, wait for currently running SQL to finish (will hang if sessions exist)
gpstop -ar -M fast # Restart the cluster quickly

1.4 Reload Configuration

gpstop -u  # Reload configuration files

1.5 Check Cluster Status

gpstate     # Display cluster status
gpstate -s  # Display the status of the entire MatrixDB cluster
gpstate -e  # Display mirror segment status
gpstate -m  # Display mirror list
gpstate -f  # Display standby master information

1.6 View and Modify Configuration Parameters

Display a configuration value:

gpconfig -s {GUC}

# Example: Check maximum connection limit
gpconfig -s max_connections

Modify a configuration value:

gpconfig -c {GUC} -v {Value} -m {Value}  # -m sets value on master, -v sets value on segments
gpconfig -c {GUC} -v {Value}             # If -m is omitted, master uses same value as segments

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

2. Connecting to the Cluster

2.1 Connect Using psql

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

Set environment variables to define default values:

# Edit the profile to set defaults
vi ~/.bash_profile  # Use mxadmin user; add the following variables to the file

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

Apply environment changes immediately:

source ~/.bash_profile

Connect again using psql:

# Now running psql connects directly
psql
# This is equivalent to:
psql -d postgres -U mxadmin -h localhost -p 5432

2.2 psql Meta-Commands

\l                    List all databases.
\d                    List all tables, views, and sequences in the current database.
\d [table_name]       Describe the structure of a specific table.
\dt                   Show only matching tables.
\di                   Show only indexes.
\dt+ table_name       Show table size.
\di+ index_name       Show index size.
\ds                   Show only sequences.
\dv                   Show only views.
\dm                   Show materialized views.
\df                   Show only functions.
\dn                   List all schemas.
\du or \dg            List all roles or users.
\dp table_name        \z table_name  Show table access privileges.
\dx                   Show installed extensions.
\sf function_name     Show function definition.
\h                    Show help for SQL commands, e.g., \h select.
\?                    Show list of psql commands.
\c                    Show current database and connection info.
\c [database_name]    Connect to another database.
\e                   Open text editor for current query.
\x                   Toggle expanded output mode for query results.
\!                   Execute operating system command.
\o /home/postgres/test.txt  Redirect output to a file; use \o alone to stop.
\drds                List database or user-defined settings.
\s                   Show command history.
\watch 1             Re-execute previous command every 1 second.

3. Creating and Dropping Databases

3.1 Create a Database

Method 1: Use the createdb utility:

createdb test

Method 2: Use SQL command:

psql postgres
postgres=# CREATE DATABASE test;

3.2 Drop a Database

Method 1: Use the dropdb utility:

dropdb test