PostgreSQL 完整运维命令手册

# 启动服务
net start postgresql-9.5
# 停止服务
net stop postgresql-9.5
# 查看psql帮助
psql --help

-- 设置客户端编码为UTF-8（临时生效，当前会话）
\encoding utf-8

-- 查看当前客户端编码
\encoding
show client_encoding;

-- 查看数据库服务端编码（核心，建议建库时指定UTF8）
show server_encoding;

# 切换到postgres系统用户（推荐登录方式）
sudo -i -u postgres
# 直接进入psql交互终端
psql

psql -h 主机地址 -U 用户名 -d 数据库名 -p 端口 -W
# 参数说明
# -h：服务器IP，本地localhost/127.0.0.1
# -U：大写，数据库角色（默认管理员postgres）
# -d：指定要打开的数据库
# -p：端口默认5432
# -W：强制弹窗输入密码

# Windows CMD 登录默认postgres库
psql -h localhost -U postgres -p 5432

# 指定数据库登录fengdos
psql -h 127.0.0.1 -U postgres -d fengdos -p 5432

# 最简快速登录（本地、默认端口、默认库）
psql -U postgres

help        -- 总帮助
\copyright  -- 版权声明
\h / \help  -- SQL语法帮助
?           -- 所有\开头元命令帮助
\q / \quit  -- 退出psql终端

\l          -- 列出所有数据库
\l+         -- 列出数据库+字符集、所有者详细信息
\c dbname   -- 切换当前连接数据库（等价use）
\d          -- 查看当前库所有表、视图、索引、序列
\d+         -- 带注释、存储引擎、行数详情
\dt         -- 只查看数据表
\dt+        -- 表详细信息
\d 表名     -- 查看单张表字段、类型、约束结构
\di         -- 查看索引
\dv         -- 查看视图
\ds         -- 查看序列
\df         -- 查看函数
\x          -- 行转列展示（MySQL \G 等价，长字段友好）
\encoding   -- 查看客户端字符集

\password 用户名  -- 修改指定用户密码，修改后\q退出生效
\i /root/db.sql  -- 执行外部SQL脚本文件（本地服务器路径）
\! 系统命令      -- 在psql内执行操作系统命令，例：\! ls

-- 修改用户密码
ALTER USER username WITH PASSWORD '新密码';

-- 查看所有数据库角色
SELECT rolname FROM pg_roles;

-- 给用户赋予角色权限
GRANT 角色名 TO 用户名;

-- 授予数据库所有权限给用户
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

CREATE DATABASE chengyao;
DROP DATABASE IF EXISTS chengyao; -- 加IF EXISTS避免库不存在报错

-- 删除表
DROP TABLE IF EXISTS student;

-- 表重命名
ALTER TABLE 旧表名 RENAME TO 新表名;

-- 新增字段
ALTER TABLE 表名 ADD COLUMN 字段名 数据类型;

-- 删除字段
ALTER TABLE 表名 DROP COLUMN IF EXISTS 字段名;

-- 字段重命名
ALTER TABLE 表名 RENAME COLUMN 旧字段 TO 新字段;

-- 设置字段非空约束
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

-- 设置字段默认值
ALTER TABLE 表名 ALTER COLUMN 字段 SET DEFAULT '默认内容';

-- 删除字段默认值
ALTER TABLE 表名 ALTER COLUMN 字段 DROP DEFAULT;

-- 插入数据
INSERT INTO student(id,name,age) VALUES(1,'张三',18);

-- 查询全表
SELECT * FROM student;

-- 更新数据（务必加WHERE条件，否则全表更新）
UPDATE student SET age=19 WHERE id=1;

-- 删除单条数据
DELETE FROM student WHERE id=1;

-- 清空整张表（两种方式，TRUNCATE更快、重置自增）
DELETE FROM student;
TRUNCATE TABLE student;

-- 查询所有数据库名称
SELECT datname FROM pg_database ORDER BY datname;

-- 查询public模式下所有自定义表名（过滤系统表）
SELECT tablename 
FROM pg_tables 
WHERE schemaname='public' 
AND tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%'
ORDER BY tablename;

-- 查询存储过程/函数
SELECT proname FROM pg_proc;

