PostgreSQL 从零到企业级完全指南

原创于 2026-09-03 12:08:07 发布 · 238 阅读

7 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

PostgreSQL 从零到企业级完全指南

📚 本文档涵盖 PostgreSQL 数据库从入门基础到企业级应用的完整知识体系
🎯 适合开发者、DBA、架构师等不同角色的学习需求

📑 目录

PostgreSQL 简介
安装与配置
基础操作
高级特性
性能优化
企业级部署与维护
最佳实践

1. PostgreSQL 简介

1.1 什么是 PostgreSQL

PostgreSQL 是一个功能强大的 开源对象-关系数据库系统，具有超过 35 年的活跃开发历史。它以可靠性、功能完整性和性能著称。

1.2 核心特性

特性类别	具体特性
SQL 支持	完整的 SQL 标准支持、高级查询功能
数据类型	JSON/JSONB、数组、hstore、几何类型、网络地址类型等
并发控制	多版本并发控制 (MVCC)、事务隔离级别
扩展性	自定义类型、函数、运算符、索引方法
安全性	SSL/TLS、行级安全策略、角色管理

1.3 PostgreSQL vs 其他数据库

┌─────────────────┬──────────────┬──────────────┬─────────────┐
│     特性        │ PostgreSQL   │ MySQL        │ SQL Server  │
├─────────────────┼──────────────┼──────────────┼─────────────┤
│ 开源            │ ✅ 完全开源   │ ✅ 开源       │ ❌ 商业      │
│ ACID 事务       │ ✅ 完整支持   │ ✅ InnoDB     │ ✅ 完整      │
│ JSON 支持       │ ✅ 原生 JSONB │ ✅ 基本 JSON  │ ✅ 支持      │
│ 扩展性          │ ⭐ 极强       │ 中等          │ 有限        │
│ 许可证          │ PostgreSQL   │ GPL          │ 商业许可    │
└─────────────────┴──────────────┴──────────────┴─────────────┘

2. 安装与配置

2.1 安装方式

Windows 安装

# 方法1: 使用官方安装程序
# 下载地址: https://www.postgresql.org/download/windows/

# 方法2: 使用 Chocolatey
choco install postgresql

# 方法3: 使用 winget
winget install PostgreSQL.PostgreSQL

Linux 安装

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib

# CentOS/RHEL
sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup initdb

# 启动服务
sudo systemctl start postgresql
sudo systemctl enable postgresql

Docker 安装

# 快速启动
docker run --name postgres-db \
  -e POSTGRES_PASSWORD=your_password \
  -p 5432:5432 \
  -d postgres:15

# 带持久化存储
docker run --name postgres-db \
  -e POSTGRES_PASSWORD=your_password \
  -v pgdata:/var/lib/postgresql/data \
  -p 5432:5432 \
  -d postgres:15

2.2 连接管理

# 连接到默认数据库
psql -U postgres

# 连接到指定数据库
psql -U username -d database_name -h host -p port

# 连接字符串方式
psql "postgresql://user:password@host:5432/database"

2.3 核心配置文件

PostgreSQL 配置文件位置:
├── postgresql.conf      # 主配置文件
├── pg_hba.conf          # 客户端认证配置
└── pg_ident.conf        # 用户名映射配置

postgresql.conf 重要参数

# 连接配置
listen_addresses = 'localhost'    # 监听地址
port = 5432                       # 监听端口
max_connections = 100             # 最大连接数

# 内存配置
shared_buffers = '256MB'          # 共享缓冲区
work_mem = '4MB'                  # 工作内存
maintenance_work_mem = '64MB'     # 维护内存

# WAL 配置
wal_level = 'replica'            # WAL 级别
max_wal_size = '1GB'             # 最大 WAL 大小

pg_hba.conf 示例

# TYPE  DATABASE  USER      ADDRESS           METHOD
local   all       postgres                    peer
host    all       all       127.0.0.1/32      scram-sha-256
host    all       all       0.0.0.0/0         scram-sha-256

3. 基础操作

3.1 数据库与表操作

创建数据库

-- 创建数据库
CREATE DATABASE my_database
    WITH OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0;

-- 查看所有数据库
\l

-- 切换数据库
\c my_database

创建表

-- 用户表示例
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- 订单表示例
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    product_name VARCHAR(100) NOT NULL,
    quantity INTEGER DEFAULT 1,
    price DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构
\d users
\d orders

3.2 CRUD 操作

插入数据

