掘金 后端 ( ) • 2024-05-08 18:32
PostgerSQL技术问答-01 psql
psql 技术

本文是《PostgreSQL技术问答》系列文章中的一篇。文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。

本文的主要内容是关于psql -- PostgreSQL的标准客户端软件。

什么是psql

psql是PostgreSQL官方的命令行(CLI)客户端管理工具。使用psql，可以连接本地或者远程的PostgreSQL服务器系统，并使用SQL和其他辅助命令，执行相关的数据管理和操作。理论上，通过执行sql命令，psql可以做到任何GUI管理工具可以做到的事情，甚至更多(比如更方便的脚本化和自动化)。

通常情况下的postgres安装后，可以在服务器系统上直接使用psql，无需安装和配置其他软件，非常方便。此外作为运维人员，也使用psql做一些初始化如数据库和用户创建的工作，或者使用psql做一些脚本自动化运维的工作。也强烈建议数据库和Web应用开发者了解和熟悉这个工具的使用，可以简化很多数据管理和操作的工作。

psql的官方技术文档，是postgres技术文档的一部分，如：

https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-INTERPOLATION

如何安装和配置psql

如果在数据库服务器上，使用默认的安装程序，安装了PostgreSQL数据库系统，那么一般情况下，它会同时安装psql这个工具。

但还有很多的应用场景是，我们不需要安装PostgreSQl数据库，只想在一台独立的管理主机上，安装psql并通过网络来进行远程的数据库管理，这时我们只需要安装psql工具。通常情况下，这个工具被包含在另一个软件包安装程序当中，如postgresql-client，或者postgresql-libs，在不同的系统上，可能软件包的名字有所差异，需要查阅相应的技术文档。

// Archlinux 
sudo pacman -S postgresql-libs 

// debian
apt install postgresql-client

// 安装和版本检查
psql --version

在Windows上，postgres是作为一个单独的安装包提供的，但可以在安装过程中，选择安装客户端工具(图)。

coUSX.png

正常安装完成之后，psql命令就会存在于操作系统的命令行执行环境中。在Linux中，psql可执行程序可能会被放置在/use/bin文件夹中，而Windows下，可能会修改环境路径。这时，就可以直接打开终端程序（Windows是PowerShell），直接输入psql命令，来检查其是否已经正确安装并配置完成。

psql如何连接数据库

使用psql连接和管理数据库，通常有两种形式，交互式和命令式。

  • 交互式

交互式就是显式的创建一个会话，然后在会话环境中进行交互式的操作，就和Linux操作系统的终端一样，但它的执行上下文环境是“数据库系统”，可以通过提示符，来区分是处于操作系统环境还是数据库系统会话环境中。

  • 命令式

所谓命令式，就是将psql作为一个命令来进行执行，执行完成后，仍然回到初始调用的那个操作系统环境当中。其实两者在数据库连接方面是一样的，只不过命令式需要配合相关参数标识，指定连接后要执行的SQL命令或者文件。psql会在连接之后，来执行这些SQL语句，完成后自动断开和退出数据库连接。显然，命令式的应用方式，更适合于做一些可以脚本化和自动化的操作，使用得当的话，可以大幅度降低数据库运维的工作复杂性。

除了这两种应用的模式之外，psql在连接数据库方面，可以支持本地连接和远程连接。其实这两种连接的方式，在逻辑上是一样的，只不过本地的连接，不需要指定服务器连接参数，而是使用默认本地连接参数进行连接而已。

本地连接通常在数据库所在的服务器操作系统环境中进行操作，可以是在服务器的控制台上，也可以是SSH远程终端。通常在默认安装PostgreSQL的过程中，会为本地连接进行相关的配置，这样就可以无需其他的额外配置和参数，直接执行psql命令进行连接操作。一般的过程如下：

// 切换到 postgres 账号

sudo su - postgres
[sudo] password for yanjh: 
postgres@ndb11-pgmain:~$ 

// 检查当前路径
pwd
/var/lib/postgresql

// 连接本地数据库
 psql
psql (15.3 (Debian 15.3-1.pgdg110+1))
Type "help" for help.

postgres=#

在进行psql连接之前，需要先切换到postgres账号(安装过程中已经创建和配置完成)，这也是默认的postgres数据库的工作账号；然后运行psql命令，它实际上应该是使用了默认的postgres账号角色，连接到本地默认开启的Unix Socket地址之上，而且使用默认的系统数据库实例postgres。由于使用的是当前的工作账号，这个过程是无需输入数据库连接密码的。当看到psql的版本编号，和postgres=# 提示符后，就可以得知，psql的连接已经成功，并且进入了postgres的操作环境了。

