第二章 psql客户端工具

时间:2024-03-03 22:56:43

 

第二章 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。