PostgreSQL 常用配置与工具

psql 常用命令

bash

# 连接数据库
psql -U username -d database -h hostname -p 5432

# 执行 SQL 文件
psql -U username -d database -f query.sql

# 导出数据
psql -U username -d database -c "COPY (SELECT * FROM users) TO STDOUT WITH CSV HEADER" > users.csv

# 导入数据
psql -U username -d database -c "COPY users FROM STDIN WITH CSV HEADER" < users.csv

# 查看表大小
psql -U username -d database -c "\dt+" 

# 查看索引
psql -U username -d database -c "\di"

# 查看函数
psql -U username -d database -c "\df"

# 查看视图
psql -U username -d database -c "\dv"

# 执行命令
psql -U username -d database -c "SELECT version();"

psql 内部命令

sql

-- 列出所有数据库
\l
\l+

-- 切换数据库
\c database_name

-- 列出所有表
\dt
\dt+

-- 列出所有索引
\di

-- 列出所有视图
\dv

-- 列出所有函数
\df
\df+

-- 查看表结构
\d table_name
\d+ table_name

-- 查看索引详情
\d index_name

-- 查看序列
\ds

-- 查看用户和权限
\du
\dp

-- 查看表空间
\db

-- 列出所有类型
\dT

-- 查看编码
\encoding

-- 查看变量
\show all

-- 执行外部文件
\i filename.sql

-- 将查询结果输出到文件
\o output.txt
SELECT * FROM users;
\o

-- 格式化输出
\x  -- 切换扩展显示模式（行列转换）
\a  -- 切换对齐模式
\t  -- 只显示数据
\H  -- HTML 格式输出

-- 启用计时
\timing

-- 编辑函数/视图
\ef function_name
\ev view_name

-- 查看历史命令
\s

-- 清空屏幕
\! cls (Windows)
\! clear (Linux/Mac)

常用 SQL 模板

分页查询

sql

-- MySQL 风格（不推荐大数据量）
SELECT * FROM users LIMIT 10 OFFSET 20;

-- Keyset 分页（推荐）
SELECT * FROM users 
WHERE id > 20 
ORDER BY id 
LIMIT 10;

-- 完整分页查询
WITH page_data AS (
    SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20
),
total_count AS (
    SELECT COUNT(*) AS total FROM users
)
SELECT p.*, t.total FROM page_data p, total_count t;

树形结构查询

sql

-- 邻接表模型
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    parent_id INTEGER REFERENCES categories(id)
);

-- 查询所有祖先（向上）
WITH RECURSIVE ancestors AS (
    SELECT id, name, parent_id, 0 AS level
    FROM categories WHERE id = 5
    UNION ALL
    SELECT c.id, c.name, c.parent_id, a.level + 1
    FROM categories c
    JOIN ancestors a ON c.id = a.parent_id
)
SELECT * FROM ancestors ORDER BY level;

-- 查询所有后代（向下）
WITH RECURSIVE descendants AS (
    SELECT id, name, parent_id, 0 AS level
    FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id, c.name, c.parent_id, d.level + 1
    FROM categories c
    JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM descendants ORDER BY level;

数据透视

sql

-- 静态透视
SELECT 
    status,
    COUNT(*) AS count,
    SUM(CASE WHEN age < 30 THEN 1 ELSE 0 END) AS young,
    SUM(CASE WHEN age >= 30 AND age < 60 THEN 1 ELSE 0 END) AS middle,
    SUM(CASE WHEN age >= 60 THEN 1 ELSE 0 END) AS old
FROM users
GROUP BY status;

-- 动态透视（使用 crosstab）
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
    'SELECT status, age_group, COUNT(*) 
     FROM (SELECT status, 
           CASE WHEN age < 30 THEN ''young''
                WHEN age < 60 THEN ''middle''
                ELSE ''old'' END AS age_group
           FROM users) t
     GROUP BY status, age_group
     ORDER BY status',
    'VALUES (''young''), (''middle''), (''old'')'
) AS ct(status VARCHAR, young BIGINT, middle BIGINT, old BIGINT);

数据去重

sql

-- 删除重复数据（保留 id 最大的）
DELETE FROM users u1
USING users u2
WHERE u1.id < u2.id 
  AND u1.email = u2.email;

