今天不寫新功能做一次“搬家”把早報站的數(shù)據(jù)從SQLite搬進(jìn)PostgreSQL——這是整個二季的地基工程。本篇產(chǎn)出一個跑在Docker里的PostgreSQL、一份可重復(fù)執(zhí)行的數(shù)據(jù)遷移腳本、以及“為什么換”的完整決策鏈。含代碼約60行。 太長不看版給想快速上手的你項(xiàng)目信息一句話說明本篇目標(biāo)SQLite → PostgreSQL 數(shù)據(jù)遷移代碼行數(shù)~60行遷移腳本依賴psycopgPython驅(qū)動postgres:18-alpineDocker鏡像核心功能Docker起庫 數(shù)據(jù)搬家 冪等腳本跑起來的命令docker run ...→python migrate_sqlite_to_pg.py核心知識點(diǎn)DSN連接串、SQL方言差異、ON CONFLICT DO NOTHING做完你能得到一個生產(chǎn)級數(shù)據(jù)庫 可重復(fù)執(zhí)行的遷移腳本??誠實(shí)聲明范圍今天完成“環(huán)境 數(shù)據(jù)”明天下一篇完成“代碼”。一、先回答“為什么換”——SQLite的三個天花板第28篇選型時我說“SQLite夠了”——當(dāng)時是真的夠單機(jī)、單寫者、每天5篇文章。但產(chǎn)品要面對真實(shí)用戶了三個天花板會依次撞上#天花板具體表現(xiàn)①并發(fā)寫鎖SQLite同一時刻只允許一個寫入者兩個用戶同時操作就是database is locked②沒有遷移體系改表結(jié)構(gòu)靠刪庫重建第17篇⑥表越多這招越危險③類型與特性類型寬松、缺JSONB/數(shù)組/全文檢索數(shù)據(jù)一復(fù)雜就捉襟見肘 決策框架技術(shù)選型不是永恒決定是“當(dāng)時場景”的最優(yōu)解。場景變了單機(jī)玩具 → 公網(wǎng)產(chǎn)品選型就該升級——推翻自己不是打臉是成長。PostgreSQL是這個場景下的生產(chǎn)標(biāo)配并發(fā)讀寫、完整的類型系統(tǒng)、成熟的遷移生態(tài)。二、兩分鐘概念課從“文件庫”到“服務(wù)”SQLite和PostgreSQL最本質(zhì)的區(qū)別一張圖說清SQLite程序直接讀寫的“文件” PostgreSQL一個獨(dú)立運(yùn)行的“服務(wù)” ┌─────────────┐ ┌──────────┐ ┌──────────┐ │ your.py │──讀寫──→ daily.db │ your.py │──→│ PG 服務(wù) │──→ data/ └─────────────┘ └──────────┘ └──────────┘ (客戶端) (端口 5432) SQLite是嵌在你程序里的一個文件PostgreSQL是獨(dú)立運(yùn)行的數(shù)據(jù)庫服務(wù)你的程序通過網(wǎng)絡(luò)連它。換成PG后多了兩個概念概念說明連接串DSNpostgresql://用戶名:密碼主機(jī):5432/庫名——五段式解剖協(xié)議 / 用戶 / 密碼 / 主機(jī):端口 / 數(shù)據(jù)庫客戶端連上服務(wù)的工具——命令行psql、圖形化DBeaver、或你代碼里的驅(qū)動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生產(chǎn)環(huán)境推薦固定小版本標(biāo)簽如18.6-alpine避免意外升級。學(xué)習(xí)階段用18-alpine即可。 逐參數(shù)拆解參數(shù)作用POSTGRES_PASSWORD設(shè)超級用戶密碼POSTGRES_DBdaily啟動時自動建一個叫daily的庫-v pg-daily-data:/var/lib/postgresql/data數(shù)據(jù)卷——數(shù)據(jù)放卷里代碼放鏡像里容器刪了數(shù)據(jù)還在-p 5432:5432把服務(wù)的默認(rèn)端口映射出來? 驗(yàn)證服務(wù)活著dockerexec-itpg-daily psql-Upostgres-ddaily-c\dt能進(jìn)入psql交互界面哪怕顯示Did not find any tables服務(wù)就緒。四、第2步安裝驅(qū)動 建表——SQL方言的第一課安裝Python驅(qū)動pipinstallpsycopg pip freezerequirements.txt建表SQL的方言差異對照把早報站的建表語句翻譯成PG版順便認(rèn)識方言差異寫法SQLite舊PostgreSQL新自增主鍵INTEGER PRIMARY KEY AUTOINCREMENTINTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY占位符?sqlite3%spsycopg去重插入INSERT OR IGNOREINSERT … ON CONFLICT (url) DO NOTHING類型檢查寬松類型親和嚴(yán)格類型不對直接拒絕最后一條要單獨(dú)強(qiáng)調(diào)SQLite的寬松是“慣著你”PG的嚴(yán)格是“保護(hù)你”——數(shù)字列塞文本當(dāng)場報錯數(shù)據(jù)質(zhì)量問題在入庫前就暴露而不是三個月后在報表里。五、第3步數(shù)據(jù)搬家腳本——可重復(fù)執(zhí)行的遷移新建migrate_sqlite_to_pg.pymigrate_sqlite_to_pg.py —— 把早報站數(shù)據(jù)從SQLite搬進(jìn)PostgreSQLimportsqlite3importpsycopg PG_DSNpostgresql://postgres:你的密碼localhost:5432/dailySQLITE_FILEdaily.dbdefmigrate()-None:# ① 從SQLite讀出全部舊數(shù)據(jù)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()# ③ 驗(yàn)證總數(shù)對得上嗎nconn.execute(SELECT COUNT(*) FROM articles).fetchone()[0]print(f遷移完成PostgreSQL中共{n}條)if__name____main__:migrate() 三個細(xì)節(jié)#細(xì)節(jié)說明①%s占位符psycopg用%sSQLite是?方言差異見第4節(jié)②ON CONFLICT (url) DO NOTHING讓腳本重復(fù)運(yùn)行不出錯冪等思想③密碼特殊字符密碼里的特殊字符如在DSN里要URL轉(zhuǎn)義或改用環(huán)境變量關(guān)鍵字參數(shù)連接? 運(yùn)行驗(yàn)證python migrate_sqlite_to_pg.py# 從SQLite讀出812條# 遷移完成PostgreSQL中共812條六、第4步驗(yàn)收清單1.dockerps→ pg-daily容器Up狀態(tài)2. psql查詢SELECT COUNT(*)FROM articles → 與SQLite原數(shù)量一致3. 抽查3行中文內(nèi)容無亂碼4. 刪掉容器重建數(shù)據(jù)卷還在→ 數(shù)據(jù)完整 → 卷持久化生效5. 腳本重跑 → 數(shù)量不變冪等6. 全程無報錯后提交Gitgitadd.gitcommit-m數(shù)據(jù)遷移SQLite → PostgreSQL腳本可重復(fù)執(zhí)行七、常見報錯這6個遷移日的標(biāo)配重點(diǎn)①psql: error: connection refused 原因容器沒起來或-p 5432:5432忘了映射。? 解法dockerps# 看容器狀態(tài)和端口映射dockerlogs pg-daily# 看啟動日志②password authentication failed for user postgres 原因密碼不對或DSN里密碼含 : /等特殊字符沒轉(zhuǎn)義。? 解法先用純字母數(shù)字密碼跑通確需特殊字符用URL編碼→%40。③relation articles does not exist 原因連接到了別的庫或建表沒執(zhí)行。? 解法psql里——\dt# 看當(dāng)前庫的表\l# 列出所有庫\c daily# 切庫先確認(rèn)“人在哪家銀行”再談余額。④ 占位符混用?寫進(jìn)PG的SQL直接報語法錯 原因sqlite3用?psycopg用%s——方言不同。? 解法本篇對照表背下來后續(xù)SQLAlchemy會替你抹平差異下一篇的主角。⑤psycopg.errors.InvalidTextRepresentation 原因PG類型嚴(yán)格——往數(shù)字列塞了文本、或空字符串塞進(jìn)了非文本列。? 解法對照建表語句檢查數(shù)據(jù)類型。這正是換PG的價值壞數(shù)據(jù)當(dāng)場攔截。⑥ 早報站還在讀SQLite 原因不是bug——代碼層的連接切換在下一篇SQLAlchemy 2.0完成本篇只搬數(shù)據(jù)。? 解法無。誠實(shí)聲明范圍今天完成“環(huán)境 數(shù)據(jù)”明天完成“代碼”。八、 PostgreSQL 18值得關(guān)注的新特性特性說明異步I/OAIO存儲讀取性能最高提升3倍UUIDv7新增uuidv7()函數(shù)時間戳排序與唯一性兼得B-tree Skip Scan多列索引查詢優(yōu)化減少全表掃描并行GIN索引構(gòu)建大表索引創(chuàng)建更快 這些特性對早報站當(dāng)前規(guī)模影響不大但了解它們有助于你理解PostgreSQL的演進(jìn)方向——性能優(yōu)化和開發(fā)者體驗(yàn)是持續(xù)投入的重點(diǎn)。九、課后練習(xí)#練習(xí)難度提示1psql三連SELECT COUNT(*)、按來源分組統(tǒng)計(jì)、找出最新的3篇文章??全部在psql里完成2泛化腳本把migrate腳本參數(shù)化python migrate.py 源.db 目標(biāo)dsn???變成通用小工具3卷實(shí)驗(yàn)docker rm -f pg-daily后重建容器同名數(shù)據(jù)卷→ 數(shù)據(jù)完整??親手驗(yàn)證第27篇的“數(shù)據(jù)放卷里”4選做pgloader對比了解一鍵遷移工具pgloader對比手寫腳本???知道工具才知道自己在簡化什么 配套代碼遷移腳本與建表SQL已上傳Git01-pg-migration/【gitee倉庫地址】