PostgreSQL 使用全面指南：从入门到企业级应用

PostgreSQL 是世界上最先进的开源关系型数据库，以其强大的功能、高度的扩展性和严格的SQL标准兼容性著称。本教程将带你从基础到高级全面掌握PostgreSQL的使用技巧，涵盖安装配置、核心操作、高级特性和性能优化等各个方面。

一、PostgreSQL概述

PostgreSQL（常简称为Postgres）是一个功能强大的开源对象关系数据库系统，具有以下核心特点：

高度符合SQL标准：支持SQL:2016标准的绝大部分功能
可扩展性：支持自定义数据类型、函数、操作符和索引方法
ACID兼容：完全支持事务处理
丰富的特性：
复杂查询
外键
触发器
可更新的视图
事务完整性
多版本并发控制(MVCC)
多语言支持：PL/pgSQL、PL/Python、PL/Perl、PL/Tcl等
强大的扩展：PostGIS（地理空间）、pgRouting（路径规划）、TimescaleDB（时序数据）等

graph TD
PostgreSQL --> 核心功能[核心功能]
PostgreSQL --> 扩展模块[扩展模块]

核心功能 --> SQL标准
核心功能 --> ACID事务
核心功能 --> MVCC
核心功能 --> 复杂索引

扩展模块 --> PostGIS
扩展模块 --> TimescaleDB
扩展模块 --> pg_partman
扩展模块 --> PL/Python

二、安装与配置

不同平台安装

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

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

# macOS (Homebrew)
brew install postgresql
brew services start postgresql

# Windows
下载安装包：https://www.postgresql.org/download/windows/

基本配置

初始化数据库集群

sudo -u postgres initdb -D /var/lib/postgres/data

启动服务

sudo systemctl start postgresql
sudo systemctl enable postgresql

配置访问权限
编辑 pg_hba.conf：

# 允许本地所有连接
localallalltrust
# 允许网络连接
hostallall0.0.0.0/0md5

修改监听地址
编辑 postgresql.conf：

listen_addresses = '*'

创建用户和数据库

-- 连接PostgreSQL
sudo -u postgres psql

-- 创建用户
CREATE USER myuser WITH PASSWORD 'securepassword';

-- 创建数据库
CREATE DATABASE mydb OWNER myuser;

-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

三、数据库基础操作

表操作

-- 创建表
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
department VARCHAR(50),
salary NUMERIC(10,2),
hire_date DATE DEFAULT CURRENT_DATE,
skills TEXT[]
);

-- 修改表
ALTER TABLE employees ADD COLUMN phone VARCHAR(15);
ALTER TABLE employees ALTER COLUMN department SET NOT NULL;

-- 删除表
DROP TABLE IF EXISTS temp_employees;

数据操作（CRUD）

-- 插入数据
INSERT INTO employees (name, email, department, salary)
VALUES
('张伟', 'zhang@company.com', '研发部', 15000),
('李娜', 'li@company.com', '市场部', 12000);

-- 查询数据
SELECT * FROM employees WHERE department = '研发部' AND salary > 13000;

-- 更新数据
UPDATE employees SET salary = salary * 1.1 WHERE department = '研发部';

-- 删除数据
DELETE FROM employees WHERE id = 5;

事务管理

BEGIN;

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

-- 提交事务
COMMIT;

-- 出错时回滚
ROLLBACK;

四、数据类型详解

PostgreSQL提供丰富的数据类型：

类别	数据类型	描述
数值	`SMALLINT`, `INTEGER`, `BIGINT`	整数类型
	`NUMERIC(precision, scale)`	精确小数
	`REAL`, `DOUBLE PRECISION`	浮点数
字符	`VARCHAR(n)`, `TEXT`	变长字符串
	`CHAR(n)`	定长字符串