-- 单行插入
INSERT INTO users (username, email, password_hash)
VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- 多行插入
INSERT INTO users (username, email, password_hash)
VALUES 
    ('jane_smith', 'jane@example.com', 'hashed_password_1'),
    ('bob_wilson', 'bob@example.com', 'hashed_password_2');

-- 批量插入
INSERT INTO orders (user_id, product_name, quantity, price)
SELECT 
    id,
    'Product ' || id,
    floor(random() * 10 + 1)::int,
    (random() * 100)::decimal(10,2)
FROM users
WHERE id <= 5;

查询数据

-- 基础查询
SELECT * FROM users WHERE is_active = TRUE;

-- 联接查询
SELECT 
    u.username,
    u.email,
    COUNT(o.order_id) as order_count,
    SUM(o.price * o.quantity) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email
HAVING COUNT(o.order_id) > 0
ORDER BY total_spent DESC;

-- 子查询
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders
    WHERE price > 50
);

-- 窗口函数
SELECT 
    username,
    email,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) as row_num,
    DENSE_RANK() OVER (ORDER BY created_at) as dense_rank
FROM users;

更新数据

-- 单行更新
UPDATE users 
SET email = 'new_email@example.com'
WHERE username = 'john_doe';

-- 批量更新
UPDATE orders 
SET status = 'completed'
WHERE created_at < CURRENT_DATE - INTERVAL '7 days'
AND status = 'pending';

-- 使用 CTE 更新
WITH user_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
)
UPDATE users 
SET is_active = FALSE
WHERE id IN (
    SELECT user_id 
    FROM user_orders 
    WHERE order_count = 0
);

删除数据

-- 单行删除
DELETE FROM users WHERE username = 'bob_wilson';

-- 批量删除
DELETE FROM orders 
WHERE status = 'cancelled' 
AND created_at < CURRENT_DATE - INTERVAL '1 year';

-- 软删除（推荐）
UPDATE users SET is_active = FALSE WHERE id = 123;

3.3 事务管理

-- 基本事务
BEGIN;

INSERT INTO users (username, email, password_hash)
VALUES ('transaction_user', 'tx@example.com', 'hash');

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

COMMIT;

-- 回滚事务
BEGIN;
-- 某些操作...
ROLLBACK;

-- 保存点
BEGIN;
INSERT INTO users (username, email, password_hash)
VALUES ('user1', 'user1@example.com', 'hash1');

SAVEPOINT sp1;

INSERT INTO users (username, email, password_hash)
VALUES ('user2', 'user2@example.com', 'hash2');

ROLLBACK TO sp1;

COMMIT;  -- 只提交 user1

4. 高级特性

4.1 索引优化

索引类型

-- B-tree 索引（默认，最常用）
CREATE INDEX idx_users_email ON users(email);

-- 复合索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 部分索引
CREATE INDEX idx_active_users ON users(email) 
WHERE is_active = TRUE;

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

-- GIN 索引（用于全文搜索、JSONB、数组）
CREATE INDEX idx_products_tags ON products USING GIN(tags);
CREATE INDEX idx_documents_search ON documents USING GIN(to_tsvector('english', content));

-- GiST 索引（用于几何数据、范围类型）
CREATE INDEX idx_locations_point ON locations USING GIST(point);

-- BRIN 索引（用于大表的范围扫描）
CREATE INDEX idx_logs_timestamp ON logs USING BRIN(created_at);

-- 查看索引
\d tablename
SELECT * FROM pg_indexes WHERE tablename = 'users';

4.2 JSONB 操作

-- 创建包含 JSONB 的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    attributes JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- 插入数据
INSERT INTO products (name, attributes)
VALUES 
    ('iPhone 15', '{"brand": "Apple", "color": "black", "specs": {"ram": "6GB", "storage": "128GB"}}'),
    ('Galaxy S24', '{"brand": "Samsung", "color": "white", "specs": {"ram": "8GB", "storage": "256GB"}}');

-- 查询 JSONB 字段
SELECT 
    name,
    attributes->>'brand' as brand,
    attributes->'specs'->>'ram' as ram
FROM products;

-- 条件过滤
SELECT * FROM products 
WHERE attributes @> '{"brand": "Apple"}';

-- 展开 JSONB
SELECT 
    name,
    jsonb_object_keys(attributes) as key
FROM products;

-- 更新 JSONB
UPDATE products 
SET attributes = attributes || '{"color": "blue"}'::jsonb
WHERE id = 1;

-- 添加嵌套字段
UPDATE products 
SET attributes = jsonb_set(
    attributes,
    '{specs, battery}',
    '"4500mAh"'
)
WHERE id = 1;

4.3 存储过程与函数

