数据库从 SQLite 迁移到 PostgreSQL：完整踩坑记录与迁移脚本

数据库从 SQLite 迁移到 PostgreSQL：完整踩坑记录与迁移脚本

"在我电脑上能跑"的终结篇。

SQLite 陪我走过了无数课程设计和毕设 demo，但真要把项目上线、给同学一起用时，它就不够用了。这篇把我从 SQLite 迁到 PostgreSQL 的全过程、踩过的坑、以及能直接抄的脚本都记下来，照着做能少走很多弯路。

一、什么时候该从 SQLite 迁走

不是所有项目都要换数据库。下面三种情况出现任意一种，我就建议迁：

如果只是本地小工具、单人用的桌面软件，SQLite 完全够，别为了"显得专业"去迁。

二、两者差异速查表

三、迁移四步走

1. 导出 schema：sqlite3 app.db .schema > schema.sql
2. 手工改类型和语法：把 SQLite 的类型和函数改成 PG 的（详见后两节）
3. 迁数据：用脚本读 SQLite、写 PG
4. 改连接串：应用从 sqlite:// 切到 postgresql://

四、类型映射表

建表时的关键改动：

-- SQLite 原写法
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  created_at DATETIME
);

-- PostgreSQL 改法
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,          -- AUTOINCREMENT 换成 SERIAL
  name TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

五、语法差异清单

坑 1：双引号当字符串。SQLite 里 "abc" 有时能当字符串，但 PG 里双引号是标识符（表名/列名）。字符串一律用单引号 'abc'。

坑 2：日期函数不同。SQLite 用 datetime('now')，PG 用 now() 或 CURRENT_TIMESTAMP。

坑 3：LIMIT 语法。两者都支持 LIMIT 10 OFFSET 20，但 SQLite 的 LIMIT 10, 20 写法在 PG 里不支持，要改成 LIMIT 10 OFFSET 20。

坑 4：布尔值。SQLite 没有真布尔，用 0/1 整数。PG 有 BOOLEAN 类型，迁移时把 1/0 改成 true/false。

六、用 Python 脚本批量迁数据

我用 sqlite3 读源库，psycopg 写目标库，分批提交避免一个大事务把内存吃爆：

import sqlite3
import psycopg

# 源库（SQLite）与目标库（PostgreSQL）连接
src = sqlite3.connect("app.db")
dst = psycopg.connect("postgresql://user:pass@localhost:5432/mydb")

# 表名按外键依赖顺序排，先父后子
tables = ["users", "orders", "order_items"]

src_cur = src.cursor()
dst_cur = dst.cursor()

BATCH = 500  # 每批提交的行数，断点可续

for table in tables:
    src_cur.execute(f"SELECT * FROM {table}")
    columns = [d[0] for d in src_cur.description]
    rows = src_cur.fetchmany(BATCH)
    count = 0
    while rows:
        # PG 占位符是 %s，不是 SQLite 的 ?
        placeholders = ", ".join(["%s"] * len(columns))
        col_list = ", ".join(columns)
        sql = f"INSERT INTO {table} ({col_list}) VALUES ({placeholders})"
        dst_cur.executemany(sql, rows)
        dst.commit()                 # 分批提交，避免单事务过大
        count += len(rows)
        print(f"{table}: 已迁移 {count} 行")
        rows = src_cur.fetchmany(BATCH)

src.close()
dst.close()

七、ORM 用户怎么改

SQLAlchemy：只改连接串和方言即可，模型几乎不动。

# 改前（SQLite）
# SQLALCHEMY_DATABASE_URI = "sqlite:///app.db"
# 改后（PostgreSQL）
SQLALCHEMY_DATABASE_URI = "postgresql+psycopg://user:pass@localhost:5432/mydb"

Django：改 settings.py 的 DATABASES：

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "mydb",
        "USER": "user",
        "PASSWORD": "pass",
        "HOST": "localhost",
        "PORT": "5432",
    }
}

注意 Django 的 AutoField 在 PG 上自动用 SERIAL，迁移后跑 python manage.py migrate 即可。

八、高频坑逐个拆

坑 1：序列不同步。迁完数据后发现 INSERT 报主键冲突，因为 SERIAL 背后的序列还停在 1。手动对齐：

-- 把序列当前值设为表里最大 id + 1
SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  COALESCE((SELECT MAX(id) FROM users), 0) + 1,
  false
);

坑 2：大小写表名带引号。PG 默认把未加引号的标识符转成小写。如果建表时用了 "Users"，查询必须一直带引号，否则找不到。建议表名、列名全小写，别用引号。

坑 3：空字符串与 NULL。SQLite 里 '' 和 NULL 有时混用，PG 区分严格。检查 NOT NULL 列有没有存了空串。

坑 4：时区。SQLite 存的是本地字符串，PG 的 TIMESTAMPTZ 会按会话时区转换。统一用 UTC 存，查询时再转换。

坑 5：迁移后变慢。不是 PG 慢，是忘了建索引。把原库 CREATE INDEX 语句也迁过去。

坑 6：连接数爆了。应用没用连接池，每次请求开一个新连接，PG 默认 max_connections 只有 100。用 psycopg 的连接池或 SQLAlchemy 的 pool_size。

九、迁移前后验证清单

• schema 全部建表成功，无报错
• 行数核对：SELECT count(*) FROM 每张表 两边一致
• 序列 setval 已对齐
• 索引、外键约束都已重建
• 关键查询跑一遍，结果和旧库抽样一致
• 应用连接串切换后冒烟测试通过

上线前我一定先在本地用一份最新备份完整跑一遍迁移脚本，确认行数对得上、关键接口能通，再切生产连接串——别直接拿生产库练手。