本文主要介绍psql基本使用一些技巧和常用的元命令使用方式，是安装后最常用的工具
Table of Contents
- 1 psql帮助文件
- 2 命令讲解
- 3 元命令
- 3.1 元命令汇总
- 3.2 \l 列出所有数据库列表
- 3.3 \db 查看表空间列表
- 3.4 \d查看表定义
- 3.5 查看表/索引占用空间大小
- 3.6 \sf 查看函数代码
- 3.7 \x 设置查询结果输出
- 3.8 显示连接信息
- 3.9 切换目录
- 3.10 显示执行时间（\timing on | off）
- 4 psqlrc文件
1 psql帮助文件
[postgres@pgserver12 ~]$ psql --help
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "postgres")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
--help=commands list backslash commands, then exit
--help=variables list special variables, then exit
Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, --no-align unaligned table output mode
--csv CSV (Comma-Separated Values) table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte
Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "postgres")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
Report bugs to <pgsql-bugs@lists.postgresql.org>.
[postgres@pgserver12 ~]$
2 命令讲解
2.1 连接命令
-d :用来指定登录数据库
-U :用来指定登录用户
-p :用来指定端口
-w :用来跳过密码提示符
-W :用来强制指定密码提示符
-h :指定主机名
[postgres@pgserver12 ~]$ psql -h localhost -p 5432 -U postgres -w
psql (12.5)
Type "help" for help.
postgres@localhost:5432=#\q
[postgres@pgserver12 ~]$ psql -h localhost -p 5432 -U postgres -W
Password:
psql (12.5)
Type "help" for help.
postgres@localhost:5432=#
2.2 通用命令
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "postgres")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
--help=commands list backslash commands, then exit
--help=variables list special variables, then exit
-c
-c :指定psql执行一个给定的命令字符串command。这个选项可以重复多次并且以任何顺序与-f选项组合在一起。当-c或者-f被指定时，psql不会从标准输入读取命令，直到它处理完序列中所有的-c和-f选项之后终止。
psql -c '\x' -c 'SELECT * FROM pg_user;'
[postgres@pgserver12 ~]$ psql -c '\x' -c 'SELECT * FROM pg_user;'
Expanded display is on.
-[ RECORD 1 ]+---------
usename | postgres
usesysid | 10
usecreatedb | t
usesuper | t
userepl | t
usebypassrls | t
passwd | ********
valuntil |
useconfig |
[postgres@pgserver12 ~]$
-f
-f :从文件filename而不是标准输入中读取命令。这个选项可以被重复多次，也可以以任意顺序与-c选项组合
[postgres@pgserver12 ~]$ cat test.sql
select now();
select current_date;
select 1;
[postgres@pgserver12 ~]$ psql -f test.sql
now
-------------------------------
2021-08-13 23:18:34.914138+08
(1 row)
current_date
--------------
2021-08-13
(1 row)
?column?
----------
1
(1 row)
-l
-l :--list 列出所有可用的数据库，然后退出。其他非连接选项会被忽略。这与元命令\list类似。
[postgres@pgserver12 ~]$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
[postgres@pgserver12 ~]$
-v
-v assignment
--set=assignment
--variable=assignment
执行一次变量赋值，和\set元命令相似。注意你必须在命令行上用等号分隔名字和值
postgres@[local]:5432=#select * from test;
id
----
1
2
3
[postgres@pgserver12 ~]$ cat test1.sql
select * from test where id=:v_id;
[postgres@pgserver12 ~]$ psql -v v_id=1 -d postgres -U postgres -f test1.sql
id
----
1
(1 row)
[postgres@pgserver12 ~]$
-V
-V 版本
[postgres@pgserver12 ~]$ psql -V
psql (PostgreSQL) 12.5
-X
-X
不读取启动文件（要么是系统范围的psqlrc文件，要么是用户的~/.psqlrc文件）。
[postgres@pgserver12 ~]$ ls -la .psqlrc
-rw-rw-r--. 1 postgres postgres 82 Apr 17 17:04 .psqlrc
[postgres@pgserver12 ~]$ cat .psqlrc
\set dba '\\i /home/postgres/postgres_dba/start.psql'
\set PROMPT1 '%/@%M:%>%R%#'
[postgres@pgserver12 ~]$ psql -X
psql (12.5)
Type "help" for help.
postgres=#
[postgres@pgserver12 ~]$ psql
psql (12.5)
Type "help" for help.
postgres@[local]:5432=#
-1
-1
--single-transaction
这个选项只能被用于与一个或者多个-c以及/或者-f选项组合。它会让psql在第一个上述选项之前发出一条BEGIN命令并且在最后一个上述选项之后发出一条COMMIT命令，这样就把所有的命令都包裹在一个事务中。这个选项可以保证要么所有的命令都成功地完成，要么不应用任何更改。
如果命令本身包含BEGIN、COMMIT或者ROLLBACK，这个选项将不会得到想要的效果。还有，如果当个命令不能在一个事务块中执行，指定这个选项将导致整个事务失败
2.3 输入输出
Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
-a
-a 把所有非空输入行按照它们被读入的形式打印到标准输出（不适用于交互式行读取）。这等效于把变量ECHO设置为 all。
[postgres@pgserver12 ~]$ psql -a -f test.sql
\set dba '\\i /home/postgres/postgres_dba/start.psql'
\set PROMPT1 '%/@%M:%>%R%#'
select now();
now
-------------------------------
2021-08-13 23:35:45.121095+08
(1 row)
select current_date;
current_date
--------------
2021-08-13
(1 row)
select 1;
?column?
----------
1
(1 row)
-b
-b 把失败的 SQL 命令打印到标准错误输出。这等效于把变量ECHO设置为errors。
-e
-e 也把发送到服务器的所有 SQL 命令复制到标准输出。这等效于把变量ECHO设置为queries。
[postgres@pgserver12 ~]$ psql -d postgres -U postgres -e
psql (12.5)
Type "help" for help.
postgres@[local]:5432=#select 1;
select 1;
?column?
----------
1
(1 row)
-E
-E 显示内部命令生成的SQL
[postgres@pgserver12 ~]$ psql -E
psql (12.5)
Type "help" for help.
postgres@[local]:5432=#\l
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres@[local]:5432=#
-L
-L 除了把所有查询输出写到普通输出目标之外，还写到文件filename中。
[postgres@pgserver12 ~]$ psql -L /tmp/1.log
psql (12.5)
Type "help" for help.
postgres@[local]:5432=#\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres@[local]:5432=#\q
[postgres@pgserver12 ~]$ more 1.log
1.log: No such file or directory
[postgres@pgserver12 ~]$ cat /tmp/1.log
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
[postgres@pgserver12 ~]$
-n
-n --no-readline 不使用Readline做行编辑并且不使用命令历史。在剪切和粘贴时，关掉 Tab 展开会有所帮助。
-o
把所有查询输出放到文件filename中。这等效于命令\o。
[postgres@pgserver12 ~]$ psql -o /tmp/2.log
psql (12.5)
Type "help" for help.
postgres@[local]:5432=#select now()
postgres-# ;
postgres@[local]:5432=#select now;
ERROR: column "now" does not exist
LINE 1: select now;
^
postgres@[local]:5432=#select now();
postgres@[local]:5432=#
postgres@[local]:5432=#\l
postgres@[local]:5432=#\q
[postgres@pgserver12 ~]$ more /tmp/2.log
now
-------------------------------
2021-08-13 23:47:50.453241+08
(1 row)
now
-------------------------------
2021-08-13 23:48:02.290964+08
(1 row)
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
[postgres@pgserver12 ~]$
这里所有的输出都文件中，没有显示到屏幕中。
-q
--quiet
指定psql应该安静地工作。默认情况下，它会打印出欢迎消息以及多种输出。如果使用了这个选项，以上那些就都不会输出。在使用-c选项时，配合这个选项很有用。这等效于设置变量QUIET为on。
-s
运行在单步模式中。这意味着在每个命令被发送给服务器之前都会提示用户一个可以取消执行的选项。使用这个选项可以调试脚本。
[postgres@pgserver12 ~]$ psql -s
psql (12.5)
Type "help" for help.
postgres@[local]:5432=#select * from test;
***(Single step mode: verify command)*******************************************
select * from test;
***(press return to proceed or enter x and return to cancel)********************
id
----
1
2
3
(3 rows)
-S
运行在单行模式中，其中新行会终止一个 SQL 命令，就像分号的作用一样。
[postgres@pgserver12 ~]$ psql -S
psql (12.5)
Type "help" for help.
postgres@[local]:5432^#select * from test
id
----
1
2
3
(3 rows)
单行没有加;也可以执行的
2.4 输入格式
Output format options:
-A, --no-align unaligned table output mode
--csv CSV (Comma-Separated Values) table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte
-A
切换到非对齐输出模式（默认输出模式是对齐的）。这等效于\pset format unaligned
[postgres@pgserver12 ~]$ psql -A
psql (12.5)
Type "help" for help.
postgres@[local]:5432=#\l
List of databases
Name|Owner|Encoding|Collate|Ctype|Access privileges
postgres|postgres|UTF8|en_US.UTF-8|en_US.UTF-8|
template0|postgres|UTF8|en_US.UTF-8|en_US.UTF-8|=c/postgres
postgres=CTc/postgres
template1|postgres|UTF8|en_US.UTF-8|en_US.UTF-8|=c/postgres
postgres=CTc/postgres
(3 rows)
postgres@[local]:5432=#
[postgres@pgserver12 ~]$ psql -A --csv
psql (12.5)
Type "help" for help.
postgres@[local]:5432=#\l
Name,Owner,Encoding,Collate,Ctype,Access privileges
postgres,postgres,UTF8,en_US.UTF-8,en_US.UTF-8,
template0,postgres,UTF8,en_US.UTF-8,en_US.UTF-8,"=c/postgres
postgres=CTc/postgres"
template1,postgres,UTF8,en_US.UTF-8,en_US.UTF-8,"=c/postgres
postgres=CTc/postgres"
postgres@[local]:5432=#
-F
使用separator作为非对齐输出的域分隔符。这等效于\pset fieldsep或者\f。
psql -Aq -F ' '
[postgres@pgserver12 ~]$ psql -Aq -F '*'
postgres@[local]:5432=#\l
List of databases
Name*Owner*Encoding*Collate*Ctype*Access privileges
postgres*postgres*UTF8*en_US.UTF-8*en_US.UTF-8*
template0*postgres*UTF8*en_US.UTF-8*en_US.UTF-8*=c/postgres
postgres=CTc/postgres
template1*postgres*UTF8*en_US.UTF-8*en_US.UTF-8*=c/postgres
postgres=CTc/postgres
(3 rows)
postgres@[local]:5432=#
-H
切换到HTML输出模式。这等效于\pset format html或者\H命令。
psql -AqH
[postgres@pgserver12 ~]$ psql -AqH
postgres@[local]:5432=#\l
<table border="1">
<caption>List of databases</caption>
<tr>
<th align="center">Name</th>
<th align="center">Owner</th>
<th align="center">Encoding</th>
<th align="center">Collate</th>
<th align="center">Ctype</th>
<th align="center">Access privileges</th>
</tr>
<tr valign="top">
<td align="left">postgres</td>
<td align="left">postgres</td>
<td align="left">UTF8</td>
<td align="left">en_US.UTF-8</td>
<td align="left">en_US.UTF-8</td>
<td align="left"> </td>
</tr>
<tr valign="top">
<td align="left">template0</td>
<td align="left">postgres</td>
<td align="left">UTF8</td>
<td align="left">en_US.UTF-8</td>
<td align="left">en_US.UTF-8</td>
<td align="left">=c/postgres<br />
postgres=CTc/postgres</td>
</tr>
<tr valign="top">
<td align="left">template1</td>
<td align="left">postgres</td>
<td align="left">UTF8</td>
<td align="left">en_US.UTF-8</td>
<td align="left">en_US.UTF-8</td>
<td align="left">=c/postgres<br />
postgres=CTc/postgres</td>
</tr>
</table>
<p>(3 rows)<br />
</p>
postgres@[local]:5432=#
-P
以\pset的形式指定打印选项。注意，这里你必须用一个等号而不是空格来分隔名称和值
-R
把separator用作非对齐输出的记录分隔符。这等效于\pset recordsep命令。
[postgres@pgserver12 ~]$ psql -A -R '*'
psql (12.5)
Type "help" for help.
postgres@[local]:5432=#\l
List of databases*Name|Owner|Encoding|Collate|Ctype|Access privileges*postgres|postgres|UTF8|en_US.UTF-8|en_US.UTF-8|*template0|postgres|UTF8|en_US.UTF-8|en_US.UTF-8|=c/postgres
postgres=CTc/postgres*template1|postgres|UTF8|en_US.UTF-8|en_US.UTF-8|=c/postgres
postgres=CTc/postgres*(3 rows)
postgres@[local]:5432=#select * from test;
id*1*2*3*(3 rows)
postgres@[local]:5432=#
-t
只显示元组，不显示类名
[postgres@pgserver12 ~]$ psql -t
psql (12.5)
Type "help" for help.
postgres@[local]:5432=#\l
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
postgres@[local]:5432=#
-T
关闭打印列名和结果行计数页脚等。这等效于\t或者\pset tuples_only命令。
-x
打开扩展表格式模式。这等效于\x或者\pset expanded命令。
3 元命令
3.1 元命令汇总
postgres-# \?
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 on)
\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 "postgres")
\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
\a
如果当前的表输出格式是非对齐的，则切换成对齐格式。如果不是非对齐格式，则设置成非对齐格式。保留这个命令是为了向后兼容性。更一般的方案请见\pset。
\c or \connect [ -reuse-previous=on|off ] [ dbname [ username ] [ host ] [ port ] | conninfo ]
| 命令 | 参数 | 描述 |
|---|---|---|
| \a | 如果当前的表输出格式是非对齐的，则切换成对齐格式。如果不是非对齐格式，则设置成非对齐格式。 | |
| \c | [ dbname [ username ] [ host ] [ port ] ] | 切换数据库或者用户 |
| \cd | [ directory ] | 把当前工作目录改为directory。如果不带参数，则切换到当前用户的主目录。要打印当前的工作目录，可以使用! pwd。 |
| \conninfo | 输出有关当前数据库连接的信息。 | |
| \copy | 拷贝文件 | |
| \crosstabview | [ colV [ colH [ colD [ sortcolH ] ] ] ] | 查询缓存区 |
| \d[S+] | [ pattern ] | 匹配关系 |
| \da | [ pattern ] | 列出聚集函数 |
| \dA | 列出访问方法 | |
| \db | 列出表空间 | |
| \dc | 列出字符编码之间的转换 | |
| \dC | 列出类型转换 | |
| \dd | 显示约束，操作符类等对象的描述 | |
| \dD | 列出域 | |
| \ddp | 列出默认的访问特权设置 | |
| \dE | 外部表 | |
| \di | 索引 | |
| \dm | 物化视图 | |
| \ds | 序列 | |
| \dt | 表 | |
| \dv | 视图 | |
| \des | 外部服务器 | |
| \det | 外部表 | |
| \deu | 用户映射 | |
| \dew | 外部数据包装器 | |
| \df | 列出函数 | |
| \dF | 文本搜索配置 | |
| \dFd | 列出搜索字典 | |
| \dFp | 列出文本搜索解析器 | |
| \dft | 列出文本搜索模板 | |
| \dg | 列出数据库角色 | |
| \dn | 列出模式 | |
| \do | 列出操作符及其操作数和结果类型 | |
| \d0 | 列出排序规则 | |
| \dp | 列出表，视图和序列 | |
| \drds | 列出已定义的配置 | |
| \dRp | 列出复制发布 | |
| \dRs | 列出复制订阅 | |
| \dT | 列出数据类型 | |
| \du | 列出数据库角色 | |
| \dx | 列出已安装拓展 | |
| \dy | 列出事件触发器 | |
| \e | [ filename ] [ line_number ] | 编辑文件 |
| \echo | 把参数打印到标准输出 | |
| \ef | 编辑函数 | |
| \encoding | [ encoding ] | 设置或显示客户端字符集编码 |
| \errverbose | 重复服务器错误消息 | |
| \ev | [ view_name [ line_number ] ] | 编辑函数定义 |
| \f | [ string ] | 设置非对齐查询输出的域分隔符 |
| \g | [ |command ] | 将查询缓冲区发送到服务器执行 |
| \gexec | ||
| \gset | [ prefix ] | |
| \gx | [ |command ] | 等价\g |
| \h | 帮助 | |
| \H | HTML查询输出格式 | |
| \i | 将文本做为键入命令执行 | |
| \l | 列出服务器中的数据库 | |
| \o | [ filename ] | 将查询结果保存到文件 |
| \o | [ |command ] | 将查询结果保存到管道 |
| \p | 将查询缓冲区打印到标准输出 | |
| \password | [ username ] | 更改该指定用户密码 |
| \q | 退出 | |
| \r | 重置缓存区 | |
| \s | 打印psql命令行历史到filename | |
| \set | [ name [ value [ … ] ] ] | 设置name为value |
| \timing | [ on\ | off ] |
| \unset | name | 删除 |
| \w | [filename command] | 将当前查询缓冲区写入文件或者管道 |
| \watch | [ seconds ] | 反复执行当前查询缓冲区 |
| \z | [ pattern ] | 列出表，序列，视图以及他们的访问特权 |
| ! | 转到shell，当shell退出psql会恢复 | |
| ? | 帮助信息 |
3.2 \l 列出所有数据库列表
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
ambari | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
: postgres=CTc/postgres
: ambari=CTc/postgres
ambarirca | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
: postgres=CTc/postgres
: mapred=CTc/postgres
mydb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
: postgres=CTc/postgres
(6 rows)
3.3 \db 查看表空间列表
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
(2 rows)
3.4 \d查看表定义
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------------------+-------+----------
public | cloud_sql_static | table | postgres
public | cloud_subscriber_devices | table | postgres
public | cloud_subscribers | table | postgres
public | cloud_table_static | table | postgres
public | sxacc-device-types | table | postgres
public | sxacc-devices | table | postgres
(6 rows)
postgres=# \d cloud_table_static
Table "public.cloud_table_static"
Column | Type | Modifiers
---------------------+--------------------------+-----------
relname | character varying(255) |
seq_scan | bigint |
seq_tup_read | bigint |
idx_scan | bigint |
idx_tup_fetch | bigint |
n_tup_ins | bigint |
n_tup_upd | bigint |
n_tup_del | bigint |
n_tup_hot_upd | bigint |
n_live_tup | bigint |
n_dead_tup | bigint |
n_mod_since_analyze | bigint |
last_vacuum | timestamp with time zone |
last_autovacuum | timestamp with time zone |
last_analyze | timestamp with time zone |
last_autoanalyze | timestamp with time zone |
vacuum_count | bigint |
autovacuum_count | bigint |
analyze_count | bigint |
autoanalyze_count | bigint |
import_date | timestamp with time zone |
postgres=#
3.5 查看表/索引占用空间大小
给测试表test插入500万数据：
postgres=# create table test(id int primary key, name varchar(100));
CREATE TABLE
postgres=# insert into test(id,name) select n,n||'_francs' from generate_series(1,5000000) n;
INSERT 0 5000000
postgres=# \di+ test_pkey
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-----------+-------+----------+-------+--------+-------------
public | test_pkey | index | postgres | test | 107 MB |
(1 row)
postgres=# \dt+ test
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+--------+-------------
public | test | table | postgres | 249 MB |
(1 row)
3.6 \sf 查看函数代码
mydb#\sf random_range
CREATE OR REPLACE FUNCTION pguser.random_range(integer,integer)
RETURN integer
LANGUAGE sql
AS $function$
SELECT ($1 + FLOOR(($2-$1 +1 )* random()))::int4;
$function$
上述\sf命令后面可以只接函数的名称，或者函数名称及输入参数类型，例如random_range(integer,integer)，Postgres支持名称相同但输入参数类型不同的函数，如果有同名函数，\sf 必须指定参数类型。
3.7 \x 设置查询结果输出
postgres=# \x
Expanded display is on.
postgres=# select * from test limit 1;
-[ RECORD 1 ]--
id | 1
name | 1_francs
3.8 显示连接信息
postgres-# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres-#
3.9 切换目录
cd 如果不带参数，则切换到当前用户的主目录。\! pwd用来显示当前工作路径（叹号后有空格）
postgres=# \! pwd
/home/postgres
postgres=# \!pwd
Invalid command \!pwd. Try \? for help.
postgres=# \cd /pgtbs
postgres=# \! pwd
/pgtbs
postgres=#
3.10 显示执行时间（\timing on | off）
mydb=> \timing on
Timing is on.
mydb=> select id,name from t1 limit 10;
4 psqlrc文件
先介绍 .psqlrc文件，如果psql没有带-X 选项，psql尝试读取和执行用户~/.psqlrc 启动文件中的命令，结合这个文件能够方便地预先定制维护脚本。
4.1.查询活动会话
# select pid,usename,datname,application_name,client_addr,age(clock_timestamp(), query_start),query from pg_stat_activity where pid<>pg_backend_pid() and state='active' order by query_start desc;
pid | usename | datname | application_name | client_addr | age | query
-------+-------------+---------+------------------------+-------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6634 | replication | | walreceiver | 10.2.38.108 | |
6636 | replication | | walreceiver | 10.2.39.66 | |
30315 | calixcloud | cloud | PostgreSQL JDBC Driver | 10.2.42.249 | 00:00:00.419892 | SELECT subscriber_id,service_addr,street,city,state,country,postcode FROM cloud_subscribers WHERE lat IS NULL and lon IS NULL AND service_addr IS NOTNULL LIMIT 1
7791 | calixcloud | cloud | PostgreSQL JDBC Driver | 10.2.35.36 | 00:00:00.602242 | UPDATE "sxacc-net-perf-test" t SET info = jsonb_set(t.info,'{worker}','"gcs4-us.calix.com.8339"',true) FROM (SELECT t.info FROM "sxacc-net-perf-test"t WHERE (t.info->>'nextRunTime')::jsonb < '{"$date":1576820536640}' AND (t.info->'worker')::jsonb IS NULL LIMIT 1) t1 WHERE t.info->>'_id' = t1.info->>'_id' RETURNING t1.*
9747 | calixcloud | cloud | PostgreSQL JDBC Driver | 10.2.36.242 | 00:00:00.979992 | UPDATE "sxacc-net-perf-test" t SET info = jsonb_set(t.info,'{worker}','"gcs3-us.calix.com.32325"',true) FROM (SELECT t.info FROM "sxacc-net-perf-test t WHERE (t.info->>'nextRunTime')::jsonb < '{"$date":1576820536261}' AND (t.info->'worker')::jsonb IS NULL LIMIT 1) t1WHERE t.info->>'_id' = t1.info->>'_id' RETURNING t1.*
8615 | calixcloud | cloud | PostgreSQL JDBC Driver | 10.2.35.127 | 00:00:01.071608 | UPDATE "sxacc-net-perf-test" t SET info = jsonb_set(t.info,'{worker}','"gcs2-us.calix.com.1277"',true) FROM (SELECT t.info FROM "sxacc-net-perf-test"t WHERE (t.info->>'nextRunTime')::jsonb < '{"$date":1576820536171}' AND (t.info->'worker')::jsonb IS NULL LIMIT 1) t1 WHERE t.info->>'_id' = t1.info->>'_id' RETURNING t1.*
14164 | postgres | cloud | psql | | 00:05:52.667716 | VACUUM (VERBOSE, ANALYZE) "sxacc-files";
(7 rows)
- active： 任务正在执行
- idle：后台进程为空闲状态，等待后续客户端发出命令
- idle in transaction：后台进程正在事务中，并不是指正在执行SQL
- idle in transaction（aborted）：和idle in transaction 类似，只是事务中的部分SQL异常
vim ~/.psqlrc
-- 设置活动会话
\set active_session 'select pid,usename,datname,application_name,client_addr,age(clock_timestamp(), query_start),query from pg_stat_activity where pid<>pg_backend_pid() and state=\'active\' order by query_start desc;'
之后重新连接数据库，执行:active_session即可。
postgres#:active_session
pid | usename | datname | application_name | client_addr | age | query
-------+-------------+---------+------------------------+-------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6634 | replication | | walreceiver | 10.2.38.108 | |
6636 | replication | | walreceiver | 10.2.39.66 | |
30315 | calixcloud | cloud | PostgreSQL JDBC Driver | 10.2.42.249 | 00:00:00.419892 | SELECT subscriber_id,service_addr,street,city,state,country,postcode FROM cloud_subscribers WHERE lat IS NULL and lon IS NULL AND service_addr IS NOTNULL LIMIT 1
7791 | calixcloud | cloud | PostgreSQL JDBC Driver | 10.2.35.36 | 00:00:00.602242 | UPDATE "sxacc-net-perf-test" t SET info = jsonb_set(t.info,'{worker}','"gcs4-us.calix.com.8339"',true) FROM (SELECT t.info FROM "sxacc-net-perf-test"t WHERE (t.info->>'nextRunTime')::jsonb < '{"$date":1576820536640}' AND (t.info->'worker')::jsonb IS NULL LIMIT 1) t1 WHERE t.info->>'_id' = t1.info->>'_id' RETURNING t1.*
9747 | calixcloud | cloud | PostgreSQL JDBC Driver | 10.2.36.242 | 00:00:00.979992 | UPDATE "sxacc-net-perf-test" t SET info = jsonb_set(t.info,'{worker}','"gcs3-us.calix.com.32325"',true) FROM (SELECT t.info FROM "sxacc-net-perf-test t WHERE (t.info->>'nextRunTime')::jsonb < '{"$date":1576820536261}' AND (t.info->'worker')::jsonb IS NULL LIMIT 1) t1WHERE t.info->>'_id' = t1.info->>'_id' RETURNING t1.*
8615 | calixcloud | cloud | PostgreSQL JDBC Driver | 10.2.35.127 | 00:00:01.071608 | UPDATE "sxacc-net-perf-test" t SET info = jsonb_set(t.info,'{worker}','"gcs2-us.calix.com.1277"',true) FROM (SELECT t.info FROM "sxacc-net-perf-test"t WHERE (t.info->>'nextRunTime')::jsonb < '{"$date":1576820536171}' AND (t.info->'worker')::jsonb IS NULL LIMIT 1) t1 WHERE t.info->>'_id' = t1.info->>'_id' RETURNING t1.*
14164 | postgres | cloud | psql | | 00:05:52.667716 | VACUUM (VERBOSE, ANALYZE) "sxacc-files";
(7 rows)
[
](javascript:void(0)😉
4.2.查询等待事件
-- check wait events
\set wait_event 'select pid,application_name,client_addr,age(clock_timestamp(),query_start),state,wait_event_type,wait_event from pg_stat_activity where pid<>pg_backend_pid() and wait_event is not null order by wait_event_type;'
cloud=# :wait_event
pid | application_name | client_addr | age | state | wait_event_type | wait_event
-------+------------------------+----------------+-----------------+--------+-----------------+---------------------
6576 | | | | | Activity | CheckpointerMain
6636 | walreceiver | 10.2.39.66 | | active | Activity | WalSenderMain
6634 | walreceiver | 10.2.38.108 | | active | Activity | WalSenderMain
6581 | | | | | Activity | LogicalLauncherMain
6577 | | | | | Activity | BgWriterMain
6578 | | | | | Activity | WalWriterMain
8466 | PostgreSQL JDBC Driver | 10.2.41.193 | 00:08:38.596477 | idle | Client | ClientRead
18770 | PostgreSQL JDBC Driver | 10.2.42.249 | 00:28:02.041479 | idle | Client | ClientRead
9401 | PostgreSQL JDBC Driver | 10.2.42.219 | 00:07:35.883319 | idle | Client | ClientRead
5690 | PostgreSQL JDBC Driver | 10.2.41.193 | 00:10:09.736458 | idle | Client | ClientRead
4.3 设置提示符
[postgres@pgserver12 ~]$ cat .psqlrc
\set dba '\\i /home/postgres/postgres_dba/start.psql'
\set PROMPT1 '%/@%M:%>%R%#'
4.4 postgresql DBA脚本工具
postgres_dba (PostgresDBA)
The missing set of useful tools for Postgres DBA and mere mortals.
⚠️ If you have great ideas, feel free to create a pull request or open an issue.
👉 See also postgres-checkup, a tool for automated health checks and SQL performance analysis.
脚本下载：
The installation is trivial. Clone the repository and put "dba" alias to your `.psqlrc` file:
```bash
git clone https://github.com/NikolayS/postgres_dba.git
echo "\\set dba '\\\\i `pwd`/postgres_dba/start.psql'" >> ~/.psqlrc # bash version; won't work in zsh
```
使用方式:
[postgres@pgserver12 sql]$ ls
0_node.sql b2_btree_estimation.sql i2_redundant_indexes.sql s1_pg_stat_statements_top_total.sql
1_databases.sql b3_table_pgstattuple.sql i3_non_indexed_fks.sql s2_pg_stat_statements_report.sql
2_table_sizes.sql b4_btree_pgstattuple.sql i4_invalid_indexes.sql t1_tuning.sql
3_load_profiles.sql b5_tables_no_stats.sql i5_indexes_migration.sql v1_vacuum_activity.sql
a1_activity.sql e1_extensions.sql l1_lock_trees.sql v2_autovacuum_progress_and_queue.sql
b1_table_estimation.sql i1_rare_indexes.sql p1_alignment_padding.sql
[postgres@pgserver12 sql]$
[postgres@pgserver12 sql]$ psql
psql (12.5)
Type "help" for help.
postgres@[local]:5432=#:dba
Menu:
0 – Node & Current DB Information: master/replica, lag, DB size, tmp files, etc
1 – Databases: Size, Statistics
2 – Table Sizes
3 – Load Profile
a1 – Current Activity: count of current connections grouped by database, user name, state
b1 – Tables Bloat, rough estimation
b2 – B-tree Indexes Bloat, rough estimation
b3 – Tables Bloat, more precise (requires pgstattuple extension; expensive)
b4 – B-tree Indexes Bloat, more precise (requires pgstattuple extension; expensive)
b5 – Tables and Columns Without Stats (so bloat cannot be estimated)
e1 – List of extensions installed in the current DB
i1 – Unused/Rarely Used Indexes
i2 – List of redundant indexes
i3 – FKs with Missing/Bad Indexes
i4 – List of invalid indexes
i5 – Unused/Redundant Indexes Do & Undo Migration DDL
l1 – Locks: analysis of "locking trees"
p1 – [EXPERIMENTAL] Alignment Padding. How many bytes can be saved if columns are ordered better?
s1 – Slowest Queries, by Total Time (requires pg_stat_statements extension)
s2 – Slowest Queries Report (requires pg_stat_statements)
t1 – Postgres parameters tuning
v1 – Vacuum: Current Activity
v2 – Vacuum: VACUUM progress and autovacuum queue
q – Quit
Type your choice and press <Enter>:
扩展：
## How to Extend (Add More Queries)
You can add your own useful SQL queries and use them from the main menu. Just add your SQL code to `./sql` directory. The filename should start with some 1 or 2-letter code, followed by underscore and some additional arbitrary words. Extension should be `.sql`. Example:
```
sql/f1_cool_query.sql
```
– this will give you an option "f1" in the main menu. The very first line in the file should be an SQL comment (starts with `--`) with the query description. It will automatically appear in the menu.
Once you added your queries, regenerate `start.psql` file:
```bash
/bin/bash ./init/generate.sh
``
测试:
当前数据库基本信息 :dba 输入0
metric | value
-----------------------------------+--------------------------------------------------------------------------------------
-------------------
Postgres Version | PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat
4.8.5-44), 64-bit
Config file | /data/pg12.5/pgdata/postgresql.conf
Role | Master
Replicas |
Started At | 2021-08-13 21:47:10+08
Uptime | 12:30:47
Checkpoints | 37
Forced Checkpoints | 0.0%
Checkpoint MB/sec | 0.000004
--------------------------------- | -------------------------------------------------------------------------------------
---
Database Name | postgres
Database Size | 8201 kB
Stats Since | 2021-08-13 21:20:22+08
Stats Age | 12:57:35
Installed Extensions | plpgsql 1.0
Cache Effectiveness | 97.91%
Successful Commits | 99.45%
Conflicts | 0
Temp Files: total size | 0 bytes
Temp Files: total number of files | 0
Temp Files: avg file size |
Deadlock
输入1 查看database大小
Database | Size | Stats Age | Cache eff. | Committed | Conflicts | Deadlocks | Temp. Files
---------------+------------------+-----------+------------+-----------+-----------+-----------+-------------
*** TOTAL *** | 24 MB (100.00%) | | 97.92% | 99.45% | 0 | 0 | 0 (0 bytes)
| | | | | | |
postgres | 8201 kB (33.75%) | 12:58:26 | 97.92% | 99.45% | 0 | 0 | 0 (0 bytes)
template1 | 8049 kB (33.12%) | | | | 0 | 0 | 0 (0 bytes)
template0 | 8049 kB (33.12%) | | | | 0 | 0 | 0 (0 bytes)
(5 rows)
输入2 table大小
2
Table | Rows | Total Size | Table Size | Index(es) Size | TOAST Size
---------------+------+----------------------+----------------------+----------------+-----------------
*** TOTAL *** | ~774 | 360 kB (100.00%) | 304 kB (100.00%) | | 56 kB (100.00%)
| | | | |
test | ~0 | 8192 bytes (100.00%) | 8192 bytes (100.00%) |
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
