PostgreSQL — 世界最先进的开源关系型数据库

# 添加 PostgreSQL 官方仓库
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# 更新并安装
sudo apt update
sudo apt install -y postgresql-17

# 检查状态
sudo systemctl status postgresql

# 切换到 postgres 用户
sudo -u postgres psql

# 安装
brew install postgresql@17

# 启动服务
brew services start postgresql@17

# 连接
psql postgres

# Windows 命令行连接
psql -U postgres -h localhost

# 快速启动
docker run -d --name postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:17-alpine

# 连接
docker exec -it postgres psql -U postgres

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    username    VARCHAR(50) UNIQUE NOT NULL,
    email       TEXT NOT NULL,
    profile     JSONB DEFAULT '{}',
    tags        TEXT[] DEFAULT '{}',
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    is_active   BOOLEAN DEFAULT true,
    balance     NUMERIC(12,2) DEFAULT 0.00
);

-- B-Tree 索引（默认，适合等值和范围查询）
CREATE INDEX idx_users_email ON users(email);

-- GIN 索引（适合 JSONB、数组、全文搜索）
CREATE INDEX idx_users_profile ON users USING GIN(profile);

-- GiST 索引（适合地理空间、范围类型）
CREATE INDEX idx_geo ON locations USING GiST(coordinates);

-- BRIN 索引（适合时序数据，极小体积）
CREATE INDEX idx_logs_time ON logs USING BRIN(created_at);

-- 部分索引（只索引满足条件的行）
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- 表达式索引
CREATE INDEX idx_lower_email ON users(LOWER(email));

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    amount      NUMERIC(12,2) CHECK (amount > 0),
    status      VARCHAR(20) DEFAULT 'pending'
                CHECK (status IN ('pending','paid','shipped','done','cancelled')),
    order_no    VARCHAR(32) UNIQUE NOT NULL,
    CONSTRAINT  positive_amount CHECK (amount > 0)
);

-- 排他约束（防止会议室时间冲突）
CREATE TABLE reservations (
    room_id INT,
    during  TSTZRANGE,
    EXCLUDE USING GiST (room_id WITH =, during WITH &&)
);

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 检查余额
DO $$
BEGIN
    IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
        RAISE EXCEPTION '余额不足';
    END IF;
END $$;

COMMIT;

-- 发生错误自动回滚
-- 也可以用 SAVEPOINT 做部分回滚
BEGIN;
SAVEPOINT sp1;
INSERT INTO logs VALUES ('step1');
SAVEPOINT sp2;
INSERT INTO logs VALUES ('step2'); -- 假设这里出错
ROLLBACK TO sp2;                  -- 只回滚 step2
COMMIT;                            -- step1 仍然提交

-- 创建 schema
CREATE SCHEMA app;
CREATE SCHEMA analytics;

-- 在指定 schema 下建表
CREATE TABLE app.users (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE analytics.events (id SERIAL PRIMARY KEY, data JSONB);

-- 设置搜索路径
SET search_path TO app, public;

-- 查看当前搜索路径
SHOW search_path;

-- 基本 CTE
WITH active_users AS (
    SELECT id, username, email
    FROM users
    WHERE is_active = true AND created_at > NOW() - INTERVAL '30 days'
),
user_orders AS (
    SELECT user_id, COUNT(*) as order_count, SUM(amount) as total
    FROM orders
    WHERE user_id IN (SELECT id FROM active_users)
    GROUP BY user_id
)
SELECT u.username, u.email, COALESCE(o.order_count, 0) as orders, COALESCE(o.total, 0) as spent
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id
ORDER BY spent DESC;

-- 递归 CTE（遍历树形结构）
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 as depth
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

-- 排名：每个部门内按薪资排名
SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank_with_ties,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank
FROM employees;

-- 累计求和
SELECT date, revenue,
    SUM(revenue) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
FROM daily_sales;

-- 同比/环比
SELECT month, revenue,
    LAG(revenue, 1) OVER (ORDER BY month) as prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) as mom_change,
    LAG(revenue, 12) OVER (ORDER BY month) as prev_year,
    revenue - LAG(revenue, 12) OVER (ORDER BY month) as yoy_change
