Greenplum Database Cheat Sheet

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


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

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;