【Web全栈进阶】PostgreSQL上手：Docker跑库 + 把早报站从SQLite迁过去

今天不写新功能做一次“搬家”把早报站的数据从SQLite搬进PostgreSQL——这是整个二季的地基工程。本篇产出一个跑在Docker里的PostgreSQL、一份可重复执行的数据迁移脚本、以及“为什么换”的完整决策链。含代码约60行。 太长不看版给想快速上手的你项目信息一句话说明本篇目标SQLite → PostgreSQL 数据迁移代码行数~60行迁移脚本依赖psycopgPython驱动postgres:18-alpineDocker镜像核心功能Docker起库 数据搬家 幂等脚本跑起来的命令docker run ...→python migrate_sqlite_to_pg.py核心知识点DSN连接串、SQL方言差异、ON CONFLICT DO NOTHING做完你能得到一个生产级数据库 可重复执行的迁移脚本⚠️诚实声明范围今天完成“环境 数据”明天下一篇完成“代码”。一、先回答“为什么换”——SQLite的三个天花板第28篇选型时我说“SQLite够了”——当时是真的够单机、单写者、每天5篇文章。但产品要面对真实用户了三个天花板会依次撞上#天花板具体表现①并发写锁SQLite同一时刻只允许一个写入者两个用户同时操作就是database is locked②没有迁移体系改表结构靠删库重建第17篇⑥表越多这招越危险③类型与特性类型宽松、缺JSONB/数组/全文检索数据一复杂就捉襟见肘 决策框架技术选型不是永恒决定是“当时场景”的最优解。场景变了单机玩具 → 公网产品选型就该升级——推翻自己不是打脸是成长。PostgreSQL是这个场景下的生产标配并发读写、完整的类型系统、成熟的迁移生态。二、两分钟概念课从“文件库”到“服务”SQLite和PostgreSQL最本质的区别一张图说清SQLite程序直接读写的“文件” PostgreSQL一个独立运行的“服务” ┌─────────────┐ ┌──────────┐ ┌──────────┐ │ your.py │──读写──→ daily.db │ your.py │──→│ PG 服务 │──→ data/ └─────────────┘ └──────────┘ └──────────┘ (客户端) (端口 5432) SQLite是嵌在你程序里的一个文件PostgreSQL是独立运行的数据库服务你的程序通过网络连它。换成PG后多了两个概念概念说明连接串DSNpostgresql://用户名:密码主机:5432/库名——五段式解剖协议 / 用户 / 密码 / 主机:端口 / 数据库客户端连上服务的工具——命令行psql、图形化DBeaver、或你代码里的驱动psycopg三、第1步Docker起PostgreSQLDocker技能dockerrun-d--namepg-daily\-ePOSTGRES_PASSWORD你的密码\-ePOSTGRES_DBdaily\-vpg-daily-data:/var/lib/postgresql/data\-p5432:5432\postgres:18-alpine为什么用18-alpine而不是18alpine变体体积更小约60MB生产环境推荐固定小版本标签如18.6-alpine避免意外升级。学习阶段用18-alpine即可。 逐参数拆解参数作用POSTGRES_PASSWORD设超级用户密码POSTGRES_DBdaily启动时自动建一个叫daily的库-v pg-daily-data:/var/lib/postgresql/data数据卷——数据放卷里代码放镜像里容器删了数据还在-p 5432:5432把服务的默认端口映射出来✅ 验证服务活着dockerexec-itpg-daily psql-Upostgres-ddaily-c\dt能进入psql交互界面哪怕显示Did not find any tables服务就绪。四、第2步安装驱动 建表——SQL方言的第一课安装Python驱动pipinstallpsycopg pip freezerequirements.txt建表SQL的方言差异对照把早报站的建表语句翻译成PG版顺便认识方言差异写法SQLite旧PostgreSQL新自增主键INTEGER PRIMARY KEY AUTOINCREMENTINTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY占位符?sqlite3%spsycopg去重插入INSERT OR IGNOREINSERT … ON CONFLICT (url) DO NOTHING类型检查宽松类型亲和严格类型不对直接拒绝最后一条要单独强调SQLite的宽松是“惯着你”PG的严格是“保护你”——数字列塞文本当场报错数据质量问题在入库前就暴露而不是三个月后在报表里。五、第3步数据搬家脚本——可重复执行的迁移新建migrate_sqlite_to_pg.pymigrate_sqlite_to_pg.py —— 把早报站数据从SQLite搬进PostgreSQLimportsqlite3importpsycopg PG_DSNpostgresql://postgres:你的密码localhost:5432/dailySQLITE_FILEdaily.dbdefmigrate()-None:# ① 从SQLite读出全部旧数据srcsqlite3.connect(SQLITE_FILE)rowssrc.execute(SELECT url, title, date, summary FROM articles).fetchall()src.close()print(f从SQLite读出{len(rows)}条)# ② 建表幂等 逐条写入url冲突自动跳过withpsycopg.connect(PG_DSN)asconn:conn.execute( CREATE TABLE IF NOT EXISTS articles ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, url TEXT UNIQUE NOT NULL, title TEXT NOT NULL, date TEXT, summary TEXT DEFAULT ))withconn.cursor()ascur:forrowinrows:cur.execute(INSERT INTO articles (url, title, date, summary) VALUES (%s, %s, %s, %s) ON CONFLICT (url) DO NOTHING,row)conn.commit()# ③ 验证总数对得上吗nconn.execute(SELECT COUNT(*) FROM articles).fetchone()[0]print(f迁移完成PostgreSQL中共{n}条)if__name____main__:migrate() 三个细节#细节说明①%s占位符psycopg用%sSQLite是?方言差异见第4节②ON CONFLICT (url) DO NOTHING让脚本重复运行不出错幂等思想③密码特殊字符密码里的特殊字符如在DSN里要URL转义或改用环境变量关键字参数连接✅ 运行验证python migrate_sqlite_to_pg.py# 从SQLite读出812条# 迁移完成PostgreSQL中共812条六、第4步验收清单1.dockerps→ pg-daily容器Up状态2. psql查询SELECT COUNT(*)FROM articles → 与SQLite原数量一致3. 抽查3行中文内容无乱码4. 删掉容器重建数据卷还在→ 数据完整 → 卷持久化生效5. 脚本重跑 → 数量不变幂等6. 全程无报错后提交Gitgitadd.gitcommit-m数据迁移SQLite → PostgreSQL脚本可重复执行七、常见报错这6个迁移日的标配重点①psql: error: connection refused 原因容器没起来或-p 5432:5432忘了映射。✅ 解法dockerps# 看容器状态和端口映射dockerlogs pg-daily# 看启动日志②password authentication failed for user postgres 原因密码不对或DSN里密码含 : /等特殊字符没转义。✅ 解法先用纯字母数字密码跑通确需特殊字符用URL编码→%40。③relation articles does not exist 原因连接到了别的库或建表没执行。✅ 解法psql里——\dt# 看当前库的表\l# 列出所有库\c daily# 切库先确认“人在哪家银行”再谈余额。④ 占位符混用?写进PG的SQL直接报语法错 原因sqlite3用?psycopg用%s——方言不同。✅ 解法本篇对照表背下来后续SQLAlchemy会替你抹平差异下一篇的主角。⑤psycopg.errors.InvalidTextRepresentation 原因PG类型严格——往数字列塞了文本、或空字符串塞进了非文本列。✅ 解法对照建表语句检查数据类型。这正是换PG的价值坏数据当场拦截。⑥ 早报站还在读SQLite 原因不是bug——代码层的连接切换在下一篇SQLAlchemy 2.0完成本篇只搬数据。✅ 解法无。诚实声明范围今天完成“环境 数据”明天完成“代码”。八、 PostgreSQL 18值得关注的新特性特性说明异步I/OAIO存储读取性能最高提升3倍UUIDv7新增uuidv7()函数时间戳排序与唯一性兼得B-tree Skip Scan多列索引查询优化减少全表扫描并行GIN索引构建大表索引创建更快 这些特性对早报站当前规模影响不大但了解它们有助于你理解PostgreSQL的演进方向——性能优化和开发者体验是持续投入的重点。九、课后练习#练习难度提示1psql三连SELECT COUNT(*)、按来源分组统计、找出最新的3篇文章⭐⭐全部在psql里完成2泛化脚本把migrate脚本参数化python migrate.py 源.db 目标dsn⭐⭐⭐变成通用小工具3卷实验docker rm -f pg-daily后重建容器同名数据卷→ 数据完整⭐⭐亲手验证第27篇的“数据放卷里”4选做pgloader对比了解一键迁移工具pgloader对比手写脚本⭐⭐⭐知道工具才知道自己在简化什么 配套代码迁移脚本与建表SQL已上传Git01-pg-migration/【gitee仓库地址】