日期/时间	`DATE`, `TIME`, `TIMESTAMP`	日期时间类型
布尔	`BOOLEAN`	true/false
二进制	`BYTEA`	二进制数据
几何	`POINT`, `LINE`, `CIRCLE`	几何图形
网络	`INET`, `CIDR`, `MACADDR`	网络地址
JSON	`JSON`, `JSONB`	JSON数据
数组	`INT[]`, `TEXT[]`	数组类型
范围	`INT4RANGE`, `TSRANGE`	范围类型

JSONB示例：

CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
attributes JSONB
);

INSERT INTO products (name, attributes)
VALUES ('Laptop', '{"color": "silver", "memory": "16GB", "ports": ["USB-C", "HDMI"]}');

-- 查询JSON字段
SELECT name, attributes->>'color' AS color
FROM products
WHERE attributes @> '{"memory": "16GB"}';

五、高级查询技术

窗口函数

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

CTE（公共表表达式）

WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
),
top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region, product, SUM(quantity) AS product_units
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

全文搜索

-- 创建全文搜索索引
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
title TEXT,
body TEXT
);

CREATE INDEX idx_documents_search ON documents USING GIN (to_tsvector('english', body));

-- 执行搜索
SELECT title, ts_headline(body, q) AS highlight
FROM documents, to_tsquery('english', 'database & performance') q
WHERE to_tsvector('english', body) @@ q;

六、索引与性能优化

索引类型

索引类型	适用场景	示例
B-tree	默认索引，适用于等值查询和范围查询	`CREATE INDEX idx_name ON table (column)`
Hash	等值查询（仅内存表）	`CREATE INDEX idx_name ON table USING HASH (column)`
GIN	JSONB、数组、全文搜索	`CREATE INDEX idx_gin ON table USING GIN (jsonb_column)`
GiST	几何数据、全文搜索	`CREATE INDEX idx_gist ON table USING GiST (geom_column)`
SP-GiST	空间分区数据	`CREATE INDEX idx_spgist ON table USING SP-GiST (phone)`
BRIN	大型表，按物理顺序存储	`CREATE INDEX idx_brin ON table USING BRIN (timestamp)`

查询优化技巧

使用EXPLAIN分析

EXPLAIN ANALYZE SELECT * FROM large_table WHERE category_id = 10;

**避免SELECT ***

-- 不推荐
SELECT * FROM orders;

-- 推荐
SELECT order_id, customer_id, order_date FROM orders;

批量操作优化

-- 使用COPY导入数据
COPY large_table FROM '/path/to/data.csv' DELIMITER ',' CSV HEADER;

分区表

-- 创建分区表
CREATE TABLE sales (
id SERIAL,
sale_date DATE,
amount NUMERIC
) PARTITION BY RANGE (sale_date);

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

七、事务与并发控制

事务隔离级别

-- 查看当前隔离级别
SHOW default_transaction_isolation;

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

锁机制

-- 显式锁定
BEGIN;
LOCK TABLE accounts IN EXCLUSIVE MODE;
-- 执行操作
COMMIT;

-- 行级锁
SELECT * FROM orders WHERE id = 100 FOR UPDATE;

MVCC（多版本并发控制）

PostgreSQL使用MVCC处理并发，避免读写冲突：

stateDiagram-v2
[*] --> Active
Active --> Committed
Active --> Aborted
Committed --> [*]
Aborted --> [*]

八、存储过程与函数

PL/pgSQL函数

CREATE OR REPLACE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
tax_rate NUMERIC := 0.1;
tax_amount NUMERIC;
BEGIN
tax_amount := amount * tax_rate;
RETURN tax_amount;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'Division by zero occurred';
RETURN 0;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT calculate_tax(1000);-- 返回100.00

返回结果集

CREATE OR REPLACE FUNCTION get_employees(dept_name VARCHAR)
RETURNS TABLE (id INT, name VARCHAR, salary NUMERIC) AS $$
BEGIN
RETURN QUERY
SELECT e.id, e.name, e.salary
FROM employees e
WHERE e.department = dept_name;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT * FROM get_employees('研发部');

