Greenplum Database Cheat Sheet

The gadget spec URL could not be found
Starting and Stopping Greenplum Database
gpstart  Start a Greenplum Database system
gpstart -R
Start a Greenplum Database system in restricted mode 
(only allow superuser connections)
gpstart -m PGOPTIONS='-c gp_session_role=utility' psql
 Start the Greenplum master instance only and connect in utility mode
gpstart -? : Display the online help for the gpstart utility
gpstop : 
Stop a Greenplum Database system in smart mode
gpstop -M fast : 
Stop a Greenplum Database system in fast mode
gpstop -r : Stop all segment instances and then restart the system
gpstop -m : Stop a master instance that was started in maintenance mode
gpstop -u : Reload the postgresql.conf and pg_hba.conf files after making runtime configuration parameter changes but do not shutdown the Greenplum Database array
gpstop -? : Display the online help for the gpstop utility

Connecting to the Greenplum database

Connection options:
-h, --host=HOSTNAME           database server host or socket directory (default: "local socket")
-p, --port=PORT                     database server port (default: "5432")
-U, --username=USERNAME  database user name 
-w, --no-password                  never prompt for password
-W, --password                       force password prompt (should happen automatically)

psql -d gpdb -h gphostname or ipaddress -p 5432 -U gpadmin
psql -d gpdb -h gphostname or ipaddress -p 5432 -U gpadmin -W
psql gpdb
# Using the utility mode
PGOPTIONS = "-c gp_session_role = utility" psql -h -d dbname hostname -p port

General options:
-c, --command=COMMAND                     run only single command (SQL or internal) and exit
-d, --dbname=DBNAME                           database name to connect to (default: "testdb")
-f, --file=FILENAME                                execute commands from file, then exit
-l, --list                                                   list available databases, then exit
-v, --set=, --variable=NAME=VALUE        set psql variable NAME to VALUE
-X, --no-psqlrc                                         do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction                  execute command file as a single transaction
--help                                                      show this help, then exit
 --version                                                output version information, then exit

Input and output options:
-a, --echo-all                                          echo all input from script
-e, --echo-queries                                   echo commands sent to server
-E, --echo-hidden                                   display queries that internal commands generate
-L, --log-file=FILENAME                         send session log to file
-n, --no-readline                                     disable enhanced command line editing (readline)
-o, --output=FILENAME                         send query results to file (or |pipe)
-q, --quiet                                              run quietly (no messages, only query output)
-s, --single-step                                     single-step mode (confirm each query)
 -S, --single-line                                     single-line mode (end of line terminates SQL command)

Output format options:
-A, --no-align                                         unaligned table output mode
-F, --field-separator=STRING                  set field separator (default: "|")
-H, --html                                              HTML table output mode
-P, --pset=VAR[=ARG]                          set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING               set record separator (default: newline)
-t, --tuples-only                                      print rows only
-T, --table-attr=TEXT                             set HTML table tag attributes (e.g., width, border)
-x, --expanded                                      turn on expanded table output

The gadget spec URL could not be found
Exit from PSQL
command line \ q

Display Configuration settings
psql -c 'SHOW ALL;'-d testdb
gpconfig -s max_connections 

Create a database
createdb -h localhost -p 5432 testdb

The gadget spec URL could not be found

View the segment configuration

select * from gp_segment_configuration;

File System
select * from pg_filespace_entry;

Disk, database space
SELECT * FROM gp_toolkit.gp_disk_free ORDER BY dfsegment;
SELECT * FROM gp_toolkit.gp_size_of_database ORDER BY sodddatname;

SELECT * FROM gp_toolkit.__gp_log_master_ext;
SELECT * FROM gp_toolkit.__gp_log_segment_ext;

Table describes the
/ D + <tablename>

Vaccum and Analyze Table

Table data distribution
SELECT gp_segment_id, count (*) FROM <table_name> GROUP BY gp_segment_id;

Table Size
SELECT relname as name, sotdsize/1024/1024 as size_MB, sotdtoastsize as toast, sotdadditionalsize as other
FROM gp_toolkit.gp_size_of_table_disk as sotd, pg_class
WHERE sotd.sotdoid = pg_class.oid ORDER BY relname;

Index Size
SELECT soisize/1024/1024 as size_MB, relname as indexname
FROM pg_class, gp_toolkit.gp_size_of_index
WHERE pg_class.oid = gp_size_of_index.soioid
AND pg_class.relkind = 'i';

The OBJECT operating statistics
SELECT schemaname as schema, objname as table, usename as role, actionname as action, subtype as type, statime as time
FROM pg_stat_operations
WHERE objname = '<name>';

SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction,, l.mode, l.granted, a.current_query
FROM pg_locks l, pg_class c, pg_stat_activity a
WHERE l.relation = c.oid
AND = a.procpid
ORDER BY c.relname;

Resource Queue
SELECT * FROM pg_resqueue_status;


The gadget spec URL could not be found

The gadget spec URL could not be found

The gadget spec URL could not be found

The gadget spec URL could not be found