前言：
postgresql数据库相比较MySQL或者oracle，一个是开源优势，另一个就是postgresql数据库的命令行更为丰富，因此，数据库的管理工作更为方便，快捷，尤其是postgresql的短命令就可以基本满足日常的数据库维护，管理工作了。
####注：短命令通常是用在pg的命令行客户端的，也就是psql -U用户 -p 端口 -h 数据库IP 登陆数据库后 \c 这样的命令，我们称之为短命令，无需通过客户端登陆数据库服务端的，例如pg_controldata ，pg_dump 这些命令我们称之为长命令。
下面就postgresql的短命令做一个详细的介绍
一，
查询短命令有哪些？
\?是帮助查询命令
test=# \? General \copyright show PostgreSQL usage and distribution terms \crosstabview [COLUMNS] execute query and display results in crosstab \errverbose show most recent error message at maximum verbosity \g [FILE] or ; execute query (and send results to file or |pipe) \gdesc describe result of query, without executing it \gexec execute query, then execute each value in its result \gset [PREFIX] execute query and store results in psql variables \gx [FILE] as \g, but forces expanded output mode \q quit psql \watch [SEC] execute query every SEC seconds Help \? [commands] show help on backslash commands \? options show help on psql command-line options \? variables show help on special variables \h [NAME] help on syntax of SQL commands, * for all commands Query Buffer \e [FILE] [LINE] edit the query buffer (or file) with external editor \ef [FUNCNAME [LINE]] edit function definition with external editor \ev [VIEWNAME [LINE]] edit view definition with external editor \p show the contents of the query buffer \r reset (clear) the query buffer \s [FILE] display history or save it to file \w FILE write query buffer to file Input/Output \copy ... perform SQL COPY with data stream to the client host \echo [STRING] write string to standard output \i FILE execute commands from file \ir FILE as \i, but relative to location of current script \o [FILE] send all query results to file or |pipe \qecho [STRING] write string to query output stream (see \o) Conditional \if EXPR begin conditional block \elif EXPR alternative within current conditional block \else final alternative within current conditional block \endif end conditional block Informational (options: S = show system objects, + = additional detail) \d[S+] list tables, views, and sequences \d[S+] NAME describe table, view, sequence, or index \da[S] [PATTERN] list aggregates \dA[+] [PATTERN] list access methods \db[+] [PATTERN] list tablespaces \dc[S+] [PATTERN] list conversions \dC[+] [PATTERN] list casts \dd[S] [PATTERN] show object descriptions not displayed elsewhere \dD[S+] [PATTERN] list domains \ddp [PATTERN] list default privileges \dE[S+] [PATTERN] list foreign tables \det[+] [PATTERN] list foreign tables \des[+] [PATTERN] list foreign servers \deu[+] [PATTERN] list user mappings \dew[+] [PATTERN] list foreign-data wrappers \df[anptw][S+] [PATRN] list [only agg/normal/procedures/trigger/window] functions \dF[+] [PATTERN] list text search configurations \dFd[+] [PATTERN] list text search dictionaries \dFp[+] [PATTERN] list text search parsers \dFt[+] [PATTERN] list text search templates \dg[S+] [PATTERN] list roles \di[S+] [PATTERN] list indexes \dl list large objects, same as \lo_list \dL[S+] [PATTERN] list procedural languages \dm[S+] [PATTERN] list materialized views \dn[S+] [PATTERN] list schemas \do[S] [PATTERN] list operators \dO[S+] [PATTERN] list collations \dp [PATTERN] list table, view, and sequence access privileges \dP[itn+] [PATTERN] list [only index/table] partitioned relations [n=nested] \drds [PATRN1 [PATRN2]] list per-database role settings \dRp[+] [PATTERN] list replication publications \dRs[+] [PATTERN] list replication subscriptions \ds[S+] [PATTERN] list sequences \dt[S+] [PATTERN] list tables \dT[S+] [PATTERN] list data types \du[S+] [PATTERN] list roles \dv[S+] [PATTERN] list views \dx[+] [PATTERN] list extensions \dy [PATTERN] list event triggers \l[+] [PATTERN] list databases \sf[+] FUNCNAME show a function's definition \sv[+] VIEWNAME show a view's definition \z [PATTERN] same as \dp Formatting \a toggle between unaligned and aligned output mode \C [STRING] set table title, or unset if none \f [STRING] show or set field separator for unaligned query output \H toggle HTML output mode (currently off) \pset [NAME [VALUE]] set table output option (border|columns|csv_fieldsep|expanded|fieldsep| fieldsep_zero|footer|format|linestyle|null| numericlocale|pager|pager_min_lines|recordsep| recordsep_zero|tableattr|title|tuples_only| unicode_border_linestyle|unicode_column_linestyle| unicode_header_linestyle) \t [on|off] show only rows (currently off) \T [STRING] set HTML <table> tag attributes, or unset if none \x [on|off|auto] toggle expanded output (currently off) Connection \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo} connect to new database (currently "test") \conninfo display information about current connection \encoding [ENCODING] show or set client encoding \password [USERNAME] securely change the password for a user Operating System \cd [DIR] change the current working directory \setenv NAME [VALUE] set or unset environment variable \timing [on|off] toggle timing of commands (currently off) \! [COMMAND] execute command in shell or start interactive shell Variables \prompt [TEXT] NAME prompt user to set internal variable \set [NAME [VALUE]] set internal variable, or list all if no parameters \unset NAME unset (delete) internal variable Large Objects \lo_export LOBOID FILE \lo_import FILE [COMMENT] \lo_list \lo_unlink LOBOID large object operations
可以看到，短命令按类别分了很多种，但，其中有一些命令是非常常用的，可以有效提高数据库运维，管理工作的，
因此，一些不太常用的或者使用率比较低的短命令，本文将会略过或者只是简单的介绍。
二，
连接方面的查询的短命令（非常常用的）
test=# \c You are now connected to database "test" as user "postgres". test=# \conninfo You are connected to database "test" as user "postgres" via socket in "/tmp" at port "5432". test=# \password postgres Enter new password: Enter it again:
以上命令 \c 是显示简略连接信息，本例是使用超级用户postgres连接到了 名为test的数据库
\conninfo 是连接详细信息，本例是使用超级用户postgres连接到了 名为test的数据库，socket文件是/tmp目录下，连接端口是5432
\password postgres是快速d修改的，安全的修改用户密码，不过通常是postgres这个超级用户才可以修改其它用户密码，本例是修改本用户postgres的密码
三，
各类信息查询，主要是数据库，表，索引，外键，主键
查询当前连接的数据库或者schema内的表名称 \d+ 多显示表的大小以及所属这些信息。
test=# \d List of relations Schema | Name | Type | Owner --------+------+-------+---------- mytest | emp | table | postgres (1 row) test=# \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+------+-------+----------+-------+------------- mytest | emp | table | postgres | 16 kB | (1 row)
仅查询表和视图：
test-# \dp Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+----------------------+----------+-------------------+-------------------+---------- public | audit | table | | | public | bonus | table | | | public | company | table | | | public | dept | table | | | public | emp | table | | | public | history_table | table | | | public | history_table_id_seq | sequence | | | public | salgrade | table | | | public | tmp | table | | | public | 計算機用語 | table | | | (10 rows)
查询有哪些数据库，以及数据库的大小：
test-# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+------------------- pg1 | pg1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | pg1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | pg1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pg1 + | | | | | pg1=CTc/pg1 template1 | pg1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pg1 + | | | | | pg1=CTc/pg1 test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (5 rows) test-# \l+ List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+----------+-------------+-------------+-------------------+---------+------------+---------------------------- ---------------- pg1 | pg1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7953 kB | pg_default | postgres | pg1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7993 kB | pg_default | default administrative conn ection database template0 | pg1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pg1 +| 7809 kB | pg_default | unmodifiable empty database | | | | | pg1=CTc/pg1 | | | template1 | pg1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/pg1 +| 7809 kB | pg_default | default template for new da tabases | | | | | pg1=CTc/pg1 | | | test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8849 kB | pg_default | (5 rows)
查询具体表结构，索引，或者视图，根据以上命令，在继续详细查询emp表和它的主键详细信息：
test=# \d emp Table "mytest.emp" Column | Type | Collation | Nullable | Default ----------+-----------------------+-----------+----------+--------- empno | numeric | | not null | ename | character varying(10) | | | job | character varying(9) | | | mgr | numeric | | | hiredate | date | | | sal | numeric(7,2) | | | comm | numeric(7,2) | | | deptno | numeric(2,0) | | | Indexes: "pk_emp" PRIMARY KEY, btree (empno) test=# \d pk_emp Index "mytest.pk_emp" Column | Type | Key? | Definition --------+---------+------+------------ empno | numeric | yes | empno primary key, btree, for table "mytest.emp"
查询该数据库支持的访问方法：
test=# \dA+ List of access methods Name | Type | Handler | Description --------+-------+----------------------+---------------------------------------- brin | Index | brinhandler | block range index (BRIN) access method btree | Index | bthandler | b-tree index access method gin | Index | ginhandler | GIN index access method gist | Index | gisthandler | GiST index access method hash | Index | hashhandler | hash index access method heap | Table | heap_tableam_handler | heap table access method spgist | Index | spghandler | SP-GiST index access method (7 rows)
查询现有的表空间有哪些，+显示表空间的大小：
test=# \db List of tablespaces Name | Owner | Location ------------+-------+---------- pg_default | pg1 | pg_global | pg1 | (2 rows) test=# \db+ List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Description ------------+-------+----------+-------------------+---------+--------+------------- pg_default | pg1 | | | | 40 MB | pg_global | pg1 | | | | 623 kB | (2 rows)
查询函数，普通函数，以及和触发器相关的函数：
test=# \df List of functions Schema | Name | Result data type | Argument data types | Type --------+---------------------+-------------------+---------------------------------------------------------------------------------- ------------------------------------+------ public | __tmp_create_user | void | | func public | auditlogfunc | trigger | | func public | copytable | integer | new_table_name character varying, old_table_name character varying, times integer | func public | emp | void | arr text[] | func public | emp_history_trigger | trigger | | func 后面的略略略 test=# \dfn List of functions Schema | Name | Result data type | Argument data types | Type --------+-------------------+-------------------+------------------------------------------------------------------------------------ ----------------------------------+------ public | __tmp_create_user | void | | func public | copytable | integer | new_table_name character varying, old_table_name character varying, times integer | func public | emp | void | arr text[] | func public | f_inittables1 | void | arr text[] | func public | ftest | character varying | fname character varying DEFAULT '水果'::character varying, cname character varying DEFAULT '西瓜'::character varying | func public | getsum | integer | talename character varying | func public | test | void | arr text[] | func public | totalrecords | integer | | func (8 rows) test=# \dft List of functions Schema | Name | Result data type | Argument data types | Type --------+---------------------+------------------+---------------------+------ public | auditlogfunc | trigger | | func public | emp_history_trigger | trigger | | func public | record_history | trigger | | func (3 rows)
查询所有用户和用户的详细信息：
test-# \dg List of roles Role name | Attributes | Member of --------------------+------------------------------------------------------------+----------- pg1 | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres | Superuser | {} postgres_exporter | | {} postgres_exporter1 | | {} zsk | | {} test-# \dg+ List of roles Role name | Attributes | Member of | Description --------------------+------------------------------------------------------------+-----------+------------- pg1 | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | postgres | Superuser | {} | postgres_exporter | | {} | postgres_exporter1 | | {} | zsk | | {} |
查询当前数据库下有哪些schema：
test-# \dn List of schemas Name | Owner --------+---------- mytest | postgres public | pg1 (2 rows) test-# \dn+ List of schemas Name | Owner | Access privileges | Description --------+----------+-------------------+------------------------ mytest | postgres | | public | pg1 | pg1=UC/pg1 +| standard public schema | | =UC/pg1 | (2 rows)
查询函数的定义（不是很准确，只是个大概的函数定义内容）：
test-# \sf copytable CREATE OR REPLACE FUNCTION public.copytable(new_table_name character varying, old_table_name character varying, times integer) RETURNS integer LANGUAGE plpgsql AS $function$ DECLARE stmt1 varchar(1000); stmt2 varchar(1000); begin for i in 1..times loop raise notice '当前次数%',i; stmt1 := format('create table "%1$s_%3$s" (like "%2$s" INCLUDING INDEXES INCLUDING DEFAULTS INCLUDING COMMENTS);',new_table_name,old_table_name,i); stmt2 := format('insert into "%1$s_%3$s" select * from "%2$s" ;',new_table_name,old_table_name,i); raise notice '%',stmt1; raise notice '%',stmt2; EXECUTE format('drop table if EXISTS "%1$s_%3$s";',new_table_name,old_table_name,i) ; EXECUTE stmt1 ; EXECUTE stmt2 ; end loop; return 110; END; $function$
查询视图的定义：
test=# create view emp_view as select empno,ename from emp; CREATE VIEW test=# \sv emp_view CREATE OR REPLACE VIEW public.emp_view AS SELECT emp.empno, emp.ename FROM emp
四，
timing SQL语句执行计时
postgres=# \timing on Timing is on. postgres=# \timing off Timing is off.
set 快速设置数据库内的变量，例如关闭自动提交，如果没有使用参数，就打印所有变量：
postgres=# \set AUTOCOMMIT Off
执行shell命令，某些时候不需要切出命令行，比较方便
postgres=# \! ls ~/ 1111.sh auto-sql.sh database-test.dump ewrwer.txt myglobals.sql test-backup test-database.sql test.tar.gz 12345.txt backup_log emp-bak1.sql logs SQL
