name: postgresql-psql description: PostgreSQL psql 的全面指南 - PostgreSQL 的交互式终端客户端。用于连接 PostgreSQL 数据库、执行查询、管理数据库/表、配置连接选项、格式化输出、编写脚本、管理事务，以及使用高级 psql 功能进行数据库管理和开发。 license: PostgreSQL version: 1.0.0

PostgreSQL psql 技能

PostgreSQL psql（PostgreSQL 交互式终端）是用于与 PostgreSQL 数据库交互的主要命令行客户端。它提供交互式查询执行和强大的脚本功能，用于数据库管理和运维。

何时使用此技能

使用此技能当：

从命令行连接 PostgreSQL 数据库
交互式执行 SQL 查询
编写 SQL 脚本进行自动化
创建和管理数据库与模式
管理数据库对象（表、视图、索引、函数）
备份和恢复数据库
配置连接和认证
格式化和导出查询结果
管理事务和权限
调试 SQL 查询
自动化数据库管理任务
设置复制和高可用性
创建存储过程和函数

核心概念

REPL 模型

psql 作为交互式 REPL（读取-求值-打印循环）运行
接受 SQL 命令和元命令（反斜杠命令）
在会话中跨命令维护连接状态
支持命令历史和编辑

命令类型

SQL 命令：标准 SQL 语句（SELECT、INSERT、UPDATE、DELETE 等）
元命令：psql 特定命令，前缀为反斜杠（如 \dt、\d）
反斜杠命令：控制查询输出、会话变量和 psql 行为

连接模型

每个会话单一数据库连接
可以在不重新连接的情况下切换数据库
连接状态包括当前数据库、用户和搜索路径
环境变量和 .pgpass 用于凭据管理

连接选项

基本连接命令

psql [OPTIONS] [DBNAME [USERNAME]]

常见连接选项

# 使用用户名和主机连接
psql -U username -h hostname -p 5432 -d database_name

# 使用连接字符串连接
psql postgresql://username:password@hostname:5432/database_name

# 使用密码提示连接
psql -U postgres -h localhost -W

# 连接到本地机器的特定数据库
psql -d myapp_development

# 环境变量（替代方法）
export PGUSER=postgres
export PGPASSWORD=mypassword
export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=mydb
psql

连接字符串格式

标准 URI 格式：

postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]

示例：

postgresql://app_user:secretpass@db.example.com:5432/production_db?sslmode=require

认证方法

密码文件（.pgpass）：

# ~/.pgpass（chmod 600）
hostname:port:database:username:password
localhost:5432:mydb:postgres:mypassword
*.example.com:5432:*:appuser:apppass

通过 SSH 隧道连接：

ssh -L 5432:localhost:5432 user@remote-host
psql -U postgres -h localhost

SSL/TLS 连接选项

# 要求 SSL
psql -h hostname -sslmode require -U username database

# 验证证书
psql -h hostname -sslmode verify-full \
  -sslcert=/path/to/client-cert.crt \
  -sslkey=/path/to/client-key.key \
  -sslrootcert=/path/to/ca-cert.crt database

# SSL 模式：disable、allow、prefer（默认）、require、verify-ca、verify-full

基本元命令

数据库和模式导航

\l 或 \list                    # 列出所有数据库
\l+ 或 \list+                  # 列出数据库及其大小
\c 或 \connect DATABASE USER   # 连接到不同数据库
\dn 或 \dn+                    # 列出模式（命名空间）
\dt 或 \dt+                    # 列出当前模式中的表
\di 或 \di+                    # 列出索引
\dv 或 \dv+                    # 列出视图
\dm 或 \dm+                    # 列出物化视图
\ds 或 \ds+                    # 列出序列
\df 或 \df+                    # 列出函数/过程
\da 或 \da+                    # 列出聚合函数
\dT 或 \dT+                    # 列出数据类型
\dF 或 \dF+                    # 列出文本搜索配置

对象检查命令