FROM monthly_revenue;

-- 查询 JSONB 字段
SELECT * FROM users WHERE profile->>'city' = '北京';
SELECT * FROM users WHERE profile @> '{"level": "vip"}';

-- JSONB 路径查询（PostgreSQL 12+）
SELECT * FROM users WHERE profile @? '$.addresses[*] ? (@.city == "上海")';

-- 更新 JSONB 字段
UPDATE users SET profile = profile || '{"verified": true}' WHERE id = 1;
UPDATE users SET profile = jsonb_set(profile, '{address,city}', '"深圳"') WHERE id = 1;

-- JSONB 聚合
SELECT profile->>'city' as city, COUNT(*) as user_count
FROM users
WHERE profile ? 'city'
GROUP BY profile->>'city'
ORDER BY user_count DESC;

-- JSONB 展开为行
SELECT id, elem->>'name' as tag_name
FROM users, jsonb_array_elements(profile->'tags') as elem;

-- 添加 tsvector 列并建立索引
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;
UPDATE articles SET search_vector =
    setweight(to_tsvector('chinese', title), 'A') ||
    setweight(to_tsvector('chinese', content), 'B');
CREATE INDEX idx_search ON articles USING GIN(search_vector);

-- 全文搜索查询
SELECT title, ts_rank(search_vector, query) as rank
FROM articles, to_tsquery('chinese', '数据库 & 优化') as query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- 搜索结果高亮
SELECT ts_headline('chinese', content, to_tsquery('chinese', '数据库'),
    'StartSel=<mark>, StopSel=</mark>, MaxWords=50') as snippet
FROM articles
WHERE search_vector @@ to_tsquery('chinese', '数据库');

