PostgreSQL 教程（从入门到进阶）

PostgreSQL（简称 Postgres 或 PG）是一个功能强大的开源关系型数据库管理系统，支持 SQL 标准、事务、复杂查询、JSON、扩展性极强，被广泛用于 Web 应用、数据分析、GIS 等场景。

---

一、安装 PostgreSQL

1.1 各系统安装方式

系统	安装命令
Ubuntu/Debian	sudo apt update && sudo apt install postgresql
CentOS/RHEL	sudo yum install postgresql-server && sudo postgresql-setup initdb
macOS	brew install postgresql
Windows	下载安装包：https://www.postgresql.org/download/windows/

安装完成后，PostgreSQL 默认监听 5432 端口，创建系统用户 postgres。

---

二、基本操作（命令行）

2.1 启动/停止服务

# Linux (systemd)
sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl enable postgresql  # 开机自启

# macOS (brew)
brew services start postgresql
brew services stop postgresql

---

2.2 切换到 postgres 用户并进入 psql

sudo -u postgres psql

或直接：

psql -U postgres -h localhost

---

三、数据库与用户管理

3.1 创建用户（角色）

CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword';

3.2 创建数据库并指定所有者

CREATE DATABASE mydb OWNER myuser;

3.3 赋予权限

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

---

四、基本 SQL 操作（CRUD）

4.1 创建表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4.2 插入数据

INSERT INTO users (username, email) 
VALUES ('alice', 'alice@example.com'), ('bob', 'bob@example.com');

4.3 查询数据

SELECT * FROM users;
SELECT username, email FROM users WHERE id > 1 ORDER BY created_at DESC;

4.4 更新数据

UPDATE users SET email = 'alice.new@example.com' WHERE username = 'alice';

4.5 删除数据

DELETE FROM users WHERE id = 1;

---

五、进阶功能

5.1 JSON / JSONB 支持（NoSQL 风格）

-- 创建带 JSON 字段的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

-- 插入 JSON 数据
INSERT INTO products (name, details) VALUES ('Phone', '{"brand": "Apple", "price": 999, "in_stock": true}');

-- 查询 JSON 字段
SELECT name, details->>'brand' AS brand FROM products;

-- 使用索引加速 JSON 查询
CREATE INDEX idx_products_details ON products USING GIN (details);

---

5.2 事务（Transaction）

BEGIN;
INSERT INTO users (username) VALUES ('charlie');
UPDATE users SET email = 'charlie@example.com' WHERE username = 'charlie';
COMMIT;  -- 或 ROLLBACK;

---

5.3 索引优化查询

-- B树索引（默认）
CREATE INDEX idx_users_username ON users(username);

-- 全文搜索索引
ALTER TABLE users ADD COLUMN tsv TSVECTOR;
UPDATE users SET tsv = to_tsvector('english', username || ' ' || coalesce(email, ''));
CREATE INDEX idx_users_fts ON users USING GIN(tsv);

-- 全文搜索查询
SELECT * FROM users WHERE tsv @@ to_tsquery('alice');

---

5.4 视图（View）

CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE email IS NOT NULL;

-- 查询视图
SELECT * FROM active_users;

---

5.5 存储过程与函数（PL/pgSQL）

CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT get_user_count();

---

六、连接数据库（编程语言）

6.1 Python (psycopg2)

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="myuser",
    password="mypassword"
)

cur = conn.cursor()
cur.execute("SELECT * FROM users")
rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
conn.close()

6.2 Node.js (pg)

const { Client } = require('pg');

const client = new Client({
  user: 'myuser',
  host: 'localhost',
  database: 'mydb',
  password: 'mypassword',
  port: 5432,
});

await client.connect();
const res = await client.query('SELECT * FROM users');
console.log(res.rows);
await client.end();

---

七、备份与恢复

7.1 逻辑备份（SQL 脚本）

# 备份数据库
pg_dump -U postgres mydb > mydb_backup.sql

# 恢复
psql -U postgres mydb < mydb_backup.sql

7.2 物理备份（pg_basebackup）

pg_basebackup -h localhost -U postgres -D /backup/pgdata -P --wal

---

八、性能调优建议

项目	建议
索引	仅为频繁查询的列建索引，避免过多索引
VACUUM	定期执行 VACUUM ANALYZE; 清理死元组
配置	调整 shared_buffers, work_mem, effective_cache_size
分区	大表使用表分区（PARTITION BY）
连接池	使用 PgBouncer 管理连接

---

九、常用工具

工具	用途
pgAdmin	图形化管理工具
DBeaver	免费多数据库 GUI
PostGIS	地理信息系统扩展
TimescaleDB	时序数据扩展

---

十、学习资源

• 官方文档：https://www.postgresql.org/docs/
• 在线练习：https://www.postgresqltutorial.com
• 中文社区：知乎、掘金、PostgreSQL 中国用户会

---

小结：快速上手命令清单

-- 1. 进入 psql
psql -U postgres

-- 2. 查看数据库
\l

-- 3. 切换数据库
\c mydb

-- 4. 查看表
\dt

-- 5. 查看表结构
\d users

-- 6. 退出
\q

---

现在就开始吧！
创建一个数据库，建一张表，插入几条数据，然后用 Python 连上来查询 —— 你就掌握了 PostgreSQL 的核心！

---

需要我为你生成一个 完整项目示例（如：用户管理系统 + API）吗？回复 项目示例 即可！