PostgreSQL CLI 完全使用指南：从连接到权限管理
概述
PostgreSQL（简称 Postgres）的 CLI 工具（psql）是管理 Postgres 数据库的核心工具 —— 它无需图形界面，通过命令行即可完成数据库连接、数据操作、权限管理、备份恢复等全流程操作，是后端开发、运维人员的必备技能。
psql 不仅支持 SQL 原生命令（如 CREATE DATABASE、SELECT），还提供了大量元命令（以 \ 开头，如 \l 列出数据库、\d 查看表结构），兼顾灵活性和高效性。本文将从「连接数据库」到「权限控制」，系统梳理 psql 的核心用法，覆盖日常开发中的高频场景。
一、基础准备：确认 psql 环境
在使用 psql 前，需确保其已正确安装（通常随 Postgres 服务器一同安装）：
# 检查 psql 版本（验证是否安装成功）
psql --version
# 正确输出示例：psql (PostgreSQL) 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)若提示 “command not found”，需先安装 Postgres 客户端（以 Ubuntu 为例）：
sudo apt update && sudo apt install -y postgresql-client二、核心操作：连接到 Postgres 数据库
psql 支持多种连接方式，需根据「是否本地 / 远程」「用户权限」选择合适的命令，核心参数包括：
- -d：指定要连接的数据库名；
- -U：指定连接使用的用户名；
- -h：指定数据库服务器地址（本地默认 localhost，远程需填 IP / 域名）；
- -p：指定数据库端口（默认 5432）。
2.1 本地连接（常见场景）
场景 1：使用 Postgres 超级用户连接默认数据库
Postgres 安装后会默认创建 postgres 超级用户和同名数据库，需通过 sudo 切换到 postgres 用户后连接（避免权限问题）：
# 切换到 postgres 用户，并连接默认的 postgres 数据库
sudo -u postgres psql postgres
# 连接成功后，终端提示符会变为：postgres=# （# 表示超级用户权限）场景 2：使用自定义用户连接指定数据库
若已创建自定义用户（如 my_username）和数据库（如 my_database），可直接通过 -d 和 -U 指定：
# 格式：psql -d 数据库名 -U 用户名
psql -d my_database -U my_username
# 若本地 Postgres 未配置“信任认证”，会提示输入密码（输入时无明文显示，输完回车即可）
# 连接成功后，提示符为：my_database=> （> 表示普通用户权限）2.2 远程连接（服务器 / 云数据库场景）
连接远程 Postgres 数据库（如阿里云 RDS、自建服务器），需额外指定 -h（地址）和 -p（端口，若非默认）：
# 格式：psql -h 远程地址 -p 端口 -d 数据库名 -U 用户名
psql -h 192.168.1.100 -p 5432 -d prod_db -U prod_user
# 注意：远程连接需满足两个前提：
# 1. 远程 Postgres 配置文件（postgresql.conf）中 listen_addresses = '*'（允许外部访问）
# 2. 访问控制文件（pg_hba.conf）中添加客户端 IP 授权（如 host all all 192.168.1.0/24 md5）2.3 退出连接
无论何种连接方式，退出 psql 可使用以下任一命令：
# 方式 1：元命令（推荐，简洁）
\q
# 方式 2：SQL 命令（需加 ; 结尾）
EXIT;
# 或
QUIT;三、数据库管理：创建、删除与备份
通过 psql 可直接执行 SQL 命令管理数据库，也可使用元命令简化操作。
3.1 查看数据库列表
使用元命令 \l（list 的缩写）查看当前 Postgres 实例中的所有数据库：
# 列出所有数据库（包含所有者、编码、权限等信息）
\l
# 若需筛选特定数据库（如包含“demo”的库），可结合 grep：
\l | grep demo3.2 创建数据库
方式 1：执行 SQL 命令（推荐，可自定义参数）
# 格式：CREATE DATABASE 数据库名 [可选参数];
CREATE DATABASE demodb1
WITH
OWNER = my_username -- 指定数据库所有者（默认当前连接用户）
ENCODING = 'UTF8' -- 指定编码（避免中文乱码，推荐 UTF8）
LC_COLLATE = 'en_US.UTF-8' -- 排序规则
LC_CTYPE = 'en_US.UTF-8'; -- 字符分类规则
# 注意：SQL 命令必须以 ; 结尾，否则 psql 会进入多行输入模式（提示符变为 -#）方式 2：使用元命令 \createdb（简化版，默认参数）
# 格式：\createdb 数据库名
\createdb demodb2
# 等价于 SQL：CREATE DATABASE demodb2;（使用默认所有者、编码）3.3 删除数据库
警告：删除数据库会彻底清除所有数据，且无法恢复，请谨慎操作！
# 格式：DROP DATABASE [IF EXISTS] 数据库名;
# 加 IF EXISTS 可避免“数据库不存在”时报错
DROP DATABASE IF EXISTS demodb1;3.4 备份与恢复数据库
Postgres 提供 pg_dump 工具（与 psql 同级）用于备份数据库，psql 用于恢复备份，两者配合完成数据迁移。
场景 1：备份数据库到 SQL 文件
在 psql 外部执行 pg_dump（无需进入 psql 环境）：
# 格式：pg_dump -U 用户名 -d 数据库名 > 备份文件路径.sql
pg_dump -U my_username -d demodb1 > ~/backups/demodb1_20240904.sql
# 若需压缩备份（节省空间），可结合 gzip：
pg_dump -U my_username -d demodb1 | gzip > ~/backups/demodb1_20240904.sql.gz场景 2：从 SQL 文件恢复数据库
需先创建空数据库（若目标库不存在），再通过 psql 导入备份：
# 1. 先创建空数据库（若已存在，跳过此步）
psql -U my_username -d postgres -c "CREATE DATABASE demodb1_restored;"
# 2. 导入备份文件（注意：连接的数据库需与目标库不同，此处用 postgres 库）
psql -U my_username -d demodb1_restored -f ~/backups/demodb1_20240904.sql
# 若备份是压缩文件，需先解压再导入：
gunzip -c ~/backups/demodb1_20240904.sql.gz | psql -U my_username -d demodb1_restored四、表与数据操作：查看结构与执行 SQL
进入数据库后（通过 \c 数据库名 切换），可管理表结构、执行增删改查（CRUD）操作。
4.1 切换数据库
若当前连接的是 A 数据库，需操作 B 数据库，无需退出重连，用 \c（connect 的缩写）切换：
# 格式：\c 目标数据库名
\c demodb1
# 切换成功后，提示符会变为：demodb1=>4.2 查看表相关信息
1. 列出当前数据库中的所有表
# 列出所有表（仅显示表名、所有者、类型）
\dt
# 若需显示更多信息（如表大小、描述），加 +：
\dt+2. 查看表结构（字段、类型、约束）
# 格式：\d 表名
\d users
# 输出包含：字段名（Column）、类型（Type）、是否非空（NotNull）、默认值（Default）、约束（Constraints）
# 示例输出：
# Column | Type | Collation | Nullable | Default
# --------+---------+-----------+----------+---------
# id | integer | | not null |
# name | text | | not null |
# age | integer | | |
# Indexes:
# "users_pkey" PRIMARY KEY, btree (id)3. 查看表的详细信息（含索引、触发器）
# 格式：\d+ 表名
\d+ users
# 会额外显示：表大小（Size）、存储位置（Storage）、索引详情、触发器等4.3 执行 SQL 操作数据
在 psql 中可直接执行任意 SQL 命令（如创建表、插入数据、查询数据），示例：
# 1. 创建表
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- SERIAL 自动增长整数，作为主键
name VARCHAR(50) NOT NULL, -- 字符串，非空
age INT, -- 整数，可空
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 时间戳，默认当前时间
);
# 2. 插入数据
INSERT INTO users (name, age) VALUES ('Alice', 25), ('Bob', 30);
# 3. 查询数据
SELECT * FROM users; -- 查询所有字段
SELECT name, age FROM users WHERE age > 28; -- 筛选条件查询
# 4. 更新数据
UPDATE users SET age = 26 WHERE name = 'Alice';
# 5. 删除数据
DELETE FROM users WHERE name = 'Bob';小贴士：若 SQL 命令较长，可按 Enter 换行（psql 会用 -# 提示多行输入），输完后加 ; 回车执行。
五、角色与权限管理：控制访问权限
Postgres 中的「角色（Role）」相当于用户或用户组，通过角色管理可精细控制谁能访问数据库、执行哪些操作（如创建表、修改数据）。
5.1 查看角色列表
使用元命令 \du（describe users 的缩写）查看所有角色及权限：
\du
# 输出包含：角色名（Role name）、属性（Attributes，如超级用户、创建数据库权限）、成员关系（Member of）5.2 创建角色
基础创建（仅登录权限）
# 格式：CREATE ROLE 角色名 WITH LOGIN ENCRYPTED PASSWORD '密码';
CREATE ROLE demorole1 WITH
LOGIN -- 允许该角色登录（无此属性则仅为“组角色”，无法登录）
ENCRYPTED PASSWORD 'password123' -- 加密存储密码（必须加单引号）
NOSUPERUSER -- 禁止超级用户权限（默认，显式声明更安全）
NOCREATEDB; -- 禁止创建数据库权限（默认）高级创建（含特殊权限）
若需让角色拥有创建数据库、管理其他角色的权限，可添加对应属性：
CREATE ROLE demorole2 WITH
LOGIN
ENCRYPTED PASSWORD 'password456'
CREATEDB -- 允许创建数据库
CREATEROLE; -- 允许创建/修改其他角色（除超级用户）5.3 修改角色属性
使用 ALTER ROLE 命令修改已存在角色的权限（如升级为超级用户、修改密码）：
# 1. 给角色添加“超级用户”和“复制”权限（谨慎使用，超级用户拥有所有权限）
ALTER ROLE demorole1 WITH SUPERUSER REPLICATION;
# 2. 修改角色密码
ALTER ROLE demorole1 WITH ENCRYPTED PASSWORD 'new_password789';
# 3. 撤销角色的“创建数据库”权限
ALTER ROLE demorole2 WITH NOCREATEDB;5.4 删除角色
注意：若角色已拥有数据库 / 表的权限，删除前需先转移权限或删除关联资源，否则会报错。
# 格式：DROP ROLE [IF EXISTS] 角色名;
DROP ROLE IF EXISTS demorole1;5.5 赋予与撤销权限
Postgres 权限控制粒度极细，可针对「数据库、表、字段」等对象赋予权限（如 SELECT、INSERT、ALL PRIVILEGES）。
场景 1：给角色赋予数据库权限
允许角色操作指定数据库（如连接、创建表）：
# 格式：GRANT 权限列表 ON DATABASE 数据库名 TO 角色名;
# 赋予 demorole2 对 demodb1 的所有权限
GRANT ALL PRIVILEGES ON DATABASE demodb1 TO demorole2;
# 仅赋予“连接数据库”和“创建表”权限（更安全）
GRANT CONNECT, CREATE ON DATABASE demodb1 TO demorole2;场景 2：给角色赋予表权限
允许角色操作指定表（如查询、插入数据）：
# 先切换到目标数据库（权限是数据库级别的）
\c demodb1
# 赋予 demorole2 对 users 表的 SELECT（查询）和 INSERT（插入）权限
GRANT SELECT, INSERT ON TABLE users TO demorole2;
# 赋予所有表的查询权限（用 * 表示所有表）
GRANT SELECT ON ALL TABLES IN SCHEMA public TO demorole2;场景 3：撤销权限
若需收回已赋予的权限，用 REVOKE 命令：
# 撤销 demorole2 对 demodb1 的“创建表”权限
REVOKE CREATE ON DATABASE demodb1 FROM demorole2;
# 撤销 demorole2 对 users 表的所有权限
REVOKE ALL PRIVILEGES ON TABLE users FROM demorole2;六、常用元命令速查表
psql 的元命令以 \ 开头，无需加 ; 结尾，以下是日常开发中高频使用的命令汇总：
| 元命令 | 功能描述 | 示例 |
|---|---|---|
| \q | 退出 psql | \q |
| \c dbname | 切换到指定数据库 | demodb1 |
| \l | 列出所有数据库 | \l |
| \du | 列出所有角色（用户） | \du |
| \dt | 列出当前数据库的所有表 | \dt |
| \d table | 查看指定表的结构 | \d users |
| \d+ table | 查看指定表的详细信息（含索引、大小） | \d+ users |
| \df | 列出当前数据库的所有函数 | \df |
| \df+ | 列出函数并显示详细定义（参数、返回值） | \df+ |
| \timing | 开启 / 关闭 SQL 执行时间统计（调试性能用） | \timing on（开启） |
| \h sqlcmd | 查看 SQL 命令的语法帮助（如 \h CREATE TABLE） | \h INSERT |
| ! shellcmd | 执行系统 Shell 命令（无需退出 psql） | ! ls ~/backups（查看备份目录） |
总结
psql 是 Postgres 最核心、最灵活的管理工具，掌握它可摆脱对图形界面的依赖，高效完成数据库全生命周期管理。核心要点可归纳为：
- 连接场景：本地用 sudo -u postgres psql 或 psql -d -U，远程需加 -h -p；
- 核心操作：用 SQL 命令（CREATE DATABASE、GRANT）实现功能，用元命令（\l、\d）简化查看；
- 权限安全：遵循 “最小权限原则”，避免随意赋予 SUPERUSER 权限，远程连接需配置 pg_hba.conf 授权。