可靠地暂存SQLite数据库文件 [英] Staging SQLite Database Files Reliably

问题描述

我有两个SQLite数据库文件:

I have two SQLite database files:

• data.db(生产)
• data.db.tmp(登台)

• data.db (Production)
• data.db.tmp (Staging)

两个数据库均处于 WAL日记模式.此外，临时数据库处于排他锁定模式(使用 PRAGMA locking_mode )读取器/写入器，而生产数据库处于共享/普通锁定模式且具有多个读取器而没有写入器.

Both databases are in WAL journaling mode. Additionally, the staging database in in exclusive locking mode (using PRAGMA locking_mode) with a single reader / writer, while the production database is in shared / normal locking mode and has several readers and no writers.

在任何给定时间点，文件结构可能如下所示:

At any given point in time, the file structure might look like this:

• data.db
• data.db-shm
• data.db-wal
• data.db.tmp
• data.db.tmp-wal

• data.db
• data.db-shm
• data.db-wal
• data.db.tmp
• data.db.tmp-wal

有时，我将要用登台数据库替换生产数据库-最好在不破坏现有[生产]阅读器的情况下，更重要的是，在不破坏数据库的情况下.

Ocasionally, I will want to replace the production database with the staging database - preferably, without disrupting existing [production] readers and, more crucially, without corrupting the database.

我最初的想法(天真的)是一个简单的mv data.db-tmp data.db，但是，由于存在多个相关文件，因此单个重命名将不能保证原子性或一致性.然后，我想到了做一个括号mv:

My initial (naive) idea was a simple mv data.db-tmp data.db but, because there are several related files, a single rename will not guarantee atomicity nor consistency. I then thought of doing a braced mv:

mv data.db{.tmp,.tmp-wal} data{.db,.db-wal}

我不知道上面是否是原子操作，但是鉴于*-shm和*-wal文件的瞬态性质，它不能正常工作:如果data.db.tmp-wal不存在，则移动将失败(我认为！)，并且可能存在的data.db-shm对应对象没有原子操作.

I don't know if the above is a atomic operation, but it wouldn't work as well given the transient nature of the *-shm and *-wal files: if data.db.tmp-wal doesn't exists the move would fail (I think!) and there is no atomic operation for the possibly existing data.db-shm counterpart.

根据info coreutils 'mv invocation':

在fileutils的版本"4.0"之前，"mv"只能常规移动 文件系统之间的文件.例如，现在"mv"可以移动整个 目录层次结构，包括来自一个分区的特殊设备文件 到另一个.它首先使用一些与cp -a相同的代码 复制请求的目录和文件，然后(假设复制 成功)删除原始文件.如果复制失败，则零件 复制到目标分区的文件将被删除.

Prior to version '4.0' of the fileutils, 'mv' could move only regular files between file systems. For example, now 'mv' can move an entire directory hierarchy including special device files from one partition to another. It first uses some of the same code that's used by 'cp -a' to copy the requested directories and files, then (assuming the copy succeeded) it removes the originals. If the copy fails, then the part that was copied to the destination partition is removed.

重命名整个文件夹也不是原子的.

Renaming whole folders is also not atomic.

如何使该暂存过程可靠?

What can I do to make this staging process reliable?

一些补充说明:

• 我的客户端API是PHP/PDO，因此我无法访问 SQLite在线备份C接口
• 数据库的大小为几个GB，因此某些内存中的解决方案可能不可行

推荐答案

您的命令扩展为:

mv data.db.tmp data.db.tmp-wal data.db data.db-wal

无论如何，多个文件系统操作不是原子操作.

Anyway, multiple file system operations are not atomic.

要同时删除-wal和-shm文件，请将日志模式更改为DELETE；您可以稍后再将其更改. 一旦拥有一个数据库文件，就可以执行原子重命名来替换它.

To get rid of both -wal and -shm files, change the journal mode to DELETE; you can change it back later. Once you have a single database file, you can execute an atomic rename to replace it.

请注意，WAL模式针对写入进行了优化；最好将(只读)生产数据库保留为回滚日志模式.

Please note that WAL mode optimizes for writing; it would be a better idea to leave the (read-only) production database in rollback journal mode.

这篇关于可靠地暂存SQLite数据库文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！