-- 创建范围分区表（按时间）
CREATE TABLE logs (
    id      BIGSERIAL,
    msg     TEXT,
    level   VARCHAR(10),
    ts      TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (ts);

-- 创建分区
CREATE TABLE logs_2025q1 PARTITION OF logs FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE logs_2025q2 PARTITION OF logs FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
CREATE TABLE logs_2025q3 PARTITION OF logs FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');
CREATE TABLE logs_2025q4 PARTITION OF logs FOR VALUES FROM ('2025-10-01') TO ('2026-01-01');

-- 在分区表上创建索引（自动应用到所有分区）
CREATE INDEX idx_logs_ts ON logs(ts);

-- 删除旧分区（比 DELETE 快得多）
DROP TABLE logs_2024q1;

-- 每个用户最近 3 条订单
SELECT u.username, r.order_no, r.amount, r.created_at
FROM users u
CROSS JOIN LATERAL (
    SELECT order_no, amount, created_at
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) r;

-- 比传统方式（窗口函数 + 子查询）更直观高效

CREATE TABLE products (
    id         SERIAL PRIMARY KEY,
    price      NUMERIC(10,2),
    tax_rate   NUMERIC(4,2) DEFAULT 0.13,
    total      NUMERIC(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED,
    full_name  TEXT GENERATED ALWAYS AS (brand || ' ' || model) STORED,
    brand      TEXT,
    model      TEXT
);

-- 存在则更新
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (1, 1, NOW())
ON CONFLICT (user_id)
DO UPDATE SET
    login_count = user_stats.login_count + 1,
    last_login = EXCLUDED.last_login;

-- 存在则跳过
INSERT INTO tags (name)
VALUES ('postgresql'), ('database'), ('sql')
ON CONFLICT (name) DO NOTHING;

-- 批量 UPSERT（配合 RETURNING）
INSERT INTO inventory (sku, quantity)
VALUES ('A001', 10), ('A002', 20), ('A003', 30)
ON CONFLICT (sku)
DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity
RETURNING *;

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_daily_stats AS
SELECT date_trunc('day', created_at) as day,
       COUNT(*) as total_orders,
       SUM(amount) as total_revenue,
       AVG(amount) as avg_order_value
FROM orders
GROUP BY date_trunc('day', created_at)
WITH DATA;

-- 在物化视图上建索引
CREATE UNIQUE INDEX idx_mv_daily ON mv_daily_stats(day);

-- 刷新（全量重算）
REFRESH MATERIALIZED VIEW mv_daily_stats;

-- 并发刷新（不阻塞读，需要唯一索引）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_stats;

-- 创建函数
CREATE OR REPLACE FUNCTION transfer_funds(
    from_id BIGINT,
    to_id BIGINT,
    amount NUMERIC
) RETURNS VOID AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    SELECT balance INTO current_balance FROM accounts WHERE id = from_id FOR UPDATE;

    IF current_balance < amount THEN
        RAISE EXCEPTION '余额不足：当前 %, 需要 %', current_balance, amount;
    END IF;

    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;

    INSERT INTO transactions (from_id, to_id, amount, ts)
    VALUES (from_id, to_id, amount, NOW());
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT transfer_funds(1, 2, 100.00);

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.username, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.username
ORDER BY order_count DESC
LIMIT 10;

-- 1. 复合索引（遵循最左前缀原则）
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- 2. 覆盖索引（INCLUDE 避免回表）
CREATE INDEX idx_orders_cover ON orders(user_id) INCLUDE (amount, status);

-- 3. 查找未使用的索引
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 4. 查找缺失索引（慢查询中的 Seq Scan）
SELECT relname, seq_scan, seq_tup_read,
       idx_scan, seq_tup_read / GREATEST(seq_scan, 1) as avg_seq_rows
FROM pg_stat_user_tables
WHERE seq_scan > 100 AND seq_tup_read / GREATEST(seq_scan, 1) > 1000
ORDER BY seq_tup_read DESC;

# 安装 PgBouncer
sudo apt install -y pgbouncer

-- 手动 VACUUM
VACUUM VERBOSE users;       -- 回收空间（不锁表）
VACUUM FULL users;          -- 重写表（锁表，释放磁盘空间）
VACUUM ANALYZE users;       -- 回收空间 + 更新统计信息

-- 查看表膨胀情况
SELECT relname,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / GREATEST(n_live_tup + n_dead_tup, 1), 1) as dead_pct,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

# 备份单个数据库（自定义格式，支持并行恢复）
pg_dump -Fc -j 4 -f mydb.dump mydb

# 备份为 SQL 文件
pg_dump -f mydb.sql mydb

# 仅备份 schema（不含数据）
pg_dump --schema-only -f schema.sql mydb

# 仅备份数据
pg_dump --data-only -f data.sql mydb

# 备份指定表
pg_dump -t users -t orders -f tables.dump mydb

# 备份所有数据库
pg_dumpall -f all_databases.sql

# 恢复
pg_restore -d mydb -j 4 mydb.dump       # 自定义格式
psql -d mydb -f mydb.sql                  # SQL 格式

# 基础物理备份
pg_basebackup -D /backup/base -Ft -z -P -U replicator

# 参数说明：
# -D: 备份目录
# -Ft: tar 格式
# -z: gzip 压缩
# -P: 显示进度
# -U: 复制用户

# 搭建流复制备库
pg_basebackup -D /var/lib/postgresql/17/standby \
  -h primary_host -p 5432 -U replicator -R -P

# 1. 确保有基础备份 + WAL 归档
# 2. 创建恢复配置

# 3. 创建 recovery.signal 文件
touch /var/lib/postgresql/17/main/recovery.signal

# 4. 启动 PostgreSQL，自动回放 WAL 到目标时间点
sudo systemctl start postgresql