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 demo

3.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 最核心、最灵活的管理工具，学会了就不用依赖图形界面，能高效把数据库的全生命周期管起来。核心要点就这么几条：

1. 连接场景：本地用 sudo -u postgres psql 或 psql -d -U，远程得加 -h -p；
2. 核心操作：用 SQL 命令（CREATE DATABASE、GRANT）实现功能，用元命令（\l、\d）简化查看；
3. 权限安全：遵循「最小权限原则」，别随便给 SUPERUSER 权限，远程连接要在 pg_hba.conf 里配好授权。