PostgreSQL 交互式终端 psql：连接、查询及管理

PostgreSQL 交互式终端 psql：连接、查询及管理详解

PostgreSQL 是一款功能强大、开源的关系型数据库管理系统 (RDBMS)。psql 是 PostgreSQL 官方提供的交互式终端，是与 PostgreSQL 数据库服务器进行交互的主要工具。通过 psql，你可以连接数据库、执行 SQL 查询、管理数据库对象（如表、视图、索引等）、配置服务器参数以及执行各种管理任务。本文将深入探讨 psql 的各个方面，包括连接方法、常用命令、查询技巧、管理功能和高级特性。

1. 连接到 PostgreSQL 数据库

psql 提供了多种连接到 PostgreSQL 数据库服务器的方式，可以根据不同的场景和需求选择合适的方法。

1.1 基本连接

最简单的连接方式是直接运行 psql 命令，不带任何参数。这将尝试使用当前操作系统用户名作为数据库用户名，并连接到与该用户名同名的数据库。

bash
psql

如果连接成功，你将看到类似以下的提示符：

```
psql (14.2, server 14.2)
Type "help" for help.

your_username=#
```

1.2 指定用户名和数据库名

可以使用 -U 选项指定数据库用户名，-d 选项指定要连接的数据库名：

bash
psql -U your_username -d your_database

例如，要以 postgres 用户身份连接到 mydatabase 数据库：

bash
psql -U postgres -d mydatabase

1.3 指定主机名和端口号

如果数据库服务器不在本地主机上，或者使用了非默认端口（5432），可以使用 -h 选项指定主机名（或 IP 地址），-p 选项指定端口号：

bash
psql -U your_username -d your_database -h your_host -p your_port

例如，要连接到 IP 地址为 192.168.1.100、端口号为 5433 的数据库服务器：

bash
psql -U postgres -d mydatabase -h 192.168.1.100 -p 5433

1.4 使用连接字符串

psql 还支持使用连接字符串（Connection URI）来指定连接参数。连接字符串的格式如下：

postgresql://[user[:password]@][host][:port][/dbname][?options]

例如：

bash
psql postgresql://postgres:[email protected]:5433/mydatabase
这种方式可以把所有连接信息写到一起，比较方便。

1.5 使用环境变量

psql 会读取一些环境变量来获取连接信息，常用的环境变量包括：

• PGHOST: 数据库服务器主机名（或 IP 地址）。
• PGPORT: 数据库服务器端口号。
• PGDATABASE: 要连接的数据库名。
• PGUSER: 数据库用户名。
• PGPASSWORD: 数据库用户密码（不推荐直接在环境变量中设置密码，出于安全考虑）。

如果设置了这些环境变量，psql 在连接时会自动使用它们的值。

1.6 密码提示

如果连接需要密码，而你没有在命令行中提供密码（例如使用 -W 选项强制提示输入密码），psql 会提示你输入密码：

Password for user your_username:

输入密码时，屏幕上不会显示任何字符，这是为了安全考虑。

2. psql 常用命令

psql 提供了许多内置命令（也称为元命令或反斜杠命令），用于执行各种操作，如查看数据库对象、切换数据库、执行脚本等。这些命令都以反斜杠 (\) 开头。

2.1 帮助命令

• \?: 显示 psql 命令的帮助信息。
• \h [COMMAND]: 显示 SQL 命令的帮助信息。例如，\h SELECT 将显示 SELECT 语句的语法和用法。

2.2 数据库对象信息

• \l: 列出所有数据库。
• \c [database_name]: 连接到另一个数据库。
• \dt: 列出当前数据库中的所有表。
• \dt+:更详细地列出表（包括大小和描述）。
• \d [table_name]: 显示表的结构（列名、数据类型、约束等）。
• \di: 列出所有索引。
• \dv: 列出所有视图。
• \df: 列出所有函数。
• \du: 列出所有用户。
• \dp [pattern]或者\z [pattern]: 显示表、视图和序列的访问权限。

2.3 执行 SQL 脚本

• \i file_name: 执行指定文件中的 SQL 脚本。

2.4 编辑命令

• \e: 使用默认编辑器（通常是 vi 或 nano）编辑当前查询缓冲区中的内容，或者编辑指定文件，例如 \e filename。
• \ef [function_name [line_number]]：编辑函数定义。
• \ev [view_name [line_number]]： 编辑视图定义。

2.5 查询历史

• \s: 显示查询历史记录。
• \s file_name: 将查询历史记录保存到文件中。
• \r: 重置（清除）查询缓冲区。

2.6 输出格式

• \pset [option] [value]: 设置输出选项，例如：
  • \pset format [unaligned | aligned | wrapped | html | asciidoc | latex | troff-ms]: 设置输出格式。
  • \pset border [0 | 1 | 2]: 设置边框样式。
  • \pset title [text]: 设置表格标题。
• \a: 在非对齐和对齐的输出模式之间切换.
• \t: 切换是否只显示数据行，而不显示列名和行数等信息。
• \H: 切换到 HTML 输出格式。

2.7 其他常用命令

• \timing: 切换是否显示每个 SQL 命令的执行时间。
• \q: 退出 psql。
• \! [command]: 执行 shell 命令。例如，\! ls 将列出当前目录中的文件。
• \set [variable] [value]: 设置 psql 变量。
• \echo [text]: 显示文本。
• \copy ...: PostgreSQL 的COPY命令的客户端版本, 允许在表和文件之间复制数据。 这是一个非常高效的数据导入导出方式。

