pg服务管理 与 psql常用命令

一、 服务启停与创建

1. 服务启动

启动方法

• 直接运行postgres进程启动
• 使用pg_ctl命令

#直接运行postgres进程启动
postgres -D $PGDATA &

#使用pg_ctl命令
pg_ctl -D $PGDATA start

在Linux 7可以配置/usr/lib/systemd/system/postgresql.service文件，以systemctl命令启动pg，但这种方法本质上跟使用pg_ctl是一样的，参考

linux - Systemd postgresql start script - Unix & Linux Stack Exchange

单用户模式

在启动pg服务时添加 --single参数，常用的一种情况是，当记录年龄离2^31还有100万时，db将阻止用户连接，此时就需要启动到单用户模式执行vacuum

postgres --single -D $PGDATA &

2. 服务停止

停止模式

• smart shutdown（相当于Oracle normal模式）：不允许新连接，待已有连接全部结束后关闭数据库
• fast shutdown（相当于Oracle immediate模式）：不允许新连接，向所有活跃的服务进程发送SIGTERM信号让它们立即退出，之后等待所有子进程退出并关闭数据库
• immediate shutdown（相当于Oracle abort模式）：不允许新连接，主进程postgres向所有子进程发送SIGQUIT信号并且立即退出，所有子进程也会立即退出。下次启动时会redo wal日志进行实例恢复

信号类型

• SIGTERM：对应smart shutdown模式
• SIGINT：对应fast shutdown模式
• SIGQUIT：对应immediate shutdown模式

停止方法

• 直接向postgres主进程发送signal信号
• 使用pg_ctl命令

#直接向postgres主进程发送signal信号
kill -TERM `head -1 /PGDATA目录/postmaster.pid`
kill -INT `head -1 /PGDATA目录/postmaster.pid`
kill -QUIT `head -1 /PGDATA目录/postmaster.pid`

#使用pg_ctl命令
pg_ctl stop -D $PGDATA -m smart
pg_ctl stop -D $PGDATA -m fast
pg_ctl stop -D $PGDATA -m immediate

3. 重启pg

pg_ctl stop -D $PGDATA -m fast
pg_ctl start -D $PGDATA

#快速重启
pg_ctl -D $PGDATA restart -m fast

4. pg_ctl功能

• 初始化pg实例
• 启动、终止、重启pg服务
• 查看pg服务状态
• reload配置文件
• 向指定进程发送信号

5. 查看控制文件内容

#不用加控制文件名
pg_controldata

二、 psql常用命令

1. 常用元命令

• \h      查看sql语法
• \?      命令行操作的帮助
• Tab键可以补全目录
• \i sql文件     执行sql文件
• \l      列出所有的数据库
• \dn     列出所有schema
• \db     显示所有表空间（pg中的表空间是一个目录，表放在表空间相当于将表的数据文件放到该目录之下）
• \d     查看当前schema 中所有的表、视图、序列
• \d+   同上，但是多额外信息，下面几个命令都有带+版，能看到更详细的信息
• \d schema.obj   查看对象结构（相当于desc）
• \dt     只显示匹配的表
• \di     只显示匹配的索引
• \ds     只显示序列
• \dv     只显示视图
• \df      只显示函数

• \sf 函数名   查看函数定义
• \du 或 \dg   列出数据库中所有角色或用户（pg中是一样的）
• \dp 或 \z 表名   显示表的权限分配情况
• \x      横纵显示切换 （类似mysql \G）
• \dT+   显示扩展类型相关属性及描述
• \q      退出pg命令行
• \z 或 \dp 表名    显示表的权限分配情况
• \timing    显示执行时间
• \watch 5    每五秒循环执行sql语句
• \c dbname      切换数据库
• \conninfo   查看连接信息
• \echo 字符串  输出一行信息
• \encoding 字符集名      设置客户端字符集

• \set [NAME [VALUE]]    设置内部变量，不加参数则列出内部变

•  set search to schema   切换schema

2. 常用参数

非交互模式执行脚本

psql -f file_path/file_name

非交互模式执行SQL

psql -d db_name -c "sql语句1;sql语句2;sql语句3;"

ssl连接

psql -h连接串  \ "user=root dbname=postgres sslmode=require"

查看版本

psql -V 
psql -c "select version()"

执行os命令

\! ls
\cd /tmp

传变量

\set v_id 2
select * from test where id=:v_id;
#或
psql -v v_id=1 mydb pguser -f select.sql

-t只显示输出结果，-A去除尾部空行（shell脚本取结果常用）

psql -At -c "sql" 

获取psql中命令实际执行的sql：在启动时加入 -E 或执行 \set ECHO_HIDDEN on

psql -E mydb pguser

关闭pg的自动提交（AUTOCOMMIT不能小写）

\set AUTOCOMMIT off

三、 其他psql命令

可以作为参考

 1. psql参数