-- 使用 DISTINCT ON
SELECT DISTINCT ON (email) * FROM users ORDER BY email, created_at DESC;

-- 查找重复数据
SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

合并数据

sql

-- 合并多个表的数据
SELECT id, name, 'A' AS source FROM table_a
UNION ALL
SELECT id, name, 'B' AS source FROM table_b;

-- 合并并去重
SELECT id, name FROM table_a
UNION
SELECT id, name FROM table_b;

-- 交集
SELECT id, name FROM table_a
INTERSECT
SELECT id, name FROM table_b;

-- 差集
SELECT id, name FROM table_a
EXCEPT
SELECT id, name FROM table_b;

备份与恢复

逻辑备份

bash

# 导出整个数据库
pg_dump -U postgres -d mydb -F c -b -v -f mydb.dump

# 导出单个表
pg_dump -U postgres -d mydb -t users -f users.sql

# 导出数据（纯 SQL）
pg_dump -U postgres -d mydb --data-only -f data.sql

# 导出 schema（不含数据）
pg_dump -U postgres -d mydb --schema-only -f schema.sql

# 导出所有数据库（需要 superuser）
pg_dumpall -U postgres -f all_databases.sql

逻辑恢复

bash

# 恢复整个数据库
pg_restore -U postgres -d mydb -v mydb.dump

# 恢复单个表
psql -U postgres -d mydb -f users.sql

# 清空数据后恢复
pg_restore -U postgres -d mydb --clean -v mydb.dump

# 仅恢复数据
pg_restore -U postgres -d mydb --data-only -v mydb.dump

# 从 pg_dumpall 恢复
psql -U postgres -f all_databases.sql

物理备份

bash

# 创建基础备份（需要配置 archive_mode）
pg_basebackup -U postgres -D /backup/base -Ft -z -P -v

# 恢复基础备份
rm -rf $PGDATA
mkdir -p $PGDATA
tar -xf /backup/base.tar.gz -C $PGDATA
pg_resetwal -f $PGDATA
pg_ctl start

常用管理查询

sql

-- 查看数据库大小
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;

-- 查看表大小（包含索引）
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
       pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
       pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) AS index_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 查看最慢的查询
SELECT 
    query,
    calls,
    mean_exec_time,
    total_exec_time,
    rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 查看连接数
SELECT 
    state,
    COUNT(*) 
FROM pg_stat_activity 
GROUP BY state;

-- 查看当前执行的查询
SELECT 
    pid,
    now() - query_start AS duration,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- 取消正在执行的查询
SELECT pg_cancel_backend(pid);  -- 温和取消
SELECT pg_terminate_backend(pid);  -- 强制终止

-- 查看锁
SELECT 
    l.locktype,
    l.relation::regclass,
    l.mode,
    l.granted,
    l.virtualxid,
    l.transactionid,
    l.pid,
    a.usename,
    a.query
FROM pg_locks l
LEFT JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
   OR l.pid IN (SELECT pid FROM pg_stat_activity WHERE wait_event_type = 'Lock');

-- 杀死阻塞进程
SELECT pg_terminate_backend(blocker.pid)
FROM pg_stat_activity blocker
JOIN pg_stat_activity victim ON blocker.pid = ANY(ARRAY[
    SELECT l.pid FROM pg_locks l 
    JOIN pg_stat_activity a ON l.pid = a.pid 
    WHERE NOT l.granted
])
WHERE blocker.pid != pg_backend_pid();

-- 查看表膨胀率
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    n_dead_tup,
    n_live_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

常用函数速查

字符串函数

sql

-- 字符串操作
SELECT 'Hello' || ' ' || 'World';  -- 连接
SELECT CONCAT('Hello', ' ', 'World');  -- 连接
SELECT LENGTH('Hello');  -- 长度
SELECT UPPER('hello');  -- 转大写
SELECT LOWER('HELLO');  -- 转小写
SELECT TRIM('  hello  ');  -- 去空格
SELECT LTRIM('  hello');  -- 去左空格
SELECT RTRIM('hello  ');  -- 去右空格
SELECT SUBSTRING('Hello World', 1, 5);  -- 截取
SELECT REPLACE('Hello World', 'World', 'PostgreSQL');  -- 替换
SELECT SPLIT_PART('a,b,c', ',', 2);  -- 分割取第N部分
SELECT POSITION('World' IN 'Hello World');  -- 查找位置
SELECT REVERSE('abc');  -- 反转

