用 SQLite VACUUM INTO 在线备份正在写入的数据库
【摘要】 介绍在另一连接持续写入时用 VACUUM INTO 生成一致性快照的做法:说明直接 cp 数据库文件为何会得到损坏或过期的副本,给出 Python 最小脚本并用 PRAGMA integrity_check 验证备份可打开。
用 SQLite VACUUM INTO 在线备份正在写入的数据库
问题场景
服务正在运行,一个连接持续往 SQLite 数据库里写数据,同时需要产出一份可用于恢复或迁移的备份。很多人第一反应是 cp app.db backup.db,或者 tar 一下数据目录。这样的副本在“备份完成”的那一刻看起来没问题,真正拿去恢复时才可能暴露异常:integrity_check 报错,或者数据停在一个更早的时间点。
本文用一个最小 Python 脚本演示:在另一个连接不停写入的情况下,用 VACUUM INTO 得到一份一致性快照,并用 PRAGMA integrity_check 验证备份文件本身可打开、结构完整。
为什么不能直接 cp 数据库文件
- 页级拷贝不是原子操作。
cp是逐块读写的,大文件拷贝期间如果写事务提交,备份文件里会混入“前一半是旧页、后一半是新页”的组合,这种撕裂状态可能通不过完整性检查。 - WAL 模式下最新数据不在主文件里。启用
journal_mode=WAL后,已提交但尚未 checkpoint 的数据保存在app.db-wal中,只拷app.db会丢掉最近的提交;而单独拷-wal、-shm又无法保证三者版本互相匹配。 - 文件系统层面没有一致性视图。SQLite 的一致性由连接内的事务和锁来保证,
cp完全绕过了这一层,拿不到“某个提交点”的数据库状态。 - 空闲页会一起被复制。删除大量数据后,主文件里仍留着可复用的空闲页,文件体积不会立刻下降。
VACUUM INTO 做了什么
VACUUM INTO 'file' 是 SQLite 3.27.0 起提供的形式:在一个读事务里遍历整个数据库,把内容重写到一个新文件中。因为它全程处于同一个读事务,拿到的是一致性快照;输出文件是重新组织的,不含空闲页,体积通常小于源文件。WAL 模式下读事务不阻塞写事务,所以备份期间写入可以继续。
它和执行 VACUUM 的区别在于:VACUUM 原地重写主库,而 VACUUM INTO 只读源库、写新文件,源库不受影响。
最小可运行示例
import os
import random
import sqlite3
import threading
import time
SRC = "app.db"
BAK_DIR = "backup"
BAK = os.path.join(BAK_DIR, "app-snapshot.db")
def init_db():
conn = sqlite3.connect(SRC, isolation_level=None)
conn.execute("PRAGMA journal_mode=WAL")
conn.execute(
"CREATE TABLE IF NOT EXISTS events("
"id INTEGER PRIMARY KEY, payload TEXT NOT NULL, ts REAL NOT NULL)"
)
conn.close()
def writer(stop, counter):
"""模拟业务写入:持续提交小事务。"""
conn = sqlite3.connect(SRC, timeout=10, isolation_level=None)
conn.execute("PRAGMA busy_timeout=10000")
while not stop.is_set():
conn.execute(
"INSERT INTO events(payload, ts) VALUES(?, ?)",
(f"row-{counter[0]}-{random.random():.6f}", time.time()),
)
counter[0] += 1
time.sleep(0.005)
conn.close()
def main():
init_db()
os.makedirs(BAK_DIR, exist_ok=True)
if os.path.exists(BAK):
os.remove(BAK) # VACUUM INTO 要求目标文件不存在
stop = threading.Event()
counter = [0]
t = threading.Thread(target=writer, args=(stop, counter), daemon=True)
t.start()
time.sleep(0.5) # 先让写入线程产生一些数据
src = sqlite3.connect(SRC, timeout=10, isolation_level=None)
src.execute("PRAGMA busy_timeout=10000")
print("sqlite version:", sqlite3.sqlite_version)
t0 = time.monotonic()
# filename 在语法上是表达式,可以绑定参数
src.execute("VACUUM INTO ?", (BAK,))
print(f"VACUUM INTO 耗时 {time.monotonic() - t0:.3f}s")
written_before = counter[0]
stop.set()
t.join()
src.close()
# 只读打开备份并校验
bak = sqlite3.connect(f"file:{BAK}?mode=ro", uri=True)
print("integrity_check:", bak.execute("PRAGMA integrity_check").fetchone()[0])
print("foreign_key_check:", bak.execute("PRAGMA foreign_key_check").fetchall())
print("备份内 (行数, 最大 id):", bak.execute("SELECT count(*), max(id) FROM events").fetchone())
bak.close()
print("备份时源库已提交到 id:", written_before)
print("备份文件大小:", os.path.getsize(BAK), "bytes")
if __name__ == "__main__":
main()
若使用的驱动或 SQLite 版本不接受参数绑定,可改为字符串拼接,但必须先转义单引号,或改用固定的、不含特殊字符的路径。
运行与验证步骤
- 在空目录执行
python3 backup.py,脚本会自动建库、启动写线程、执行备份。 - 观察输出中的
sqlite version,确认不低于 3.27.0;若低于该版本,VACUUM INTO不被支持。 - 确认
integrity_check输出ok,foreign_key_check输出空列表。 - 用命令行复核:
sqlite3 backup/app-snapshot.db "PRAGMA integrity_check;"与sqlite3 backup/app-snapshot.db "SELECT count(*) FROM events;",结果应与脚本一致。 - 多次运行验证:备份文件每次都不同名或先删除旧文件;同时观察写入线程的 id 持续增长,说明备份期间写入没有被中断。
- 抽查业务语义:
integrity_check只代表页结构与索引一致,仍需比对表清单与关键行内容。
边界与限制
- 目标文件必须不存在。
VACUUM INTO不会覆盖已有文件,会直接报错,因此备份脚本要先清理或使用带时间戳的文件名。 - 需要额外磁盘空间。它会把整个数据库重写一遍,临时占用约等于源库大小的空间,并带来相应的 IO 与 CPU 开销,不适合高频调度,建议放在低峰期。
- 得到的是时间点快照。备份前后源库仍在写入,因此备份内的行数通常少于备份结束时源库的行数,不要用“行数相等”做断言。它也不等同于基于 WAL 归档的持续增量备份。
- 读锁的影响与模式有关。WAL 模式下读事务不阻塞写;但在回滚日志模式下,大库的备份可能长时间持有读锁并阻塞写入,务必设置
busy_timeout并评估窗口。 - 只处理当前连接的主数据库。通过
ATTACH挂载的其他数据库文件需要各自执行备份。 - 权限与目录。目标目录必须可写;备份完成后建议以只读方式打开校验,避免误写。
- 不替代真正的灾备。
VACUUM INTO适合做一致性副本、迁移前的导出、收缩体积;跨机器、跨版本的恢复策略仍需单独验证。
【版权声明】本文为华为云社区用户原创内容,未经允许不得转载,如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)