-- 1. 给表新增自增临时字段
ALTER TABLE deltest ADD COLUMN rownum SERIAL PRIMARY KEY;

-- 2. 删除重复数据，只保留每组最大rownum记录
DELETE FROM deltest WHERE rownum NOT IN (SELECT MAX(rownum) FROM deltest GROUP BY 重复判断字段);

-- 3. 删除临时字段
ALTER TABLE deltest DROP COLUMN rownum;

-- x'十六进制值'::integer 强转整型
INSERT INTO tableAAA VALUES(x'0001f'::integer, '鉴权', 'Authority');

-- 错误写法：INSERT INTO TG_RELNAME ...
-- 正确写法
EXECUTE 'INSERT INTO ' || TG_RELNAME || ' VALUES ($1,$2,$3)' USING NEW.start_time, NEW.id, NEW.end_time;

-- 表数据导出到服务器文件
COPY 表名 TO '/服务器绝对路径/file.csv' WITH (FORMAT csv, DELIMITER ',', HEADER);

-- 服务器文件导入表
COPY 表名 FROM '/服务器绝对路径/file.csv' WITH (FORMAT csv, DELIMITER ',', HEADER);

-- psql客户端本地导出（推荐）
\copy student TO 'D:/student.csv' WITH csv header;

-- psql客户端本地导入
\copy student FROM 'D:/student.csv' WITH csv header;

-- 二进制高速导出（跨平台兼容性差）
COPY 表名 TO STDOUT WITH BINARY;

# 1. 单库备份为SQL文本（-C 附带建库语句）
pg_dump -U postgres -C -f db_backup.sql chengyao

# 2. 单库自定义压缩格式（推荐，可选择性恢复单表）
pg_dump -U postgres -F c -f db_backup.dump chengyao

# 3. 整个PG集群全量备份（所有库、角色、表空间）
pg_dumpall -U postgres > all_cluster_backup.sql

# 4. 仅备份单张表
pg_dump -U postgres -t public.student -f student_table.sql chengyao

# 1. SQL文件恢复（先手动创建目标库）
psql -U postgres -d new_db < db_backup.sql

# 2. 自定义压缩包恢复（pg_restore）
pg_restore -U postgres -d new_db db_backup.dump

# 3. 集群全量备份恢复（必须postgres超级用户）
psql -U postgres < all_cluster_backup.sql

-- 服务端绝对路径脚本
\i /opt/backup/db_backup.sql

-- 方案1：安装uuid-ossp扩展（支持uuid_generate_v4）
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- 验证调用
SELECT uuid_generate_v4();

-- 方案2：安装pgcrypto（推荐，PG13+内置，gen_random_uuid无需扩展）
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT gen_random_uuid();

CREATE TABLE test(
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT
);

EXTRACT(提取域 FROM 时间字段/时间常量)

-- 基础年、季度、月、日、时、分、秒
SELECT
EXTRACT(century FROM NOW()) AS 世纪,
EXTRACT(year FROM NOW()) AS 年份,
EXTRACT(quarter FROM NOW()) AS 季度,
EXTRACT(month FROM NOW()) AS 月份,
EXTRACT(week FROM NOW()) AS 周数,
EXTRACT(day FROM NOW()) AS 当月第几天,
EXTRACT(doy FROM NOW()) AS 当年第几天,
EXTRACT(dow FROM NOW()) AS 星期(0周日~6周六),
EXTRACT(hour FROM NOW()) AS 小时,
EXTRACT(minute FROM NOW()) AS 分钟,
EXTRACT(second FROM NOW()) AS 秒数,
-- epoch：Unix时间戳（1970-01-01至今总秒数，最常用）
EXTRACT(epoch FROM NOW()) AS 时间戳秒,
-- 毫秒级时间戳
EXTRACT(epoch FROM NOW())*1000 AS 时间戳毫秒;

-- 时间戳转时间
SELECT TO_TIMESTAMP(1785000000);

-- 日期加减运算
SELECT NOW() + INTERVAL '1 day';    -- 加1天
SELECT NOW() - INTERVAL '3 month';  -- 减3个月

-- 格式化输出
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');

CURRENT_DATE    -- 当前日期
CURRENT_TIME    -- 当前时间带时区
NOW() / LOCALTIMESTAMP -- 当前完整时间