psql语法和选项介绍

2.2.4 psql语法和选项介绍

[postgres@otter3 ~]$ psql --help
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
DBNAME指数据名称,USERNAME指用户名,OPTION有很多选项,以下看重点参数：
1、-A设置非对齐输出模式
  psql执行SQL的输出默认对齐方式,如下：其中tt是数据库名称 postgres是登录数据库用户名
  [postgres@otter3 ~]$ psql -c "select * from company_01 where id = 1"   tt postgres
   id | name
   ----+------
   1 | a
   (1 row)
  -- 返回结果这里有空行

  注意以上输出格式是对齐的,psql 加上-A 选项如下所示:
  [postgres@otter3 ~]$ psql -A -c "select * from company_01 where id = 1" tt postgres
  id|name
  1|a
  (1 row) -- 返回结果这里没有空行
  加上-A后输出格式变得不对齐了,并且返回结果中没有空行,接着看-t选项

2、-t只显示数据,不显示表头和返回行数,如下所示:
  [postgres@otter3 ~]$ psql -t -c "select * from company_01 where id = 1" tt postgres
  1 | a
  -- 返回结果这里有空行

  注意以上结果,表头名称字段不在显示,返回结果也不显示返回行数,并且返回结果有空行,因此加上选项-At
  [postgres@otter3 ~]$ psql -At -c "select * from company_01 where id   = 1" tt postgres
  1|a
  以上结果不仅返回了数据本身,在编写shell脚本时非常有效,尤其是只取一个字段时:
  [postgres@otter3 ~]$ psql -At -c "select name from company_01 where id = 1" tt postgres
  a

3、-q不显示输出信息
  默认情况下psql执行命令是会返回多种信息,使用-q参数后将不显示这些信息,下面通过例子演示,首先编写脚本test.sql并输出如下内容:
  drop table if exists cc;
  create table cc(id int4,name varchar(10));
  truncate table cc;
  insert into cc(id,name) values(1,'a');
  insert into cc(id,name) values(2,'b');

  执行脚本test.sql输出一下内容:
  [postgres@otter3 scripts]$ psql -f test.sql
   DROP TABLE
  CREATE TABLE
  TRUNCATE TABLE
  INSERT 0 1
  INSERT 0 1

  可以看到执行完成后返回了大量的信息,加上-q参数后,这些信息不再显示,如下:
  [postgres@otter3 scripts]$ psql -q -ftest.sql -- 直接执行,没有任何信息输出

  -q选项通常和-c和-f一起使用,在维护操作中非常有用,当输出信息不重要时,这个特性非常重要.

2.2.5 psql执行SQL脚本

  psql的-c选项支持在操作系统层面通过psql向数据库发起SQL命令,如下命令:
  [postgres@otter3 scripts]$ psql -c "select   current_user,current_timestamp"
  current_user | current_timestamp
  --------------+-------------------------------
  postgres | 2021-03-04 06:40:27.249367+08
  (1 row)

  -c后接执行SQL命令,可以用单引号或双引号都可以,同时也支持格式化输出,如果仅想输出结果,可以配合上一节说的 -At 参数:
  [postgres@otter3 scripts]$ psql -At -c "select   current_user,current_timestamp" -- 双引号
  postgres|2021-03-04 06:43:33.334065+08

  [postgres@otter3 scripts]$ psql -At -c 'select   current_user,current_timestamp' -- 单引号
  postgres|2021-03-04 06:43:09.197978+08

  上述内容说明了操作系统层面通过psql执行SQL命令,如果想导入文件可以通过-f参数导入脚本dd.sql,如下:
  准备脚本：
  create table dd(id int4,name varchar(10));
  truncate table dd;
  insert into dd(id,name) values(1,'a');
  insert into dd(id,name) values(2,'b');

  执行:
  [postgres@otter3 scripts]$ psql -f dd.sql
  CREATE TABLE
  TRUNCATE TABLE
  INSERT 0 1
  INSERT 0 1
  以上脚本执行输出结果没有报错,表明文件中SQL全部导入成功。

2.2.6 psql如何传变量到SQL

  如何通过psql工具将变量传递到SQL中？例如以下SQL:
  select * from company_01 where name = 变量;

  以下通过两种方式演示