如果psql和postgres数据库服务不在同一个操作系统中，就需要通过网络来建立网络连接，这时候需要在连接命令中加入相关的连接参数。例如下面的命令行例子：


psql -h 192.168.9.32 -U uldata -d ulzsb
Password for user uldata:
psql (13.13 (Debian 13.13-1.pgdg100+1), server 15.3 (Debian 15.3-1.pgdg110+1))
WARNING: psql major version 13, server major version 15.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

ulzsb=>

连接后，可以看到如下的信息：

  • 客户端psql(本例子中是13.13)和服务器的版本(本例子中是15.3)，可以不一致
  • 版本不一致时，可能会有一些兼容性的问题
  • 默认连接是使用了TLS，算法是TLS_AES_256_GCM-SHA384，表明这个网络连接是安全的
  • help命令可以查询帮助信息
  • ulzsb是当前连接和工作的数据库实例（当前用户是uldata，但连接信息中没有）

在远程连接中，通常需要提供以下参数

  • -h 数据库服务所在的主机地址或者主机名，默认是localhost
  • -p 数据库服务监听端口，默认是5432，可省略
  • -U 指定连接用的数据库账号
  • -d 指定连接使用的工作数据库实例

每次连接都要输入密码，有简单的方法吗？

当然是有的，而且不止一种。此外，用户使用方便是一方面，另一方面更为重要，通过这种方式，可以实现psql的操作脚本的程序调用执行和自动化。

  • .pgpass

.pgpass 是一个pg用户的配置文件，它应当位于 ~/ 用户文件夹中。当使用psql进行数据库连接的时候，如果需要密码信息，它会查询这个文件中匹配的记录，并自动使用预配置的密码进行登录。

这个文件中，相关的数据库连接信息的格式如下：

hostname:port:database:username:password

  • 环境变量

在连接之前，可以先设置一个环境变量如下，随后就可以使用psql进行连接了：

export PGPASSWORD=mypassword

psql -U myuser -d mydb

  • 连接字符串

psql支持接收一个带有密码的连接字符串，这时可以设置登录账号和密码：

psql -h 192.168.9.32 "user=dbuser password=dbpasswd dbname=db"

建议的方式是使用第一个 .pgpass文件。因为这个方式，需要先对用户进行认证，并且不会在shell或者环境变量中遗留密码的历史记录，相对比较安全。

除了标准SQL语句之外，常用的psql命令有那些

psql会话环境中，可以执行标准的SQL数据库操作命令和语句。但为了方便管理和简化，它也提供了很多额外的psql特有的操作命令，这些命令通常使用 \ 作为开头。如 \q 就是退出当前psql会话。

下面列举一些日常常用的psql命令：

  • ? psql命令帮助，可以看到，psql命令集还是比较丰富和强大的
  • \d 描述当前数据库内容(schma)
  • \d table 当前表结构
  • \c 连接到另一个数据库
  • \conninfo 当前连接信息，如主机、端口、数据库实例、用户、连接协议等等
  • \timeing 开启或关闭执行计时，经常用于SQL语句的性能评估
  • \o 将查询结果写入文件
  • \! 如果不想退出psql会话，可以临时性的执行一个shell命令
  • \copy 执行SQL COPY，并将数据流复制到客户端主机，这个特性对于数据批量操作非常有用
  • \q 退出psql会话，也可以使用exit命令。注意，这时使用ctrl-c是无效的，它只会中断当前的psql命令，但不会从psql会话中退出。

如何使用psql直接执行SQL命令

前面已经提到，除了交互模式之外，可以通过-c或者-f命令行标记，来实现psql的命令执行方式。

  • -c SQL语句

这个命令，可以在psql连接数据库之后，执行参数中的sql语句，并将结果打印在标准输出(一般就是终端环境)中。这个命令不会建立psql会话，执行完毕后，返回终端环境。

$ psql -h 192.168.9.32 -U uldata -d ulzsb -c "select count(1) from payments;select count(1) from students;"
用户 uldata 的口令：
 count
-------
    26
(1 行记录)

 count
--------
 113913
(1 行记录)

在这个选项中，可以使用双引号将sql语句包围引用，并且支持多条SQL语句，它们将会依次执行。

  • -f SQL文件