3. 查询技巧

除了基本的 SQL 查询，psql 还提供了一些技巧来提高查询效率和可读性。

3.1 查询缓冲区

psql 会将你输入的 SQL 命令保存在查询缓冲区中。你可以使用上下箭头键来浏览查询历史记录，并重新执行之前的命令。

3.2 多行查询

如果 SQL 查询太长，可以在一行末尾输入反斜杠 (\)，然后按回车键继续输入下一行。psql 会将多行输入视为一个完整的查询。

3.3 自动补全

psql 支持 Tab 键自动补全。当你输入 SQL 关键字、表名、列名等时，可以按 Tab 键来自动补全。这可以减少输入错误并提高效率。

3.4 变量替换

可以使用 \set 命令定义变量，然后在 SQL 查询中使用这些变量。变量名以冒号 (:) 开头。

sql
\set my_table 'my_table'
SELECT * FROM :my_table;
还可以使用:'variable_name'引用包含空格或其他特殊字符的变量。

3.5 使用 EXPLAIN 分析查询

EXPLAIN 命令可以显示 SQL 查询的执行计划。这对于分析查询性能、找出慢查询的原因非常有帮助。

sql
EXPLAIN SELECT * FROM your_table WHERE your_condition;

EXPLAIN ANALYZE 不仅显示执行计划，还会实际执行查询，并显示每个步骤的实际执行时间。

4. 管理功能

psql 不仅可以用于查询数据，还可以用于执行各种数据库管理任务。

4.1 创建和删除数据库对象

可以使用 SQL 命令在 psql 中创建、修改和删除数据库对象，如表、视图、索引、函数、触发器等。

```sql
-- 创建表
CREATE TABLE my_table (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
age INTEGER
);

-- 创建索引
CREATE INDEX idx_name ON my_table (name);

-- 创建视图
CREATE VIEW my_view AS SELECT * FROM my_table WHERE age > 18;

-- 删除表
DROP TABLE my_table;
```

4.2 管理用户和权限

可以使用 CREATE USER、ALTER USER、DROP USER 等命令来管理数据库用户。

```sql
-- 创建用户
CREATE USER newuser WITH PASSWORD 'newpassword';

-- 修改用户密码
ALTER USER newuser WITH PASSWORD 'newpassword2';

-- 删除用户
DROP USER newuser;
```

可以使用 GRANT 和 REVOKE 命令来管理用户权限。

```sql
-- 授予用户对表的 SELECT 权限
GRANT SELECT ON my_table TO newuser;

-- 撤销用户对表的 SELECT 权限
REVOKE SELECT ON my_table FROM newuser;
```

4.3 备份和恢复

psql 本身不直接提供备份和恢复功能，但可以结合其他 PostgreSQL 工具（如 pg_dump、pg_restore）来完成这些任务。

```bash
-- 使用 pg_dump 备份数据库
pg_dump -U your_username -d your_database > backup.sql

-- 在 psql 中执行备份文件来恢复数据库
\i backup.sql
``
更推荐直接用pg_restore` 来恢复。

4.4 配置服务器参数

可以使用 SHOW 命令查看服务器参数：

sql
SHOW all; -- 显示所有参数
SHOW max_connections; -- 显示最大连接数

可以使用 ALTER SYSTEM 命令修改服务器参数（需要超级用户权限）：

sql
ALTER SYSTEM SET max_connections = 200;

修改后，需要重新加载配置或重启数据库服务器才能生效。
用select pg_reload_conf();来重新加载配置。

5. 高级特性

psql 还提供了一些高级特性，可以进一步增强其功能和灵活性。

5.1 自定义提示符

可以使用 \set PROMPT1、\set PROMPT2 和 \set PROMPT3 来自定义 psql 的提示符。这些变量可以包含特殊字符和转义序列，例如：

• %M: 完整的主机名（或 IP 地址）。
• %m: 主机名到第一个点为止。
• %>: 端口号。
• %n: 数据库用户名。
• %/: 当前数据库名。
• %#: 如果当前用户是超级用户，则显示 #，否则显示 >。
• %x: 事务状态(例如空闲, 活动等)。
• %?: 上一条命令的返回码。

例如，要将提示符设置为显示用户名、数据库名和 # 或 >：

sql
\set PROMPT1 '%n@%/%# '

5.2 条件执行

可以使用 \if、\elif、\else 和 \endif 命令来根据条件执行不同的命令块。

```sql
\set my_variable 10

\if :my_variable > 5
\echo 'my_variable is greater than 5'
\else
\echo 'my_variable is not greater than 5'
\endif
```

5.3 扩展命令

psql 允许你定义自己的扩展命令，这些命令可以像内置命令一样使用。使用 CREATE EXTENSION 命令安装。例如安装adminpack扩展：

sql
CREATE EXTENSION adminpack;

5.4 psqlrc 文件

psql启动时会读取并执行~/.psqlrc文件（或者通过环境变量PSQLRC指定的其他文件）。你可以在这个文件里预设一些变量，设置输出格式，定义常用函数等等，使得psql更符合你的使用习惯。

总结

psql 是 PostgreSQL 数据库管理系统的强大交互式终端。通过掌握 psql 的连接方法、常用命令、查询技巧、管理功能和高级特性，你可以更高效地与 PostgreSQL 数据库进行交互，执行各种数据库操作和管理任务。 psql 的灵活性和可扩展性使其成为 PostgreSQL DBA 和开发人员不可或缺的工具。希望本文能够帮助你深入理解和熟练使用 psql。