postgresql |数据库 |postgresql数据库的短命令详细介绍

前言：

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 secondsHelp\? [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 commandsQuery 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 fileInput/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 blockInformational(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 \dpFormatting\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 userOperating 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 shellVariables\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 variableLarge 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=# \dList of relationsSchema | Name | Type  |  Owner   
--------+------+-------+----------mytest | emp  | table | postgres
(1 row)test=# \d+List of relationsSchema | Name | Type  |  Owner   | Size  | Description 
--------+------+-------+----------+-------+-------------mytest | emp  | table | postgres | 16 kB | 
(1 row)

仅查询表和视图：

test-# \dpAccess privilegesSchema |         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-# \lList of databasesName    |  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/pg1template1 | pg1      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pg1           +|          |          |             |             | pg1=CTc/pg1test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)test-# \l+List of databasesName    |  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 databasetemplate0 | 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 empTable "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_empIndex "mytest.pk_emp"Column |  Type   | Key? | Definition 
--------+---------+------+------------empno  | numeric | yes  | empno
primary key, btree, for table "mytest.emp"

查询该数据库支持的访问方法：

test=# \dA+List of access methodsName  | Type  |       Handler        |              Description               
--------+-------+----------------------+----------------------------------------brin   | Index | brinhandler          | block range index (BRIN) access methodbtree  | Index | bthandler            | b-tree index access methodgin    | Index | ginhandler           | GIN index access methodgist   | Index | gisthandler          | GiST index access methodhash   | Index | hashhandler          | hash index access methodheap   | Table | heap_tableam_handler | heap table access methodspgist | Index | spghandler           | SP-GiST index access method
(7 rows)

查询现有的表空间有哪些，+显示表空间的大小：

test=# \dbList of tablespacesName    | Owner | Location 
------------+-------+----------pg_default | pg1   | pg_global  | pg1   | 
(2 rows)
test=# \db+List of tablespacesName    | Owner | Location | Access privileges | Options |  Size  | Description 
------------+-------+----------+-------------------+---------+--------+-------------pg_default | pg1   |          |                   |         | 40 MB  | pg_global  | pg1   |          |                   |         | 623 kB | 
(2 rows)

查询函数，普通函数，以及和触发器相关的函数：

test=# \dfList of functionsSchema |        Name         | Result data type  |                                                 Argument data types              | Type 
--------+---------------------+-------------------+----------------------------------------------------------------------------------
------------------------------------+------public | __tmp_create_user   | void              |                                                                                  | funcpublic | auditlogfunc        | trigger           |                                                                                  | funcpublic | copytable           | integer           | new_table_name character varying, old_table_name character varying, times integer| funcpublic | emp                 | void              | arr text[]                                                                       | funcpublic | emp_history_trigger | trigger           |                                                                                  | func
后面的略略略
test=# \dfnList of functionsSchema |       Name        | Result data type  |                                                 Argument data types                | Type 
--------+-------------------+-------------------+------------------------------------------------------------------------------------
----------------------------------+------public | __tmp_create_user | void              |                                                                                    | funcpublic | copytable         | integer           | new_table_name character varying, old_table_name character varying, times integer  | funcpublic | emp               | void              | arr text[]                                                                         | funcpublic | f_inittables1     | void              | arr text[]                                                                         | funcpublic | ftest             | character varying | fname character varying DEFAULT '水果'::character varying, cname character varying 
DEFAULT '西瓜'::character varying | funcpublic | getsum            | integer           | talename character varying                                                         | funcpublic | test              | void              | arr text[]                                                                         | funcpublic | totalrecords      | integer           |                                                                                    | func
(8 rows)test=# \dftList of functionsSchema |        Name         | Result data type | Argument data types | Type 
--------+---------------------+------------------+---------------------+------public | auditlogfunc        | trigger          |                     | funcpublic | emp_history_trigger | trigger          |                     | funcpublic | record_history      | trigger          |                     | func
(3 rows)

查询所有用户和用户的详细信息：

test-# \dgList of rolesRole name      |                         Attributes                         | Member of 
--------------------+------------------------------------------------------------+-----------pg1                | Superuser, Create role, Create DB, Replication, Bypass RLS | {}postgres           | Superuser                                                  | {}postgres_exporter  |                                                            | {}postgres_exporter1 |                                                            | {}zsk                |                                                            | {}test-# \dg+List of rolesRole name      |                         Attributes                         | Member of | Description 
--------------------+------------------------------------------------------------+-----------+-------------pg1                | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | postgres           | Superuser                                                  | {}        | postgres_exporter  |                                                            | {}        | postgres_exporter1 |                                                            | {}        | zsk                |                                                            | {}        |

查询当前数据库下有哪些schema：

test-# \dnList of schemasName  |  Owner   
--------+----------mytest | postgrespublic | pg1
(2 rows)test-# \dn+List of schemasName  |  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 integerLANGUAGE plpgsql
AS $function$
DECLAREstmt1 varchar(1000);stmt2 varchar(1000);
beginfor i in 1..times loopraise 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 ASSELECT emp.empno,emp.enameFROM 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