-f可以指定一个SQL文件，文件中编写需要执行的SQL语句。执行时，psql连接数据库，加载并执行文件中的语句，并输出查询结果。

// 编辑sql文件内容 (1.sql)
select count(1) from cusers;

// 执行SQL文件
psql -h 192.168.9.32 -U uldata -d ulzsb -f 1.sql
count
-------
   177
(1 row)
  • -o 执行结果输出到文件

默认情况下psql会将查询执行结果输出到标准输出设备，一般就是当前的命令行环境中。可以通过 -o 参数，将这个结果重定向输出到指定的文件当中：

// 执行文件查询
psql -h 192.168.9.32 -U uldata -d ulzsb -f 1.sql -o o.txt

// 查看执行结果
cat o.txt
count
-------
   177
(1 row)

如何使用psql导入导出数据

在日常的数据管理中，数据的导入导出是一个非常常见的需求和操作。Postgres提供了COPY语句，来帮助完成这些任务。使用这个语句，我们可以不用编写SQL Insert语句和相关程序，就可以完成数据从一个普通文本文件中导入到数据库系统，而且可以将一个查询的结果，输出导出到一个文件当中。从理论上来看，COPY命令将会直接操作数据，导入导出数据的性能和效率，应该远远超过使用外部程序和SQL语句处理的方式，应该是大批量数据管理优先使用的方式。

但是，Postgres提供的COPY命令，有一个问题，就是它只能在服务器所在的操作系统的文件系统中工作，这并不适用于我们一般通过网络进行远程数据管理的方式。幸运的是，psql也提供了类似的 \copy命令，可以让我们在客户端系统上也可以完成类似的工作，导入导出的文件都是在客户端操作系统的文件系统之中。

为了方便读者理解，笔者基于一个实际使用案例，编写了一个shell脚本程序，内容和调用过程如下：

// 编辑 shell 脚本
#!/bin/bash

## databas config
PASSWD=dbpassword
HOST=192.168.9.32
USER=uldata
DBNAME=ulzsb

## copy command 
CMD_COPY="\copy (select idhash,name from students) to stulist.csv csv header"

# psql exec command
PGPASSWORD=$PASSWD psql -h $HOST -d $DBNAME -U $USER -c "\timing " -c "$CMD_COPY"

echo "EXPORT OK"

// 执行 shell 脚本
~$ ./dexport.sh
Timing is on.
COPY 113913
Time: 538.492 ms
EXPORT OK

// 查看导出内容
~$ cat stulist.csv

这段程序的要点包括：

  • 可以在脚本中设置服务器连接参数，当然也可以作为脚本执行参数传入
  • 使用此参数可以自动连接数据库服务器并且执行后续命令
  • 导出的方式是基于psql \copy 命令
  • 该命令将一个查询结果，导出到本地csv文件，可选设置格式和表头
  • 最后将以上内容，组装成单一的psql命令，并且执行该命令
  • 执行结果可以看到导出记录数量和操作时间
  • 在笔者的环境中，500多毫秒可以处理11万条记录，性能是令人满意的

类似的，我们可以用相同的方法，实现从csv文件中的数据导入，如下：

// 导入脚本

... 
// 创建数据表
## CMD_COPY="create table students_bk (like students including all) "
// 导入命令 
CMD_COPY="\copy students_bk (idhash,name) from stulist.csv delimiter ',' csv "
...

// 导入执行
~$ ./dimport.sh
Timing is on.
COPY 113914
Time: 2471.861 ms (00:02.472)
IMPORT OK

代码中导入操作的要点如下：

  • 可能需要先创建导入目标的数据表
  • 导入命令的格式为 \copy table from file
  • 文件默认位置为当前文件夹，也可以使用绝对路径
  • 可选导入的字段，但需要匹配记录文本字段的顺序
  • 执行方式和导出完全相同
  • 导入性能也非常可观，2.5秒处理11万条记录

psql如何执行参数化查询

开发者都知道，基于安全(防止注入攻击)的考虑，建议使用带有参数的SQL来进行查询操作，而不是自行拼接SQL语句；此外，参数化语句还可以让数据库预先对SQL语句进行预编译、优化和缓存，有利于提高查询和操作的性能；参数化还可以将固定的SQL语句和变动的查询参数进行分类，方便维护和管理。所以，在尽可能的情况下，实现参数化的SQL执行，是一种比较好的软件工程实践。

在Postgres中，参数化SQL的实现方式包括占位符、命名参数和预备语句等方式。参考的语句代码如下：