\d 或 \d NAME                  # 描述表、视图、索引、序列或函数
\d+ 或 \d+ NAME                # 扩展描述，带详细信息
\da PATTERN                    # 列出匹配模式的聚合函数
\db 或 \db+                    # 列出表空间
\dc 或 \dc+                    # 列出字符集编码
\dC 或 \dC+                    # 列出类型转换
\dd 或 \dd+                    # 列出对象描述/注释
\dD 或 \dD+                    # 列出域
\de 或 \de+                    # 列出外部数据包装器
\dE 或 \dE+                    # 列出外部服务器
\dF 或 \dF+                    # 列出文本搜索配置
\dFd 或 \dFd+                  # 列出文本搜索字典
\dFp 或 \dFp+                  # 列出文本搜索解析器
\dFt 或 \dFt+                  # 列出文本搜索模板
\dg 或 \dg+                    # 列出数据库角色/用户
\dl 或 \dl+                    # 列出大对象（同 \lo_list）
\dL 或 \dL+                    # 列出过程语言
\dO 或 \dO+                    # 列出排序规则
\dp 或 \dp+                    # 列出表访问权限
\dRp 或 \dRp+                  # 列出复制源
\dRs 或 \dRs+                  # 列出复制订阅
\ds 或 \ds+                    # 列出序列
\dt 或 \dt+                    # 列出表
\dU 或 \dU+                    # 列出用户映射
\du 或 \du+                    # 列出角色
\dv 或 \dv+                    # 列出视图
\dx 或 \dx+                    # 列出扩展
\dX 或 \dX+                    # 列出扩展统计

格式化和输出命令

\a                             # 切换对齐和非对齐输出
\C [STRING]                    # 设置表标题
\f [STRING]                    # 为非对齐输出设置字段分隔符
\H                             # 切换 HTML 输出模式
\pset OPTION [VALUE]           # 设置输出选项（详见下文）
\t [on|off]                    # 切换仅元组输出（无头尾）
\T [STRING]                    # 设置 HTML 表标签属性
\x 或 \x [on|off|auto]         # 切换扩展/垂直输出
\g 或 \g [FILENAME|COMMAND]    # 执行查询并将输出发送到文件/命令

\pset 选项

\pset border [0-2]             # 设置边框显示（0=无，1=ascii，2=unicode）
\pset columns WIDTH            # 设置列宽限制
\pset csv                      # 设置 CSV 输出格式
\pset expanded [on|off|auto]   # 切换扩展输出
\pset fieldsep STRING          # 设置字段分隔符
\pset footer [on|off]          # 切换页脚显示
\pset format [aligned|unaligned|csv|tsv|html|latex|latex-longtable|troff-ms]
\pset header [on|off]          # 切换页眉显示
\pset linestyle [ascii|old-ascii|unicode] # 设置线绘制样式
\pset null STRING              # 设置表示 NULL 的字符串
\pset numericlocale [on|off]   # 切换区域特定的数字格式
\pset pager [on|off|always]    # 控制分页器使用
\pset recordsep STRING         # 设置记录分隔符
\pset recordsep0 [on|off]      # 使用空终止符分隔记录
\pset tableattr STRING         # 设置 HTML 表属性
\pset title STRING             # 设置查询标题
\pset tuples_only [on|off]     # 切换仅元组模式

文件和历史命令

\copy QUERY TO FILENAME [FORMAT]          # 客户端 COPY（需要较少权限）
\copy QUERY TO STDOUT                     # 复制到标准输出
\copy TABLE FROM FILENAME [FORMAT]        # 从文件导入数据
\e 或 \edit                               # 在编辑器中编辑当前查询缓冲区
\e FILENAME                               # 在编辑器中编辑文件
\ef [FUNCNAME]                            # 编辑函数定义
\ev [VIEWNAME]                            # 编辑视图定义
\w FILENAME 或 \write FILENAME            # 将当前查询缓冲区写入文件
\i FILENAME 或 \include FILENAME          # 从文件执行 SQL 命令
\ir FILENAME 或 \include_relative FILE    # 执行相对路径文件
\s [FILENAME]                             # 显示命令历史（或保存到文件）
\o FILENAME 或 \out FILENAME              # 将所有输出发送到文件
\o                                        # 将输出返回到终端

批处理和脚本命令

