PostgreSQL命令行使用教程：掌握 psql 工具

psql                                      # 用当前系统用户连本地默认库
psql -U app -d sales_db                   # 指定用户和库
psql -h db.example.com -p 5432 -U app -d sales_db

psql "postgresql://app:secret@db.example.com:5432/sales_db?sslmode=require"

# 格式：hostname:port:database:username:password
cat > ~/.pgpass <<'EOF'
db.example.com:5432:sales_db:app:your_password
*:*:*:app:fallback_password
EOF
chmod 600 ~/.pgpass      # 权限必须是 600，否则 psql 会忽略它

\d products

                               Table "public.products"
 Column |     Type      | Collation | Nullable |               Default
--------+---------------+-----------+----------+--------------------------------------
 id     | integer       |           | not null | nextval('products_id_seq'::regclass)
 name   | text          |           |          |
 price  | numeric(10,2) |           |          |
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)

\l              -- 列出所有数据库（等价 \list）
\c dbname       -- 切换数据库（等价 \connect）
\c dbname otheruser  -- 切换数据库同时切用户
\du             -- 列出所有角色（用户）
\du+            -- 附角色描述
\conninfo       -- 显示当前连接信息

\conninfo

You are connected to database "postgres" as user "postgres" via socket in
"/var/run/postgresql" at port "5432".

\?              -- 所有元命令的帮助
\h              -- SQL 命令列表
\h CREATE TABLE -- 查看某条 SQL 的语法
\h CREATE INDEX

\x auto
SELECT * FROM big_wide_table;

-[ RECORD 1 ]---
id    | 1
name  | 张三
price | 9.99

\pset null '[NULL]'      -- 空值显示为 [NULL]（默认是空白，容易和空字符串混淆）
\pset border 2           -- 边框样式（0/1/2）
\pset format csv         -- 输出格式（aligned/unaligned/csv/html/latex/json）
\pset pager off          -- 关闭分页器（结果直接刷屏，配合 \o 写文件）
\pset expanded auto      -- 等价 \x auto

\timing on
SELECT count(*) FROM big_table;

 count
--------
 1000000
(1 row)

Time: 123.456 ms

SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
\watch 2

\o /tmp/report.txt
SELECT * FROM sales_summary;
SELECT * FROM top_customers;
\o                    -- 不带参数表示恢复输出到终端

SELECT
    first_name,
    last_name
FROM employees
WHERE hire_date > '2020-01-01'
;                          -- 分号结束并执行

\set tbl employees
SELECT count(*) FROM :tbl;

\set minimum 100
SELECT * FROM products WHERE price > :minimum;

SELECT count(*) AS cnt FROM products \gset
\echo '产品数量: ' :cnt

产品数量:  2

psql -d sales_db -f setup.sql -v ON_ERROR_STOP=1

if psql -d sales_db -v ON_ERROR_STOP=1 -f migrate.sql; then
    echo "迁移成功"
else
    echo "迁移失败，退出码 $?"
    exit 1
fi

# 无对齐 + 无表头，便于 awk/cut 处理
psql -A -t -d sales_db -c "SELECT id, name FROM products"

# 直接输出 CSV
psql --csv -d sales_db -c "SELECT id, name FROM products" > products.csv

-- \copy：文件在本地机器
\copy (SELECT * FROM users) TO '/tmp/users.csv' CSV HEADER
\copy users FROM '/tmp/users.csv' CSV HEADER

-- COPY：文件在数据库服务器上
COPY employees FROM '/var/lib/postgresql/employees.csv' DELIMITER ',' CSV HEADER;

# 纯文本 SQL（可读，但恢复慢）
pg_dump -U app sales_db > sales_db.sql

# 自定义格式（推荐：压缩 + 支持并行恢复 + 选择性恢复）
pg_dump -U app -Fc sales_db > sales_db.dump

# 只导出结构 / 只导出数据
pg_dump -U app --schema-only sales_db > schema.sql
pg_dump -U app --data-only sales_db > data.sql

# 只导出某张表
pg_dump -U app -t products sales_db > products.sql

# 并行导出（大库提速，-Fd 目录格式才支持 -j）
pg_dump -U app -Fd -j 4 sales_db -f /backup/sales_db_dir

# 纯文本：用 psql 执行
psql -U app -d sales_db -f sales_db.sql