// 占位符 格式为$n
SELECT * FROM users WHERE id = $1;

// 命名参数，格式为 :name
SELECT * FROM users WHERE name = :user_name;

// 预备语句 结合占位符
PREPARE user_query(int, text) AS SELECT * FROM users WHERE id = $1 AND name = $2;
EXECUTE user_query(123, 'John Doe');

在理解了上述原理之后，我们可以为psql构造相关的语句和参数。这里的要点在于psql支持 -v参数来设置会话中的变量。psql文档中，没有太详细的说明这个参数的用法，也没有相关的示例代码。经过反复的测试，笔者发现只有下面这种形式，才是能够正常工作：

PGPASSWORD=passwd psql -h 192.168.9.32 -d ulzsb -U uldata -v v1="'金%'" -v v2=10 <<< "select idhash,name from students where name like :v1 limit :v2"

这个SQL语句的原意是希望通过两个参数，来控制查询语句，在students表中，查询姓为“金”的学生，并且限定结果数量为10，参数分别就是这个姓和限定数量。这里看到的要点和问题如下：

  • 不能直接使用 -c 语句，而是需要使用 <<< 来向psql注入这个命令
  • 使用-v来声明参数，多个参数需要多次声明，并且需要在注入前操作
  • 似乎不能使用占位符，而只能使用命名参数
  • 命名参数的名称，和参数设置需要严格对应，但顺序应该不重要

整体来看，如果笔者的用法和理解没有错误的话，笔者觉得这个参数查询的功能，psql的实现并不是特别的好。对格式的要求比较严格，而且不能使用占位符操作，过程和形式比较繁琐。

PG有其他的客户端吗？

作为官方的客户端软件，psql是命令行工具。但PostgreSQL应该没有官方的GUI工具(比如SQL Develper For 之于Oracle)，但有一个推荐的GUI工具： pgAdmin，也算是一个半官方工具吧。

pgadmin.webp

和一般的GUI工具不同，pgAdmin其实是一个Web应用程序。但由于其半官方的性质，它对于Postgres的特性的支持还是比较丰富的。此外，pgAdmin还可以作为“管理工具服务器”来进行部署，应该可以方便分布式的开发和测试环境。这个工具笔者接触不多，如果读者有兴趣，可以自行深入研究。

此外，有一些其他的非官方或者通用的关系数据库管理软件，也可以很好的支持PostgreSQL，笔者觉得这些工具对于日常使用的差异不大，开发者可以根据自己的需求和喜好选择使用。 这里例举几个：

  • dbeaver(数据海狸)

dbeaver不是db-eaver，而是d-beaver的意思。这是一个开源的通用数据库管理工具。它的社区版是免费的，也有商业化的专业版。这个软件应该是基于Java开发的，有很浓的eclipse的味道，但是意外的笔者觉得还是非常好用，也是笔者日常使用的主力数据管理工具。

11.png

  • navicat(导航猫)

navicat是国人比较喜欢使用的通用数据库管理工具。它的特点是功能比较丰富强大，支持的数据库种类比较多，中文支持比较好，界面美观易用。但这个软件是商业软件，需要付费使用。

12.png

  • DbVisualizer(数据可视化工具)

和dbeaver类似，dbvisualizer也是一个通用的数据库客户端和管理工具。它同样通过可扩展的驱动软件系统，来提供多种数据库类型的支持，其标准版也可以免费使用。根据其官网的说法，它特地增强了对于Postgres的支持，如更完善的数据库对象。可惜的是，这个软件主打的可视化查询编辑器和数据库结构，只有在付费的版本才提供。

0a.png

笔者也是最近才发现了这个软件，简单的使用感觉还不错。虽然这个工具同样基于Java开发，但比dbeaver少了很多咖喱的味道，界面和体验，包括运行的性能，更像一个现代化的Web应用。

  • vscode

笔者最近发现，由于vscode这种IDE系统基于插件的扩展机制，很多工具都是可以作为扩展，集成到开发工具当中。如果没有很特殊的要求，一般情况下，只是需要一些SQL开发方面的支持，这些工具，其实是比较实用和方便的。在这个时候，开发工具，本身就可以作为数据库客户端和数据管理工具使用。

以vscode为例，我们可以在插件面板中，搜索“postgres”，就可以看到很多postgres工具可以选择使用(图)。

0a.png

小结

本文探讨了postgreSQL的标准客户端程序psql，包括其安装过程，数据库连接方式、psql命令选项、SQL语句和执行等相关内容。