postgresql工具指令之psql
2016-09-04 20:28:07 
阿炯
psql是Postgresql自带的必不可少的命令行执行工具，除了执行sql语句这个基本功能外，还可用于执行自动化脚本，数据导入导出，数据恢复及数据库管理，或简单的报表生成工具。它有相当丰富的功能选项，它有两种操作模式：交互模式和非交互模式。在psql界面键入\?会列出交互模式下所支持的所有命令，键入\h或后跟关键词则会打印出其相关的用法。如果只使用psql不提供任何参数，则psql试图连接到与当前发出命令的操作系统用户名同名的数据库，如果提供命令行参数-U指定要使用的登录角色名，那么psql试图去连接与登录名同名的数据库，如果提供了-d 命令行参数明确的指定要连接的数据库，则psql试图连接到这个指定的数据库。它允许我们交互地输入、编辑、执行SQL命令。使用psql进行数据库管理非常简单、轻量、高效，查看具体的指令可在psql环境下输入\?。
psql变量
psql 提供变量替换特性，非常类似于shell的变量。它提供的变量就是简单的名字/值对，名字只能有字母、数字和下划线组成。
使用\set命令来设置变量:
=> \set foo freeoa
这样设置了变量foo,其值为bar。这样来显示变量的值:
=> \echo :foo
freeoa
使用\set只指定变量名，不指定值时，会设置该变量，只不过其值为空。
使用\unset命令来销毁变量：
=> \unset foo
使用\set命令，不提供任何参数，显示全部的变量。下面展示的是系统预置的变量:
=> \set
AUTOCOMMIT = 'on'
PROMPT1 = '%n@%M@%/# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
VERBOSITY = 'default'
VERSION = 'PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (CRUX-x86_64-multilib) 5.2.0, 64-bit'
DBNAME = 'dba'
USER = 'dba'
HOST = '172.18.0.8'
PORT = '5432'
ENCODING = 'UTF8'
foo = 'freeoa'
查看PostgreSQL版本
因此获取postgresql版本就有了另外一种方法：
=> \echo :VERSION
PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (CRUX-x86_64-multilib) 5.2.0, 64-bit
另一种方法是:
=> SELECT VERSION();
version
-----------------------------------------------------------------------------------------------
PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (CRUX-x86_64-multilib) 5.2.0, 64-bit
(1 行记录)
二者结果是一样的。
psql配置文件psqlrc
psql启动时会先读取系统范围的psqlrc文件，然后读取用户主目录下的.psqlrc文件。
下面是一个简单的配置，可根据自己实际的需要提取：
-- 静默模式
\set QUIET ON
-- 设置扩展显示
\x auto
--以大写字母自动完成关键字
\set COMP_KEYWORD_CASE upper
-- null显示为(null)
\pset null '(null)'
-- 显示query执行时间,这个非常有用,比mysql的时间还精确
\timing
-- 不同的数据库使用不同的命令历史文件
\set HISTFILE ~/.psql_history- :DBNAME
-- 或更为细致的
\set HISTFILE ~/.psql_history- :USER - :HOST - :PORT - :DBNAME
-- 历史记录忽略重复的命令
\set HISTCONTROL ignoredups
-- 更详细的错误报告
\set VERBOSITY verbose
-- 关闭自动提交事务.
\set AUTOCOMMIT OFF
-- 提示符1
\set PROMPT1 '%n@%M@%/# '
\set PROMPT1 '%[%033[1m%]%n@%/(%M)%R%[%033[0m%]%# '
-- 关闭静默模式
\unset QUIET
-- 不过瘾的话在导入一个外部提示shell自定义文件
\i ~/.psqlrc.local
自动提交事务
AUTOCOMMIT是默认打开的，当命令执行成功时会自动进行提交。在自动提交事务打开时，如果要显式手动提交，那么必须在执行命令或命令块之前执行BEGIN或START TRANSACTION命令执行完命令后再执行EDN或COMMIT来手动提交事务。
当关闭自动提交时，命令执行成功后需要手动END或COMMIT来提交。关闭自动提交时，系统默默的在任何没有开启事务的语句前自动添加BEGIN或START TRANSACTION等事务开始语句。自动提交关闭时，要用ABORT或ROLLBACK显式的撤销回滚事务，如果没有提交退出当前会话，则未提交事务会自动回滚。
注意:这里有一个问题,当关闭事务自动提交时,使用psql导入数据库时,可能会因为sql文件中没有提交语句而最后回滚,但没有错误提示,只是没有什么数据也没导入而已.比如,pg_dump/pg_dumpall导出的sql脚本文件就没有COMMIT语句,从而导入不会成功.
提示符变量
%[ 和 %] 之间的字符解释为终端控制字符序列。
%digits 转换为八进制字符序列，比如 %033 转换为 \033 ,所以 %033[ 正好转换为终端转义控制序列起始字符 \033[ ,后面紧跟的正是终端转义控制序列，可以指定字体的颜色，粗细等等属性。
psql支持\copy的命令用于数据的导入和导出，相关的文件本中默认使用制表符是制表符，当然也可以另外指定分隔符。它与sql语句中COPY指令是不一样的。
使用方法
psql [选项]... [数据库名称 [用户名称]]
通用选项:
-c,--command=命令 执行单一命令(SQL或内部指令)然后结束
-d, --dbname=DBNAME 指定要连接的数据库 (默认："postgres")
-f, --file=文件名 从文件中执行命令然后退出
-l, --list 列出所有可用的数据库,然后退出
-v, --set=, --variable=NAME=VALUE
设置psql变量NAME为VALUE
(例如，-v ON_ERROR_STOP=1)
-V, --version 输出版本信息, 然后退出
-X, --no-psqlrc 不读取启动文档(~/.psqlrc)
-1 ("one"), --single-transaction
作为一个单一事务来执行命令文件(如果是非交互型的)
-?, --help[=options] 显示此帮助，然后退出
--help=commands 列出反斜线命令，然后退出
--help=variables 列出特殊变量，然后退出
输入和输出选项：
-a, --echo-all 显示所有来自于脚本的输入
-b, --echo-errors 回显失败的命令
-e, --echo-queries 显示发送给服务器的命令
-E, --echo-hidden 显示内部命令产生的查询
-L, --log-file=文件名 将会话日志写入文件
-n, --no-readline 禁用增强命令行编辑功能(readline)
-o, --output=FILENAME 将查询结果写入文件(或 |管道)
-q, --quiet 以沉默模式运行(不显示消息，只有查询结果)
-s, --single-step 单步模式 (确认每个查询)
-S, --single-line 单行模式 (一行就是一条 SQL 命令)
输出格式选项 :
-A, --no-align 使用非对齐表格输出模式
-F, --field-separator=STRING
为字段设置分隔符,用于不整齐的输出(默认："|")
-H, --html HTML 表格输出模式
-P, --pset=变量[=参数] 设置将变量打印到参数的选项(查阅 \pset 命令)
-R, --record-separator=STRING
为不整齐的输出设置字录的分隔符(默认：换行符号)
-t, --tuples-only 只打印记录i
-T, --table-attr=文本 设定 HTML 表格标记属性（例如,宽度,边界)
-x, --expanded 打开扩展表格输出
-z, --field-separator-zero
为不整齐的输出设置字段分隔符为字节0
-0, --record-separator-zero
为不整齐的输出设置记录分隔符为字节0
联接选项:
-h, --host=主机名 数据库服务器主机或socket目录(默认："本地接口")
-p, --port=端口 数据库服务器的端口(默认："5432")
-U, --username=用户名 指定数据库用户名(默认："postgres")
-w, --no-password 永远不提示输入口令
-W, --password 强制口令提示 (自动)
更多信息，请在psql中输入"\?"(用于内部指令)或者 "\help"(用于SQL命令)，或者参考PostgreSQL文档中的psql章节。
postgres=# \?
一般性
\copyright 显示PostgreSQL的使用和发行许可条款
\crosstabview [COLUMNS] 执行查询并且以交叉表显示结果
\errverbose 以最冗长的形式显示最近的错误消息
\g [文件] or; 执行查询 (并把结果写入文件或 |管道)
\gdesc 描述查询结果，而不执行它
\gexec 执行策略，然后执行其结果中的每个值
\gset [PREFIX] 执行查询并把结果存到psql变量中
\gx [FILE] 就像\g,但强制扩展输出模式
\q 退出 psql
\watch [SEC] 每隔SEC秒执行一次查询
帮助
\? [commands] 显示反斜线命令的帮助
\? options 显示 psql 命令行选项的帮助
\? variables 显示特殊变量的帮助
\h [名称] SQL命令语法上的说明，用*显示全部命令的语法说明
查询缓存区
\e [FILE] [LINE] 使用外部编辑器编辑查询缓存区(或文件)
\ef [FUNCNAME [LINE]] 使用外部编辑器编辑函数定义
\ev [VIEWNAME [LINE]] 用外部编辑器编辑视图定义
\p 显示查询缓存区的内容
\r 重置(清除)查询缓存区
\s [文件] 显示历史记录或将历史记录保存在文件中
\w 文件 将查询缓存区的内容写入文件
输入/输出
\copy ... 执行 SQL COPY，将数据流发送到客户端主机
\echo [字符串] 将字符串写到标准输出
\i 文件 从文件中执行命令
\ir FILE 与 \i类似, 但是相对于当前脚本的位置
\o [文件] 将全部查询结果写入文件或 |管道
\qecho [字符串] 将字符串写到查询输出串流(参考 \o)
条件
\if EXPR 开始条件块
\elif EXPR 当前条件块内的备选方案
\else 当前条件块内的最终备选方案
\endif 条件块的结尾
资讯性
(选项: S = 显示系统对象, + = 其余的详细信息)
\d[S+] 列出表,视图和序列
\d[S+] 名称 描述表，视图，序列，或索引
\da[S] [模式] 列出聚合函数
\dA[+] [模式] 列出访问方法
\db[+] [模式] 列出表空间
\dc[S+] [模式] 列表转换
\dC[+] [模式] 列出类型强制转换
\dd[S] [模式] 显示没有在别处显示的对象描述
\dD[S+] [模式] 列出共同值域
\ddp [模式] 列出默认权限
\dE[S+] [模式] 列出引用表
\det[+] [模式] 列出引用表
\des[+] [模式] 列出外部服务器
\deu[+] [模式] 列出用户映射
\dew[+] [模式] 列出外部数据封装器
\df[anptw][S+] [PATRN] 列出[只包括 聚合/常规/程序/触发器/窗口]函数
\dF[+] [模式] 列出文本搜索配置
\dFd[+] [模式] 列出文本搜索字典
\dFp[+] [模式] 列出文本搜索解析器
\dFt[+] [模式] 列出文本搜索模版
\dg[S+] [模式] 列出角色
\di[S+] [模式] 列出索引
\dl 列出大对象， 功能与\lo_list相同
\dL[S+] [模式] 列出所有过程语言
\dm[S+] [模式] 列出所有物化视图
\dn[S+] [模式] 列出所有模式
\do[S+] [PATTERN] list operators
\dO[S+] [模式] 列出所有校对规则
\dp [模式] 列出表，视图和序列的访问权限
\drds [模式1 [模式2]] 列出每个数据库的角色设置
\dRp[+] [模式] 列出复制出版物
\dRs[+] [模式] 列出复制订阅
\ds[S+] [模式] 列出序列
\dt[S+] [模式] 列出表
\dT[S+] [模式] 列出数据类型
\du[S+] [模式] 列出角色
\dv[S+] [模式] 列出视图
\dx[+] [模式] 列出扩展
\dy[+] [PATTERN] list event triggers
\l[+] [模式] 列出所有数据库
\sf[+] FUNCNAME 显示一个函数的定义
\sv[+] VIEWNAME 显示一个视图的定义
\z [模式] 和\dp的功能相同
格式化
\a 在非对齐模式和对齐模式之间切换
\C [字符串] 设置表的标题，或如果没有的标题就取消
\f [字符串] 显示或设定非对齐模式查询输出的字段分隔符
\H 切换HTML输出模式 (目前是 关闭)
\pset [NAME [VALUE]] 设置表输出选项
(NAME := {border|columns|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 [开|关] 只显示记录 (目前是 关闭)
\T [字符串] 设置HTML <表格>标签属性, 或者如果没有的话取消设置
\x [on|off|auto] 切换扩展输出模式(目前是 关闭)
连接
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
连接到新数据库（当前是"postgres"）
\conninfo 显示当前连接的相关信息
\encoding [编码名称] 显示或设定客户端编码
\password [USERNAME] 安全地为用户更改口令
操作系统
\cd [目录] 更改目前的工作目录
\setenv NAME [VALUE] 设置或清空环境变量
\timing [开|关] 切换命令计时开关 (目前是 关闭)
\! [命令] 在 shell中执行命令或启动一个交互式shell
变量
\prompt [文本] 名称 提示用户设定内部变量
\set [名称 [值数]] 设定内部变量，若无参数则列出全部变量
\unset 名称 清空(删除)内部变量
大对象
\lo_export LOBOID 文件
\lo_import 文件 [注释]
\lo_list
\lo_unlink LOBOID 大对象运算
常用示例
## 查看客户端工具版本信息
psql -v
## 使用-c参数执行查询数据库版本信息
psql -c "select version()";
## psql登录到数据库后去执行操作系统的命令
\! top
## psql登录到数据库
psql -h 127.0.0.1 -U postgres -d postgres
## 查看当前连接状态=
\conninfo
## 查看当前数据库
select current_database();
## 查看当前连接用户
select * from current_user;
select user;
## 查看所有数据库
select * from pg_database;
\l+
## 查看数据库用户情况
\du
select * from pg_user;
## 切换数据库
\c <dbname>
## 使用某个用户连接到某个数据库
\c <dbname> <username>
## 查看当前数据库下的对象
\d+
## 查看当数数据库下的表
\dt
## 查看当前数据库下的索引
\di
## 查看当前数据库下的序列
\ds
## 查看当前数据库下的视图
\dv
## 查看命令执行时间
\timing on
## 设置自动提交（默认）
\set autocommit on
参考来源：
psql
an-explained-psqlrc
PostgreSQL最常用命令参考