一、PostgreSQL 是什么

PostgreSQL（简称 Postgres）是一款功能极其强大的 开源对象关系型数据库管理系统（ORDBMS），以其 稳定性、可扩展性、SQL 标准兼容度高 而著称。

一句话概括：

PostgreSQL = 稳定 + 强大 + 可扩展的企业级数据库。

PostgreSQL 被广泛应用于金融、电信、数据分析、GIS、AI 等高可靠性领域。

---

二、PostgreSQL 的核心特性

特性	说明
ACID 事务	强一致性与可靠性
多版本并发控制（MVCC）	支持高并发读写
自定义数据类型与函数	极强扩展性
SQL/JSON 支持	可做结构化 + 半结构化存储
全文检索	支持模糊匹配与搜索引擎功能
复制与高可用	主从复制、流复制、逻辑复制
窗口函数、CTE	强大的数据分析能力

---

三、安装与初始化

1. Linux 安装

sudo apt update sudo apt install postgresql postgresql-contrib -y

2. 启动与状态

sudo systemctl start postgresql sudo systemctl enable postgresql sudo systemctl status postgresql

3. 登录数据库

sudo -i -u postgres psql

退出：

\q

---

四、数据库与用户管理

创建数据库

CREATE DATABASE company;

创建用户

CREATE USER admin WITH PASSWORD '123456';

授权

GRANT ALL PRIVILEGES ON DATABASE company TO admin;

切换数据库：

\c company

---

五、基本操作

创建表

CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(50), age INT, department VARCHAR(30), salary NUMERIC(10,2), hire_date DATE );

插入数据

INSERT INTO employees (name, age, department, salary, hire_date) VALUES ('Tom', 29, 'IT', 7500.00, '2024-01-15');

查询数据

SELECT * FROM employees; SELECT name, salary FROM employees WHERE salary > 6000;

更新与删除

UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT'; DELETE FROM employees WHERE id = 5;

---

六、高级查询

排序与分页

SELECT * FROM employees ORDER BY salary DESC LIMIT 5 OFFSET 10;

分组聚合

SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department;

公用表表达式（CTE）

WITH high_salary AS ( SELECT * FROM employees WHERE salary > 8000 ) SELECT name FROM high_salary;

参考案例：www.aardi.cn

---

七、索引优化

创建索引：

CREATE INDEX idx_department ON employees(department);

唯一索引：

CREATE UNIQUE INDEX idx_unique_name ON employees(name);

查看索引：

\d employees

删除索引：

DROP INDEX idx_department;

使用执行计划分析性能：

EXPLAIN ANALYZE SELECT * FROM employees WHERE department='IT';

---

八、视图与物化视图

普通视图

CREATE VIEW high_salary_view AS SELECT name, salary FROM employees WHERE salary > 9000;

物化视图

CREATE MATERIALIZED VIEW emp_summary AS SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department;

刷新物化视图：

REFRESH MATERIALIZED VIEW emp_summary;

---

九、事务与锁

BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE id = 1; UPDATE accounts SET balance = balance + 1000 WHERE id = 2; COMMIT;

回滚：

ROLLBACK;

查看锁信息：

SELECT * FROM pg_locks;

---

十、约束与外键

CREATE TABLE departments ( id SERIAL PRIMARY KEY, name VARCHAR(50) UNIQUE NOT NULL ); ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (department) REFERENCES departments(name);

---

十一、函数与存储过程

自定义函数

CREATE OR REPLACE FUNCTION add_salary(a NUMERIC, b NUMERIC) RETURNS NUMERIC AS $$ BEGIN RETURN a + b; END; $$ LANGUAGE plpgsql;

调用：

SELECT add_salary(5000, 1200);

存储过程

CREATE OR REPLACE PROCEDURE raise_salary(dept TEXT, percent NUMERIC) LANGUAGE plpgsql AS $$ BEGIN UPDATE employees SET salary = salary * (1 + percent/100) WHERE department = dept; END; $$;

调用：

CALL raise_salary('IT', 10);

---

十二、触发器（Trigger）

CREATE OR REPLACE FUNCTION log_update() RETURNS TRIGGER AS $$ BEGIN INSERT INTO logs (table_name, operation, update_time) VALUES ('employees', TG_OP, NOW()); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER after_employee_update AFTER UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION log_update();

---

十三、JSON 与半结构化数据

CREATE TABLE orders ( id SERIAL PRIMARY KEY, data JSONB ); INSERT INTO orders (data) VALUES ('{"user":"Tom", "items":["apple","banana"], "price":12.5}'); SELECT data->>'user' AS username FROM orders; SELECT jsonb_array_elements(data->'items') FROM orders;

---

十四、全文检索

CREATE TABLE articles ( id SERIAL, title TEXT, content TEXT ); CREATE INDEX idx_search ON articles USING gin(to_tsvector('english', content)); SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('database & performance');

---

十五、权限管理

CREATE ROLE dev LOGIN PASSWORD '123456'; GRANT CONNECT ON DATABASE company TO dev; GRANT SELECT, INSERT ON employees TO dev;

撤销权限：

REVOKE INSERT ON employees FROM dev;

---

十六、备份与恢复

备份数据库

pg_dump -U postgres company > company.sql

恢复数据库

psql -U postgres -d company -f company.sql

导出表结构

pg_dump -s -U postgres company > schema.sql

---

十七、复制与高可用

1. 主从复制配置

主库设置：

wal_level = replica max_wal_senders = 5

从库配置：

primary_conninfo = 'host=192.168.1.10 port=5432 user=replica password=123456'

启动复制：

pg_basebackup -h 192.168.1.10 -U replica -D /var/lib/postgresql/data -R

2. 逻辑复制

CREATE PUBLICATION mypub FOR TABLE employees; CREATE SUBSCRIPTION mysub CONNECTION 'host=192.168.1.10 dbname=company user=replica password=123456' PUBLICATION mypub;

---

十八、性能优化

1. 分析慢查询

   EXPLAIN ANALYZE SELECT ...

2. 启用自动清理（Autovacuum）

3. 合理使用索引（B-tree、GIN、HASH）

4. 分区表优化大数据集

   CREATE TABLE sales ( id SERIAL, amount INT, created_at DATE ) PARTITION BY RANGE (created_at);

5. 缓存与连接池

   • 使用 pgbouncer 管理连接

6. 调整配置参数

   shared_buffers = 2GB work_mem = 64MB maintenance_work_mem = 256MB

---

十九、监控与日志

查看连接状态：

SELECT * FROM pg_stat_activity;

统计查询性能：

SELECT datname, blks_hit, blks_read, (blks_hit*100.0/(blks_hit+blks_read)) AS cache_hit_ratio FROM pg_stat_database;

日志路径：

/var/log/postgresql/postgresql-*.log

---

二十、PostgreSQL 企业级实战应用

场景	实践方案
高并发系统	使用连接池 + MVCC 提高读写性能
地理信息系统（GIS）	扩展 PostGIS 支持空间数据
数据仓库	分区表 + 物化视图加速聚合查询
金融系统	强事务一致性与行级锁保障安全
微服务架构	结合 Docker/K8s 容器化部署
混合数据应用	JSONB 与关系表混合建模
容灾与高可用	主从复制 + 热备份方案