AkatQuas/sqlite-fast-migration-skill

SQLite Fast Migration Skill

Replace rsync-based direct .db copy — use SQLite's built-in .dump, gzip compression, and local rebuild to migrate GB-sized SQLite databases across machines, solving slow transfer speeds and file corruption.

When to Use

Migrating, backing up, or copying large SQLite databases (hundreds of MB to GB) from a remote server to your local machine. Especially useful when:

• The database has many indexes, making the .db file bloated with redundant data
• The source database is being written to during transfer (risk of corruption)
• You have limited bandwidth or a slow network connection
• rsync reports database disk image is malformed or takes too long

For small databases (~250 MB or less), direct rsync is fine — no need for this approach.

Problem

The traditional approach — rsync copying the entire .db binary file — has two major flaws:

1. Inefficient transfers — SQLite stores indexes to speed up queries, but indexes are just redundant copies of the data. A single index can account for 50% of the total disk space, wasting massive bandwidth during transfer.

2. File corruption — Large databases take a long time to copy. During transfer, the live source database may be updated, causing rsync to read a mix of old and new data. The result is a corrupted file with database disk image is malformed.

Solution

Stop transferring raw .db binaries. Instead, use a three-phase approach: SQL dump + compressed transfer + local rebuild.

Phase	What	Why
Deduplicate	sqlite3 .dump converts the binary DB to SQL text	Indexes become single CREATE INDEX statements — no redundant index data transferred
Compress	gzip the SQL text	SQL text is highly repetitive, achieving excellent compression ratios
Static snapshot	Transfer the fixed text file	The dump is a point-in-time snapshot, immune to concurrent writes on the source
Rebuild locally	sqlite3 executes the SQL script	Auto-creates tables, inserts data, and rebuilds all indexes — identical to the source

Measured Results (3.4 GB production database)

File	Disk Usage	Note
Original SQLite .db	3.4 GB	Includes massive index redundancy
SQLite dump (plain text)	1.3 GB	Business data only, indexes reduced to CREATE statements
gzip compressed dump	240 MB	14× compression vs original

Procedure

The standard 6-step pipeline:

# ── 1. Remote: dump and compress ──
ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"

# ── 2. Local: pull the compressed package ──
rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz

# ── 3. Clean up remote temp file ──
ssh username@server "rm my_remote_database.db.txt.gz"

# ── 4. Local: decompress ──
gunzip my_local_database.db.txt.gz

# ── 5. Local: rebuild database from SQL (auto creates tables, inserts data, builds indexes) ──
cat my_local_database.db.txt | sqlite3 my_local_database.db

# ── 6. Clean up local temp text file ──
rm my_local_database.db.txt

Quick one-liners

Dump + compress only (for later manual processing):

sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz

Rebuild from local SQL text only:

cat my_local_database.db.txt | sqlite3 my_reconstructed_database.db

What NOT to do (anti-pattern)

# Don't do this — slow and fragile for large files
rsync --progress username@server:my_remote_database.db my_local_database.db

Pitfalls

• Index redundancy is the main culprit — the dump approach reduces indexes to single-line CREATE INDEX statements, achieving up to 14× compression. Always dump first, don't copy the .db raw.
• Dump is a static snapshot — it captures the database at a single point in time, immune to concurrent writes. This eliminates the version-mixing corruption that plagues rsync.
• Locking under high concurrency — if the source database is under heavy write load, .dump may wait for a lock. Run during off-peak hours, or use .backup first and dump the backup.
• Local rebuild has a cost — rebuilding indexes locally consumes CPU and disk I/O, but this is negligible compared to the time saved on network transfer.
• Clean up temp files — always remove *.txt.gz and *.txt afterward to avoid cluttering server and local disk.
• Zero dependencies — only needs sqlite3 CLI and standard compression tools (gzip/gunzip). No plugins, services, or frameworks required.

Verification

After migration, verify:

1. Opening the local database produces no database disk image is malformed error
2. Query the local database — row counts, table structure, and indexes match the remote source
3. Overall migration time is significantly shorter than rsync with the raw .db file under the same network conditions
4. No *.txt.gz or *.txt temp files remain on either server or local machine

References

• A faster way to copy SQLite databases between computers — original technical inspiration
• SQLite CLI documentation (.dump command)

License

MIT