Top 20 frequently used commands by Greenplum DBA's
1. Creates a new database.
CREATE DATABASE name [ [WITH] [OWNER [=] dbowner]
[TEMPLATE [=] template]
[ENCODING [=] encoding]
[TABLESPACE [=] tablespace]
[CONNECTION LIMIT [=] connlimit ] ]
2. Create users and role
CREATE USER: Defines a new database role with the LOGIN privilege by default.
CREATE USER name [ [WITH] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE rolename [, ...]
| IN GROUP rolename [, ...]
| ROLE rolename [, ...]
| ADMIN rolename [, ...]
| USER rolename [, ...]
| SYSID uid
| RESOURCE QUEUE queue_name
Note:As of Greenplum Database release 2.2, CREATE USER has been replaced by CREATE ROLE, although it is still accepted for backwards compatibility.The only difference between CREATE ROLE and CREATE USER is that LOGIN is assumed by default with CREATE USER, whereas NOLOGIN is assumed by default with CREATE ROLE.
Defines a new database role (user or group).
CREATE ROLE name [[WITH] option [ ... ]]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEEXTTABLE | NOCREATEEXTTABLE
[ ( attribute='value'[, ...] ) ]
where attributes and values are:
type='readable'|'writable'
protocol='gpfdist'|'http'
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE rolename [, ...]
| ROLE rolename [, ...]
| ADMIN rolename [, ...]
| RESOURCE QUEUE queue_name
| [ DENY deny_point ]
| [ DENY BETWEEN deny_point AND deny_point]
3. CREATE SCHEMA
CREATE SCHEMA schema_name [AUTHORIZATION username]
[schema_element [ ... ]]
CREATE SCHEMA AUTHORIZATION rolename [schema_element [ ... ]]
Example:
Create a schema:
CREATE SCHEMA myschema;
Create a schema for role joe (the schema will also be named joe):
CREATE SCHEMA AUTHORIZATION joe;
4. ALTER DATABASE
ALTER DATABASE name [ WITH CONNECTION LIMIT connlimit ]
ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET parameter
ALTER DATABASE name RENAME TO newname
ALTER DATABASE name OWNER TO new_owner
5. ALTER USER/ROLE
ALTER ROLE name RENAME TO newname
ALTER ROLE name SET config_parameter {TO | =} {value | DEFAULT}
ALTER ROLE name RESET config_parameter
ALTER ROLE name RESOURCE QUEUE {queue_name | NONE}
ALTER ROLE name [ [WITH] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEEXTTABLE | NOCREATEEXTTABLE
[ ( attribute='value'[, ...] ) ]
where attributes and values are:
type='readable'|'writable'
protocol='gpfdist'|'http'
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| CONNECTION LIMIT connlimit
| [ENCRYPTED | UNENCRYPTED] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| [ DENY deny_point ]
| [ DENY BETWEEN deny_point AND deny_point]
| [ DROP DENY FOR deny_point ]
6. ALTER SCHEMA
ALTER SCHEMA
Changes the definition of a schema.
ALTER SCHEMA name RENAME TO newname
ALTER SCHEMA name OWNER TO newowner
7. ALTER TABLE
ALTER TABLE
Changes the definition of a table.
Synopsis
ALTER TABLE [ONLY] name RENAME [COLUMN] column TO new_column
ALTER TABLE name RENAME TO new_name
ALTER TABLE name SET SCHEMA new_schema
ALTER TABLE [ONLY] name SET
DISTRIBUTED BY (column, [ ... ] )
| DISTRIBUTED RANDOMLY
| WITH (REORGANIZE=true|false)
ALTER TABLE [ONLY] name action [, ... ]
ALTER TABLE name
[ ALTER PARTITION { partition_name | FOR (RANK(number))
| FOR (value) } partition_action [...] ]
partition_action
where action is one of:
ADD [COLUMN] column_name type
[ ENCODING ( storage_directive [,...] ) ]
[column_constraint [ ... ]]
DROP [COLUMN] column [RESTRICT | CASCADE]
ALTER [COLUMN] column TYPE type [USING expression]
ALTER [COLUMN] column SET DEFAULT expression
ALTER [COLUMN] column DROP DEFAULT
ALTER [COLUMN] column { SET | DROP } NOT NULL
ALTER [COLUMN] column SET STATISTICS integer
ADD table_constraint
DROP CONSTRAINT constraint_name [RESTRICT | CASCADE]
DISABLE TRIGGER [trigger_name | ALL | USER]
ENABLE TRIGGER [trigger_name | ALL | USER]
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
SET (FILLFACTOR = value)
RESET (FILLFACTOR)
INHERIT parent_table
NO INHERIT parent_table
OWNER TO new_owner
SET TABLESPACE new_tablespace
where partition_action is one of:
ALTER DEFAULT PARTITION
DROP DEFAULT PARTITION [IF EXISTS]
DROP PARTITION [IF EXISTS] { partition_name |
FOR (RANK(number)) | FOR (value) } [CASCADE]
TRUNCATE DEFAULT PARTITION
TRUNCATE PARTITION { partition_name | FOR (RANK(number)) |
FOR (value) }
RENAME DEFAULT PARTITION TO new_partition_name
RENAME PARTITION { partition_name | FOR (RANK(number)) |
FOR (value) } TO new_partition_name
ADD DEFAULT PARTITION name [ ( subpartition_spec ) ]
ADD PARTITION [name] partition_element
[ ( subpartition_spec ) ]
EXCHANGE PARTITION { partition_name | FOR (RANK(number)) |
FOR (value) } WITH TABLE table_name
[ WITH | WITHOUT VALIDATION ]
EXCHANGE DEFAULT PARTITION WITH TABLE table_name
[ WITH | WITHOUT VALIDATION ]
SET SUBPARTITION TEMPLATE (subpartition_spec)
SPLIT DEFAULT PARTITION
{ AT (list_value)
| START([datatype] range_value) [INCLUSIVE | EXCLUSIVE]
END([datatype] range_value) [INCLUSIVE | EXCLUSIVE] }
[ INTO ( PARTITION new_partition_name,
PARTITION default_partition_name ) ]
SPLIT PARTITION { partition_name | FOR (RANK(number)) |
FOR (value) } AT (value)
[ INTO (PARTITION partition_name, PARTITION
partition_name)]
where partition_element is:
VALUES (list_value [,...] )
| START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
[ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
| END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[ TABLESPACE tablespace ]
where subpartition_spec is:
subpartition_element [, ...]
and subpartition_element is:
DEFAULT SUBPARTITION subpartition_name
| [SUBPARTITION subpartition_name] VALUES (list_value [,...] )
| [SUBPARTITION subpartition_name]
START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
[ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
[ EVERY ( [number | datatype] 'interval_value') ]
| [SUBPARTITION subpartition_name]
END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
[ EVERY ( [number | datatype] 'interval_value') ]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[ TABLESPACE tablespace ]
where storage_parameter is:
APPENDONLY={TRUE|FALSE}
BLOCKSIZE={8192-2097152}
ORIENTATION={COLUMN|ROW}
COMPRESSTYPE={ZLIB|QUICKLZ|RLE_TYPE|NONE}
COMPRESSLEVEL={0-9}
FILLFACTOR={10-100}
OIDS[=TRUE|FALSE]
where storage_directive is:
COMPRESSTYPE={ZLIB | QUICKLZ | RLE_TYPE | NONE}
| COMPRESSLEVEL={0-9}
| BLOCKSIZE={8192-2097152}
Where column_reference_storage_directive is:
COLUMN column_name ENCODING (storage_directive [, ... ] ), ...
|
DEFAULT COLUMN ENCODING (storage_directive [, ... ] )
7. COPY
Copies data between a file and a table.
COPY table [(column [, ...])] FROM {'file' | STDIN}
[ [WITH]
[OIDS]
[HEADER]
[DELIMITER [ AS ] 'delimiter']
[NULL [ AS ] 'null string']
[ESCAPE [ AS ] 'escape' | 'OFF']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[CSV [QUOTE [ AS ] 'quote']
[FORCE NOT NULL column [, ...]]
[FILL MISSING FIELDS]
[ [LOG ERRORS INTO error_table] [KEEP]
SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]
COPY {table [(column [, ...])] | (query)} TO {'file' | STDOUT}
[ [WITH]
[OIDS]
[HEADER]
[DELIMITER [ AS ] 'delimiter']
[NULL [ AS ] 'null string']
[ESCAPE [ AS ] 'escape' | 'OFF']
[CSV [QUOTE [ AS ] 'quote']
[FORCE QUOTE column [, ...]] ]
8. EXPLAIN
Shows the query plan of a statement.
EXPLAIN [ANALYZE] [VERBOSE] statement
9. ANALYZE
Collects statistics about a database.
ANALYZE [VERBOSE] [table [ (column [, ...] ) ]]
10: VACUUM
Garbage-collects and optionally analyzes a database.
VACUUM [FULL] [FREEZE] [VERBOSE] [table]
VACUUM [FULL] [FREEZE] [VERBOSE] ANALYZE
[table [(column [, ...] )]]
Examples
Vacuum all tables in the current database:
VACUUM;
Vacuum a specific table only:
VACUUM mytable;
Vacuum all tables in the current database and collect statistics for the query planner:
VACUUM ANALYZE;
11. TABLE DISTRIBUTION POLICY: Select * from gp_distribution_policy;
gp_distribution_policy
The gp_distribution_policy table contains information about Greenplum Database tables and their policy for distributing table data across the segments. This table is populated only on the master. This table is not globally shared, meaning each database has its own copy of this table.
12. gp_id and gp_interface
The gp_id system catalog table identifies the Greenplum Database system name and number of segments for the system. It also has local values for the particular database instance (segment or master) on which the table resides. This table is defined in the pg_global tablespace, meaning it is globally shared across all databases in the system.
The gp_interfaces table contains information about network interfaces on segment hosts. This information, joined with data from gp_db_interfaces, is used by the system to optimize the usage of available network interfaces for various purposes, including fault detection.
13. pg_authid and pg_auth_members
The pg_auth_members system catalog table shows the membership relations between roles. Any non-circular set of relationships is allowed. Because roles are system-wide, pg_auth_members is
shared across all databases of a Greenplum Database system.
The pg_authid table contains information about database authorization identifiers (roles). A role subsumes the concepts of users and groups. A user is a role with the rolcanlogin flag set. Any role
(with or without rolcanlogin) may have other roles as members. See pg_auth_members.
Since this catalog contains passwords, it must not be publicly readable. pg_roles is a publicly readable view on pg_authid that blanks out the password field.Because user identities are system-wide, pg_authid is shared across all databases in a Greenplum Database system: there is only one copy of pg_authid per system, not one per database.
14.pg_autovacuum and pg_class
The pg_autovacuum system catalog table stores optional per-relation configuration parameters for the autovacuum daemon. If there is an entry here for a particular relation, the given parameters will be
used for autovacuuming that table. If no entry is present, the system-wide defaults will be used.
The autovacuum daemon will initiate a VACUUM operation on a particular table when the number of updated or deleted tuples exceeds vac_base_thresh plus vac_scale_factor times the number of live
tuples currently estimated to be in the relation. Similarly, it will initiate an ANALYZE operation when the number of inserted, updated or deleted tuples exceeds anl_base_thresh plus anl_scale_factor times
the number of live tuples currently estimated to be in the relation.
Also, the autovacuum daemon will perform a VACUUM operation to prevent transaction ID wraparound if the table’s pg_class.relfrozenxid field attains an age of more than freeze_max_age transactions,
whether the table has been changed or not. The system will launch autovacuum to perform such VACUUMs even if autovacuum is otherwise disabled.
Any of the numerical fields can contain -1 to indicate that the system-wide default should be used for this particular value. Observe that the vac_cost_delay variable inherits its default value from the
autovacuum_vacuum_cost_delay configuration parameter, or from vacuum_cost_delay if the former is set to a negative value. The same applies to vac_cost_limit. Also, autovacuum will ignore
attempts to set a per-table freeze_max_age larger than the system-wide setting (it can only be set smaller), and the freeze_min_age value will be limited to half the system-wide autovacuum_freeze_max_age setting.
pg_class : The system catalog table pg_class catalogs tables and most everything else that has columns or is otherwise similar to a table (also known as relations). This includes indexes (see also pg_index),
sequences, views, composite types, and TOAST tables. Not all columns are meaningful for all relation types.
pg_constraint: The pg_constraint system catalog table stores check, primary key, unique, and foreign key constraints on tables. Column constraints are not treated specially. Every column constraint is equivalent to some table constraint. Not-null constraints are represented in the pg_attribute catalog. Check constraints on domains are stored here, too.
pg_database: The pg_database system catalog table stores information about the available databases. Databases are created with the CREATE DATABASE SQL command. Unlike most system catalogs, pg_database is shared across all databases in the system. There is only one copy of pg_database per system, not one per database.
pg_exttable: The pg_exttable system catalog table is used to track external tables and web tables created by the CREATE EXTERNAL TABLE command.
pg_locks: The view pg_locks provides access to information about the locks held by open transactions within Greenplum Database.
pg_locks contains one row per active lockable object, requested lock mode, and relevant transaction. Thus, the same lockable object may appear many times, if multiple transactions are holding or waiting
for locks on it. However, an object that currently has no locks on it will not appear at all. There are several distinct types of lockable objects: whole relations (such as tables), individual pages
of relations, individual tuples of relations, transaction IDs, and general database objects. Also, the right to extend a relation is represented as a separate lockable object.
pg_partition: The pg_partition system catalog table is used to track partitioned tables and their inheritance level relationships. Each row of pg_partition represents either the level of a partitioned table in the partition
hierarchy, or a subpartition template description. The value of the attribute paristemplate determines what a particular row represents. pg_partition_columns:The pg_partition_columns system view is used to show the partition key columns of a partitioned table.
pg_roles: The view pg_roles provides access to information about database roles. This is simply a publicly readable view of pg_authid that blanks out the password field. This view explicitly exposes the OID
column of the underlying table, since that is needed to do joins to other catalogs.
pg_stat_activity: The view pg_stat_activity shows one row per server process and details about it associated user session and query. The columns that report data on the current query are available unless the parameter stats_command_string has been turned off. Furthermore, these columns are only visible if the user examining the view is a superuser or the same as the user owning the process being reported on.
pg_stat_last_operation: The pg_stat_last_operation table contains metadata tracking information about database objects (tables, views, etc.).
pg_stat_operations: The view pg_stat_operations shows details about the last operation performed on a database object (such as a table, index, view or database) or a global object (such as a role).
Important SQL's for Greenplum DBA's - May 02, 2013 10:56:8 PM