第二章 psql客户端工具

时间：2022-04-23 22:55:53

第二章 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 [] LOG:  checkpoints are occurring too frequently (25 seconds apart)

2019-06-26 16:29:47.498 CST [] 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。

相关文章