- tpch=# \d region
- Append-Only Columnar Table "public.region"
- Column | Type | Modifiers
- -------------+------------------------+--------------------------------------------------------------
- r_regionkey | integer | not null default nextval('region_r_regionkey_seq'::regclass)
- r_name | character(25) |
- r_comment | character varying(152) |
- Checksum: t
- Distributed by: (r_regionkey)
- tpch=# \dn
- List of schemas
- Name | Owner
- --------------------+---------
- gp_toolkit | dgadmin
- information_schema | dgadmin
- pg_aoseg | dgadmin
- pg_bitmapindex | dgadmin
- pg_catalog | dgadmin
- pg_toast | dgadmin
- public | dgadmin
- (7 rows)
- tpch=# select objname,actionname,statime from pg_stat_operations where objname like 'region';
- objname | actionname | statime
- ---------+------------+-------------------------------
- region | CREATE | 2017-05-21 00:32:28.672208+08
- region | ANALYZE | 2017-06-30 06:55:57.658525+08
- (2 rows)
- 查看表的大小:
- tpch=# select pg_size_pretty(pg_relation_size('public.customer'));
- pg_size_pretty
- ----------------
- 122 MB
- (1 row)
- 查看表和索引的大小:
- tpch=# select pg_size_pretty(pg_total_relation_size('public.customer'));
- pg_size_pretty
- ----------------
- 155 MB
- (1 row)
- tpch=# select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB" from pg_tables where schemaname='public' group by 1;
- schemaname | Size_MB
- ------------+---------
- public | 10698
- (1 row)
- tpch=# select pg_size_pretty(pg_database_size('tpch'));
- pg_size_pretty
- ----------------
- 10 GB
- (1 row)
- tpch=# select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
- datname | pg_size_pretty
- -----------+----------------
- tpch | 10 GB
- postgres | 111 MB
- tpch_1g | 1100 MB
- template1 | 29 MB
- template0 | 27 MB
- (5 rows)
- select schemaname,tablename,round(sum(pg_total_relation_size(schemaname "" '.' "" partitiontablename))/1024/1024) "MB"
- from pg_partitions where tablename='employee_daily' group by 1,2;
- schemaname " tablename " MB
- -----------+----------------+-----
- public " employee_daily " 254
- tpch=# \h alter table
- Command: ALTER TABLE
- Description: change the definition of a table
- Syntax:
- 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]
- tpch=# \h create index
- Command: CREATE INDEX
- Description: define a new index
- Syntax:
- CREATE [UNIQUE] INDEX name ON table
- [USING btree|bitmap|gist]
- ( {column | (expression)} [opclass] [, ...] )
- [ WITH ( FILLFACTOR = value ) ]
- [TABLESPACE tablespace]
- [WHERE predicate]
- dgadmin@flash:~$ PGOPTIONS='-c gp_session_role=utility' psql -p 25432 -h flash -d postgres
- psql (8.2.15)
- Type "help" for help.
- postgres=# \q
- Master节点的数据库日志存在$MASTER_DATA_DIRECTORY/pg_log/目录下,文件名根据数据库的log_filename参数生成。
- dgadmin@flash:~$ gpconfig -s log_filename
- Values on all segments are consistent
- GUC : log_filename
- Master value: gpdb-%Y-%m-%d_%H%M%S.csv
- Segment value: gpdb-%Y-%m-%d_%H%M%S.csv
- 默认安装的日志文件格式为:$MASTER_DATA_DIRECTORY/pg_log/gpdb-yyyy-mm-dd_000000.csv
- gpstart\gpstop\gpstate和另外utility生成的日志存储在:~gpadmin/gpAdminLogs/目录下
- Primary Segment日志位置用下面语句查询:select dbid,
- hostname,
- datadir || '/pg_log'from gp_configuration where content not in ( - 1) and isprimary is true;
- Mirror Segment日志位置用下面语句查询:Primary Segment日志位置用下面语句查询:select dbid,
- hostname,
- datadir || '/pg_log'from gp_configuration where content not in ( - 1) and isprimary is false;
- tpch=# \df pub*.*test*
- List of functions
- Schema " Name " Result data type " Argument data types
- --------+-------------+------------------+---------------------
- public " bugtest " integer "
- public " test " boolean " integer
- public " test " void "
- (3 rows)
- dgadmin@flash:/dgdata/primary/dg0/pg_log$ gpstate -Q
- 20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-Starting gpstate with args: -Q
- 20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.99.00 build Deepgreen DB'
- 20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.99.00 build Deepgreen DB) on x86_64-unknown-linux-gnu, compiled by GCC gcc (Ubuntu 4.9.4-2ubuntu1~14.04.1) 4.9.4 compiled on May 18 2017 05:19:19'
- 20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-Obtaining Segment details from master...
- 20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:--Quick Greenplum database status from Master instance only
- 20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-----------------------------------------------------------
- 20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-# of up segments, from configuration table = 4
- 20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-----------------------------------------------------------
- postgres=# \l
- List of databases
- Name | Owner | Encoding | Access privileges
- -----------+---------+----------+---------------------
- postgres | dgadmin | UTF8 |
- template0 | dgadmin | UTF8 | =c/dgadmin
- : dgadmin=CTc/dgadmin
- template1 | dgadmin | UTF8 | =c/dgadmin
- : dgadmin=CTc/dgadmin
- tpch | dgadmin | UTF8 |
- tpch_1g | dgadmin | UTF8 |
- (5 rows)
- postgres=# select * from pg_database;
- datname | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datconfig | datacl
- -----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+----------------------------------
- tpch | 10 | 6 | f | t | -1 | 10898 | 888 | 1663 | |
- postgres | 10 | 6 | t | t | -1 | 10898 | 888 | 1663 | |
- tpch_1g | 10 | 6 | f | t | -1 | 10898 | 888 | 1663 | |
- template1 | 10 | 6 | t | t | -1 | 10898 | 888 | 1663 | | {=c/dgadmin,dgadmin=CTc/dgadmin}
- template0 | 10 | 6 | t | f | -1 | 10898 | 888 | 1663 | | {=c/dgadmin,dgadmin=CTc/dgadmin}
- (5 rows)
- postgres=# \c
- You are now connected to database "postgres" as user "dgadmin".
- \h 显示任何SQL语法帮助
- \? 显示所有psql命令语法帮助
----------------------------------------------------
- postgres=# \h create database
- Command: CREATE DATABASE
- Description: create a new database
- Syntax:
- CREATE DATABASE name
- [ [ WITH ] [ OWNER [=] dbowner ]
- [ TEMPLATE [=] template ]
- [ ENCODING [=] encoding ]
- [ TABLESPACE [=] tablespace ]
- [ CONNECTION LIMIT [=] connlimit ] ]
- dgadmin@flash: ~$ createdb--help createdb creates a PostgreSQL database.
- Usage: createdb[OPTION]... [DBNAME][DESCRIPTION]
- Options: -D,
- --tablespace = TABLESPACE
- default tablespace
- for the database - e,
- --echo show the commands being sent to the server - E,
- --encoding = ENCODING encoding
- for the database - O,
- --owner = OWNER database user to own the new database - T,
- --template = TEMPLATE template database to copy--help show this help,
- then exit--version output version information,
- then exit
- Connection options: -h,
- --host = HOSTNAME database server host or socket directory - p,
- --port = PORT database server port - U,
- --username = USERNAME user name to connect as - w,
- --no - password never prompt
- for password - W,
- --password force password prompt
- By
- default,
- a database with the same name as the current user is created.
- Report bugs to < pgsql - bugs@postgresql.org > .
- postgres=# \h drop database
- Command: DROP DATABASE
- Description: remove a database
- Syntax:
- DROP DATABASE [ IF EXISTS ] name
- dgadmin@flash:~$ dropdb --help
- dropdb removes a PostgreSQL database.
- Usage:
- dropdb [OPTION]... DBNAME
- Options:
- -e, --echo show the commands being sent to the server
- -i, --interactive prompt before deleting anything
- --help show this help, then exit
- --version output version information, then exit
- Connection options:
- -h, --host=HOSTNAME database server host or socket directory
- -p, --port=PORT database server port
- -U, --username=USERNAME user name to connect as
- -w, --no-password never prompt for password
- -W, --password force password prompt
- Report bugs to <pgsql-bugs@postgresql.org>.
来源: https://yq.aliyun.com/articles/114599