-- 字符串格式化
SELECT FORMAT('%s, %s', 'Hello', 'World');
SELECT FORMAT('ID: %s, Name: %s', 1, '张三');

日期时间函数

sql

-- 当前时间
SELECT NOW();  -- 当前时间戳
SELECT CURRENT_DATE;  -- 当前日期
SELECT CURRENT_TIME;  -- 当前时间
SELECT CURRENT_TIMESTAMP;  -- 当前时间戳

-- 日期提取
SELECT EXTRACT(YEAR FROM NOW());  -- 年
SELECT EXTRACT(MONTH FROM NOW());  -- 月
SELECT EXTRACT(DAY FROM NOW());  -- 日
SELECT EXTRACT(DOW FROM NOW());  -- 周几（0=周日）
SELECT EXTRACT(DOY FROM NOW());  -- 年第几天

-- 日期计算
SELECT NOW() + INTERVAL '1 day';  -- 加一天
SELECT NOW() - INTERVAL '1 week';  -- 减一周
SELECT AGE(NOW(), '2024-01-01');  -- 计算年龄/差值
SELECT DATE_TRUNC('month', NOW());  -- 截断到月

-- 日期格式化
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_CHAR(NOW(), 'YYYY年MM月DD日');
SELECT TO_DATE('20240101', 'YYYYMMDD');

聚合函数

sql

SELECT COUNT(*);  -- 总行数
SELECT COUNT(DISTINCT column);  -- 去重计数
SELECT SUM(column);  -- 求和
SELECT AVG(column);  -- 平均值
SELECT MIN(column);  -- 最小值
SELECT MAX(column);  -- 最大值
SELECT STRING_AGG(column, ',');  -- 字符串聚合
SELECT ARRAY_AGG(column);  -- 数组聚合
SELECT JSON_AGG(column);  -- JSON 聚合
SELECT JSONB_AGG(column);  -- JSONB 聚合
SELECT XML_AGG(column);  -- XML 聚合

条件表达式

sql

-- CASE
SELECT CASE 
    WHEN age < 18 THEN '未成年'
    WHEN age < 30 THEN '青年'
    WHEN age < 60 THEN '中年'
    ELSE '老年'
END FROM users;

-- COALESCE（返回第一个非 NULL）
SELECT COALESCE(NULL, NULL, 'default', 'value');  -- 'default'

-- NULLIF（NULL 则返回 NULL，否则返回第一个参数）
SELECT NULLIF(0, 0);  -- NULL
SELECT NULLIF('a', 'b');  -- 'a'

-- GREATEST（返回最大值）
SELECT GREATEST(1, 5, 3);  -- 5

-- LEAST（返回最小值）
SELECT LEAST(1, 5, 3);  -- 1

Spring框架

Spring基础

SpringBoot快速开发

SpringMVC开发

SpringData

SpringSecurity

SpringCloud

Spring高级

基础应用服务

数据库

MySQL数据库

PostgreSQL数据库

核心原理

PostgreSQL 常用配置与工具

psql 常用命令

psql 内部命令

常用 SQL 模板

分页查询

树形结构查询

数据透视

数据去重

合并数据

备份与恢复

逻辑备份

逻辑恢复

物理备份

常用管理查询

常用函数速查

字符串函数

日期时间函数

聚合函数

条件表达式

Spring基础

MySQL数据库

PostgreSQL数据库

核心原理

PostgreSQL 常用配置与工具 ​

psql 常用命令 ​

psql 内部命令 ​

常用 SQL 模板 ​

分页查询 ​

树形结构查询 ​

数据透视 ​

数据去重 ​

合并数据 ​

备份与恢复 ​

逻辑备份 ​

逻辑恢复 ​

物理备份 ​

常用管理查询 ​

常用函数速查 ​

字符串函数 ​

日期时间函数 ​

聚合函数 ​

条件表达式 ​

PostgreSQL 常用配置与工具

psql 常用命令

psql 内部命令

常用 SQL 模板

分页查询

树形结构查询

数据透视

数据去重

合并数据

备份与恢复

逻辑备份

逻辑恢复

物理备份

常用管理查询

常用函数速查

字符串函数

日期时间函数

聚合函数

条件表达式