PostgreSQL 详解

PostgreSQL 详解

PostgreSQL 是一个功能强大的开源关系型数据库管理系统，以其可靠性、功能健壮性和性能著称。以下是 PostgreSQL 的全面详解：

1. PostgreSQL 核心特性

1.1 高级数据类型支持

• 原生支持 JSON/JSONB
• 数组和复合类型
• 几何和地理空间数据(GIS)
• 网络地址类型(IP, MAC)
• 全文搜索类型

1.2 扩展性

• 自定义函数(支持多种语言：PL/pgSQL, Python, Perl等)
• 自定义数据类型
• 自定义操作符
• 表函数和窗口函数

1.3 并发控制

• 多版本并发控制(MVCC)
• 行级锁定
• 可序列化快照隔离(SSI)

2. 安装与配置

2.1 安装方法

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

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

# macOS (Homebrew)
brew install postgresql

2.2 基本配置

# 初始化数据库
sudo postgresql-setup initdb

# 启动服务
sudo systemctl start postgresql

# 设置开机启动
sudo systemctl enable postgresql

3. 数据库操作

3.1 连接数据库

psql -U username -d dbname -h host -p port

3.2 基本SQL操作

-- 创建数据库
CREATE DATABASE mydb;

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');

-- 查询数据
SELECT * FROM users WHERE name LIKE '张%';

-- 更新数据
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 1;

4. 高级功能

4.1 JSON支持

-- 创建包含JSONB的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSONB,
    tags TEXT[]
);

-- 插入JSON数据
INSERT INTO products (details, tags) 
VALUES ('{"name": "Laptop", "price": 999.99, "specs": {"cpu": "i7", "ram": "16GB"}}', 
        '{"electronics", "computer"}');

-- JSON查询
SELECT details->>'name' AS product_name 
FROM products 
WHERE details @> '{"specs": {"cpu": "i7"}}';

4.2 窗口函数

SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

4.3 表继承

-- 父表
CREATE TABLE cities (
    name TEXT,
    population FLOAT,
    altitude INT
);

-- 子表
CREATE TABLE capitals (
    state CHAR(2)
) INHERITS (cities);

5. 性能优化

5.1 索引

-- 创建B-tree索引
CREATE INDEX idx_users_name ON users (name);

-- 创建GIN索引(用于JSONB和数组)
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- 创建部分索引
CREATE INDEX idx_active_users ON users (email) WHERE active = true;

5.2 查询优化

-- 使用EXPLAIN分析查询计划
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

-- 强制使用索引
SET enable_seqscan = OFF;

5.3 分区表

-- 创建分区表
CREATE TABLE measurement (
    city_id INT NOT NULL,
    logdate DATE NOT NULL,
    peaktemp INT,
    unitsales INT
) PARTITION BY RANGE (logdate);

-- 创建分区
CREATE TABLE measurement_y2023 PARTITION OF measurement
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

6. 安全管理

6.1 用户和权限

-- 创建用户
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';

-- 授予权限
GRANT SELECT, INSERT, UPDATE ON users TO app_user;

-- 创建只读用户
CREATE ROLE readonly WITH LOGIN PASSWORD 'readonly_pass';
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

6.2 行级安全

-- 启用行级安全
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- 创建策略
CREATE POLICY user_access_policy ON users
    USING (current_user = owner OR current_user = 'admin');

7. 备份与恢复

7.1 逻辑备份

# 备份单个数据库
pg_dump -U username -d dbname -f backup.sql

# 备份所有数据库
pg_dumpall -U postgres -f alldb.sql

7.2 物理备份

# 基础备份
pg_basebackup -D /backup/location -Ft -z -P -U replicator

7.3 恢复

# 从逻辑备份恢复
psql -U postgres -d dbname -f backup.sql

# 从物理备份恢复
pg_restore -U postgres -d dbname backup.dump

8. 扩展与插件

8.1 常用扩展

-- PostGIS(地理信息系统)
CREATE EXTENSION postgis;

-- UUID支持
CREATE EXTENSION "uuid-ossp";

-- 全文搜索
CREATE EXTENSION pg_trgm;

8.2 安装扩展

# 查找可用扩展
sudo apt-cache search postgresql-14-*

# 安装PostGIS
sudo apt-get install postgresql-14-postgis-3

9. 监控与维护

9.1 监控查询

-- 查看活动查询
SELECT * FROM pg_stat_activity;

-- 查看锁
SELECT * FROM pg_locks;

9.2 维护任务

-- 分析表(更新统计信息)
ANALYZE users;

-- 重建表(减少膨胀)
VACUUM FULL users;

-- 重建索引
REINDEX TABLE users;

10. 高可用与复制

10.1 设置主从复制

# 主服务器配置(postgresql.conf)
wal_level = replica
max_wal_senders = 3

# 从服务器配置
hot_standby = on

10.2 逻辑复制

-- 创建发布
CREATE PUBLICATION mypub FOR TABLE users, products;

-- 创建订阅
CREATE SUBSCRIPTION mysub 
CONNECTION 'host=master dbname=mydb user=replicator' 
PUBLICATION mypub;

PostgreSQL 的强大功能使其成为处理复杂数据需求的理想选择，从简单的Web应用到大规模数据分析系统都能胜任。