psql 命令行运维操作详解

PostgreSQL 提供了功能强大的交互式命令行工具 **psql**，用于数据库的管理、查询、维护与调试。本文系统阐述了在 `psql` 环境下的常用运维操作，涵盖数据库、模式（Schema）、表、用户与权限管理、连接与会话监控、数据导入导出及日常维护命令。本文基于 PostgreSQL 16.1 版本进行说明。

---

## 一、连接与登录

### 1. 基本连接命令

可在命令行中直接连接指定数据库：

```bash
psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
```

参数说明：

* `-h`：数据库主机地址
* `-p`：端口号（默认 5432）
* `-U`：连接用户名
* `-d`：数据库名称

登录后提示符通常显示为：

```
psql (16.1)
Type "help" for help.

postgres=#
```

### 2. 密码中包含特殊字符的处理

若密码中包含特殊符号（如 `$`, `!`, `@`, `#`, `^`, `&` 等），需进行转义或使用安全的方式传递。例如：

```bash
PGPASSWORD='P@ss$word!' psql -h localhost -U postgres -d mydb
```

推荐使用环境变量 `PGPASSWORD`，避免在命令行中明文输入或被历史记录保存。
也可使用 `.pgpass` 文件保存认证信息（权限设为 0600）：

```
hostname:port:database:username:password
```

---

## 二、数据库管理

切换数据库（在 psql 内）：

\c target_db           -- 切换到 target_db，保持当前用户
\c target_db someuser  -- 切换并以 someuser 身份连接（如果有权限）

### 1. 查看与切换数据库

```sql
\l         			       -- 列出所有数据库
\l+ 				          -- 显示数据库大小与详细信息
\c target_db                  -- 切换到 target_db数据库，保持当前用户
\c target_db someuser         -- 切换并以 someuser 身份连接（如果有权限）
\conninfo     			    -- 查看当前连接信息
```

### 2. 创建与删除数据库

```sql
CREATE DATABASE analytics OWNER dbadmin ENCODING 'UTF8' TEMPLATE template0;
DROP DATABASE analytics;
```

若存在连接占用，需先断开：

```sql
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'analytics' AND pid <> pg_backend_pid();

DROP DATABASE analytics;
```

### 3. 查看数据库大小

```sql
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;
```

---

## 三、模式（Schema）管理

### 1. 查看与切换搜索路径

```sql
\dn                 -- 列出所有模式
SHOW search_path;   -- 查看当前搜索路径
SET search_path TO audit, public;
```

### 2. 创建与删除模式

```sql
CREATE SCHEMA audit AUTHORIZATION audit_user;
DROP SCHEMA audit CASCADE;
```

---

## 四、表管理

### 1. 查看与描述表

```sql
\dt                     -- 列出当前模式下的所有表
\dt schema_name.*       -- 列出指定模式下的表
\d table_name           -- 查看表结构
\d+ table_name          -- 查看表结构及表大小
```

### 2. 表的创建、修改与删除

```sql
CREATE TABLE public.users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE public.users ADD COLUMN email TEXT;
ALTER TABLE public.users RENAME COLUMN username TO name;
DROP TABLE public.users CASCADE;
```

### 3. 清空与重置序列

```sql
TRUNCATE TABLE public.users;
TRUNCATE TABLE public.users RESTART IDENTITY;
```

### 4. 查看表大小

```sql
SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
```

---

## 五、用户与角色管理

### 1. 查看与创建角色

```sql
\du
CREATE ROLE appuser WITH LOGIN PASSWORD 'securePwd123';
CREATE ROLE readonly NOLOGIN;
```

### 2. 修改与删除角色

```sql
ALTER ROLE appuser WITH PASSWORD 'newPwd' VALID UNTIL '2026-01-01';
DROP ROLE appuser;
```

若删除角色失败，需先转移对象所有权：

```sql
REASSIGN OWNED BY appuser TO postgres;
DROP OWNED BY appuser;
DROP ROLE appuser;
```

### 3. 授权与角色继承

```sql
GRANT readonly TO analyst;
REVOKE readonly FROM analyst;
```

---

## 六、权限管理

### 1. 授予与回收表权限

```sql
GRANT SELECT, INSERT ON TABLE public.users TO appuser;
REVOKE INSERT ON TABLE public.users FROM appuser;
```

### 2. 设置默认权限

```sql
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;
```

### 3. 查看权限

```
\dp
\dp public.users
```

---

## 七、连接与会话管理

### 1. 查看活动会话