-- 创建函数
CREATE OR REPLACE FUNCTION calculate_order_total(
    p_user_id INTEGER
) RETURNS DECIMAL(10, 2) AS $$
DECLARE
    v_total DECIMAL(10, 2);
BEGIN
    SELECT COALESCE(SUM(quantity * price), 0)
    INTO v_total
    FROM orders
    WHERE user_id = p_user_id;
    
    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

-- 使用函数
SELECT calculate_order_total(1) as user_total;

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER update_users_modtime
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column();

-- 创建存储过程
CREATE OR REPLACE PROCEDURE process_orders()
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE orders 
    SET status = 'shipped'
    WHERE status = 'paid'
    AND created_at < CURRENT_DATE - INTERVAL '2 days';
    
    RAISE NOTICE 'Orders processed at %', NOW();
END;
$$;

-- 调用存储过程
CALL process_orders();

4.4 分区表

-- 创建分区表
CREATE TABLE orders_partitioned (
    order_id SERIAL,
    user_id INTEGER NOT NULL,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id, created_at)
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE orders_2024_q1 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE orders_2024_q3 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE orders_2024_q4 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

-- 查看分区
SELECT * FROM pg_partitions WHERE tablename = 'orders_partitioned';

5. 性能优化

5.1 查询优化

使用 EXPLAIN 分析

-- 基本分析
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 详细分析（包含实际执行时间）
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- 格式化输出
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT * FROM users WHERE email = 'test@example.com';

-- 常见问题识别
EXPLAIN (ANALYZE, VERBOSE)
SELECT u.*, o.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.is_active = TRUE;

常见优化技巧

-- 1. 避免 SELECT *
SELECT id, username, email FROM users WHERE is_active = TRUE;

-- 2. 使用合适的 JOIN 类型
-- 优先使用 INNER JOIN
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 3. 批量操作
-- 使用 COPY 替代多条 INSERT
COPY users FROM STDIN WITH CSV;

-- 4. 使用 CTE 替代子查询（复杂查询）
WITH active_users AS (
    SELECT id FROM users WHERE is_active = TRUE
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users);

-- 5. 合理使用 LIMIT
SELECT * FROM large_table ORDER BY id LIMIT 100 OFFSET 0;

5.2 配置优化

-- 查看当前配置
SHOW shared_buffers;
SHOW work_mem;

-- 动态调整（会话级别）
SET work_mem = '8MB';
SET maintenance_work_mem = '128MB';

-- 查看数据库统计
SELECT * FROM pg_stat_database;
SELECT * FROM pg_stat_user_tables;

-- 查看慢查询
SELECT * FROM pg_stat_activity 
WHERE state = 'active' 
AND query_start < NOW() - INTERVAL '5 minutes';

5.3 索引维护

-- 查看索引使用情况
SELECT 
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 查找未使用的索引
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%pkey%';

-- 重建索引
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

-- 分析表统计
ANALYZE users;

-- VACUUM 清理
VACUUM users;
VACUUM FULL users;  -- 完全清理（会锁表）

6. 企业级部署与维护

6.1 备份与恢复

# 逻辑备份
pg_dump -U postgres -d my_database -f backup.sql

# 备份特定表
pg_dump -U postgres -d my_database -t users -t orders -f tables_backup.sql

# 自定义格式备份（推荐，支持并行恢复）
pg_dump -U postgres -d my_database -Fc -f backup.dump

# 并行备份
pg_dump -U postgres -d my_database -j 4 -Fc -f backup.dump

# 全库备份（包含所有数据库）
pg_dumpall -U postgres -f all_databases.sql

# 恢复备份
pg_restore -U postgres -d my_database -Fc backup.dump

# 并行恢复
pg_restore -U postgres -d my_database -j 4 -Fc backup.dump

定时备份脚本

#!/bin/bash
# backup.sh

BACKUP_DIR="/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/backup_$DATE.dump"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
pg_dump -U postgres -d production_db -Fc -f $BACKUP_FILE

# 删除7天前的备份
find $BACKUP_DIR -name "backup_*.dump" -mtime +7 -delete

# 记录日志
echo "Backup completed: $BACKUP_FILE" >> /var/log/pg_backup.log

6.2 高可用架构

流复制配置

# 主库配置 (postgresql.conf)
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1024
synchronous_commit = on

# 主库认证 (pg_hba.conf)
host replication replicator 192.168.1.0/24 scram-sha-256

# 从库初始化
pg_basebackup -h master_host -U replicator -D /var/lib/postgresql/data -Fp -Xs -P -R

