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

\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;

\dt+                    -- 查看所有表及大小
SELECT schemaname, tablename, n_live_tup 
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

-- 使用psql变量
\set table_name 'users'
\copy :table_name TO '/tmp/:table_name.csv' WITH CSV HEADER;

-- 查看当前连接数
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';