\echo TEXT                     # 打印文本（在脚本中有用）
\errverbose                    # 以详细形式显示最后错误
\q 或 \quit                    # 退出 psql
\! COMMAND 或 \shell COMMAND   # 执行 shell 命令
\cd DIRECTORY                  # 更改工作目录
\pwd                           # 打印当前工作目录
\set VARIABLE VALUE            # 设置 psql 变量
\unset VARIABLE                # 取消设置 psql 变量
\setenv VARNAME VALUE          # 设置环境变量
\getenv VARNAME                # 获取环境变量值
\prompt [TEXT] VARIABLE        # 提示用户输入并设置变量

事务命令

\begin 或 BEGIN                # 开始事务
\commit 或 COMMIT              # 提交事务
\rollback 或 ROLLBACK          # 回滚事务
\savepoint NAME                # 创建保存点
\release SAVEPOINT             # 释放保存点
\rollback TO SAVEPOINT         # 回滚到保存点

信息命令

\d+ TABLENAME                  # 显示表及其扩展信息和存储信息
\dt *.*                        # 列出所有模式中的所有表
\dn *                          # 列出所有模式
\du                            # 列出所有用户/角色
\db                            # 列出表空间
\dx                            # 列出已安装的扩展
\h 或 \help                    # 列出可用的 SQL 命令
\h COMMAND 或 \help COMMAND    # 显示特定 SQL 命令的帮助
\?                             # 显示 psql 帮助
\copyright                     # 显示 PostgreSQL 版权/许可信息
\version 或 SELECT version()   # 显示 PostgreSQL 版本

命令行选项

连接选项

-h, --host=HOSTNAME           # 服务器主机名（默认：localhost）
-p, --port=PORT               # 服务器端口（默认：5432）
-U, --username=USERNAME       # PostgreSQL 用户名（默认：$USER）
-d, --dbname=DBNAME           # 要连接的数据库名
-w, --no-password             # 从不提示密码
-W, --password                # 强制密码提示

输出和格式化选项

-A, --no-align                # 非对齐表输出模式
-c, --command=COMMAND         # 运行单个命令并退出
-C, --copy-only               # （已弃用，使用 \copy 代替）
-d, --dbname=DBNAME           # 指定数据库
-E, --echo-hidden             # 显示内部查询
-e, --echo-all                # 发送前显示每个命令
-b, --echo-errors             # 显示失败命令
-f, --file=FILENAME           # 从文件执行命令
-F, --field-separator=CHAR    # 为非对齐输出设置字段分隔符
-H, --html                    # HTML 表输出模式
-l, --list                    # 列出可用数据库并退出
-L, --log-file=FILENAME       # 将会话记录到文件
-n, --no-readline             # 禁用 readline（行编辑）
-o, --output=FILENAME         # 将结果写入文件
-P, --pset=VARIABLE=VALUE     # 设置打印选项
-q, --quiet                   # 静默运行（无横幅，单行模式）
-R, --record-separator=CHAR   # 为非对齐输出设置记录分隔符
-S, --single-step             # 单步模式（确认每个命令）
-s, --single-transaction      # 在单个事务中执行文件
-t, --tuples-only             # 仅打印行（无头尾）
-T, --table-attr=STRING       # 设置 HTML 表标签属性
-v, --set=VARIABLE=VALUE      # 设置 psql 变量
-V, --version                 # 显示版本并退出
-x, --expanded                # 扩展表输出模式
-X, --no-psqlrc               # 不读取 ~/.psqlrc 启动文件
-1, --single-line             # 行尾终止 SQL 命令

其他选项

-a, --all                     # （已弃用）
-j, --job=NUM                 # （用于 pg_dump 的并行转储）
--help                        # 显示帮助消息
--version                     # 显示版本
--on-error-stop               # 在首次错误时停止

变量和配置

内置变量

# 提示变量
psql -v PROMPT1='%/%R%# '     # 设置主提示
psql -v PROMPT2='%R%# '       # 设置续行提示
psql -v PROMPT3='>> '         # 设置输出模式提示

# 提示扩展代码：
# %n = 数据库用户名
# %m = 数据库服务器主机名（第一部分）
# %> = 数据库服务器主机名完整
# %p = 数据库服务器端口
# %d = 数据库名
# %/ = 当前模式
# %~ = 类似 %/，但如果模式匹配用户名则为 ~
# %# = 如果是超级用户则为 #，否则为 >
# %? = 最后查询结果状态
# %% = 文字 %
# %[..%] = 不可见字符（用于终端控制序列）

