分：系统登录、元命令、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

-- 删表（安全写法）
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.tgrelid=tbl.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