基本语法	支持参数		参数含义
psql	连接参数	-h	数据库服务器的主机名或IP
		-p	数据库服务器的端口号
		-U	连接该数据库服务器时使用的数据库用户
		-w	连接服务器时不需要输入用户密码
		-W	连接服务器时需要输入用户密码
	通用参数	-c	执行单一SQL命令
		-d	指定要连接的数据库
		-f	sql脚本文件
		-l	输出服务器中的所有数据库名
		-v	设置psql的变量，形如 -v PARAMETER=VALUE
		-V	输出版本信息
		-X	不读取启动文件~/.psqlrc
		-1（one）	以单一事务执行命令文件
		-？	显示帮助信息
	输入输出参数	-a	显示所有来自脚本的输入
		-b	回显失败的命令
		-e	回显发送给服务器的命令
		-E	回显内部命令产生的查询
		-L	指定会话日志文件
		-n	禁用增强命令行编辑功能（readline）
		-o	将查询结果输出到文件或Linux管道
		-q	静默方式运行（只输出结果，不显示消息）
		-s	单步模式，该模式下需要对每个命令进行确认
		-S	单行模式，该模式下一行就是一条 SQL 命令
	输出格式参数	-A	非对齐表格输出模式
		-F	为字段设置分隔符，用于不整齐的输出，缺省为"|"
		-H	HTML表格输出模式
		-P	设置将变量打印到参数的选项(查阅 \pset 命令)
		-R	为不整齐的输出设置行分隔符(默认：换行符号)
		-t	只打印记录
		-T	设定HTML表格标记属性（例如,宽度,边界)
		-x	打开扩展表格输出
		-z	为不整齐的输出设置字段分隔符为字节0
		-O	为不整齐的输出设置行分隔符为字节0

2. 元命令

功能	命令	命令作用
获取帮助	\? [commands]	显示元命令帮助说明
	\? options	显示psql命令的帮助信息
	\? variables	显示psql变量的帮助信息
	\h [commands]	显示SQL命令的帮助信息
一般性	\copyright	显示PostgreSQL的发行和使用条款
	\g [file]	执行命令，然后将结果输出到文件
	\gset [PREFIX]	执行命令，然后以psql变量存储结果
	\q	退出psql
	\watch [sec]	每个[SEC]秒，重复执行sql命令
查询	\d[S+]	列出当前数据库中的表、视图、序列
	\da[S]	列出聚合函数
	\db[+]	列出表空间
	\dc[S+]	列出字符转码函数
	\dC[+]	列出类型转换函数
	\dd[S]	列出对象描述
	\ddp	列出缺省权限
	\dD[S+]	列出域
	\det[+]	列出foreign table
	\des[+]	列出foreign server
	\deu[+]	列出用户映射
	\dew[+]	列出foreign data wrapper
	\df[antw][S+]	列出[聚合/常规/触发器/窗口]函数
	\dF[+]	列出文本检索配置
	\dFd[+]	列出文本检索字典
	\dFp[+]	列出文件检索解析器
	\dFt[+]	列出文件检索模板
	\dg[+]	列出角色
	\di[S+]	列出索引
查询	\dl	列出大对象
	\dL[S+]	列出所有过程语言
	\dm[S+]	列出所有物化视图
	\dn[S+]	列出所有模式
	\do[S]	列出所有操作符
	\dO[S+]	列出所有排序规则
	\dp	列出表、视图、序列的访问权限
	\drds	列出每个数据库的角色定义
	\ds[S+]	列出序列
	\dt[S+]	列出表
	\dT[S+]	列出数据类型
	\du[S+]	列出角色
	\dv[S+]	列出视图
	\dE[S+]	列出外部表
	\dx[+]	列出扩展
	\dy	列出事件触发器
	\l[+]	列出数据库
	\sf[+]	列出指定函数的定义
	\z	等同于\dp
连接	\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-]	在psql下，以指定用户连接到指定端口服务器的数据库
	\encoding [ENCODING]	设置客户端编码
	\password [USERNAME]	安全的设置用户密码
	\conninfo	显示当前连接的相关信息
操作系统	\cd [DIR]	切换工作目录
	\setenv NAME [VALUE]	设置/清空环境变量
	\timing [on|off]	是否开启命令计时命令
	\! [COMMAND]	执行操作系统命令
内置变量	\prompt [TEXT] NAME	提示用户设置内置变量
	\set [NAME [VALUE]]	设置内置变量
	\unset NAME	重置变量
输入输出	\copy ...	执行copy命令，将数据流发送到客户端主机
	\echo [STRING]	标准屏幕输出指定字符串
	\i FILE	执行操作系统脚本
	\ir FILE	与\i
	\o [FILE]	将查询结果输出到指定文件
	\qecho [STRING]	用法类似\o，将字符串写到查询输出流
缓存区	\e [FILE] [LINE]	使用外部编辑器编辑查询缓存区(或文件)
	\ef [FUNCNAME [LINE]]	使用外部编辑器编辑函数定义
	\p	显示查询缓存区的内容
	\r	重置(清除)查询缓存区
	\s [FILE]	显示历史记录或将历史记录保存在文件中
	\w FILE	将查询缓存区的内容写入文件
输出格式	\a	在非对齐模式和对齐模式之间切换
	\C [STRING]	设置表的标题，如果没有的标题就取消
	\f [STRING]	显示或设定非对齐模式查询输出的字段分隔符
	\H	切换为HTML输出模式 (缺省关闭)
	\pset [NAME [VALUE]]	设置表输出选项
	\t [on|off]	只显示记录 (缺省关闭)
	\T [STRING]	设置HTML <表格>标签属性, 如果没有的话取消设置
	\x [on|off|auto]	切换扩展输出模式(缺省关闭)

 

参考

PGCE课程《psql使用》

psql元素周期表