配置文件（~/.psqlrc）

# psql 启动时自动加载
# 设置默认选项
\set QUIET ON
\set SQLHISTSIZE 10000

# 配置输出
\pset null '[NULL]'
\pset border 2
\pset linestyle unicode
\pset expanded auto
\pset pager always

# 定义有用变量
\set conn_user 'SELECT current_user;'
\set dbsize 'SELECT pg_size_pretty(pg_database_size(current_database()));'
\set tables 'SELECT tablename FROM pg_tables WHERE schemaname = ''public'';'
\set functions 'SELECT proname FROM pg_proc;'

# 定义快捷方式
\set uptime 'SELECT now() - pg_postmaster_start_time() AS uptime;'
\set locks 'SELECT pid, usename, pg_blocking_pids(pid) as blocked_by, query, state FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;'

# 设置计时
\timing ON

# 连接到默认数据库
\c mydb

变量替换

-- 使用 :variable 语法
\set table_name mytable
SELECT * FROM :table_name;

-- 使用 :'variable' 用于文字字符串
\set schema_name public
SELECT * FROM :"schema_name".mytable;

-- 使用 :'variable' 语法在字符串上下文中
\set username 'postgres'
SELECT * FROM pg_tables WHERE tableowner = :'username';

-- 使用 :' ' 用于标识符引用
\set id_name "customTable"
SELECT * FROM :"id_name";

基本 SQL 操作

查询执行

-- 简单查询，立即执行
SELECT * FROM users;

-- 多行查询（直到分号）
SELECT id, name, email
FROM users
WHERE active = true;

-- 查询结果到文件
SELECT * FROM large_table \g output.txt

-- 查询管道到命令
SELECT * FROM users \g | wc -l

-- 执行先前命令
\g

-- 作为仅元组执行（无头尾）
SELECT * FROM users;

创建对象

-- 创建数据库
CREATE DATABASE myapp_db;

-- 创建模式
CREATE SCHEMA app_schema;

-- 创建表
CREATE TABLE app_schema.users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX idx_users_email ON app_schema.users(email);

-- 创建视图
CREATE VIEW app_schema.active_users AS
SELECT id, name, email FROM app_schema.users WHERE active = true;

-- 创建函数
CREATE OR REPLACE FUNCTION app_schema.get_user_count()
RETURNS INTEGER AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM app_schema.users);
END;
$$ LANGUAGE plpgsql;

数据操作

-- 插入单行
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

-- 插入多行
INSERT INTO users (name, email) VALUES
  ('Jane Smith', 'jane@example.com'),
  ('Bob Johnson', 'bob@example.com');

-- 从查询插入
INSERT INTO users_backup SELECT * FROM users;

-- 更新数据
UPDATE users SET active = false WHERE last_login < now() - interval '30 days';

-- 删除数据
DELETE FROM users WHERE id = 999;

-- RETURNING 子句（查看更改内容）
UPDATE users SET status = 'active' 
WHERE id = 1 
RETURNING id, name, status;

事务管理

事务控制

-- 开始事务
BEGIN;
-- 或
START TRANSACTION;

-- 提交更改
COMMIT;
-- 或
END;

-- 回滚更改
ROLLBACK;

-- 创建保存点
SAVEPOINT sp1;
-- ... 执行语句 ...
ROLLBACK TO sp1;  # 回滚到保存点
RELEASE sp1;      # 释放保存点

-- 多语句事务
BEGIN;
  INSERT INTO accounts (name, balance) VALUES ('Alice', 1000);
  INSERT INTO accounts (name, balance) VALUES ('Bob', 1000);
  UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
  UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;

事务隔离级别

-- 设置事务隔离级别
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;  # PostgreSQL 默认
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 显示当前事务状态
SHOW transaction_isolation;

高级功能

全文搜索

-- 创建全文搜索向量
ALTER TABLE documents ADD COLUMN search_vector tsvector;

UPDATE documents SET search_vector = 
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''));

-- 创建索引以便快速搜索
CREATE INDEX idx_documents_search ON documents USING GIN(search_vector);

-- 搜索文档
SELECT * FROM documents 
WHERE search_vector @@ to_tsquery('english', 'database & tutorial');

-- 按相关性排序结果
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM documents, to_tsquery('english', 'database') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

