第二章 psql客户端工具
pgAdmin是一款功能丰富、开源免费的PostgreSQL图形化工具。psql是PostgreSQL自带的命令行工具，功能全面，是PostgreSQL数据库工程师必须熟练掌握的命令行工具之一。
pgAdmin的下载网址是：https://www.pgadmin.org/download/
2.2 pgsql功能及应用
psql是PostgreSQL自带的命令行客户端工具，具有丰富的功能，类似于Oracle命令行客户端工具sqlplus，这一节将介绍psql常用的功能和少数特殊功能。
熟练掌握psql能便捷处理PostgreSQL日常维护工作。
2.2.1 使用psql连接数据库
用psql连接数据库非常简单，可以在数据库服务端执行，也可以远程连接数据库，在数据库服务端连接本地数据库实例所示：
[postgres@fudao_db_cluster_003 ~]$ cat ~/.bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin:/opt/pgsql/bin export PATH [postgres@fudao_db_cluster_003 ~]$ [postgres@fudao_db_cluster_003 ~]$ psql postgres postgres psql (10.0) Type "help" for help. postgres=#
psql后面的第一个postgres表示库名，第二个postgres表示用户名，端口号默认使用变量$PGPORT配置的数据库端口号，这里是1921端口，为了后续演示方便，创建一个测试库mydb，
归属为用户mydb库分配一个新表空间tbs_mydb，如下所示：
-- 创建用户 postgres=# CREATE ROLE pguser WITH ENCRYPTED PASSWORD \'pguser\'; CREATE ROLE -- 创建表空间目录 [postgres@fudao_db_cluster_003 ~]$ mkdir -p /data01/pgdata/10/pg_tbs/tbs_mydb -- 创建表空间 postgres=# CREATE TABLESPACE tbs_mydb OWNER pguser LOCATION \'/data01/pgdata/10/pg_tbs/tbs_mydb\'; CREATE TABLESPACE -- 创建数据库 CREATE DATABASE mydb WITH OWNER = pguser TEMPLATE = template0 ENCODING = \'UTF8\' TABLESPACE = \'tbs_mydb\'; -- 赋权 GRANT ALL ON DATABASE mydb TO pguser WITH GRANT OPTION; GRANT ALL ON TABLESPACE tbs_mydb TO pguser; -- 查看角色 postgres=# select rolname from pg_roles; rolname ---------------------- pg_monitor pg_read_all_settings pg_read_all_stats pg_stat_scan_tables pg_signal_backend postgres pguser (7 rows) postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- pguser | Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres=# 授权登录 postgres=# alter role pguser login; ALTER ROLE postgres=# \du
CREATE DATABASE 命令中的OWNER选项表示数据库属主，TEMPLATE表示数据库模板，默认有template0和template1模板，也能自定义数据库模板，ENCODING表示数据库字符集，
这里设置成UTF8,TABLESPACE 表示数据库的默认表空间，新建数据库对象将默认创建在此表空间上，通过psql远程连接数据库的语法如下：
psql [option...] [dbname] [username]
服务器pghost1的IP为10.192.30.60, pghost2的IP为10.192.30.59，在pghost2主机上远程连接pghost1的mydb的命令如下：
[postgres@fudao_db_cluster_002 ~]$ psql -h pghost1 -p 1921 mydb pguser Password for user pguser: psql (10.0) Type "help" for help. mydb=>
2.2.2 psql元命令介绍
pgsql中的元命令是指以反斜杠开头的命令，psql提供丰富的元命令，能够便捷地管理数据库，比如查看数据库对象定义、查看数据库对象占用空间大小，列出
数据库各种对象名称、数据导入导出等，比如查看数据库列表，如下所示：
[postgres@fudao_db_cluster_002 ~]$ psql -h pghost1 -p 1921 mydb pguser psql (10.0) Type "help" for help. mydb=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- mydb | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/pguser + | | | | | pguser=C*T*c*/pguser 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 (4 rows) mydb=> 1. \db查看表空间列表 mydb=> \db List of tablespaces Name | Owner | Location ------------+----------+----------------------------------- pg_default | postgres | pg_global | postgres | tbs_mydb | pguser | /data01/pgdata/10/pg_tbs/tbs_mydb (3 rows) mydb=>
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后接表名，如下所示： postgres=# \d test_1 Table "public.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) mydb=>
3. 查看表、索引占用空间大小
给测试表test_1插入500w数据，如下所示：
mydb=> insert into test_1(id,name) select n,n || \'_francs\' from generate_series(1,5000000) n; 2019-06-26 16:29:47.498 CST [19689] LOG: checkpoints are occurring too frequently (25 seconds apart) 2019-06-26 16:29:47.498 CST [19689] HINT: Consider increasing the configuration parameter "max_wal_size". INSERT 0 5000000 mydb=> 查看表大小执行\dt+ 后接表名，如下所示： mydb=> \dt+ test_1 List of relations Schema | Name | Type | Owner | Size | Description --------+--------+-------+--------+--------+------------- public | test_1 | table | pguser | 287 MB | (1 row) 查看索引大小执行\di+ 后接索引名，如下所示： mydb=> \di+ test_1_pkey List of relations Schema | Name | Type | Owner | Table | Size | Description --------+-------------+-------+--------+--------+--------+------------- public | test_1_pkey | index | pguser | test_1 | 107 MB | (1 row)
4. \sf 查看函数代码
5. \x 设置查询结果输出
mydb=> select * from test_1 limit 1; id | name | create_time ----+----------+---------------------------- 1 | 1_francs | 2019-06-26 16:32:36.100912 (1 row) mydb=> \x Expanded display is on. mydb=> select * from test_1 limit 1; -[ RECORD 1 ]--------------------------- id | 1 name | 1_francs create_time | 2019-06-26 16:32:36.100912 mydb=>
6. 获取元数据对应的SQL代码
psql提供的元命令实质上向数据库发出相应的SQL查看，当使用psql连接数据库时，-E选项可以获取元命令的SQL代码，如下所示：
[postgres@fudao_db_cluster_002 ~]$ psql -h pghost1 -p 1921 mydb pguser -E Password for user 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 | /data01/pgdata/10/pg_tbs/tbs_mydb (3 rows) mydb=>
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的语法列出，对日常的数据库管理工作带来极大的便利，例如：
mydb=> \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 [, ... ] ) ] mydb=>
2.2.3 psql导入、导出表数据
psql支持文件数据导入到数据，也支持数据表数据导出到文件中。COPY命令和\copy命令都支持这两类操作，但两者有如下区别：
1) COPY命令是SQL命令，\copy是元命令；
2) COPY命令必须具有SUPERUSER超级权限(将数据通过stdin、stuout方式导入导出情况外),而\copy元命令不需要SUPERUSER权限。
3) COPY命令读取或写入数据库服务端主机上的文件，而\copy元命令是从psql客户端主机上写入文件。
4) 从性能方面看，大数据量导出到文件或大文件数据导入数据库，COPY比、copy性能高。
1. 使用COPY命令导入导出数据
先来看看COPY命令如何将文件数据导入到数据表中，首先在mydb中创建测试表 test_copy,如下：
[postgres@fudao_db_cluster_002 ~]$ psql -h 10.192.30.60 -p 1921 -U pguser -W -d mydb Password for user pguser: psql (10.0) Type "help" for help. mydb=> \d List of relations Schema | Name | Type | Owner --------+--------+-------+-------- public | test_1 | table | pguser (1 row) mydb=> CREATE TABLE test_copy(id int4, name text); CREATE TABLE mydb=>
之后编写数据文件test_copy_in.txt，字段分隔符用TAB键，也可以设置其他分隔符，导入时再指定设置的字段分隔符。test_copy_in.txt文件如下所示：
[postgres@fudao_db_cluster_003 ~]$ cat /home/postgres/test_copy_in.txt 1,a 2,b 3,c
[postgres@fudao_db_cluster_002 ~]$
之后以postgres用户登录mydb库，并将test_copy_in.txt文件中的数据导入到test_copy表中。导入命令如下：
[postgres@fudao_db_cluster_002 ~]$ psql -h 10.192.30.60 -p 1921 -d mydb -U postgres Password for user postgres: psql (10.0) Type "help" for help. mydb=# copy public.test_copy from \'/home/postgres/test_copy_in.txt\' with csv; COPY 3 mydb=# select * from test_copy; id | name ----+------ 1 | a 2 | b 3 | c (3 rows)
# 使用普通用户
[postgres@fudao_db_cluster_002 ~]$ psql -h 10.192.30.60 mydb pguser Password for user pguser: psql (10.0) Type "help" for help. mydb=> copy public.test_copy from \'/home/postgres/test_cpoy_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. mydb=>
# 从远程登录看看
[postgres@fudao_db_cluster_002 ~]$ cat /home/postgres/test_copy_in.txt 1,a 2,b 3,c 4,d [postgres@fudao_db_cluster_002 ~]$ psql -h 10.192.30.60 -p 1921 mydb pguser Password for user pguser: psql (10.0) Type "help" for help. mydb=> \dt List of relations Schema | Name | Type | Owner --------+-----------+-------+-------- public | test_1 | table | pguser public | test_copy | table | pguser (2 rows) mydb=> select * from test_copy; id | name ----+------ (0 rows) mydb=> copy test_copy from \'/home/postgres/test_copy_in.txt\' with csv; 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. mydb=> \copy test_copy from \'/home/postgres/test_copy_in.txt\' with csv; COPY 4 mydb=> select * from test_copy ; id | name ----+------ 1 | a 2 | b 3 | c 4 | d (4 rows)
# 使用postgres用户远程登录，copy导出数据的文件，是在postgres数据的服务器端，不在客户端机器上
[postgres@fudao_db_cluster_002 ~]$ psql -h 10.192.30.60 -p 1921 mydb postgres Password for user postgres: psql (10.0) Type "help" for help. mydb=# \dt List of relations Schema | Name | Type | Owner --------+-----------+-------+-------- public | test_1 | table | pguser public | test_copy | table | pguser (2 rows) mydb=# copy pguser.test_copy to \'/tmp/test_copy_out.txt\'; ERROR: schema "pguser" does not exist mydb=# copy public.test_copy to \'/tmp/test_copy_out.txt\'; COPY 4 mydb=# \! cat /tmp/test_copy_out.txt cat: /tmp/test_copy_out.txt: No such file or directory mydb=#
# 使用非postgres用户登录，是在本地产生
[postgres@fudao_db_cluster_002 ~]$ psql -h 10.192.30.60 -p 1921 mydb pguser Password for user pguser: psql (10.0) Type "help" for help. mydb=> copy public.test_copy to \'/tmp/test_copy_out.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. mydb=> \copy public.test_copy to \'/tmp/test_copy_out.txt\'; COPY 4 mydb=> \! cat /tmp/test_copy_out.txt 1 a 2 b 3 c 4 d mydb=> mydb=> truncate table test_copy mydb-> ; TRUNCATE TABLE mydb=> select * from test_copy ; id | name ----+------ (0 rows) mydb=> \copy test_copy from /tmp/test_copy_out.txt\'; ERROR: unterminated quoted string at or near "\';" LINE 1: COPY test_copy FROM STDIN \'; ^ mydb=> \copy test_copy from \'/tmp/test_copy_out.txt\'; COPY 4 mydb=> select * from test_copy ; id | name ----+------ 1 | a 2 | b 3 | c 4 | d (4 rows) mydb=>
经常有运营或开发人员要求DBA提供生产库的运营数据，为了显示方便，这时需要将数据导出到csv格式。
[postgres@fudao_db_cluster_002 ~]$ psql -h 10.192.30.60 -p 1921 mydb postgres Password for user postgres: psql (10.0) Type "help" for help. mydb=# \dt List of relations Schema | Name | Type | Owner --------+-----------+-------+-------- public | test_1 | table | pguser public | test_copy | table | pguser (2 rows) mydb=# \copy public.test_copy to \'/tmp/test_copy_out.csv\' with csv header; COPY 4 mydb=# \! cat \'/tmp/test_copy_out.csv\' id,name 1,a 2,b 3,c 4,d mydb=#
上述命令中的with scv header是指导出格式为csv格式并且显示字段名称，以csv为后缀名的文件可以使用office execl打开。以上数据导出示例
都是基于全表数据导出的，如何仅导出表的一部分数据呢？如下代码仅导出表test_copy中ID等于1的数据记录。
mydb=# copy (select * from public.test_copy where id = 1) to \'/tmp/1.txt\'; COPY 1 mydb=# \! cat /tmp/1.txt 1 a mydb=#
2. 使用copy元命令导入导出数据
COPY命令是从数据库服务端主机读取或写入文件数据，而\copy元命令从psql用客户端主机读取或写入文件数据。并且\copy元命令不需要超级用户权限。
下面在pghost2中以普通用户pguser远程登录pghost1主机上的mydb库，并且使用\copy元命令导出text_copy数据。如下：
mydb=> \copy test_copy to \'/tmp/2_test_copy.dump.txt\'; COPY 4 mydb=> \! cat /tmp/2_test_copy.dump.txt 1 a 2 b 3 c 4 d mydb=> [postgres@fudao_db_cluster_002 tmp]$ cat 3_test_copy.dump.txt 1 a 2 b 3 c 4 d 5 e 6 f 7 h [postgres@fudao_db_cluster_002 tmp]$ mydb=> select * from test_copy ; id | name ----+------ 1 | a 2 | b 3 | c 4 | d (4 rows) mydb=> \copy test_copy from \'/tmp/3_test_copy.dump.txt\'; COPY 7 mydb=> mydb=> select * from test_copy ; id | name ----+------ 1 | a 2 | b 3 | c 4 | d 1 | a 2 | b 3 | c 4 | d 5 | e 6 | f 7 | h (11 rows)
没有超级用户权限的情况下，需要导出小表，通常使用\copy元命令。如果是大表导入导出，建议在数据库服务器端主机使用copy命令，效率更好
2.2.4 psql的语法和选项介绍
psql连接数据库语法如下：
psql [OPTION]... [DBNAME [USERNAME]]
其中dbname指连接的数据库名称，username指登录数据库的用户名，option有很多参数选项，这节列出重要的参数选项。
1. -A设置非对齐输出模式
psql执行SQL的输出默认是对齐模式，例如：
[postgres@fudao_db_cluster_003 ~]$ psql -c "select * from test_copy where id=1" mydb pguser id | name ----+------ 1 | a (1 row) [postgres@fudao_db_cluster_003 ~]$
注意以上输出，格式是对齐的，psql加上-A选项如下：
[postgres@fudao_db_cluster_003 ~]$ psql -A -c "select * from test_copy where id=1" mydb pguser id|name 1|a (1 row) [postgres@fudao_db_cluster_003 ~]$
加上-A选项后，以上输出的格式变成不对齐的了，并且返回结果中没有空行，接着看-t选项。
2. -t只显示记录数据
另一个psql重要选项参数为-t，-t参数设置输出只显示数据，而不显示字段名称和返回的结果集行数，如下所示：
[postgres@fudao_db_cluster_003 ~]$ psql -t -c "select * from test_copy where id=1" mydb pguser 1 | a [postgres@fudao_db_cluster_003 ~]$
注意以上结果，字段名称不再显示，返回的结果行数也没有显示，但尾部仍然有空行，因此-t参数通常和-A参数结合使用，
这时金返回数据本身，如下所示：
[postgres@fudao_db_cluster_003 ~]$ psql -At -c "select * from test_copy where id=1" mydb pguser 1|a [postgres@fudao_db_cluster_003 ~]$
以上结果进返回了数据本身，在编写shell脚本时非常有效，特别是只取一个字段的时候，如下所示：
[postgres@fudao_db_cluster_003 ~]$ psql -At -c "select name from test_copy where id=1" mydb pguser a [postgres@fudao_db_cluster_003 ~]$
3. -q不显示输出信息
默认情况下，使用psql执行SQL命令时会返回多种消息，使用-q参数后将不再显示这些信息，下面通过一个例子进行演示，首先创建 test_q.sql。
并输入一下SQL:
[postgres@fudao_db_cluster_003 ~]$ psql mydb -f /tmp/test_q.sql DROP TABLE CREATE TABLE TRUNCATE TABLE INSERT 0 1 INSERT 0 1 [postgres@fudao_db_cluster_003 ~]$
执行脚本后，返回了一对消息，加上-q参数后，这些信息不再显示了。
[postgres@fudao_db_cluster_003 ~]$ psql -q mydb -f /tmp/test_q.sql [postgres@fudao_db_cluster_003 ~]$
-q选项通常和-c或-f选项使用，在执行维护操作过程中，当输出信息不重要时，这个特性非常重要。
2.2.5 psql执行sql脚本
psql的-c选项支持在操作系统层面通过psql向数据库发起SQL命令，如下所示：
[postgres@fudao_db_cluster_003 ~]$ psql -c "select * from test_copy" mydb id | name ----+------ 1 | a 2 | b 3 | c 4 | d 5 | e 6 | f 7 | h (7 rows) [postgres@fudao_db_cluster_003 ~]$
-c选项后接执行的SQL命令，可以使用单引号或者双引号，同时支持格式化输出，如果想仅仅显示命令返回的结果，psql加上-At选项即可。
[postgres@fudao_db_cluster_003 ~]$ psql -At -c "select * from test_copy" mydb 1|a 2|b 3|c 4|d 5|e 6|f 7|h [postgres@fudao_db_cluster_003 ~]$
上述内容演示了在操作系统层面通过psql执行SQL命令，那么如何导入数据库脚文件了？首先编写一下文件，文件名称为test_1.sql
[postgres@fudao_db_cluster_003 ~]$ cat /tmp/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); [postgres@fudao_db_cluster_003 ~]$
通过-f参数导入次脚本，命令如下：
[postgres@fudao_db_cluster_003 ~]$ psql mydb pguser -f /tmp/test_2.sql CREATE TABLE INSERT 0 1 INSERT 0 1 INSERT 0 1 [postgres@fudao_db_cluster_003 ~]$
以上命令的输出结果没有报错，表示文件中所有的SQL正常导入。psql的 --single-transaction或-l选项支持在一个事务中执行脚本，要么脚本中的所有
SQL执行成功，如果其中有SQL执行失败，则文件中的所有SQL。
2.2.6 psql如何传递变量到SQL
如何通过psql工具将变量传递到SQL中，例如以下SQL:
SELECT * FROM test_name WHERE clumn_name = \'变量\';
下面演示两种传递变量的方式。
1. \set 元命令方式传递变量
\set 元子命令可以设置变量，格式如下：name表示变量名称，value表示变量值，如果不填写value，变量值为空。
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脚本，脚本内容如下：
[postgres@fudao_db_cluster_003 ~]$ cat select_1.sql select * from test_copy where id=:v_id ; [postgres@fudao_db_cluster_003 ~]$ [postgres@fudao_db_cluster_003 ~]$ psql mydb pguser -v v_id=1 -f /home/postgres/select_1.sql id | name ----+------ 1 | a (1 row) [postgres@fudao_db_cluster_003 ~]$ 以上设置变量v_id值为1。