闻术苑 2017-07-01 3844浏览量
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>.
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。
分享数据库前沿,解构实战干货,推动数据库技术变革