第2章 客户端工具
本章将介绍PostgreSQL客户端工具，例如pgAdmin和psql。pgAdmin是一款功能丰富、开源免费的PostgreSQL图形化客户端工具，psql是PostgreSQL自带的命令行客户端工具，功能全面，是PostgreSQL数据库工程师必须熟练掌握的命令行工具之一，本章将会详细介绍它的独特之处。
2.1 pgAdmin 4简介
pgAdmin是最流行的PostgreSQL图形化客户端工具，项目主页为：https://www.pgadmin.org/，由于pgAdmin 4工具使用比较简单，这里仅简单介绍。
2.1.1 pgAdmin 4 安装
pgAdmin支持Linux、Unix、Mac OS X和Windows，由于编写此书时pgAdmin最新的大版本为4，后面提到pgAdmin时我们都称为pgAdmin 4，本节以在Windows 7上安装pgAdmin 4为例，简单介绍pgAdmin 4。
安装包下载地址为：https://www.postgresql.org/ftp/pgadmin/pgadmin4/v1.6/windows/，下载完后根据提示安装即可，安装完打开pgAdmin 4的界面如图2-1所示。
图2-1 pgAdmin 4 界面
2.1.2 pgAdmin 4 使用
pgAdmin 4的使用非常简单，这一小节将演示如何使用pgAdmin 4连接PostgreSQL数据库以及日常数据库操作。
1．pgAdmin 4连接数据库
打开pgAdmin 4界面并创建服务，填写界面上的表单，如图2-2所示。
图2-2 使用pgAdmin 4 连接数据库
图General界面用于配置数据库连接别名，这里配置成db1, Connection配置页完成之后连接数据库如图2-3所示。
图2-3 使用pgAdmin 4 连接数据库
2．pgAdmin 4查询工具的使用
在pgAdmin 4面板上点击Tools菜单中的Query Tool，在弹出的窗口中可以进行日常的数据库DDL、DML操作，例如创建一张测试表，如图2-4所示。
图2-4 使用pgAdmin 4创建表
以上演示了使用pgAdmin 4连接数据库并创建测试表。由于篇幅有限，创建函数、序列、视图、DDL等操作这里不再演示。
3．用pgAdmin 4显示统计信息
pgAdmin 4具有丰富的监控功能，如图2-5所示，显示了数据库进程、每秒事务数、记录数变化等相关信息。
图2-5 使用pgAdmin 4 显示数据库统计信息
pgAdmin 4工具先介绍到这里，pgAdmin 4其他图形化功能读者可自行测试。
2.2 psql功能及应用
psql是PostgreSQL自带的命令行客户端工具，具有非常丰富的功能，类似于Oracle命令行客户端工具sqlplus，这一节将介绍psql常用功能和少数特殊功能，熟练掌握psql能便捷处理PostgreSQL日常维护工作。
2.2.1 使用psql连接数据库
用psql连接数据库非常简单，可以在数据库服务端执行，也可以远程连接数据库，在数据库服务端连接本地库示例如下所示：
[postgres@pghost1~]$ psql postgres postgres
psql (10.0)
Type "help" for help.
postgres=#
psql后面的第一个postgres表示库名，第二个postgres表示用户名，端口号默认使用变量$PGPORT配置的数据库端口号，这里是1921端口，为了后续演示方便，创建一个测试库mydb，归属为用户pguser，同时为mydb库分配一个新表空间tbs_mydb，如下所示：
--创建用户
postgres=# CREATE ROLE pguser WITH ENCRYPTED PASSWORD 'pguser';
CREATE ROLE
--创建表空间目录
[postgres@pghost1~]$ mkdir -p /database/pg10/pg_tbs/tbs_mydb
--创建表空间
postgres=# CREATE TABLESPACE tbs_mydb OWNER pguser LOCATION '/database/pg10/pg_
tbs/tbs_mydb';
CREATE TABLESPACE
--创建数据库
postgres=# CREATE DATABASE mydb
WITH OWNER = pguser
TEMPLATE = template0
ENCODING = 'UTF8'
TABLESPACE = tbs_mydb;
CREATE DATABASE
--赋权
GRANT ALL ON DATABASE mydb TO pguser WITH GRANT OPTION;
GRANT ALL ON TABLESPACE tbs_mydb TO pguser;
CREATE DATABASE命令中的OWNER选项表示数据库属主，TEMPLATE表示数据库模板，默认有template0和template1模板，也能自定义数据库模板，ENCODING表示数据库字符集，这里设置成UTF8, TABLESPACE表示数据库的默认表空间，新建数据库对象将默认创建在此表空间上，通过psql远程连接数据库的语法如下所示：
psql [option...] [dbname [username]]
服务器pghost1的IP为192.168.28.74, pghost2的IP为192.168.28.75，在pghost2主机上远程连接pghost1上的mydb库命令如下：
[postgres@pghost2~]$ psql -h 192.168.28.74-p 1921 mydb pguser
Password for user pguser:
psql (10.0)
Type "help" for help.
断开psql客户端连接使用\q元命令或CTRL+D快捷键即可，如下所示：
[postgres@pghost1~]$ psql mydb pguser
psql (10.0)
Type "help" for help.
mydb=> \q
下一小节将详细介绍psql支持的元命令。
2.2.2 psql元命令介绍
psql中的元命令是指以反斜线开头的命令，psql提供丰富的元命令，能够便捷地管理数据库，比如查看数据库对象定义、查看数据库对象占用空间大小、列出数据库各种对象名称、数据导入导出等，比如查看数据库列表，如下所示：
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
mydb | postgres | UTF8 | C | C | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | pguser=C/postgres
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
1．\db查看表空间列表
使用元命令\db查看表空间，如下所示：
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+---------+----------------------------------
pg_default | postgres |
pg_global | postgres |
tbs_mydb | pguser | /database/pg10/pg_tbs/tbs_mydb
(3 rows)查看表定义
2．\d查看表定义
先创建一张测试表，如下所示：
mydb=> CREATE TABLE test_1(id int4, name text,
create_time timestamp without time zone default clock_timestamp());
CREATE TABLE
mydb=> ALTER TABLE test_1 ADD PRIMARY KEY (id);
ALTER TABLE
generate_series函数产生连续的整数，使用这个函数能非常方便地产生测试数据，查看表test_1定义只需要执行元命令\d后接表名，如下所示：
mydb=> \d test_1
Table "pguser.test_1"
Column | Type | Collation | Nullable | Default
-----------+----------------------------+-----------+----------+-----------------
id | integer | | not null |
name | text | | |
create_time| timestamp without time zone | | | clock_timestamp()
Indexes:
"test_1_pkey" PRIMARY KEY , btree (id)
3．查看表、索引占用空间大小
给测试表test_1插入500万数据，如下所示：
mydb=> INSERT INTO test_1(id, name)
SELECT n, n || '_francs'
FROM generate_series(1,5000000) n;
INSERT 05000000
查看表大小执行\dt+后接表名，如下所示：
mydb=> \dt+ test_1
List of relations
Schema | Name | Type | Owner | Size | Description
-------+--------+-------+--------+--------+-------------
pguser | test_1 | table | pguser | 287 MB |
(1 row)
查看索引大小执行\di+后接索引名，如下所示：
mydb=> \di+ test_1_pkey
List of relations
Schema | Name | Type | Owner | Table | Size | Description
-------+-------------+-------+--------+--------+--------+-------------
pguser | test_1_pkey | index | pguser | test_1 | 107 MB |
(1 row)
4．\sf查看函数代码
元命令\sf后接函数名可查看函数定义，如下所示：
mydb=> \sf random_range
CREATE OR REPLACE FUNCTION pguser.random_range(integer, integer)
RETURNS integer
LANGUAGE sql
AS $function$
SELECT ($1 + FLOOR(($2- $1 + 1) ＊ random() ))::int4;
$function$
上述\sf命令后面可以只接函数的名称，或者函数名称及输入参数类型，例如random_range(integer, integer), PostgreSQL支持名称相同但输入参数类型不同的函数，如果有同名函数，\sf必须指定函数的参数类型。
5．\x设置查询结果输出
使用\x可设置查询结果输出模式，如下所示：
mydb=> SELECT ＊ FROM test_1 LIMIT 1;
id | name | create_time
-------+----------+---------------------------
1 | 1_pguser | 2017-07-22 11:16:15.97559
(1 row)
mydb=> \x
Expanded display is on.
mydb=> SELECT ＊ FROM test_1 LIMIT 1;
-[ RECORD 1 ]--------------------------
id | 1
name | 1_francs
create_time | 2017-07-22 11:16:15.97559
6．获取元命令对应的SQL代码
psql提供的元命令实质上向数据库发出相应的SQL查询，当使用psql连接数据库时，-E选项可以获取元命令的SQL代码，如下所示：
[postgres@pghost1~]$ psql -E mydb pguser
psql (10.0)
Type "help" for help.
mydb=> \db
＊＊＊＊＊＊＊＊＊ QUERY ＊＊＊＊＊＊＊＊＊＊
SELECT spcname AS "Name",
pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
ORDER BY 1;
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
List of tablespaces
Name | Owner | Location
---------------+----------+----------------------------------
pg_default | postgres |
pg_global | postgres |
tbs_mydb | pguser | /database/pg10/pg_tbs/tbs_mydb
(3 rows)
7．\？元命令
PostgreSQL支持的元命令很多，当忘记具体的元命令名称时可以查询手册，另一种便捷的方式是执行\？元命令列出所有的元命令，如下所示：
mydb=> \?
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)
\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
\？元命令可以迅速列出所有元命令以及这些元命令的说明及语法，给数据库维护管理带来很大的便利。
8．便捷的HELP命令
psql的HELP命令非常方便，使用元命令\h后接SQL命令关键字能将SQL命令的语法列出，对日常的数据库管理工作带来了极大的便利，例如\h CREATE TABLESPACE能显示此命令的语法，如下所示：
postgres=# \h CREATE TABLESPACE
Command: CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
[ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
LOCATION 'directory'
[ WITH ( tablespace_option = value [, ... ] ) ]
\h元命令后面不接任何SQL命令则会列出所有的SQL命令，为不完全记得SQL命令语法时提供了检索的途径。
2.2.3 psql导入、导出表数据
psql支持文件数据导入到数据库，也支持数据库表数据导出到文件中。COPY命令和\copy命令都支持这两类操作，但两者有以下区别：
1）COPY命令是SQL命令，\copy是元命令。
2）COPY命令必须具有SUPERUSER超级权限（将数据通过stdin、stdout方式导入导出情况除外），而\copy元命令不需要SUPERUSER权限。
3）COPY命令读取或写入数据库服务端主机上的文件，而\copy元命令是从psql客户端主机读取或写入文件。
4）从性能方面看，大数据量导出到文件或大文件数据导入数据库，COPY比\copy性能高。
1．使用COPY命令导入导出数据
先来看看COPY命令如何将文本文件数据导入到数据库表中，首先在mydb库中创建测试表test_copy，如下所示：
mydb=> CREATE TABLE test_copy(id int4, name text);
CREATE TABLE
之后编写数据文件test_copy_in.txt，字段分隔符用TAB键，也可设置其他分隔符，导入时再指定已设置的字段分隔符。test-copy-in.txt文件如下所示：
[pg10@pghost1 script]$ cat test_copy_in.txt
1 a
2 b
3 c
之后以postgres用户登录mydb库，并将test_copy_in.txt文件中的数据导入到test_copy表中。导入命令如下所示：
[pg10@pghost1 script]$ psql mydb postgres
psql (10.0)
Type "help" for help.
mydb=# COPY pguser.test_copy FROM '/home/postgres/script/test_copy_in.txt';
COPY 3
mydb=# SELECT ＊ FROM pguser.test_copy ;
id | name
-------+------
1 | a
2 | b
3 | c
(3 rows)
如果使用普通用户pguser导入文件数据，则报以下错误。
[pg10@pghost1 script]$ psql mydb pguser
psql (10.0)
Type "help" for help.
mydb=> COPY test_copy FROM '/home/postgres/script/test_copy_in.txt';
ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
报错信息示很明显，COPY命令只有超级用户才能使用，而\copy元命令普通用户即可使用。接下来演示通过COPY命令将表test_copy中的数据导出到文件，同样使用postgres用户登录到mydb库，如下所示。
[pg10@pghost1 script]$ psql mydb postgres
psql (10.0)
Type "help" for help.
mydb=# COPY pguser.test_copy TO '/home/postgres/test_copy.txt';
COPY 3
查看test_copy.txt文件，如下所示：
[postgres@pghost1~]$ cat test_copy.txt
1 a
2 b
3 c
也可以将表数据输出到标准输出，而且不需要超级用户权限，如下所示：
[postgres@pghost1~]$ psql mydb pguser
psql (10.0)
Type "help" for help.
mydb=> COPY test_copy TO stdout;
1 a
2 b
3 c
也能从标准输入导入数据到表中，有兴趣的读者自行测试。
经常有运营或开发人员要求DBA提供生产库运营数据，为了显示方便，这时需要将数据导出到csv格式。
[postgres@pghost1~]$ psql mydb postgres
psql (10.0)
Type "help" for help.
mydb=# COPY pguser.test_copy TO '/home/postgres/test_copy.csv' WITH csv header;
COPY 4
上述命令中的with csv header是指导出格式为csv格式并且显示字段名称，以csv为后缀的文件可以使用office excel打开。以上数据导出示例都是基于全表数据导出的，如何仅导出表的一部分数据呢？如下代码仅导出表test_copy中ID等于1的数据记录。
mydb=# COPY (SELECT ＊ FROM pguser.test_copy WHERE id=1) TO '/home/postgres/1.txt';
COPY 1
mydb=# \q
[postgres@pghost1~]$ cat 1.txt
1 a
关于COPY命令更多说明详见手册https://www.postgresql.org/docs/10/static/sql-copy.html。
2．使用\copy元命令导入导出数据
COPY命令是从数据库服务端主机读取或写入文件数据，而\copy元命令从psql客户端主机读取或写入文件数据，并且\copy元命令不需要超级用户权限，下面在pghost2主机上以普通用户pguser远程登录pghost1主机上的mydb库，并且使用\copy元命令导出表test_copy数据，如下所示：
[postgres@pghost2~]$ psql -h 192.168.28.74-p 1921 mydb pguser
Password for user pguser:
psql (10.0)
Type "help" for help.
mydb=> \copy test_copy to '/home/postgres/test_copy.txt';
COPY 3
查看test_copy.txt文件，数据已导出，如下所示：
[postgres@pghost2~]$ cat test_copy.txt
1 a
2 b
3 c
\copy导入文件数据和copy命令类似，首先编写test_copy_in.txt文件，如下所示：
[postgres@pghost2~]$ cat test_copy_in.txt
4 d
使用\copy命令导入文本test_copy_in.txt数据，如下所示：
[postgres@pghost2~]$ psql -h 192.168.28.74-p 1921 mydb pguser
Password for user pguser:
psql (10.0)
Type "help" for help.
mydb=> \copy test_copy from '/home/postgres/test_copy_in.txt';
COPY 1
mydb=> SELECT ＊ FROM test_copy WHERE id=4;
id | name
-------+------
4 | d
(1 row)
没有超级用户权限的情况下，需要导出小表数据，通常使用\copy元命令，如果是大表数据导入导出操作，建议在数据库服务器主机使用COPY命令，效率更高。
2.2.4 psql的语法和选项介绍
psql连接数据库语法如下：
psql [option...] [dbname [username]]
其中dbname指连接的数据库名称，username指登录数据库的用户名，option有很多参数选项，这节列出重要的参数选项。
1．-A设置非对齐输出模式
psql执行SQL的输出默认是对齐模式，例如：
[postgres@pghost1~]$ psql -c "SELECT ＊ FROM user_ini WHERE id=1" mydb pguser
id | user_id | user_name | create_time
-------+---------+-----------+-------------------------------
1 | 186536 | KTU89H | 2017-08-05 15:59:25.359148+08
(1 row)
--注意返回结果，这里有空行
注意以上输出，格式是对齐的，psql加上-A选项如下所示：
[postgres@pghost1~]$ psql -A -c "SELECT ＊ FROM user_ini WHERE id=1" mydb pguser
id|user_id|user_name|create_time
1|186536|KTU89H|2017-08-05 15:59:25.359148+08
(1 row) --注意返回结果，没有空行
加上-A选项后以上输出的格式变成不对齐的了，并且返回结果中没有空行，接着看-t选项。
2．-t只显示记录数据
另一个psql重要选项参数为-t, -t参数设置输出只显示数据，而不显示字段名称和返回的结果集行数，如下所示：
[postgres@pghost1~]$ psql -t -c "SELECT ＊ FROM user_ini WHERE id=1" mydb pguser
1 | 186536 | KTU89H | 2017-08-05 15:59:25.359148+08
--注意返回结果，这里有空行
注意以上结果，字段名称不再显示，返回的结果集行数也没有显示，但尾部仍然有空行，因此-t参数通常和-A参数结合使用，这时仅返回数据本身，如下所示：
[postgres@pghost1~]$ psql -At -c "SELECT ＊ FROM user_ini WHERE id=1" mydb pguser
1|186536|KTU89H|2017-08-05 15:59:25.359148+08
以上结果仅返回了数据本身，在编写shell脚本时非常有效，特别是只取一个字段的时候，如下所示：
[postgres@pghost1~]$ psql -At -c "SELECT user_name FROM user_ini WHERE id=1" mydb
pguser
KTU89H
3．-q不显示输出信息
默认情况下，使用psql执行SQL命令时会返回多种消息，使用-q参数后将不再显示这些信息，下面通过一个例子进行演示，首先创建test_q.sql，并输入以下SQL：
DROP TABLE if exists test_q;
CREATE TABLE test_q(id int4);
TRUNCATE TABLE test_q;
INSERT INTO test_q values (1);
INSERT INTO test_q values (2);
执行脚本test_q.sql，如下所示：
[postgres@pghost1~]$ psql mydb pguser -f test_q.sql
DROP TABLE
CREATE TABLE
TRUNCATE TABLE
INSERT 0 1
INSERT 0 1
执行脚本test_q.sql后返回了大量信息，加上-q参数后，这些信息不再显示，如下所示：
[postgres@pghost1~]$ psql -q mydb pguser -f test_q.sql
--这里不再显示输出信息
-q选项通常和-c或-f选项使用，在执行维护操作过程中，当输出信息不重要时，这个特性非常有用。
2.2.5 psql执行sql脚本
psql的-c选项支持在操作系统层面通过psql向数据库发起SQL命令，如下所示：
[postgres@pghost1~]$ psql -c "SELECT current_user; "
current_user
--------------
postgres
(1 row)
-c后接执行的SQL命令，可以使用单引号或双引号，同时支持格式化输出，如果想仅显示命令返回的结果，psql加上-At选项即可，上一小节也有提到，如下所示：
[postgres@pghost1~]$ psql -At -c "SELECT current_user; "
postgres
上述内容演示了在操作系统层面通过psql执行SQL命令，那么如何导入数据库脚本文件呢？首先编写以下文件，文件名称为test_2.sql：
CREATE TABLE test_2(id int4);
INSERT INTO test_2 VALUES (1);
INSERT INTO test_2 VALUES (2);
INSERT INTO test_2 VALUES (3);
通过-f参数导入此脚本，命令如下：
[postgres@pghost1~]$ psql mydb pguser -f script/test_2.sql
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
以上命令的输出结果没有报错，表示文件中所有SQL正常导入。
注意
psql的-single-transaction或-1选项支持在一个事务中执行脚本，要么脚本中的所有SQL执行成功，如果其中有SQL执行失败，则文件中的所有SQL回滚。
2.2.6 psql如何传递变量到SQL
如何通过psql工具将变量传递到SQL中？例如以下SQL：
SELECT ＊ FROM table_name WHERE column_name = 变量；
下面演示两种传递变量的方式。
1 ．\set元命令方式传递变量
\set元子命令可以设置变量，格式如下所示，name表示变量名称，value表示变量值，如果不填写value，变量值为空。
\set name value
test_copy表有四条记录，设置变量v_id值为2，查询id值等于2的记录，如下所示：
mydb=> \set v_id 2
mydb=> SELECT ＊ FROM test_copy WHERE id=:v_id;
id | name
-------+------
2 | b
(1 row)
如果想取消之前变量设置的值，\set命令后接参数名称即可，如下所示：
mydb=> \set v_id
通过\set元命令设置变量的一个典型应用场景是使用pgbench进行压力测试时使用\set元命令为变量赋值。
2．psql的-v参数传递变量
另一种方法是通过psql的-v参数传递变量，首先编写select_1.sql脚本，脚本内容如下所示：
SELECT ＊ FROM test_3 WHERE id=:v_id;
通过psql接-v传递变量，并执行脚本select_1.sql，如下所示：
[postgres@pghost1~]$ psql -v v_id=1 mydb pguser -f select_1.sql
id | name
-------+------
1 | a
(1 row)
以上设置变量v_id值为1。
2.2.7 使用psql定制日常维护脚本
编写数据库维护脚本以提高数据库排障效率是DBA的工作职责之一，当数据库异常时，能够迅速发现问题并解决问题将为企业带来价值，当然数据库的健康检查和监控也要加强。这里主要介绍通过psql元命令定制日常维护脚本，预先将常用的数据库维护脚本配置好，数据库排障时直接使用，从而提高排障效率。
1．定制维护脚本：查询活动会话
先来介绍．psqlrc文件，如果psql没有带-X选项，psql尝试读取和执行用户~/.psqlrc启动文件中的命令，结合这个文件能够方便地预先定制维护脚本，例如，查看数据库活动会话的SQL如下所示：
SELECT pid, usename, datname, query, client_addr
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() AND state='active' ORDER BY query;
pg_stat_activity视图显示PostgreSQL进程信息，每一个进程在视图中存在一条记录，pid指进程号，usename指数据库用户名称，datname指数据库名称，query显示进程最近执行的SQL，如果state值为active则query显示当前正在执行的SQL, client_addr是进程的客户端IP, state指进程的状态，主要值为：
❑ active：后台进程正在执行SQL。
❑ idle：后台进程为空闲状态，等待后续客户端发出命令。
❑ idle in transaction：后台进程正在事务中，并不是指正在执行SQL。
❑ idle in transaction (aborted)：和idle in transaction状态类似，只是事务中的部分SQL异常。
关于此视图更多信息请参考手册https://www.postgresql.org/docs/10/static/monitoring-stats.html#pg-stat-activity-view。
首先找到~/.psqlrc文件，如果没有此文件则手工创建，编写以下内容，注意\set这行命令和SQL命令在一行中编写。
--查询活动会话
\set active_session 'select pid, usename, datname, query, client_addr 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
----------+---------+---------+-----------------------------------------------------+
14351 | pguser | mydb | update test_per1 set create_time=now() WHERE id=$1; |
14352 | pguser | mydb | update test_per1 set create_time=now() WHERE id=$1; |
14353 | pguser | mydb | update test_per1 set create_time=now() WHERE id=$1; |
14354 | pguser | mydb | update test_per1 set create_time=now() WHERE id=$1; |
14355 | pguser | mydb | update test_per1 set create_time=now() WHERE id=$1; |
(5 rows)
通过以上设置，数据库排障时不需要临时手工编写查询活动会话的SQL，只需输入：active_session即可，方便了日常维护操作。
2．定制维护脚本：查询等待事件
PostgreSQL也有等待事件的概念，对于问题诊断有较大参考作用，查询等待事件SQL如下所示：
SELECT pid, usename, datname, query, client_addr, 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;
同样，通过\set元命令将上述代码追加到~/.psqlrc文件，注意\set命令和SQL命令在同一行中编写，如下所示：
--查看会话等待事件
\set wait_event 'select pid, usename, datname, query, client_addr, 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;
之后，重新连接数据库，执行wait_event命令，冒号后接变量名即可，如下所示：
postgres=# :wait_event
pid | usename | datname| query | client_addr | wait_event_type | wait_event
---------+----------+--------+-------+------------+---------------+-------------------
2652 | | | | | Activity | AutoVacuumMain
2655 | postgres | | | | Activity | LogicalLauncherMain
2650 | | | | | Activity | BgWriter Hibernate
2649 | | | | | Activity | CheckpointerMain
2651| | | | | Activity | WalWriterMain
(5 rows)
以上介绍了查询活动会话和会话等待事件，其他维护脚本可根据实际情况定制，比如查看数据库连接数，如下所示：
--查看数据库连接数
\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; '
通过元命令\set变量定制维护脚本只能在一定程度上方便数据库日常维护操作，工具化的监控工具不能少，比如Zabbix或Nagios，这些监控工具可以非常方便地定制数据库各维度监控告警，并以图表形式展现性能数据。
2.2.8 psql亮点功能
psql还有其他非常突出的功能，比如显示SQL执行时间、反复执行当前SQL、自动补全、历史命令上下翻动、客户端提示符等，这节主要介绍psql的这些常用的亮点功能。
1．\timing显示SQL执行时间
\timing元命令用于设置打开或关闭显示SQL的执行时间，单位为毫秒，例如：
mydb=> \timing
Timing is on.
mydb=> SELECT count(＊) FROM user_ini;
count
---------
1000000
(1 row)
Time: 47.114 ms
以上显示count语句的执行时间为47.114毫秒，这个特性在调试SQL性能时非常有用，如果需要关闭这个选项，再次执行\timing元命令即可，如下所示：
mydb=> \timing
Timing is off.
2．\watch反复执行当前SQL
\watch元命令会反复执行当前查询缓冲区的SQL命令，直到SQL被中止或执行失败，语法如下：
\watch [ seconds ]
seconds表示两次执行间隔的时间，以秒为单位，默认为2秒，例如，每隔一秒反复执行now()函数查询当前时间：
mydb=> SELECT now();
now
-------------------------------
2017-08-14 11:20:02.157567+08
(1 row)
mydb=> \watch 1
Mon 14 Aug 2017 11:20:04 AM CST (every 1s)
now
-------------------------------
2017-08-14 11:20:04.299584+08
(1 row)
Mon 14 Aug 2017 11:20:05 AM CST (every 1s)
now
-------------------------------
2017-08-14 11:20:05.300991+08
以上设置是每秒执行一次now()命令。
3．Tab键自动补全
psql对Tab键自动补全功能的支持是一个很赞的特性，能够在没有完全记住数据库对象名称或者SQL命令语法的情况下使用，帮助用户轻松地完成各项数据库维护工作。例如，查询mydb库中某个test打头的表，但不记得具体表名，可以输入完test字符后按Tab键，psql会提示以字符test打头的表，如下所示：
mydb=> SELECT ＊ FROM test_
test_1 test_2 test_copy test_per1
mydb=> SELECT ＊ FROM test_
DDL也是支持Tab键自动补全的，如下所示：
mydb=> ALTER TABLE test_1 DROP CO
COLUMN CONSTRAINT
mydb=> ALTER TABLE test_1 DROP CO
4．支持箭头键上下翻历史SQL命令
psql支持箭头键上下翻历史SQL命令，非常方便，如下所示：
[postgres@pghost1~]$ psql mydb pguser
psql (10.0)
Type "help" for help.
mydb=> SELECT count(＊) FROM pg_stat_activity ; --这里使用箭头键上下翻历史命令
想要psql支持箭头键上下翻历史SQL命令，在编译安装PostgreSQL时需打开readline选项，这个选项在编译PostgreSQL时默认打开，也可以在编译时加上--without-readline选项关闭readline，但不推荐。
5．psql客户端提示符
以下命令显示了psql客户端提示符，“postgres=#”是默认的客户端提示符：
[postgres@pghost1~]$ psql
psql (10.0)
Type "help" for help.
postgres=#
用户可根据喜好设置psql客户端提示符，psql提供一系列选项供用户选择并设置，常用选项如下：
❑ %M：数据库服务器别名，不是指主机名，显示的是psql的-h参数设置的值；当连接建立在Unix域套接字上时则是[local]。
❑ %>：数据库服务器的端口号。
❑ %n：数据库会话的用户名，在数据库会话期间，这个值可能会因为命令SET SESSION AUTHORIZATION的结果而改变。
❑ %/：当前数据库名称。
❑ %#：如果是超级用户则显示“#”，其他用户显示“>”，在数据库会话期间，这个值可能会因为命令SET SESSION AUTHORIZATION的结果而改变。
❑ %p：当前数据库连接的后台进程号。
❑ %R：在PROMPT1中通常显示“=”，如果进程被断开则显示“! ”。
上面仅介绍了主要的提示符，后面的演示示例可参考以上选项的解释。先来看psql客户端默认prompt1的配置，如下所示：
[postgres@pghost1~]$ psql
psql (10.0)
Type "help" for help.
postgres=# \echo :PROMPT1
%/%R%#
元命令\echo是指显示变量值，PROMPT1是系统提示符的变量，PROMPT1是指当psql等待新命令发出时的常规提示符，它的默认设置为%/%R%#，根据以上选项参数的解释很容易理解%/指当前数据库名称postgres, %R指显示字符“=”, %#显示字符“#”，下面看下%M选项，在数据库服务器主机通过Unix套接字连接，并设置PROMPT1值为%M%R%#，如下所示：
[postgres@pghost1~]$ psql
psql (10.0)
Type "help" for help.
postgres=# \set PROMPT1 '%M%R%#'
[local]=#
这时，psql客户端显示[local]，接下来在pghost2主机上远程连接pghost1上的库测试，并设置PROMPT1变量值为“%M%R%#”，如下所示：
[postgres@pghost2~]$ psql -h 192.168.28.74 mydb pguser -p 1921
Password for user pguser:
psql (10.0)
Type "help" for help.
mydb=> \set PROMPT1 '%M%R%#'
192.168.28.74=>
从上面看到，设置PROMPT1变量值为“%M%R%#”字符后，显示了192.168.28.74的IP地址，正好为psql参数-h的值。接下来演示稍复杂点的设置，如下所示：
[postgres@pghost2~]$ psql -h 192.168.28.74 mydb pguser -p 1921
Password for user pguser:
psql (10.0)
Type "help" for help.
mydb=> \set PROMPT1 '%/@%M:%>%R%#'
mydb@192.168.28.74:1921=>
这里将PROMPT1设置成“%/@%M:%>%R%#”, “%>”是指数据库端口号，其他选项之前已介绍过，根据实践也非常好理解，设置好PROMPT1的格式后，可以将PROMPT1的设置命令写到客户端主机操作系统用户家目录的．psqlrc文件中，关于．psqlrc文件在2.2.7节中有详细介绍，在客户端主机操作系统用户家目录创建．psqlrc文件并写入以下代码，如下所示：
[postgres@pghost2~]$ touch .psqlrc
[postgres@pghost2~]$ vim .psqlrc
\set PROMPT1 '%/@%M:%>%R%#'
再次登录验证，代码已生效，如下所示：
[postgres@pghost2~]$ psql -h 192.168.28.74 mydb pguser -p 1921
Password for user pguser:
psql (10.0)
Type "help" for help.
mydb@192.168.28.74:1921=>
用户可根据自己的喜好设置PROMPT1并写入．psqlrc文件，psql连接数据库时会读取．psqlrc文件并执行里面的命令。
提示
psql默认有三个提示符：PROMPT1、PROMPT2、PROMPT3, PROMPT1是指当psql等待新命令发出时的常规提示符，这个提示符使用得最多；PROMPT2是指在命令输入过程中等待更多输入时发出的提示符，例如当命令没有使用分号终止或者引用没有被关闭时就会发出这个提示符，PROMPT2的默认设置值与PROMPT1一样；PROMPT3指在运行一个SQL COPY FROM STDIN命令并且需要在终端上输入一个行值时发出的提示符。
2.3 本章小结
本章介绍了PostgreSQL客户端连接工具pgAdmin 4和psql命令行工具，其中重点介绍了psql命令行工具的强大功能。通过学习本章内容，读者一方面了解到pgAdmin 4的基本用法，另一方面了解到psql工具的主要功能，比如元命令、数据导入导出、执行SQL脚本、带参数执行脚本、定制维护脚本等，熟练掌握psql能够提高数据库管理工作效率，对本书后续核心篇、进阶篇章节的阅读奠定了基础。