PG系列3-客户端工具使用

最新推荐文章于 2024-02-19 21:29:26 发布

玻璃酸钠滴眼液

最新推荐文章于 2024-02-19 21:29:26 发布

阅读量1k

点赞数

分类专栏： PostgreSQL 文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/weixin_41191813/article/details/118417569

版权

PostgreSQL 专栏收录该内容

8 篇文章 4 订阅

订阅专栏

前言

本篇将介绍PostgreSQL客户端工具，例如pgAdmin和 psql。pgAdmin是一款功能丰富、开源免费的PostgreSQL图形化客户端工具，psql是PostgreSQL自带的命令行客户端工具，功能全面，是PostgreSQL数据库工程师必须熟练掌握的命令行工具之一，本篇将会详细介绍它的独特之处。

1、pgAdmin 4简介

pgAdmin是最流行的PostgreSQL图形化客户端工具，项目主页为：https://www.pgadmin.org/，由于pgAdmin 4工具使用比较简单，这里仅简单介绍。

1.1 pgAdmin 4安装

pgAdmin支持Linux、Unix、Mac OS X和Windows，pgAdmin最新的大版本为4，后面提到pgAdmin时我们都称为pgAdmin 4，本节以在mac上安装pgAdmin 4为例，简单介绍pgAdmin 4。安装包下载地址为：https://www.postgresql.org/download/macosx/ 下载完后根据提示安装即可，安装完打开pgAdmin 4的界面如图所示
在这里插入图片描述

1.2 pgAdmin 4 使用

pgAdmin 4的使用非常简单，这一小节将演示如何使用 pgAdmin 4连接PostgreSQL数据库以及日常数据库操作。这里不想再介绍了，太简单了，都是图形化操作，自己百度吧，下面列出几个使用点，重点还是熟悉psql吧。

连接数据库
查询工具使用
显示统计信息

2、psql功能及应用

psql是PostgreSQL自带的命令行客户端工具，具有非常丰富的功能，类似于Oracle命令行客户端工具sqlplus，这一节将介绍psql常用功能和少数特殊功能，熟练掌握psql能便捷处理 PostgreSQL日常维护工作。

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 -d mydb -U pguser 
Password for user pguser: 
psql (10.0) 
Type "help" for help.

联接选项:
  -h, --host=主机名        数据库服务器主机或socket目录(默认："本地接口")
  -p, --port=端口        数据库服务器的端口(默认："5432")
  -U, --username=用户名    指定数据库用户名(默认："shiguangsheng")
  -w, --no-password       永远不提示输入口令
  -W, --password           强制口令提示 (自动)
  -d, --dbname=数据库名称   指定要连接的数据库

断开psql客户端连接使用\q元命令或CTRL+D快捷键即
可，如下所示：

[postgres@pghost1 ~]$ psql mydb pguser 
psql (10.0) 
Type "help" for help. 
mydb=> \q

2.2 psql元命令介绍

psql中的元命令是指以反斜线开头的命令，psql提供丰富的元命令，能够便捷地管理数据库，比如查看数据库对象定义、查看数据库对象占用空间大小、列出数据库各种对象名称、数据导入导出等，比如查看数据库列表，如下所示：

postgres=# \l 
                                        List of databases
   Name    |    Owner    | Encoding |   Collate   |    Ctype    |        Access privileges
-----------+-------------+----------+-------------+-------------+---------------------------------
 likeyyds  | haojin-test | UTF8     | en_US.utf-8 | en_US.utf-8 |
 likelinux | haojin-test | UTF8     | en_US.utf-8 | en_US.utf-8 |
 postgres  | haojin-test | UTF8     | en_US.utf-8 | en_US.utf-8 |
 template0 | haojin-test | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/"haojin-test"               +
           |             |          |             |             | "haojin-test"=CTc/"haojin-test"
 template1 | haojin-test | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/"haojin-test"               +
           |             |          |             |             | "haojin-test"=CTc/"haojin-test"

2.2.1 \db查看表空间

表空间可以把不同的表放到不同的存储介质或文件系统下。在PostgreSQL中，表空间实际上是为表指定一个存储目录。

postgres=# \db 
             List of tablespaces 
      Name |   Owner  | Location 
------------+---------+---------------------------------- 
pg_default | postgres | 
pg_global  | postgres | 
tbs_mydb   | pguser   | /database/pg10/pg_tbs/tbs_mydb

2.2.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)

2.2.3 查看表、索引占用空间大小

generate_series函数产生连续的整数，使用这个函数能非常方便地产生测试数据。

给测试表test_1插入500万数据，如下所示：

mydb=> INSERT INTO test_1(id,name) 
       SELECT n,n || '_francs' 
       FROM generate_series(1,5000000) n; 
INSERT 0 5000000

查看表大小执行\dt+后接表名，如下所示：

mydb=> \dt+ test_1 
           List of relations 
Schema | Name | Type | Owner | Size | Description 
-------+--------+-------+--------+--------+------------- 
pguser | test_1 | table | pguser | 287 MB |

查看索引大小执行\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)

2.2.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必须指定函数的参数类型。

2.2.5 \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

2.2.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)

2.2.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

\？元命令可以迅速列出所有元命令以及这些元命令的说明及语法，给数据库维护管理带来很大的便利。

2.2.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.3 psql导入、导出表数据

psql支持文件数据导入到数据库，也支持数据库表数据导出到文件中。COPY命令和\copy命令都支持这两类操作，但两者有以下区别：
1）COPY命令是SQL命令，\copy是元命令。

2）COPY命令必须具有SUPERUSER超级权限（将数据通过stdin、stdout方式导入导出情况除外），而\copy元命令不需
要SUPERUSER权限。

3）COPY命令读取或写入数据库服务端主机上的文件，而 \copy元命令是从psql客户端主机读取或写入文件。

4）从性能方面看，大数据量导出到文件或大文件数据导入数据库，COPY比\copy性能高。

2.3.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.3.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.4 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.5 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.6 psql亮点功能

psql还有其他非常突出的功能，比如显示SQL执行时间、反复执行当前SQL、自动补全、历史命令上下翻动、客户端提示符等，这节主要介绍psql的这些常用的亮点功能。

2.6.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.6.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（）命令。

2.6.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

2.6.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，但不推荐。

3、总结

本篇介绍了PostgreSQL客户端连接工具pgAdmin 4和psql命令行工具，其中重点介绍了psql命令行工具的强大功能。了解到pgAdmin 4的基本用法，另一方面了解到psql工具的主要功能，比如元命令、数据导入导出、执行SQL脚本、带参数执行脚本、定制维护脚本等，熟练掌握psql能够提高数据库管理工作效率。