PostgreSQL 常用终端命令总结
📌 连接与基本操作 连接数据库 # 连接到默认数据库（postgres） psql -U username # 连接到指定数据库 psql -U username -d database_name # 连接指定主机和端口 psql -h localhost -p 5432 -U username -d database_name # 直接执行SQL并退出 psql -U username -d database_name -c "SELECT * FROM users;" # 执行SQL文件 psql -U username -d database_name -f script.sql 退出psql \q -- 退出 exit -- 也可用 📋 查看信息命令 数据库相关 -- 列出所有数据库 \l \l+ -- 显示更多信息 -- 查看当前连接的数据库 \c -- 切换数据库 \c database_name -- 查看数据库大小 \l+ -- 包含大小信息 SELECT pg_database_size('database_name'); 表相关 -- 列出所有表 \dt \dt+ -- 显示更多信息（大小、描述等） \dt schema.* -- 查看指定schema下的表 -- 查看表结构 \d table_name \d+ table_name -- 更详细，包含注释 -- 列出所有关系（表、视图、序列等） \d \d+ -- 更详细 其他对象 -- 查看所有schema \dn \dn+ -- 查看所有视图 \dv \dv+ -- 查看所有索引 \di \di+ -- 查看所有序列 \ds \ds+ -- 查看所有函数 \df \df+ \df pattern -- 按模式匹配 -- 查看所有触发器 \dtr -- 查看所有用户/角色 \du \du+ 🔧 交互式命令设置 查询格式化 -- 设置显示模式 \x on -- 扩展显示模式（每字段一行） \x off -- 标准表格模式 \x auto -- 根据屏幕宽度自动切换 -- 设置输出格式 \a -- 切换对齐模式（对齐/不对齐） \pset format aligned -- 对齐格式 \pset format csv -- CSV格式 \pset format html -- HTML格式 \pset format json -- JSON格式 -- 显示查询时间 \timing on \timing off -- 显示执行SQL \echo on \echo off 常用组合设置 -- 生产环境推荐 \pset format aligned \pset border 2 \timing on -- 导出数据推荐 \pset format csv \pset footer off \o output.csv 📁 数据导入导出 导出数据 -- 导出查询结果为CSV \copy (SELECT * FROM table_name) TO 'output.csv' WITH CSV HEADER; -- 导出整个表为CSV \copy table_name TO 'output.csv' WITH CSV HEADER; -- 导出为CSV指定分隔符 \copy table_name TO 'output.csv' WITH CSV DELIMITER '|' HEADER; -- 导出为INSERT语句 \copy table_name TO 'dump.sql' WITH INSERT; -- 导出到文件 \o output.txt SELECT * FROM table_name; \o -- 恢复输出到屏幕 导入数据 -- 从CSV导入 \copy table_name FROM 'data.csv' WITH CSV HEADER; -- 从CSV导入指定列 \copy table_name(col1, col2) FROM 'data.csv' WITH CSV HEADER; -- 执行SQL文件 \i script.sql \include script.sql 🔍 性能调试 -- 查看查询执行计划 EXPLAIN SELECT * FROM users; EXPLAIN ANALYZE SELECT * FROM users; -- 实际执行 -- 查看详细执行计划 EXPLAIN (BUFFERS, ANALYZE, VERBOSE) SELECT * FROM users; -- 查看正在运行的查询 SELECT * FROM pg_stat_activity WHERE state = 'active'; -- 查看慢查询 SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10; -- 分析表（更新统计信息） ANALYZE table_name; ANALYZE; -- 分析所有表 💾 数据库维护 备份与恢复 # 备份数据库（命令行） pg_dump -U username database_name > backup.sql pg_dump -U username -d database_name -Fc > backup.dump # 自定义格式 # 备份特定表 pg_dump -U username -t table_name database_name > table_backup.sql # 恢复数据库 psql -U username -d database_name < backup.sql # 恢复自定义格式 pg_restore -U username -d database_name backup.dump 维护命令 -- 清理并分析数据库 VACUUM; VACUUM ANALYZE; -- 清理并更新统计信息 VACUUM FULL; -- 完全清理（锁表） -- 重建索引 REINDEX INDEX index_name; REINDEX TABLE table_name; REINDEX DATABASE database_name; -- 查看数据库大小 SELECT pg_database_size('database_name'); -- 查看表大小 SELECT pg_total_relation_size('table_name'); -- 查看索引大小 SELECT pg_indexes_size('table_name'); 🛠️ 权限管理 -- 查看权限 \z \dp \dp table_name -- 授予权限 GRANT SELECT ON table_name TO username; GRANT ALL PRIVILEGES ON database_name TO username; -- 撤销权限 REVOKE INSERT ON table_name FROM username; 🎯 实用脚本示例 1. 快速查看表信息和数据量 \dt+ -- 查看所有表及大小 SELECT schemaname, tablename, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC; 2. 批量导出表为CSV -- 使用psql变量 \set table_name 'users' \copy :table_name TO '/tmp/:table_name.csv' WITH CSV HEADER; 3. 监控连接数 -- 查看当前连接数 SELECT count(*) FROM pg_stat_activity; -- 查看各数据库连接数 SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname; -- 终止连接 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='database_name'; ⚡ 快速参考卡片 命令 用途 \l 列出所有数据库 \c dbname 切换数据库 \dt 列出所有表 \d tablename 查看表结构 \du 查看用户/角色 \dn 查看schema \df 查看函数 \x 切换扩展显示 \timing 显示执行时间 \copy ... TO 导出数据 \copy ... FROM 导入数据 \i file.sql 执行SQL文件 \q 退出 💡 小技巧 自动补全：在psql中按 Tab 键可以自动补全程SQL命令和对象名 ...