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 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 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 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_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). |
Top 20 frequently used commands by Greenplum DBA's
Important SQL's for Greenplum DBA's
select count(*) from pg_tables where schemaname='sachi'; select rolname from pg_roles where rolcanlogin is TRUE; -- List users select rolname from pg_roles where rolcanlogin is FALSE; --- List Roles select * from pg_views ; select * from information_schema.table_privileges; select * from pg_user; select * from pg_views where viewname like '%priv%'; select * from pg_views where viewname like '%role%'; select * from pg_views where viewname like '%gran%'; select * from pg_views where viewname like '%part%'; select * from pg_views where viewname like '%schema%'; select * from pg_views where viewname like '%stat%'; select * from information_schema.role_table_grants; select * from information_schema.role_routine_grants; select * from information_schema.applicable_roles; select * from information_schema.enabled_roles; select * from gp_toolkit.gp_roles_assigned; |
1-1 of 1