# 自定义/目录格式：用 pg_restore
pg_restore -U app -d sales_db sales_db.dump
pg_restore -U app -d sales_db -j 4 /backup/sales_db_dir   # 并行恢复

# 只恢复某张表
pg_restore -U app -d sales_db -t products sales_db.dump

-- 空值显示
\pset null '[NULL]'

-- 宽表自动扩展
\x auto

-- 显示执行时间
\timing on

-- 历史文件按数据库分开
\set HISTFILE ~/.psql_history-:DBNAME
\set HISTSIZE 5000

-- 出错即停（交互式下建议关掉，脚本里建议开）
\set ON_ERROR_STOP off

-- 常用查询的快捷方式（用 \set + :var）
\set active 'SELECT pid, state, query FROM pg_stat_activity WHERE state = $q$active$q$;'

EXPLAIN SELECT * FROM products WHERE price > 10;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products WHERE price > 10;

-- 正在运行的查询
SELECT pid, usename, state, wait_event_type, wait_event,
       now() - query_start AS duration, left(query, 60) AS q
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- 连接数统计
SELECT usename, state, count(*) FROM pg_stat_activity GROUP BY usename, state;

SELECT pg_cancel_backend(12345);       -- 温和取消（等价 Ctrl+C）
SELECT pg_terminate_backend(12345);    -- 强制断开连接

-- 表的增删改查次数、顺序扫描次数
SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

-- 索引使用情况（idx_scan 为 0 的索引可能是多余的）
SELECT relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan;

-- 单表大小
SELECT pg_size_pretty(pg_total_relation_size('products'));

-- 各表大小排行
SELECT 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
LIMIT 10;

-- 当前数据库大小
SELECT pg_size_pretty(pg_database_size(current_database()));

VACUUM products;                 -- 回收死元组空间（不锁表，可并发）
VACUUM ANALYZE products;         -- 回收 + 更新统计信息
VACUUM FULL products;            -- 彻底整理并归还空间给操作系统（⚠️ 会锁表！）
ANALYZE products;                -- 只更新统计信息
REINDEX TABLE products;          -- 重建索引（索引膨胀时用）
REINDEX TABLE CONCURRENTLY products;  -- 并发重建，不阻塞写（更慢）

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed:
FATAL:  password authentication failed for user "app"

# 看当前的 hba 规则
psql -c "SHOW hba_file;"

SHOW search_path;
SET search_path TO myschema, public;

-- 看某张表的权限
\dp products

-- 授权
GRANT SELECT, INSERT, UPDATE ON products TO app;
GRANT USAGE ON SCHEMA public TO app;             -- 访问 schema 本身也要授权
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;   -- 自增序列要单独授

-- —— 连接与数据库 ——
\l / \list            列出数据库
\c dbname [user]      切换数据库（可换用户）
\conninfo             当前连接信息
\du [pattern]         列出角色
\dn                   列出 schema

-- —— 看结构 ——
\d [table]            列出对象 / 看表结构
\d+ table             附大小和描述
\dt / \dv / \di / \ds / \df   表/视图/索引/序列/函数
\dp table             权限
\di+                  索引详情

-- —— 显示控制 ——
\x [auto]             扩展显示
\pset null '[NULL]'   空值显示
\pset border 2        边框
\timing [on|off]      显示耗时
\watch N              每 N 秒重跑
\o file / \o          输出到文件 / 恢复终端

-- —— 缓冲与编辑 ——
\p  打印缓冲    \e  编辑    \r  清空    \g  执行    \gx  扩展执行    \gset  存变量

-- —— 帮助 ——
\?                    元命令帮助
\h [SQL]              SQL 语法帮助

-- —— 其他 ——
\i file.sql           执行文件
\copy ...             客户端导入导出
\echo text            打印文本
\q                    退出

# —— 命令行 ——
psql -U app -d db                  交互连接
psql -c "SELECT 1"                 执行一条
psql -f file.sql -v ON_ERROR_STOP=1 执行文件（遇错即停）
psql -A -t -c "SQL"                无对齐输出，便于脚本
psql --csv -c "SQL" > out.csv      输出 CSV

# —— 备份恢复 ——
pg_dump -Fc db > db.dump           自定义格式（推荐）
pg_dump -Fd -j 4 db -f /backup/dir 并行导出
pg_restore -d db db.dump           恢复