```sql
SELECT pid, usename, datname, client_addr, state, query
FROM pg_stat_activity;
```

### 2. 终止连接与取消查询

```sql
SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE pid = 12345;
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';
```

### 3. 查看锁信息

```sql
SELECT pid, relation::regclass, mode, granted
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE relation IS NOT NULL;
```

---

## 八、数据导入与导出

### 1. 使用 `\copy` 导入导出数据（客户端执行）

```sql
\copy public.users TO '/tmp/users.csv' CSV HEADER;
\copy public.users FROM '/tmp/users.csv' CSV HEADER;
```

### 2. 使用 `COPY`（服务器端执行）

```sql
COPY public.users TO '/var/lib/postgresql/users.csv' CSV HEADER;
COPY public.users FROM '/var/lib/postgresql/users.csv' CSV HEADER;
```

### 3. `pg_dump` 与 `pg_restore`

**全库备份：**

```bash
pg_dump -U postgres -h 127.0.0.1 -F c -f /backup/mydb.dump mydb
```

**恢复：**

```bash
pg_restore -U postgres -d targetdb /backup/mydb.dump
```

**单表导出：**

```bash
pg_dump -U postgres -t public.users -f users.sql mydb
```

### 4. SQL 文件恢复

```bash
# 遇到错误跳过
psql -U postgres -d mydb -f users.sql
# 遇到错误终止
psql -U postgres -d mydb -v ON_ERROR_STOP=1  -f users.sql
```

---

## 九、数据库维护操作

### 1. 垃圾回收与统计信息更新

```sql
VACUUM;
VACUUM FULL;
ANALYZE;
VACUUM ANALYZE;
```

### 2. 重建索引与数据重排

```sql
REINDEX TABLE public.users;
CLUSTER public.users USING users_pkey;
```

### 3. 查看维护状态

```sql
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables;
```

---

## 十、psql 使用技巧

| 功能     | 命令                                                |
| ------ | ------------------------------------------------- |
| 查看帮助   | `\?`、`\h`                                         |
| 切换输出格式 | `\x` 或 `\x auto`                                  |
| 显示执行时间 | `\timing`                                         |
| 禁用分页   | `\pset pager off`                                 |
| 设置输出格式 | `\pset format aligned` 或 `\pset format unaligned` |
| 执行脚本   | `\i /path/script.sql`                             |
| 执行系统命令 | `\! ls -l`                                        |
| 退出会话   | `\q`                                              |

---

## 十一、安全与操作注意事项

1. **执行危险操作前**（DROP、TRUNCATE、REASSIGN OWNED）应确保数据库备份可用。
2. **VACUUM FULL**、**CLUSTER** 等命令会加排他锁，应在维护窗口执行。
3. 删除用户前务必先执行 `REASSIGN OWNED` 与 `DROP OWNED`。
4. **不要**在命令行直接输入明文密码；应使用 `PGPASSWORD` 或 `.pgpass` 文件。
5. 修改配置文件（`postgresql.conf`、`pg_hba.conf`）后应执行：

```sql
   SELECT pg_reload_conf();
   ```
6. 对于生产系统，应定期执行统计信息更新与索引重建，保持查询性能稳定。

---

## 十二、常用命令汇总

```sql
-- 数据库
\l+; \c dbname; \conninfo;

-- 模式
\dn; CREATE SCHEMA audit; DROP SCHEMA audit CASCADE;

-- 表
\dt+; \d+ table; TRUNCATE table; DROP TABLE table;

-- 用户
\du; CREATE ROLE u1 LOGIN PASSWORD 'p'; REASSIGN OWNED BY u1 TO postgres;

-- 权限
GRANT SELECT ON TABLE t1 TO u1; \dp;

-- 会话
SELECT * FROM pg_stat_activity; SELECT pg_terminate_backend(pid);

-- 导出/导入
\copy table TO 'file.csv' CSV HEADER;
pg_dump -U user -F c -f backup.dump db;
pg_restore -U user -d db backup.dump;

-- 维护
VACUUM ANALYZE; REINDEX TABLE t1;
```

---

## 结语

`psql` 是 PostgreSQL 最核心的管理工具。熟练掌握本文所列命令，可覆盖绝大多数数据库日常运维场景，包括对象管理、权限控制、性能维护与备份恢复。建议在生产环境中配合审计与版本控制系统使用，保证操作的可追溯性与可恢复性。

---

是否希望我将这篇文章排版为 **Markdown 博客格式文件** 或导出为 **PDF 技术手册样式**（带代码高亮与索引）？