九、触发器与规则

创建触发器

-- 审计日志表
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name VARCHAR(100),
action VARCHAR(10),
old_data JSONB,
new_data JSONB,
change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 触发器函数
CREATE OR REPLACE FUNCTION log_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO audit_log (table_name, action, old_data)
VALUES (TG_TABLE_NAME, 'DELETE', to_jsonb(OLD));
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO audit_log (table_name, action, old_data, new_data)
VALUES (TG_TABLE_NAME, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO audit_log (table_name, action, new_data)
VALUES (TG_TABLE_NAME, 'INSERT', to_jsonb(NEW));
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER employees_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION log_employee_changes();

十、备份与恢复

逻辑备份（pg_dump）

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

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

# 压缩备份
pg_dump -U username -d dbname | gzip > backup.gz

物理备份（PITR）

# 开启WAL归档
# postgresql.conf中设置：
wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/wal_archive/%f'

# 创建基础备份
pg_basebackup -D /path/to/backup -U replicator -P -Fp -Xs -R

恢复数据库

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

# 时间点恢复(PITR)
1. 恢复基础备份
2. 创建recovery.conf文件：
restore_command = 'cp /path/to/wal_archive/%f %p'
recovery_target_time = '2023-05-01 12:00:00'
3. 启动PostgreSQL

十一、高可用与复制

流复制配置

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

# 创建复制用户
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replpass';

# 备库配置
pg_basebackup -h master-host -U replicator -D /var/lib/postgres/data -P -Xs -R

# 备库postgresql.auto.conf中自动生成：
primary_conninfo = 'host=master-host user=replicator password=replpass'

使用pgPool-II负载均衡

# 安装pgpool-II
sudo apt install pgpool2

# 配置/etc/pgpool2/pgpool.conf
backend_hostname0 = 'master-host'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = 'replica1-host'
backend_port1 = 5432
backend_weight1 = 1

# 启动pgpool
systemctl start pgpool

十二、Python集成

import psycopg2
from psycopg2 import sql

# 连接数据库
conn = psycopg2.connect(
dbname="mydb",
user="myuser",
password="mypassword",
host="localhost",
port="5432"
)

try:
# 创建游标
with conn.cursor() as cursor:
# 执行查询
cursor.execute("SELECT * FROM employees WHERE department = %s", ('研发部',))
for record in cursor.fetchall():
print(f"ID: {record[0]}, Name: {record[1]}, Salary: {record[4]}")

# 插入数据
insert_query = sql.SQL("""
INSERT INTO employees (name, email, department, salary)
VALUES (%s, %s, %s, %s)
RETURNING id
""")
cursor.execute(insert_query, ('王芳', 'wang@company.com', '市场部', 13000))
new_id = cursor.fetchone()[0]
print(f"New employee ID: {new_id}")

# 提交事务
conn.commit()

except Exception as e:
print(f"Database error: {e}")
conn.rollback()

finally:
# 关闭连接
if conn:
conn.close()

十三、学习资源

官方文档 - 最权威的参考资料
PGExercises - PostgreSQL交互式练习平台
PostgreSQL Tutorial - 免费教程网站
pgAdmin - 官方图形化管理工具
PostgreSQL Weekly - 每周更新资讯

graph LR
A[学习基础SQL] --> B[掌握PostgreSQL特性]
B --> C[性能优化]
C --> D[高可用架构]
D --> E[扩展开发]
E --> F[成为PostgreSQL专家]

PostgreSQL作为功能最强大的开源数据库，适用于从嵌入式系统到企业级应用的各种场景。通过本教程，你已经掌握了其核心功能和高级技巧，建议结合实际项目深入实践。记住：正确的数据库设计比后期优化更重要！

参考&致谢

夜法之书

https://blog.17lai.site/posts/16bffe88/

本博客所有文章除特別声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源夜法之书 !

sql postgresql