PostgreSQL 从零到企业级完全指南

PostgreSQL 从零到企业级完全指南

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

📑 目录

1. PostgreSQL 简介

1.1 什么是 PostgreSQL

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

1.2 核心特性

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