PostgreSQL 高频常用命令整理

分系统登录、元命令、SQL 运维、库 / 表 / 索引 / 触发器、权限、FDW、性能排查、数据迁移一、登录 psql 命令行# 常规登录 psql -h 地址 -p 5432 -U 用户名 -d 数据库名 # 本地默认postgres用户 su - postgres psql # 免密本地快速进库 psql -d 库名二、psql 内置元命令反斜杠 \ 开头最常用\? # 查看所有元命令帮助 \q # 退出psql \l # 列出所有数据库 \c 库名 # 切换数据库 \dn # 查看当前库所有schema \dt # 查看当前schema所有表 \dt # 查表表注释 \d 表名 # 查看表结构、主键、约束 \d 表名 # 查看结构字段注释触发器大小你最常用 \di # 查看索引 \di 表名* # 查看某张表全部索引 \dy # 查看触发器 \dy 表名 # 查看指定表触发器 \df # 查看函数 \df 函数名 # 查看函数源码 \dx # 查看已安装扩展 \dx 插件名 # 查看插件详情 \du # 查看所有用户/角色 \set 变量 值 # 设置变量 \copy # 客户端导入导出CSV最常用高速导入三、数据库操作 SQL-- 创建数据库 CREATE DATABASE test_db; -- 删除数据库 DROP DATABASE IF EXISTS test_db; -- 查看当前所在库 SELECT current_database(); -- 查看PG版本 SELECT version(); SHOW server_version; SELECT server_version_num; -- 查看当前用户 SELECT current_user;四、Schema 模式操作PG 核心概念-- 创建schema CREATE SCHEMA IF NOT EXISTS biz; -- 删除schema连带内部所有对象 DROP SCHEMA IF EXISTS biz CASCADE; -- 查看搜索路径查表默认去哪里找 SHOW search_path; SET search_path TO biz,public;五、表常用 SQL你 article_works_base_info 日常操作-- 删表安全写法 DROP TABLE IF EXISTS 表名; DROP TABLE IF EXISTS 表名 CASCADE; -- 级联删外键 -- 清空表数据速度极快 TRUNCATE TABLE 表名; TRUNCATE TABLE 表名 CASCADE; -- 查询前N行 SELECT * FROM 表名 LIMIT 10; -- 统计总行数 SELECT COUNT(*) FROM 表名; -- 添加字段 ALTER TABLE 表名 ADD COLUMN 字段名 类型; -- 删除字段 ALTER TABLE 表名 DROP COLUMN IF EXISTS 字段名; -- 修改字段类型 ALTER TABLE 表名 ALTER COLUMN 字段名 TYPE 新类型; -- 重命名字段 ALTER TABLE 表名 RENAME COLUMN 旧名 TO 新名; -- 添加表注释 COMMENT ON TABLE 表名 IS 表说明; -- 添加字段注释 COMMENT ON COLUMN 表名.字段 IS 字段说明;六、索引管理-- 创建普通索引 CREATE INDEX idx_col ON 表名(字段); -- 创建表达式索引你works_link用到 CREATE INDEX idx_works_link ON article_works_base_info(left(works_link,255)); -- 删除索引 DROP INDEX IF EXISTS idx_col; -- 查看表所有索引 SELECT indexname, indexdef FROM pg_indexes WHERE tablename表名;七、触发器 函数-- 查看表触发器 SELECT tgname, pg_get_triggerdef(t.oid) FROM pg_trigger t JOIN pg_class tbl ON t.tgrelidtbl.oid WHERE NOT t.tgisinternal AND tbl.relname表名; -- 禁用表所有触发器同步数据必用 ALTER TABLE 表名 DISABLE TRIGGER ALL; -- 启用 ALTER TABLE 表名 ENABLE TRIGGER ALL; -- 删除触发器 DROP TRIGGER IF EXISTS 触发器名 ON 表名; -- 删除函数 DROP FUNCTION IF EXISTS 函数名;八、扩展插件管理mysql_fdw、pg_stat_statements 等-- 查看可用插件 SELECT name FROM pg_available_extensions ORDER BY name; -- 查看已安装插件 SELECT * FROM pg_extension; -- 安装插件 CREATE EXTENSION IF NOT EXISTS mysql_fdw; -- 卸载插件 DROP EXTENSION IF EXISTS mysql_fdw;九、mysql_fdw 跨 MySQL 访问全套命令-- 1.创建外部服务 CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host IP, port 3306, dbname mysql库名); -- 2.账号映射 CREATE USER MAPPING FOR 当前用户名 SERVER mysql_svr OPTIONS (username mysql账号, password mysql密码); -- 3.一键导入MySQL整张表为外部表 IMPORT FOREIGN SCHEMA public LIMIT TO (article_works_base_info) FROM SERVER mysql_svr INTO public; -- 4.跨库迁移数据 INSERT INTO pg本地表 SELECT * FROM mysql外部表;十、权限 用户常用-- 创建用户 CREATE USER test_user WITH PASSWORD 123456; -- 授权数据库 GRANT ALL ON DATABASE 库名 TO test_user; -- 授权schema所有表 GRANT ALL ON SCHEMA public TO test_user; GRANT ALL ON ALL TABLES IN SCHEMA public TO test_user; -- 修改密码 ALTER USER test_user WITH PASSWORD 新密码;十一、数据导入导出\copy 高速同步# 导出表到本地CSV \copy 表名 TO /tmp/data.csv WITH (FORMAT csv, HEADER, ENCODING UTF8); # CSV导入进表 \copy 表名 FROM /tmp/data.csv WITH (FORMAT csv, HEADER, ENCODING UTF8);十二、性能排查常用 SQL-- 查看慢SQL统计需要pg_stat_statements插件 SELECT query, total_time, calls FROM pg_stat_statements ORDER BY total_time DESC; -- 查看表大小 SELECT pg_size_pretty(pg_total_relation_size(表名)); -- 查看连接、杀掉慢连接 SELECT pid, usename, query FROM pg_stat_activity; SELECT pg_terminate_backend(pid); -- 查看索引使用情况 SELECT relname, idx_scan FROM pg_stat_user_indexes WHERE relname表名; -- 执行计划分析SQL快慢 EXPLAIN ANALYZE SELECT * FROM 表名 WHERE xxx;十三、备份恢复命令pg_dump/pg_restore# 整库备份 pg_dump -h IP -U 用户 -d 库名 backup.sql # 单表备份 pg_dump -h IP -U 用户 -d 库名 -t 表名 table_backup.sql # 恢复 psql -h IP -U 用户 -d 目标库 backup.sql