PostgreSQL进阶教程
目录
- 事务和并发控制
- 事务
- 事务隔离级别
- 锁
- 高级查询
- 联合查询
- 窗口函数
- 子查询
- CTE（公用表表达式）
- 数据类型
- 自定义数据类型
- 数组
- JSON
- 高级索引
- 部分索引
- 表达式索引
- GIN和GiST索引
- 性能调优
- 查询优化
- 配置优化
- 备份与恢复
- 物理备份
- 逻辑备份
- 扩展与插件
- PostGIS
- pg_cron
- 集群与高可用
- Streaming Replication
- Patroni
事务和并发控制
事务
事务是一个或多个SQL语句的组合，这些语句作为一个单元执行，要么全部成功要么全部失败。事务的使用方式如下：
BEGIN;
-- SQL 语句
COMMIT; -- 提交事务
-- 或者
ROLLBACK; -- 回滚事务
事务隔离级别
PostgreSQL支持四种事务隔离级别：
- Read Uncommitted: 允许读取未提交的数据（脏读）。
- Read Committed: 只读取已提交的数据，默认级别。
- Repeatable Read: 在事务期间保持一致视图，不允许不可重复读。
- Serializable: 最严格的隔离级别，完全防止脏读、不可重复读和幻读。
设置事务隔离级别：
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
锁
PostgreSQL支持多种锁，以控制并发访问：
- 行级锁: 影响特定行（例如：
SELECT FOR UPDATE）。 - 表级锁: 影响整个表（例如：
LOCK TABLE）。
示例：
SELECT * FROM mytable WHERE id = 1 FOR UPDATE;
高级查询
联合查询
联合查询使用UNION操作符合并多个查询结果集：
SELECT name FROM employees
UNION
SELECT name FROM customers;
窗口函数
窗口函数在结果集中执行计算，并保留原始行：
SELECT name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC)
FROM employees;
子查询
子查询是嵌套在另一个查询中的查询：
SELECT name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');
CTE（公用表表达式）
CTE是一种在查询中定义临时结果集的方式：
WITH DepartmentSalary AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT * FROM DepartmentSalary WHERE avg_salary > 50000;
数据类型
自定义数据类型
PostgreSQL允许创建自定义数据类型：
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
数组
PostgreSQL支持数组数据类型：
CREATE TABLE contacts (
id SERIAL PRIMARY KEY,
phone_numbers TEXT[]
);
INSERT INTO contacts (phone_numbers) VALUES ('{123-4567, 890-1234}');
JSON
PostgreSQL支持JSON数据类型，并提供多种JSON函数和操作符：
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
order_info JSON
);
INSERT INTO orders (order_info) VALUES ('{"customer": "John", "items": ["book", "pen"]}');
查询JSON数据：
SELECT order_info->>'customer' AS customer FROM orders;
高级索引
部分索引
部分索引只索引满足特定条件的行：
CREATE INDEX idx_active_users ON users (email) WHERE active = TRUE;
表达式索引
表达式索引使用表达式创建索引：
CREATE INDEX idx_lower_email ON users (LOWER(email));
GIN和GiST索引
GIN和GiST索引用于复杂数据类型（如全文搜索和地理数据）：
CREATE INDEX idx_gin ON documents USING GIN (to_tsvector('english', content));
性能调优
查询优化
使用EXPLAIN分析查询执行计划：
EXPLAIN SELECT * FROM mytable WHERE id = 1;
配置优化
调整PostgreSQL配置文件（postgresql.conf）以优化性能：
- shared_buffers: 增加共享内存缓冲区大小。
- work_mem: 增加查询中使用的内存大小。
- maintenance_work_mem: 增加维护操作使用的内存大小。
备份与恢复
物理备份
使用pg_basebackup进行物理备份：
pg_basebackup -D /path/to/backup -Fp -Xs -P
逻辑备份
使用pg_dump进行逻辑备份：
pg_dump mydatabase > mydatabase_backup.sql
扩展与插件
PostGIS
PostGIS扩展为PostgreSQL添加地理空间数据支持：
CREATE EXTENSION postgis;
pg_cron
pg_cron扩展允许在PostgreSQL中调度作业：
CREATE EXTENSION pg_cron;
SELECT cron.schedule('nightly_backup', '0 3 * * *', 'pg_dump mydatabase > /path/to/backup.sql');
集群与高可用
Streaming Replication
设置流复制以实现主从复制：
-- 在主服务器上
wal_level = replica
max_wal_senders = 3
hot_standby = on
-- 在从服务器上
standby_mode = 'on'
primary_conninfo = 'host=master_host port=5432 user=replicator password=yourpassword'
restore_command = 'cp /path/to/wal_archive/%f %p'
Patroni
Patroni是一个自动化高可用解决方案：
# 安装Patroni
pip install patroni
# 配置Patroni
patroni /path/to/patroni.yml
以上是PostgreSQL的进阶教程，涵盖了事务和并发控制、高级查询、数据类型、高级索引、性能调优、备份与恢复、扩展与插件以及集群与高可用等内容。希望这些内容能帮助您更深入地理解和使用PostgreSQL。
