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
==========================================================================================================
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
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;
Logs
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
VACUUM ANALYZE tablename;
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>';
Locks
SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, l.pid, l.mode, l.granted, a.current_query
FROM pg_locks l, pg_class c, pg_stat_activity a
WHERE l.relation = c.oid
AND l.pid = a.procpid
ORDER BY c.relname;
Resource Queue
SELECT * FROM pg_resqueue_status;
==========================================================================================================