🐘 PostgreSQL 完整 Cheatsheet

📋 工具介绍

PostgreSQL 是一个功能强大的开源关系型数据库管理系统，以其稳定性、可扩展性和标准兼容性而闻名。对于现代 Web 开发者来说，PostgreSQL 通常通过 ORM 工具（如 Prisma）进行操作，本指南专为这类开发者设计。

核心功能：

ACID 事务支持
丰富的数据类型（JSON、数组、地理位置等）
强大的查询优化器
扩展性和自定义函数支持

⭐ 核心优势

🔒 数据完整性：严格的 ACID 事务保证数据一致性
🚀 高性能：优秀的查询优化和并发处理
🔧 功能丰富：支持 JSON、全文搜索、地理信息等
🌍 开源免费：无许可费用，社区活跃
📈 可扩展：支持水平和垂直扩展
🛡️ 安全可靠：企业级安全特性

🎯 适用场景

Web 应用：电商、社交、内容管理系统
数据分析：商业智能、报表系统
API 后端：RESTful API、GraphQL 服务
微服务架构：分布式系统的数据存储
原型开发：快速验证业务逻辑

🔧 前提条件

基本的数据库概念（表、字段、关系）
了解 SQL 基础语法（可选，主要用 Prisma）
Node.js 开发环境
对 ORM 工具有基本了解

⚡ 快速体验（5分钟上手）

本地安装方式

# macOS 使用 Homebrew
brew install postgresql
brew services start postgresql

# 创建数据库
createdb myapp_dev

# 连接数据库
psql -d myapp_dev

使用云数据库（推荐新手）

# 1. 注册云服务（推荐）
# - Supabase (免费额度丰富)
# - Railway (简单易用)
# - Neon (无服务器 PostgreSQL)

# 2. 获取连接字符串
# postgresql://username:password@host:port/database

# 3. 在项目中配置
echo "DATABASE_URL=postgresql://..." > .env

🛠️ 核心命令

🔐 连接和认证

# 连接本地数据库
psql -U postgres
psql -U username -d database_name

# 连接远程数据库
psql -h hostname -U username -d database_name

# 使用连接字符串
psql "postgresql://username:password@host:port/database"

# 退出 psql
\q

📊 数据库管理

# 列出所有数据库
\l

# 创建数据库
CREATE DATABASE myapp_production;

# 删除数据库
DROP DATABASE myapp_old;

# 切换数据库
\c database_name

# 查看当前数据库
SELECT current_database();

🗂️ 表和模式管理

# 列出所有表
\dt

# 查看表结构
\d table_name

# 查看表详细信息
\d+ table_name

# 列出所有模式
\dn

# 查看索引
\di

👤 用户和权限

# 列出所有用户
\du

# 创建用户
CREATE USER myapp_user WITH PASSWORD 'secure_password';

# 授予数据库权限
GRANT ALL PRIVILEGES ON DATABASE myapp_dev TO myapp_user;

# 授予表权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myapp_user;

⚙️ 配置文件

Prisma 配置示例

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

环境变量配置

# .env
# 本地开发
DATABASE_URL="postgresql://postgres:password@localhost:5432/myapp_dev"

# 生产环境（示例）
DATABASE_URL="postgresql://user:pass@host.com:5432/myapp_prod?sslmode=require"

# Supabase 示例
DATABASE_URL="postgresql://postgres:[password]@db.[project-ref].supabase.co:5432/postgres"

Docker Compose 配置

# docker-compose.yml
version: '3.8'
services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_USER: myapp
      POSTGRES_PASSWORD: password
      POSTGRES_DB: myapp_dev
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:

💡 实用技巧

🔄 开发最佳实践

# 使用 Prisma 进行数据库操作
npm install prisma @prisma/client
npx prisma init

# 生成和应用迁移
npx prisma migrate dev --name init
npx prisma generate

# 查看数据库内容
npx prisma studio

# 重置数据库
npx prisma migrate reset

🚀 性能优化

-- 创建索引提升查询性能
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_post_author ON posts(author_id);

-- 复合索引
CREATE INDEX idx_post_status_date ON posts(published, created_at);

-- 查看查询执行计划
EXPLAIN ANALYZE SELECT * FROM posts WHERE published = true;

🔍 调试和监控

# 查看活跃连接
SELECT * FROM pg_stat_activity;

# 查看数据库大小
SELECT pg_size_pretty(pg_database_size('myapp_dev'));

# 查看表大小
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

🚨 故障排除

常见问题

Q: 连接被拒绝 "connection refused"

# 检查 PostgreSQL 是否运行
brew services list | grep postgresql
# 或
sudo systemctl status postgresql

# 启动服务
brew services start postgresql
# 或
sudo systemctl start postgresql

Q: 权限不足 "permission denied"

-- 检查用户权限
\du

-- 授予必要权限
GRANT ALL PRIVILEGES ON DATABASE myapp_dev TO username;
GRANT USAGE ON SCHEMA public TO username;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;

Q: Prisma 迁移失败

# 检查数据库连接
npx prisma db pull

# 重置并重新迁移
npx prisma migrate reset
npx prisma migrate dev

# 手动同步模式
npx prisma db push

Q: 数据库性能慢

-- 查看慢查询
SELECT query, mean_time, calls 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

-- 分析表统计信息
ANALYZE;

-- 重建索引
REINDEX TABLE table_name;

数据备份和恢复

# 备份数据库
pg_dump -U username -h hostname database_name > backup.sql

# 恢复数据库
psql -U username -h hostname -d database_name < backup.sql

# 备份特定表
pg_dump -U username -t table_name database_name > table_backup.sql

📚 相关资源

官方资源

云服务推荐

Supabase - 开源 Firebase 替代品
Railway - 简单的云部署平台
Neon - 无服务器 PostgreSQL
PlanetScale - MySQL 兼容，但概念相似

学习资源

工具推荐

pgAdmin - PostgreSQL 管理工具
DBeaver - 通用数据库工具
Prisma Studio - 数据库可视化工具

💡 小贴士：对于新手开发者，建议先使用云数据库服务（如 Supabase）开始学习，避免本地环境配置的复杂性。当你熟悉了基本操作后，再考虑本地安装和高级配置！

PostgreSQL Cheatsheet