窗口函数

-- 行号
SELECT id, name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- 运行总和
SELECT id, amount, date,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

-- 分区结果
SELECT id, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- LEAD/LAG（下一行/上一行）
SELECT id, date, amount,
  LAG(amount) OVER (ORDER BY date) AS prev_amount,
  LEAD(amount) OVER (ORDER BY date) AS next_amount
FROM transactions;

JSON 操作

-- 存储 JSON
INSERT INTO documents VALUES (1, '{"name": "Alice", "age": 30}');

-- 访问 JSON 字段
SELECT data -> 'name' AS name FROM documents;

-- 访问并返回文本
SELECT data ->> 'name' AS name_text FROM documents;  # 返回文本

-- 检查键是否存在
SELECT * FROM documents WHERE data ? 'name';

-- JSON 数组操作
SELECT json_array_length(data) FROM documents;

-- JSON 聚合
SELECT json_agg(name) FROM users;

-- JSONB（二进制 JSON）性能更佳
CREATE TABLE config (id INT, settings JSONB);
INSERT INTO config VALUES (1, '{"theme": "dark", "lang": "en"}');

-- JSONB 操作符更高效
SELECT settings @> '{"theme": "dark"}' FROM config;

公用表表达式（CTE）

-- 简单 CTE
WITH active_users AS (
  SELECT id, name, email FROM users WHERE active = true
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';

-- 递归 CTE（树遍历）
WITH RECURSIVE category_hierarchy AS (
  SELECT id, name, parent_id, 0 AS level
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id, h.level + 1
  FROM categories c
  JOIN category_hierarchy h ON c.parent_id = h.id
)
SELECT * FROM category_hierarchy;

-- 多个 CTE
WITH orders_2024 AS (
  SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024
),
customer_totals AS (
  SELECT customer_id, SUM(total_amount) AS total
  FROM orders_2024
  GROUP BY customer_id
)
SELECT c.name, ct.total
FROM customers c
JOIN customer_totals ct ON c.id = ct.customer_id
ORDER BY ct.total DESC;

使用 psql 脚本

运行 SQL 文件

# 执行文件
psql -d mydb -f script.sql

# 执行并输出到文件
psql -d mydb -f script.sql -o results.txt

# 执行并在错误时停止
psql -d mydb -f script.sql --on-error-stop

# 在单个事务中执行
psql -d mydb -f script.sql -s

# 多个文件（按顺序执行）
psql -d mydb -f init.sql -f seed.sql -f verify.sql

SQL 脚本最佳实践

-- sample_script.sql

-- 设置执行模式
\set ON_ERROR_STOP ON
\set QUIET OFF

-- 如果需要，删除现有对象
DROP TABLE IF EXISTS temp_table;

-- 创建表
CREATE TABLE temp_table (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

-- 插入数据
INSERT INTO temp_table (name) VALUES
  ('Record 1'),
  ('Record 2'),
  ('Record 3');

-- 验证结果
SELECT * FROM temp_table;

-- 清理
DROP TABLE temp_table;

-- 报告
\echo 'Script completed successfully!'

动态 SQL 脚本

#!/bin/bash

# 使用 psql 变量的 Bash 脚本
DATABASE="myapp_db"
TABLE_NAME="users"
SCHEMA_NAME="public"

# 执行变量替换
psql -d $DATABASE -v table_name=$TABLE_NAME \
  -v schema_name=$SCHEMA_NAME -c "
  SELECT COUNT(*) FROM :schema_name.:table_name;
"

# 循环遍历数据库
for db in $(psql -l | awk '{print $1}'); do
  if [[ ! "$db" =~ "template" ]]; then
    echo "Backing up $db..."
    pg_dump $db > /backups/$db.sql
  fi
done

导入和导出

COPY 命令

-- 服务器端 COPY（需要对文件操作具有超级用户权限）
COPY users (id, name, email) 
TO '/tmp/users.csv' 
WITH (FORMAT CSV, HEADER TRUE, QUOTE '"', ESCAPE '\\');

-- 导入 CSV
COPY users (id, name, email)
FROM '/tmp/users.csv'
WITH (FORMAT CSV, HEADER TRUE, QUOTE '"', ESCAPE '\\');

-- 制表符分隔值
COPY users TO '/tmp/users.tsv' WITH (FORMAT TEXT, DELIMITER E'\t');

-- 处理 NULL
COPY users TO '/tmp/users.csv' 
WITH (FORMAT CSV, NULL 'N/A', QUOTE '"');

客户端 COPY（\copy）

# 导出到 CSV（从 psql）
\copy users TO '/home/user/users.csv' WITH (FORMAT CSV, HEADER)

# 导出查询结果
\copy (SELECT id, name, email FROM users WHERE active = true) \
  TO '/tmp/active_users.csv' WITH (FORMAT CSV, HEADER)

# 导入 CSV
\copy users (id, name, email) FROM '/tmp/users.csv' WITH (FORMAT CSV, HEADER)

# 导出到标准输出（管道到文件）
\copy users TO STDOUT WITH (FORMAT CSV, HEADER) > users.csv

# 从标准输入导入
cat users.csv | \copy users FROM STDIN WITH (FORMAT CSV, HEADER)

使用 pg_dump 和 pg_restore

# 转储整个数据库
pg_dump -d mydb -U postgres > mydb_backup.sql

# 自定义格式转储（压缩）
pg_dump -d mydb -Fc > mydb_backup.dump

# 转储特定表
pg_dump -d mydb -t users > users_backup.sql

# 仅转储数据
pg_dump -d mydb -a > mydb_data.sql

# 仅转储模式
pg_dump -d mydb -s > mydb_schema.sql

# 从 SQL 文件恢复
psql -d mydb_restored -f mydb_backup.sql

# 从自定义格式恢复
pg_restore -d mydb_restored mydb_backup.dump

# 列出转储内容
pg_restore -l mydb_backup.dump

性能和调试

查询分析

-- 显示查询执行计划
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 详细分析实际执行
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- 显示更多细节
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
  SELECT * FROM users WHERE active = true;

-- JSON 输出用于程序解析
EXPLAIN (FORMAT JSON, ANALYZE)
  SELECT COUNT(*) FROM users;

查看查询性能

-- 当前查询
SELECT pid, usename, state, query FROM pg_stat_activity;

-- 长时间运行查询
SELECT pid, usename, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- 阻塞查询
SELECT blocked_pid, blocking_pid, blocked_statement, blocking_statement
FROM pg_stat_statements;

-- 表大小
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 数据库大小
SELECT pg_size_pretty(pg_database_size('mydb'));

设置计时

# 启用查询计时
\timing ON

# 禁用查询计时
\timing OFF

# 在批处理模式中
psql -d mydb -c "\timing ON" -f script.sql

查询日志记录

# 记录所有查询到文件
psql -d mydb -L query.log -f script.sql

# 显示内部查询（系统查询）
psql -d mydb -E

用户和权限管理

创建和管理用户

-- 创建用户（角色）
CREATE USER appuser WITH PASSWORD 'secure_password';

-- 创建无登录权限的角色
CREATE ROLE admin_role;

-- 更改用户
ALTER USER appuser WITH PASSWORD 'new_password';

-- 创建超级用户
CREATE USER superuser_name WITH PASSWORD 'password' SUPERUSER;

-- 列出用户
\du

-- 删除用户
DROP USER appuser;

授予权限

-- 授予数据库使用权限
GRANT USAGE ON SCHEMA public TO appuser;

-- 授予表权限
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO appuser;

-- 授予所有权限
GRANT ALL PRIVILEGES ON users TO appuser;

-- 授予序列权限（用于自动递增）
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO appuser;

-- 授予所有表权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO appuser;

-- 为未来表设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO appuser;

-- 查看权限
\dp users
\dp+ users

行级安全性（RLS）

-- 在表上启用 RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- 创建策略
CREATE POLICY user_policy ON users
  USING (id = current_user_id());  # 这需要实现

-- 查看策略
\d+ users

高级 psql 功能

元命令技巧

# 详细显示最后错误
\errverbose

# 执行计时
\timing

# 回显发送到服务器的所有命令
\set ECHO all

# 列出所有变量
\set

# 查看特定变量
\echo :DBNAME

# 动态查询执行
\set query 'SELECT * FROM users WHERE id = ' :user_id
:query;

提示自定义

# 设置自定义提示
psql -v PROMPT1='user@db> '
psql -v PROMPT1='%/%R%# '   # database/role# 

# 在 .psqlrc 中
\set PROMPT1 '%n@%m:%>/%/ %R%# '
\set PROMPT2 '> '
\set PROMPT3 '>> '

函数和过程管理

-- 列出函数
\df

-- 显示函数源代码
\df+ function_name

-- 创建函数
CREATE OR REPLACE FUNCTION get_user(user_id INT)
RETURNS TABLE(id INT, name TEXT, email TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT u.id, u.name, u.email FROM users u WHERE u.id = user_id;
END;
$$ LANGUAGE plpgsql;

-- 执行函数
SELECT * FROM get_user(1);

-- 存储过程（无返回值）
CREATE OR REPLACE PROCEDURE archive_old_records()
AS $$
BEGIN
  INSERT INTO archived_users
  SELECT * FROM users WHERE created_at < now() - interval '1 year';
  DELETE FROM users WHERE created_at < now() - interval '1 year';
  COMMIT;
END;
$$ LANGUAGE plpgsql;

-- 调用过程
CALL archive_old_records();

触发器和事件

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_user_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER user_update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_user_timestamp();

-- 查看触发器
\d+ users

-- 删除触发器
DROP TRIGGER user_update_timestamp ON users;

备份和恢复

数据库备份策略

# 完整数据库备份（自定义格式）
pg_dump -d production_db -Fc -j 4 > backup.dump

# 压缩备份
pg_dump -d production_db -Fc -Z 9 > backup.dump

# 并行备份（大型数据库更快）
pg_dump -d production_db -Fd -j 4 -f backup_dir

# 备份特定模式
pg_dump -d production_db -n public -n app > schemas.sql

# 自定义格式备份（允许选择性恢复）
pg_dump -d production_db -Fc > backup.dump

# 查看备份内容
pg_restore -l backup.dump | less

# 恢复特定表
pg_restore -d restored_db -t users backup.dump

# 列出可用备份
pg_dump -U postgres -l -w postgres

时间点恢复

# 完整备份
pg_dump -d mydb > base_backup.sql

# 启用 WAL 归档（在 postgresql.conf 中）
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'

# 恢复到时间点
pg_restore -d recovered_db base_backup.sql
# 然后应用 WAL 文件直到目标时间

常见模式和示例

连接池脚本

#!/bin/bash
# 使用 psql 的简单连接池

MAX_CONNECTIONS=10
CONNECTION_POOL=()

for i in {1..$MAX_CONNECTIONS}; do
  (
    while true; do
      psql -d mydb -c "SELECT 1"
      sleep 60
    done
  ) &
  CONNECTION_POOL+=($!)
done

# 保持脚本运行
wait

数据库健康检查

-- health_check.sql
SELECT 
  'PostgreSQL Version' AS check_type,
  version() AS result
UNION ALL
SELECT 
  'Database Size',
  pg_size_pretty(pg_database_size(current_database()))
UNION ALL
SELECT 
  'Active Connections',
  count(*)::text
FROM pg_stat_activity
UNION ALL
SELECT 
  'Cache Hit Ratio',
  ROUND(sum(heap_blks_hit)::numeric / (sum(heap_blks_hit) + sum(heap_blks_read)), 4)::text
FROM pg_statio_user_tables;

自动化维护

#!/bin/bash
# 每周维护脚本

DATABASES=$(psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres';")

for db in $DATABASES; do
  echo "Analyzing $db..."
  psql -d "$db" -c "ANALYZE;"
  
  echo "Vacuuming $db..."
  psql -d "$db" -c "VACUUM;"
  
  echo "Reindexing $db..."
  psql -d "$db" -c "REINDEX DATABASE \"$db\";"
done

最佳实践

使用连接池 - 对于应用程序，交互式 psql 不需要
启用 SSL/TLS - 生产环境中始终使用加密连接
使用 .pgpass - 避免在脚本中硬编码密码
设置 ON_ERROR_STOP - 在脚本中防止错误后继续
使用事务 - 在显式事务中包装相关操作
策略性索引 - 分析查询计划，在频繁过滤/连接列上创建索引
监控性能 - 定期检查慢查询和表大小
定期备份 - 使用自定义格式的 pg_dump 以获得灵活性
使用模式 - 逻辑组织数据库对象
文档化权限 - 保持清晰的用户角色和权限记录
测试恢复 - 定期练习从备份恢复
使用参数化查询 - 在应用程序中防止 SQL 注入
监控锁 - 在 pg_stat_activity 中检查阻塞查询
维护统计 - 定期运行 ANALYZE 以优化查询

提示和技巧

快速导航

# 连接并在一行中执行
psql -d mydb -c "SELECT COUNT(*) FROM users;"

# 执行文件并退出
psql -d mydb -f script.sql

# 静默模式（最小输出）
psql -q -d mydb -c "SELECT * FROM users LIMIT 1;"

# 管道输出到其他命令
psql -d mydb -t -c "SELECT name FROM users;" | sort | uniq

# 验证连接而不执行命令
psql -d mydb -c ""

有用的 .psqlrc 快捷方式

# 添加到 ~/.psqlrc 以便捷快捷方式
\set dbsize 'SELECT pg_size_pretty(pg_database_size(current_database()))'
\set uptime 'SELECT now() - pg_postmaster_start_time() AS uptime'
\set psql_version 'SELECT version()'
\set table_sizes 'SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'\''.\'\'||tablename)) FROM pg_tables ORDER BY pg_total_relation_size(schemaname||'\''.\'\'||tablename) DESC'

# 在 psql 中使用：
# :dbsize
# :table_sizes

处理大型结果集

# 为大型结果设置分页器
\pset pager always

# 使用 LIMIT 测试
SELECT * FROM huge_table LIMIT 10;

# 使用 OFFSET 分页
SELECT * FROM users LIMIT 10 OFFSET 0;
SELECT * FROM users LIMIT 10 OFFSET 10;

# 将结果获取到文件而非终端
\copy (SELECT * FROM huge_table) TO huge_export.csv;

故障排除

连接问题

# 详细连接诊断
psql -d mydb -v verbose=on --echo-queries

# 检查连接设置
psql --version
psql -d postgres -c "SHOW password_encryption;"

# TCP/IP 连接性测试
psql -h hostname -d postgres -U postgres -c "SELECT 1;"

常见错误消息

FATAL: password authentication failed
  → 检查密码、用户存在、.pgpass 权限正确（600）

FATAL: no pg_hba.conf entry for host
  → 数据库服务器的 pg_hba.conf 需要连接规则

FATAL: database "name" does not exist
  → 创建数据库或检查数据库名称拼写

ERROR: permission denied for schema
  → 授予用户模式使用权限

ERROR: syntax error
  → 检查 SQL 语法，使用 \h 获取命令帮助

性能问题

-- 查找慢查询
SELECT * FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

-- 检查缺失索引
SELECT schemaname, tablename, attname 
FROM pg_stat_user_tables, pg_attribute 
WHERE pg_stat_user_tables.relid = pg_attribute.attrelid
AND seq_scan > 0;

-- 检查缓存效率
SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

高级配置

性能调优参数

# 在 ~/.psqlrc 中
\set HISTSIZE 10000
\pset pager always
\pset null '[NULL]'
\pset linestyle unicode

# 默认环境变量
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=mydb
export PGPASSFILE=$HOME/.pgpass

输出格式比较

-- 对齐（默认）
\pset format aligned

-- CSV
\pset format csv
\copy (SELECT * FROM users) TO STDOUT WITH (FORMAT CSV);

-- HTML
\pset format html
SELECT * FROM users LIMIT 5;

-- LaTeX
\pset format latex
SELECT * FROM users LIMIT 5;

-- 扩展（垂直）
\x
SELECT * FROM users LIMIT 1;

资源和文档

官方 PostgreSQL 文档：https://www.postgresql.org/docs/
psql 手册：https://www.postgresql.org/docs/current/app-psql.html
PostgreSQL 维基：https://wiki.postgresql.org/
pgAdmin（GUI 工具）：https://www.pgadmin.org/
DBA 最佳实践：https://www.postgresql.org/docs/current/sql-syntax.html

总结

psql 是一个强大、灵活的命令行工具，用于 PostgreSQL 数据库管理和开发。关键优势：

交互式 REPL 用于即时查询反馈
强大的元命令用于对象检查和管