# 从库配置 (postgresql.auto.conf)
primary_conninfo = 'host=master_host port=5432 user=replicator password=xxx'

逻辑复制

-- 主库：创建发布
ALTER SYSTEM SET wal_level = 'logical';
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- 从库：创建订阅
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=master_host dbname=my_database user=replicator password=xxx'
PUBLICATION my_publication;

-- 查看订阅状态
SELECT * FROM pg_stat_subscription;

6.3 监控与告警

监控查询

-- 连接数监控
SELECT 
    state,
    COUNT(*) as count
FROM pg_stat_activity
GROUP BY state;

-- 长事务检测
SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND state != 'idle';

-- 表空间使用
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 数据库大小
SELECT 
    datname,
    pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- 锁监控
SELECT 
    l.pid,
    l.mode,
    l.granted,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

Prometheus + Grafana 监控

# docker-compose.yml
version: '3.8'
services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_PASSWORD: secure_password
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

  postgres_exporter:
    image: prometheuscommunity/postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://postgres:secure_password@postgres:5432/postgres?sslmode=disable"
    ports:
      - "9187:9187"
    depends_on:
      - postgres

  prometheus:
    image: prom/prometheus
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
    ports:
      - "9090:9090"

  grafana:
    image: grafana/grafana
    ports:
      - "3000:3000"
    environment:
      - GF_SECURITY_ADMIN_PASSWORD=admin

volumes:
  pgdata:

6.4 安全配置

-- 创建只读用户
CREATE ROLE readonly_role;
GRANT CONNECT ON DATABASE my_database TO readonly_role;
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

CREATE USER readonly_user WITH PASSWORD 'secure_password';
GRANT readonly_role TO readonly_user;

-- 创建读写用户
CREATE ROLE readwrite_role;
GRANT CONNECT ON DATABASE my_database TO readwrite_role;
GRANT USAGE ON SCHEMA public TO readwrite_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite_role;

CREATE USER readwrite_user WITH PASSWORD 'secure_password';
GRANT readwrite_role TO readwrite_user;

-- 行级安全策略
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_orders ON orders
FOR ALL
USING (user_id = current_setting('app.current_user_id')::integer);

-- 启用 SSL
-- postgresql.conf
ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'

7. 最佳实践

7.1 开发规范

命名规范

-- 表名：小写 + 下划线，复数形式
CREATE TABLE users (...);
CREATE TABLE order_items (...);

-- 列名：小写 + 下划线
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    created_at TIMESTAMP
);

-- 索引名：idx_表名_列名
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 外键名：fk_表名_引用表名
ALTER TABLE orders ADD CONSTRAINT fk_orders_users 
FOREIGN KEY (user_id) REFERENCES users(id);

-- 检查约束名：chk_表名_条件
ALTER TABLE orders ADD CONSTRAINT chk_orders_quantity 
CHECK (quantity > 0);

类型选择

-- 优先使用 SERIAL 或 GENERATED ALWAYS AS IDENTITY
CREATE TABLE users (
    id GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- 推荐
    -- 或 id SERIAL PRIMARY KEY
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    price DECIMAL(10, 2),  -- 金额使用 DECIMAL
    created_at TIMESTAMPTZ DEFAULT NOW(),
    data JSONB  -- 灵活数据使用 JSONB
);

7.2 性能最佳实践

-- 1. 合理使用索引
-- 为 WHERE、JOIN、ORDER BY 的列创建索引
-- 避免过度索引，影响写入性能

-- 2. 批量操作
-- 使用 COPY 命令批量导入
-- 使用 INSERT ... VALUES (...), (...), ... 批量插入

-- 3. 分页优化
-- 使用游标或 keyset 分页代替 OFFSET
SELECT * FROM orders 
WHERE id > last_id 
ORDER BY id 
LIMIT 100;

-- 4. 避免锁竞争
-- 使用 SKIP LOCKED 处理队列
SELECT * FROM job_queue 
WHERE status = 'pending' 
ORDER BY created_at 
LIMIT 1 
FOR UPDATE SKIP LOCKED;

-- 5. 使用连接池
-- 推荐使用 PgBouncer 或应用层连接池

7.3 运维最佳实践

# 1. 定期维护
# 每周执行 VACUUM ANALYZE
vacuumdb --all --analyze

# 2. 监控磁盘空间
df -h
SELECT pg_size_pretty(pg_database_size('my_database'));

# 3. 检查复制延迟
SELECT 
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    replay_lag
FROM pg_stat_replication;

# 4. 日志管理
# postgresql.conf
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 100MB