1. \set元命令方式传递变量
  \set 元命令可以设置变量,格式如下,name表示变量名称,value表示变量值,如果不写value,变量值为空.
  \set name value
  company_01表有4条记录,设置变量v_id = 3,查询id值等于3的记录,如下:
  tt=# \set v_id 3
  tt=# select * from company_01 where id = :v_id;
  id | name
  ----+------
  3 | c
  (1 row)

  如果想取消之前参数的值,可以\set 变量名称即可,如下:
  tt=# \set v_id

  \set 使用典型场景是为参数动态赋值

2. psql的-v参数传递
  另一种方法是通过psql的-v参数传递变量,首先编辑脚本ee.sql,内容如下:
  select * from company_01 where id =:v_id;
  通过psql接-v传递变量,并且执行脚本ee.sql,如下所示:
  [postgres@otter3 scripts]$ psql -v v_id=3 tt postgres -f ee.sql
  id | name
  ----+------
  3 | c
  (1 row)
  以上变量v_id设置值为3

2.2.7 使用psql定制日常维护脚本

1、定制维护脚本:查询活动会话
  select pid,usename,datname,query,client_addr
    from pg_stat_activity
  where pid<>pg_backend_pid()
      and state='activity'
    order by query;

  pg_stat_activity视图显示PostgreSQL进程信息,每一个进程在视图中存一条记录,pid指进程号,usename指数据库用户名称,datname指数据库名称,
  query显示进程最近执行的SQL,client_addr是进程客户端IP,state指进程状态,主要值为:
  active:后台正在执行的SQL
  idle:后台进程为空闲状态,等待客户端发起请求
  idle in transaction:后台进程正在事务中,并不指正在执行的SQL
  idle in transaction(aborted):后台进程正在事务中,并不指正在执行的SQL(只是事务中的SQL异常)

  首先找到~/.psqlrc文件,如果没有则创建,写入命令(注意:\set和SQL语句要放到同一行):
-- 查询活跃会话
\set active_session 'select pid,usename,datname,query,client_addr,state from pg_stat_activity where pid<>pg_backend_pid() and state='active' order by query;'
之后,重新连接数据库,执行active_session命令,冒号后面直接变量名称,即:
postgres=# :active_session
pid | usename | datname | query | client_addr | state
-------+----------+---------+---------------------------------------------------------+-------------+--------
33428 | postgres | tt | update company_01 set name = pg_sleep(20) where id = 2; | | active
(1 row)
通过以上设置,数据库排障时不需要临时编写SQL,只需要输入:active_session 即可,方便了日常维护

2、定制维护脚本:查询等待事件
select pid,usename,datname,query,client_addr
from pg_stat_activity
where pid<>pg_backend_pid() and wait_event is not null
order by wait_event_type;

同样编辑~/.psqlrc文件,追加写入到文件:
-- 查询等待事件
\set wait_event 'select pid,usename,datname,query,client_addr from pg_stat_activity where pid<>pg_backend_pid() and wait_event is not null order by wait_event_type;'

之后,重新连接数据库,执行wait_event命令,冒号后面直接变量名称,即:

postgres=# :wait_event
pid | usename | datname | query | client_addr
-------+----------+---------+---------------------------------------------------+-------------
30681 | | | |
30684 | postgres | | |
30682 | | | |
30680 | | | |
30679 | | | |
33457 | postgres | tt | update company_01 set name = 'tt' where id = 2; |
33505 | postgres | tt | update company_01 set name = 'tt01' where id = 2; |
(7 rows

3、查询数据库连接数
select datname,usename,client_addr,count(*)
from pg_stat_activity
where pid<>pg_backend_pid()
group by 1,2,3
order by 1,2,4 desc;

同样编辑~/.psqlrc文件,追加写入到文件:
-- 查看数据库连接数
\set connections 'select datname,usename,client_addr,count(*) from pg_stat_activity where pid<>pg_backend_pid() group by 1,2,3 order by 1,2,4 desc;'

之后,重新连接数据库,执行wait_event命令,冒号后面直接变量名称,即:
postgres=# :connections
datname | usename | client_addr | count
---------+----------+-------------+-------
tt | postgres | | 2
| postgres | | 1
| | | 4
(3 rows)